Excel tips: Ultimate crash course series
- Part 1 – Copy, cut & paste in Excel
- Part 2 – Conditional formatting in Excel
- Part 3 – Pivot Tables in Excel
- Part 4 – Mathematical Formulas in Excel
- Part 5 – Excel formatting guide for a great looking spreadsheet
- Part 6 – Essential Excel formulas like vlookup
- Part 7 – Filtering your data in Excel
It’s the final lesson in this series of Excel. Your Excel development doesn’t have to end here. You’ve learned a lot in this seven part series, but Excel still has a further 400+ functions that we haven’t even looked at yet. You’ll probably never need to know all of them, but there are many that you’ll find useful. That’s our entire approach to learning. We use AI to teach you just the things you need to know. It’s what our 160 module online Excel course was created for and it’s what our patented technology, The Filter, does. In part seven we’ll cover Filters, a super powerful function that allows you to clean, sort and get quick answers from the data you are using.
What is filtering?Filtering allows you to choose what information you view, hiding what you don’t want without removing it. Filters on columns hide rows that do not match your filter. Filtering makes it easy to analyse large amounts of data and quickly focus on specific information, saving you time and making your spreadsheets more effective. Excel has many options when it comes to filtering your data. If you have a database with numerous entries, you can filter the data so that only the relevant information appears. When you remove the filter, all of your data is still there. Built-in comparison operators such as “greater than”, “less than”, “between”, “top 10”, or custom filters can show the data you want and hide the rest. To use filters:
- Select the data you want to filter. It’s helpful if your columns have headers.
- Data > Sort and Filter > Filter
- Your columns should now have arrows next to each of the headers
- Click the arrow in the column header, then choose Text Filters or Number Filters
- Choose one of the comparison operators.
- In the Custom AutoFilter box, type or select the criteria you wish to use to filter the data
- Click OK to apply the filter.
Copying from filtered dataAs a default, Excel copies the hidden or grouped but minimised cells in addition to all visible cells in a worksheet. In earlier versions of Excel, cells not visible in a Filter were also copied, but in Excel 2013 they will not be. If you want to be certain hidden cells are not copied, follow these steps to copy only the visible cells in your database.
Checking and cleaning dataOne important use of the Sort and Filter functions in Excel is to check your large database for errors such as typos. As explained earlier, when you turn on the filter, drop-down arrows will appear in each of your column headers. When you click on a drop-down arrow, a menu pops down. The menu gives you options for sorting and several ways to filter your data. It also shows each unique entry in the column of data. This is a good way to spot if you have typos, duplicates, or other incorrect entries in your data. It’s practice time. Download the exercise below and practice using a simple filter to filter for multiple criteria:
Advanced filterSuppose you have a large database with duplicate values. It may be useful to have a list just containing one of each of the different values, so you can see what values there are. The Advanced Filter produces unique values from a list with duplicate values. Let’s take a look at how we can filter a list for unique records only.
That’s it! Don’t forget, as a special bonus for completing this course you can get our full Excel course at a huge discount, click below to see the price in your local currency. AAT students and professional members can access a wide range of Excel resources and training.
Filtered provides algorithmically personalised Excel courses written by subject matter expert.