Bottom line: In this FREE video series you will learn how to use the Filters in Excel. I share time saving tips, techniques, and shortcuts that will help make you a filtering ninja!
Skill level: Beginner
Video #1: Introduction to Excel Filters
In this first video I explain how to apply filters to your data. You will learn the advantages of using Excel Tables for filtering data. I also explain some navigation shortcuts that will help you find filtered columns.
Double-click video to view full screen HD.
Downloads
Download the file I used in the video to follow along.
We Spend A LOT of Time Filtering Data!
Filters are an extremely powerful feature of Excel that help us cleanse and analyze our data. They help us tie out numbers to summary reports and pivot tables, cleanup formulas, fix errors, and prepare our data for analysis.
It would be hard to live without filters… 🙂
We also spend a ton of time working with filters. Some of these filtering tasks can be very time consuming, and lead to frustration. Throughout this video series I am going to share tips, techniques, and shortcuts that will help save you time when working with filters.
In the video above I show the advantages of using Excel Tables for filtering data. Checkout my video on a Beginners Guide to Excel Tables to learn more about this awesome feature of Excel.
Filter Mate is Coming!
Filter Mate is an Excel Add-in that will save you a ton of time when working with filters in Excel. At the end of the video I shared a feature called Filter Hop. Filter Hop allows you to navigate to the filtered columns in a range or table with a click of a button.
This eliminates the time consuming horizontal scrolling that we often do to find columns with filters applied. The Filter Mate Add-in will be available in a few weeks. You can signup here to get notified when Filter Mate is available.
What's Your Favorite Filtering Tip?
I shared quite a few tips for working with filters in this first video. I'm curious to know if you have a favorite tip or shortcut that you use.
Please leave a comment below with your favorite tip. You can also leave a question with a tip or technique that you would like to learn.
I'll be back next week with video #2 of the series. Thank you!
Hi Jon,
I love the filter by color tool. I often need to find duplicate values in a column so I set up the conditional formatting to highlight the duplicate values and then filter on the cell or font color. Voila!…now I see all of the duplicates in my table.
Hi Jen,
That is an awesome tip! And a great way to review and clean duplicates.
For anyone that has not used this, the sort and filter by color features were introduced in Excel 2007. You can sort or filter by font or fill color. Here is a screenshot of where you can find them on the filter drop-down menu.
All of the font and fill colors used in the column will be listed in the sort and filter by color sub menus.
Thanks again Jen! 🙂
The timing for this was great, but I have a question
How do I code a VBA script that adjust one filter based on other filter selections each time the user changes selections.
Hi Larry,
You can use worksheet events in VBA to run a macro when a user selects a cell. This is the Worksheet_SelectionChange event in VBA, and you can add this macro to the worksheet’s code module.
Unfortunately, there is no event that will fire a macro when the user applies a filter. Since the user can apply a filter without selecting a cell, the action can go unnoticed by VBA. We are currently trying to find some workarounds for this because it would also be nice to have this functionality with the Filter Mate Add-in.
I will write an article in the future on worksheet events, and also share any workarounds we find for the filtering event issue. Let me know if you have any questions. Thanks!
Hi Jon! Can filters also be applied to lookup tables, as I know cell location is vital to the lookup functioning accurately.
Thanks,
Sandy
Hi Sandy,
Great question! Yes, filters can be applied to lookup tables. When a filter is applied, the location of the values in the cells do not change. The filters hide and unhide rows, but the values do not change.
Lookup formulas like vlookup and index/match reference a lookup_array or range. This lookup range does NOT change when filters are applied. The lookup formula will still find and return a value, even if the cell that contains the return value is hidden by a filter.
I hope that helps. Please let me know if you have any questions. Thanks!
Clear Filters,
After a applying, for example, three filters in a table in can take at least six clicks to clear all the filters and start a new query. I like to add the “Clear Filter” button from the Data Ribbon to the Quick Access Toolbar. I also like to always show the QAT below the ribbon. Then when I’ve applied several filters I can clear them all with one click. This clear all filters button also works in Pivot Tables.
Great tip Frank! I add the Clear Filter button to the QAT as well. As I mentioned in the video, the Clear Filter button on the Data tab will be enabled and display in color when filters are applied. When there are no filters applied the button becomes disabled and grayed out. This same behavior happens when you add the button to the QAT. This means you can quickly see if filters are applied WITHOUT having to navigate to the Data tab of the ribbon.
This reminded me that I recorded a video on this tip, but forgot to publish it. I will find it and post a link later for anyone else that wants to learn more. Thanks again Frank! 🙂
The total row is a great little feature. Using it not just for sums but counts, etc. I love showing it to people who haven’t used filtering but build things using sum ifs.
Great video! Thanks.
Hi Jon
ALT-Page down, I never knew that shortcut, thanks mate.
My favourite keyboard function with filters is to type the part of the name required in the filter seacrch box, saves me heaps of time when looking for data with similar words in very large data sets
I look forward to the next video in he series
Peter
Hi Peter,
I think you are going to enjoy the next video on keyboard shortcuts for the filter drop-down menus. I explain more about Alt+Down arrow and it’s friend Shift+Alt+Down Arrow. I will also explain how to jump to that search box with a shortcut key (e). Thank you!
It is an useful tutorial and I particular like the keyboard shortcut of Control page up/down to go to left or right of the page
I don’t hear about it much seems like, maybe for good reasons, but Advanced Filter seems pretty amazing to me. Maybe the same things can be done in easier ways for all I know.
Hey Rich,
I agree that the Advanced Filter is a pretty amazing tool. It’s just not the easiest thing to use and requires quite a bit of setup work. Therefore, most people use the Filters feature and filter drop-down menus instead. I will create some videos on the Advanced Fitler in the future. Thanks for the suggestion!
Hi
Your session was excelent
Very helpful at work
I would love to know key board shortcuts which will reduce our time at work while handling big data
Thank you Praveen! The next video is packed with keyboard shortcuts for the filter drop-down menus. Learning these shortcuts will save you a lot of time.
I also have an article on the best keyboard for Excel keyboard shortcuts that you might enjoy.
Hi Jon,
Good video, I haven’t used filters very much yet.
One thing I would like to know if it can be done, when I’m in a cell, say D15, and I have a sum in that cell like this “=34+24.99” and I want to add another number, how can I go to the formula bar by using a keyboard command instead of using the mouse and clicking there??
Thanks,
Mike
Hi Mike,
Great question! The keyboard shortcut to edit a formula is F2. Press the F2 function key to start editing the formula or value in any cell.
If you are using a laptop then you might have to press the Fn (function) key with F2. Some laptops split these keys with a media button. Some laptops also have a function lock option so you can make the function keys the primary keys, and not have to press Fn in combination with the function key. This is my preferred setup when I’m using a laptop. I use the F2 through F5 keys frequently in Excel.
I also have an article on the best keyboards for Excel keyboard shortcuts that explains more about this. I hope that helps. Thanks!
Ctrl + Alt + L for a filter refresh is a cool one.
I have created VBA codes to filter by selected value (avoiding clicking on a filter menu) as well as Clear just a specific column filter. What I am curious if it is possible to code a VBA that would exclude a selected value from a filter while not changing the existing exclusions?
Great tip Ivars! That is the shortcut key that will press the Reapply Filters button. I will explain more about that button in the 3rd video.
For anyone wanting to learn more keyboard shortcuts, you can hover over most buttons in the ribbon and see the keyboard shortcut in the screentip. Here is a screenshot of how to find the Ctrl+Alt+L shortcut to reapply filters.
Video #2 will be packed with keyboard shortcuts, and it will be out next week. Thanks again!
Hi..
Its too helpful.
I want to take Complete training, please give contact details.
Thanks,
Tabish.
Thank you Tabish! I will be launching a complete training course on Filters with Filter Mate. The course will be called Filters 101. I will provide more details in the coming weeks.
Thanks Jon,
I have overlooked that number filter in the past and it’s ability to narrow down on a range.
I love your videos and training materials.
Thank you so much for your support Lauren!
Hi Jon,
Love the shortcut tip. And I love the tip of Jen 🙂
Hi, tried your shortcuts but used the wrong key 🙂 Ctrl page down and you will jump to an other tab.
Hi Akke,
Yes, Alt+Page Up/ Down is not a common one. Ctrl+Page Up/Down is another handy shortcut that I use all the time.
Great Video! I purchased a tool called Add-Ins which offers many shortcuts for excel. The problem is I do not know how to use them. it is nice seeing how add-in tools can be utilized.
Thank you Lee! Yes, we can create add-ins with VBA (which is built into Excel) to add and enhance the features of Excel. They can really help improve productivity for common and specialized tasks.
Good work. The Insert Table function looks useful in a filtering context. Thanks for that Jon.
Thanks Neil! Tables are awesome! 🙂
My favorite from the video is using tables – which means new rows and columns filter too. My favorite all time was finding the various text filters. I like the Total feature too but wish it would total all columns not just the last one.
Thanks Julie! I do have another video and article on how to total all columns in the Table. There is no direct way to apply totals to every column, but I share a few tips to make the process faster in that video. I hope that helps.
Thanks, Jon!
good job. i love it.
Thanks Nurlan! 🙂
Enjoyed this video immensely and learned several tips I did not know. I look forward to more intermediate and advanced videos.
Thank you Marcia! I’m happy to hear you learned something new. Awesome! 🙂
Jon,
I have several tables that are created by MSQuery of our ERP data. Many times I need to add a column to the left of the query to concatenate 2 fields to create a unique key that I can do a vlookup against. If I place the formula within the query or to the right of the query it automatically become part of the table but if I place it to the left it doesn’t become part of the table.
Any suggestions?
Trish
Hey Trish,
Great question! You are right that the table does not automatically expand to the left. However, there are two ways to expand it.
1. You can insert a column to the left by right-clicking any cell in the first column of the table, then choose Insert > Table Columns to the left. This will insert a new column to the left and move the table to the right. So if your table starts in column B, it will still start in column B and all columns of the table will be shifted to the right. You would want to do this step before you paste or insert data to the left of the table.
2. You can resize the table to include the new column. Select any cell in the table. On the Design tab of the ribbon click the Resize Table button on the left side. Then edit the range reference to include the new first column. If the table reference is currently =$B$5:$I$29, then change it to =$A$5:$I$29. This will NOT shift the table to the right. Instead it will just add a new table column to the left of the table.
I hope that helps. Thanks!
I typically have thousands of row data in my tables. Something I use in the filter feature’s drop down menu is the search box to automatically pare down the choices.
Sometimes if my list of choices is long and I don’t want to scroll through the list and uncheck or check boxes, I type in my value being searched and then check “add current selection to filter” to what I’ve already previously chosen. In this way, I don’t have to wade through the whole list unchecking or checking boxes here and there, and I stand a better chance of not missing anything as my eyes can sometimes do.
I have also employed wildcard characters within the search box with success.
Thanks for your training courses. I always learn something valuable.
Hey Trish,
Great tip on the “add current selection to filter” feature. I think that’s one that is not very well known, and I will include it in a future video.
Thanks again!
Hi Jon,
I have a table that has quite a few columns that pull data from other tables using index match. When filtering on these columns I usually get a few “stray” values at the bottom of the table. I.e. If filtering on ‘Fred’ I might get 50 Freds, a John and a Janet.
Why is that and what if anything can i do to prevent it?
I use Excel 2010.
Hi Brent,
That is probably because the new rows are not included in the filtered range. If you are using Excel Tables, then the new rows will automatically be included in the Table AND the filtered range.
However, if you are not using Excel Tables then you will have to turn the filters OFF and back ON to include the new rows. The filtered range does not always automatically include new rows or columns.
Excel attempts to find the used range of cells when you turn the filters on, and have a single cell in the range selected. If you think that it is not included some rows, or you have blank rows in your data set, then it is best to first select the entire range of data and then turn the filters on.
I will create another video on this behavior in the future. To be safe, I recommend using Excel Tables if you can. As long as the new rows are included in the table you won’t have to worry about this.
I hope that helps.
Hi Jon,
Thank-you for responding, however the tables I am using are Excel tables as are the two tables I pull from and I don’t lose rows I gain them I.e. If I filter for Fred I get all the Freds (have checked to make sure of this) and some John’s and Janet (usually at or near the bottom). Despite that I have tried turning the filters on and off and even resizing the tables makes no difference. If I sort first, then filter this sometime works – must admit I haven’t fully checked under what conditions sort and filter works.
If I insert a Pivot this works fine and reports all the rows and values as expected, so the data would appear to be OK in most respects. I use tables quite a lot and have not come across this behaviour in filters before.
Hi Brent,
The only other thing I can think of is that those leftover names have blank spaces at the beginning or end, and don’t match the filter criteria. You can use the TRIM function to remove the blank spaces.
Hi, my favorite tip is the ability to create a table within an existing excel. Curious if this feature is carried into Google Sheets as well.
Great tutorials, thanks much
Thanks Nancy! I don’t believe Google Sheets has a robust Tables feature like Excel does. I honestly don’t use it much though. 🙂
Using the space bar instead of the mouse to click a selection on or off is really handy and less tedious. You can Select All on or off with one press or work through a list using the space bar and down arrow instead of that wrist-wearing mouse work!
Great tip Cath! Anything to save some wrist pain, right?
In the 2nd video I explain keyboard shortcuts for the filter drop-down menus. You can also press the letter E to jump to the search box, then press the down arrow jump down to the search box.
So Alt+Down Arrow, E, Down Arrow will select the “Select All” item in the checkbox list.
Alternatively, can also press Alt+Down Arrow, Up Arrow 3 times to highlight Select All. This works if your column contains dates, where the method above does not.
The Home, End, Page Up, and Page Down buttons also work in the checkbox list to quickly navigate around it and check/uncheck the first and last items with the space bar.
I should probably create a separate video on all those shortcuts alone… 🙂 Thanks again!
Dear Jan,
That is greet way to review. I have the next question how can I filter the 12 Months in 12 sheets need filter. like report of taxation.
Thank you very much,
Shakir Rahimi
I have created 4 pivot tables from my data set. When I reset my filters with new criteria in a new pivot table, the results in a previous pivot table change or disappear. How do I freeze a pivot table so it is unaffected by filter changes in a new pivot table? Is there a way to merge a column from one pivot table (with XX filters) into another pivot table (with YY filters)? In M/S Word, I would just do a cut and paste from one document to another.
It seems that many of the questions that you get would be answered in your pivot table course of instruction. Am I correct? About how long does it take to complete your course?
How to overcome Excel Limitation of 10,000 Rows over Filter?
[IMG]https://images.mobilism.org/?dm=4MUWGVON[/IMG]
need help to solve this please
Thank you for this amazing series of videos.
I save everything as a table. However, in order to add a new column or row complete with the table formatting, I have to drag the little triangle in the lower right corner of the table to the right or down to accommodate new columns or rows and have the formulas fill automatically. In the video you just clidked the header of the new column the table automatically expanded. What might I be missing?
Thanks!