Excel Filters - Free Video Training Series - Part 1 of 3 - Excel Campus
50

Excel Filters – Free Video Training Series – Part 1 of 3

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

learn-how-to-become-a-filtering-ninja-in-excel

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.

Excel Filters Training - Part 1.xlsx (101.4 KB)

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.

Filter Mate Logo

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!

Videos in this Series

Video #2 – Keyboard Shortcuts for Excel Filters

Video #3 – Excel Filter Logic for Multiple Columns

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 50 comments
Cath - December 8, 2016

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!

Reply
    Jon Acampora - December 8, 2016

    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!

    Reply
Nancy - December 1, 2016

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

Reply
    Jon Acampora - December 10, 2016

    Thanks Nancy! I don’t believe Google Sheets has a robust Tables feature like Excel does. I honestly don’t use it much though. 🙂

    Reply
Brent - November 22, 2016

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.

Reply
    Jon Acampora - November 24, 2016

    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.

    Reply
      Brent - November 25, 2016

      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.

      Reply
        Jon Acampora - November 28, 2016

        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.

        Reply
Trish - November 22, 2016

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.

Reply
    Jon Acampora - November 23, 2016

    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!

    Reply
Trish - November 21, 2016

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

Reply
    Jon Acampora - November 21, 2016

    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!

    Reply
Marcia - November 19, 2016

Enjoyed this video immensely and learned several tips I did not know. I look forward to more intermediate and advanced videos.

Reply
Nurlan - November 19, 2016

Thanks, Jon!

good job. i love it.

Reply
Julie - November 18, 2016

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.

Reply
Neil - November 18, 2016

Good work. The Insert Table function looks useful in a filtering context. Thanks for that Jon.

Reply
Lee - November 18, 2016

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.

Reply
    Jon Acampora - November 18, 2016

    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.

    Reply
Akke - November 18, 2016

Hi, tried your shortcuts but used the wrong key 🙂 Ctrl page down and you will jump to an other tab.

Reply
    Jon Acampora - November 18, 2016

    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.

    Reply
Akke - November 18, 2016

Hi Jon,

Love the shortcut tip. And I love the tip of Jen 🙂

Reply
Lauren Rider - November 18, 2016

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.

Reply
Tabish - November 18, 2016

Hi..

Its too helpful.

I want to take Complete training, please give contact details.

Thanks,

Tabish.

Reply
    Jon Acampora - November 18, 2016

    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.

    Reply
Ivars - November 18, 2016

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?

Reply
    Jon Acampora - November 18, 2016

    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.

    Reapply Filters in Excel Keyboard Shorcut

    Video #2 will be packed with keyboard shortcuts, and it will be out next week. Thanks again!

    Reply
Mike - November 17, 2016

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

Reply
    Jon Acampora - November 18, 2016

    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!

    Reply
Praveen - November 17, 2016

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

Reply
Rich - November 17, 2016

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.

Reply
    Jon Acampora - November 18, 2016

    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!

    Reply
Philip - November 17, 2016

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

Reply
Peter Collins - November 17, 2016

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

Reply
    Jon Acampora - November 17, 2016

    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!

    Reply
Joan - November 17, 2016

Great video! Thanks.

Reply
Tony Madeira - November 17, 2016

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.

Reply
Frank Byl - November 17, 2016

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.

Reply
    Jon Acampora - November 17, 2016

    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! 🙂

    Reply
Sandy - November 17, 2016

Hi Jon! Can filters also be applied to lookup tables, as I know cell location is vital to the lookup functioning accurately.

Thanks,
Sandy

Reply
    Jon Acampora - November 17, 2016

    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!

    Reply
Larry Kayser - November 17, 2016

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.

Reply
    Jon Acampora - November 17, 2016

    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!

    Reply
Jen Snyder - November 17, 2016

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.

Reply
    Jon Acampora - November 17, 2016

    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.

    Filter by Font or Fill Color in Excel

    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! 🙂

    Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x