Excel filter not working after certain row
on channel: AuditExcel Advanced Excel and Financial Model Training and Consulting
You have switched on your data filter and tried to use it but you find that the Excel filter is not working after a certain row. The main cause will be how you set it up and applies to any Excel tool which ‘guesses’ what you want to include.
Filter’s source data- guess or specific
Because of how intuitive Excel is, users often let Excel ‘guess’ what should be included in a filter. In the below spreadsheet we can click on cell A2 and click on the DATA ribbon and click Filter. Excel knows enough to realise you want to include columns A to F and it looks like your database is covered.
------------------------------------------------------------------------------------------------------------------------------------------------------
✅- Free check of your Excel Skill Level- https://excel-skills-assessment.com/s...
🏅- Test employee Excel skill levels- https://excel-skills-assessment.com/e...
🏆- Improve you Excel Skill Levels- https://online-excel-training.auditex...
🇿🇦- Our main website- https://www.auditexcel.co.za/blogs/
------------------------------------------------------------------------------------------------------------------------------------------------------
⏲Time Stamps
00:00 Filter missing rows
00:30 Reason it is missing- your filter set up
01:08 Correct way to set up filter
01:24 Quick way to correct where the filter was looking
------------------------------------------------------------------------------------------------------------------------------------------------------
For images see https://www.auditexcel.co.za/blog/exc...
You have switched on your data filter and tried to use it but you find that the Excel filter is not working after a certain row. The main cause will be how you set it up and applies to any Excel tool which ‘guesses’ what you want to include.
Filter’s source data- guess or specific
Because of how intuitive Excel is, users often let Excel ‘guess’ what should be included in a filter. In the below spreadsheet we can click on cell A2 and click on the DATA ribbon and click Filter. Excel knows enough to realise you want to include columns A to F and it looks like your database is covered.
But when you run a filter you will see that it does not seem to include what you expected. So below in column B we filtered on 31 Aug. You can see it finds two, but note that below it there is other data and other 31 Aug’s. The clue of the problem is in the red box. Note that the row numbers have turned blue. This means that those rows are part of the filter. All the other row numbers are black and means they are not part of the filter. So in this case, after a certain row, the filter does not include them.
Excel automatically only includes rows up to the first blank
Unless you specifically tell Excel what to look at (highlight the relevant cells), Excel will only include rows up to the first blank it finds. So in the above image you will note that row 18, where the number 18 is coloured black, is the first row not in the filter as it is a blank row.
Always specify what Excel must look at
It is best practice to always specify exactly what Excel should look at, even if you think there are no blanks. It is easy for a blank to be in row 10 000 so you wouldn’t notice it, but Excel will stop including the rows below in the filter.
As shown below, make it a habit of highlighting all the rows you want to include (for a quick way to do this look at the navigating Excel cells pages) and then switch the Filter on.
Easy way to switch filter off and back onto the correct rows
The easiest way to correct this mistake is, even if the filter is already on,
highlight all the cells you want to include,
click the filter button (it will switch it off), then
immediately click it again to switch it on.
This same issue occurs with Pivot Tables. Don’t allow Excel to guess. Specify the cells to include in any Excel tool, then click the relevant button.
Watch video Excel filter not working after certain row online without registration, duration hours minute second in high quality. This video was added by user AuditExcel Advanced Excel and Financial Model Training and Consulting 20 January 2021, don't forget to share it with your friends and acquaintances, it has been viewed on our site 91,048 once and liked it 328 people.