Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The ability to filter the data in your spreadsheet is really one of the most powerful options that you have when you're working in Excel and I'm going to demonstrate for you a very quick way that you can add an auto filtering option to your tables. For those of you that would like to follow along, please go to your exercise files and open up EatCake Sales Forecast3. Once you've opened it up, select the area that you would like to filter and in this case, it is columns A through E in a row 3.
So I clicked, dragged and highlighted those areas using my mouse. You'll notice that they're shaded somewhat to identify that's the area that I'm working with. I now want to apply an automatic filter to those so I go up to my Editing group on my home tab in the ribbon. Click on Sort and Filter and scroll all the way down to the Filter command. Doing that adds drop-down lists in all of the different cells in that particular row. Now I'd like to filter by year, I simply need to click on the downward pointing arrow in the year, deselect the areas that I'm not interested in looking at, so removing moving the checkmark from the 2005 box.
Clicking OK and it presents me only with the 2006 information. You notice that it identifies that I'm filtering on that column by changing the icon in the top of the row. If I'd like to filter on yet one more column let's say I'd like to see what the partners are doing in this particular year, I can go to my next column and do the same thing. Click on the downward pointing arrow. Click Select All to remove the check boxes from anything that I am not interested in looking at, select the one I am, click OK and it just pulls the information for that catering option. Pretty powerful stuff.
To filtering options that I have set, I simply go out to the Sort and Filter command again. Click on it once, and select the Clear command, which removes all the filtering options that I have set. Ig I'd like to reapply them I simply select them and add them in again. One thing to know, you can only apply the auto filtering option to columns. If I tried selecting rows that particular functionality would not be available to me. Next we're going to see how you can add all of that formatting options when you use the Format as Table commands.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90597 Viewers
80 Video lessons · 137947 Viewers
59 Video lessons · 56701 Viewers
52 Video lessons · 70314 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.