Look Who it Was Random video

Excel - Filter When Headings Are Not in Row 1 - Episode 1310

961 views · Published 21 December 2010 · 2:52 · Indexed 6 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Bob writes with a question about using AutoFilters when the headings are not in row 1. With Episode #1310 Bill looks at a solution, as well as a method for totaling the visible cells.

Welcome back to the MrExcel podcast. In this episode, we will be discussing filter dropdowns and how to use them effectively in your Excel spreadsheets. This topic was suggested by Bob, who sent me a spreadsheet with filter dropdowns that he was having trouble with. Let's dive in and see how we can solve this issue.

In Bob's spreadsheet, he had filter dropdowns set up to choose a specific product and see the totals in row 2. However, the real headings for the data set were in row 3, and the subtotal function was being used to only show visible cells. This was causing confusion and making it difficult to get accurate totals. So, what's the solution?

The answer is actually quite simple. All you need to do is insert a blank row between row 2 and row 3, and make it very small so it's not noticeable. Then, turn on the filter dropdowns and you will see that they are now in row 4, where the real headings are. This allows you to filter by product and get accurate totals for that specific product line.

If you're wondering how to set this up in the first place, here's a great trick. Filter the data set first, and then use the AutoSum button to get the SUBTOTAL function. Copy and paste this function to create an ad hoc subtotal at the top. Just be aware that when you paste it, the formulas will become invalid, but it's a quick and easy way to get the subtotal beforehand.

So, there you have it. The solution to using filter dropdowns effectively is simply inserting a blank row to keep the real headings away from the calculations above the table. This allows the filter dropdowns to be in row 4 instead of row 1, making it easier to get accurate totals. Thank you for watching this episode of the MrExcel podcast. Don't forget to subscribe and tune in next time for more helpful tips and tricks.

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/


Table of Contents:
(00:00) Filter Dropdowns 
(00:10) Explanation of the Problem 
(00:20) Solution: Inserting a Blank Row 
(00:30) Explanation of Subtotal Function 
(00:46) Using AutoFilter to Show Visible Cells 
(01:07) Simple Solution: Inserting a Blank Row 
(01:39) Bonus Tip: Using AutoSum with Filtered Data 
(02:09) Clicking Like really helps the algorithm

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

More from this channel