Look Who it Was Random video

Excel - Excel Tutorial: How to Total Visible Rows in Filtered Data - Easy Trick! - Episode 667

469 views · Published 20 February 2009 · 2:48 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Total Visible Rows in Filtered Data - Easy Trick!

Welcome back to the MrExcel netcast where we answer your Excel questions and provide helpful tips and tricks. I'm Bill Jelen and today we have a question sent in by Mustafa from Washington. Mustafa had a great question about totaling visible rows in Excel. In this video, I'll be showing you a simple trick to get a running total of just the filtered rows in your data set.

To demonstrate this, I have a regular data set that I will be using. Mustafa's frustration was that when he applied a filter to his data, the totals he had previously added disappeared. This is a common issue that many Excel users face. But fear not, I have a solution for you.

First, we need to turn off the filter by going to "Data" and selecting "Filter". Then, we need to turn on the filter again by going to "Data" and selecting "Filter" once more. Now, here's the trick - you need to apply a filter to at least one column before adding the totals. This is crucial for the trick to work. So, I'll choose a customer and then go to the last visible row beneath our data and apply the "Autosum" button.

Normally, the autosum button gives us a SUM formula, but in this case, it will give us a special formula called SUBTOTAL. This formula, with the number 9, will give us a total of the filtered rows. The beauty of this trick is that the totals will appear below the data, just like we want them to. And the best part is, if we change the filter, the totals will automatically update to reflect the new filtered rows.

However, there is one issue with this trick - if we choose a customer with a large number of records, the totals may get lost. To avoid this, I like to insert a couple of new rows at the top of the worksheet and label them as "Total". Then, I cut and paste the live totals from the bottom to the top. This way, the totals will always be visible at the top of the worksheet, no matter how many records are filtered.

I want to thank Mustafa for sending in this question and I hope this trick helps you in your Excel journey. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. Thanks for watching and we'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Mustafa asks a question of how to see the totals from only the visible rows in a filtered data set. There is an easy way to do this, but it is not completely obvious. Episode 667 shows you how.

Table of Contents
(00:00) Keeping Sum at bottom of filtered data
(00:42) How to total visible cells after applying a filter
(00:55) Apply Filter first
(01:14) Use AutoSum below the filtered data set to get SUBTOTAL formula
(01:42) Move totals to the top
(02:14) 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 

This video answers these common search terms:
Apply filter to one column
Autofilter
Autosum button
Autosum formula
Data filtering
Excel autosum trick
Insert new rows
Public transit data
Running total
Subtotal formula
Turn off Autofilter

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152244/

More from this channel