By Filtered Excel tipsThe ultimate crash course in Excel – part 710 Dec 2018 Please enable cookies in your browser to watch the videos.Note: we are currently experiencing difficulties with the videos in this article, but are working to resolve it ASAP. Thanks for your patience.Excel tips: Ultimate crash course seriesPart 1 – Copy, cut & paste in ExcelPart 2 – Conditional formatting in ExcelPart 3 – Pivot Tables in ExcelPart 4 – Mathematical Formulas in ExcelPart 5 – Excel formatting guide for a great looking spreadsheetPart 6 – Essential Excel formulas like vlookupPart 7 – Filtering your data in ExcelIt’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 > FilterYour columns should now have arrows next to each of the headersClick 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 dataClick OK to apply the filter. You’ll notice that for this example we used the Between comparison filter. Your data should now look like this:Since we’ve filtered the data in the Daily Star column the drop down arrow in that column now appears as a filter. You can clear the filter and restore your data to its original state by clicking on the filter icon and selecting Clear Filter from “Daily Star”.Comparison operators are not the only way to filter your Excel data. You can also choose items from a list, search for data, or even filter by cell colour or font colour.Copying from filtered data As 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 data One 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:Download practice documentAdvanced filter Suppose 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.