Excel - How To Sum Only Visible Cells When Filtering In Excel - Episode 1976

Microsoft Excel Tutorial - Technique to total the visible rows after applying a filter.

Welcome to the MrExcel podcast, sponsored by "MrExcel XL" - the ultimate guide to Excel with over 40 tips, cartoons, cocktails, tweets, and jokes. In this episode, we will be discussing a handy trick to total the visible cells in Excel.

As most of us know, the AutoSum button is the go-to formula for quickly totaling a column of numbers. But did you know that there is a way to total only the visible cells when using filters? That's right, by using the Filter by Selection trick, you can get the total of only the visible cells.

But here's where it gets even better. If you insert two rows above your data and then use the AutoSum button, you can easily cut and paste the total to the top of your data. This means that even if you have more data than can fit on your screen, the total will always be visible at the top. This trick was shared with me by a financial analyst at a live power Excel seminar, and it has been a game-changer ever since.

But what about manually hiding rows? Don't worry, we've got you covered. By using the CTRL+' (apostrophe) shortcut, you can copy the formula down without changing the cell references. And by changing the 9 in the formula to 109, you can get the true total of the visible cells, even when manually hiding rows. This is a great trick to have in your Excel arsenal.

This is just one of the many tips and tricks covered in "MrExcel XL". So if you want to become an Excel pro, be sure to check out the book in print or e-book format. And don't forget to click the "i" in the top-right corner for more information.

Thank you for tuning in to this episode of the MrExcel podcast. We hope you found this tip helpful and we'll see you next time for another netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. Happy Excel-ing!

Table of Contents:
(00:00) Total the Visible Cells in Excel
(00:17) AutoSum is the first formula most people create
(00:30) AutoSum for multiple cells
(01:07) AutoSum below a Filtered Data Set
(01:49) Cut to two blank rows at top
(02:26) Manually hiding rows and using AutoSum
(03:13) Using CTRL+' to change formula to SUBTOTAL(109
(03:57) Recap of concepts in video
(04:12) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions #excelfilter #exceldatafiltering #filter #excelsubtotal

This video answers these common search terms:
how to sum only visible cells when filtering in excel
how to total visible cells in excel
how to total only visible cells in excel
how to sum visible cells only in excel?
how to sum only visible cells in excel
how do you sum only visible cells in excel
how to only sum visible cells in excel
how to take a sum of visible sells on excel
how to sum rows in excel excluding hidden rows
how to sum with hidden cells in excel
how to exclude hidden rows in sum excel

Create ad-hoc totals for an Excel data set where you will be filtering the data. Also in this video: Using SUBTOTAL(109,) for summing data that has been manual hidden rows. Using Ctrl+Ditto to copy the exact formula down one row.

[excelisfun]

Thanks for the bonus 2b tips: 9 and 109!!!

[drsteele]

CTRL'   is fantastic! I frequently wish to copy a giant formula as-is to a new cell to conduct experiments. The procedure is to put the cell into edit mode, copy the contents, exit edit mode, go to a new cell and paste it. Now it's simply navigate to a cell below and press CTRL'  ---  awesome, Bill!

[WHS]

I use the aggregate function to sum and ignore hidden rows... Works fine for me!

[mohamedchakroun]

Very Cool subtotal with 109

Please a link to Excel Files

[TonyP]

So..109 is a secret code? Where do I get these numbers from?