In the previous video we learned how to use the Flash Fill and Text to Columns features in Excel to help clean data. In this video we will discuss how to filter and sort our data to enable us to control what information is displayed, and how it’s displayed in our worksheets. Filtering your data enables you to gain more control over which parts of your data are displayed at any given time in Excel. This can help with the visibility of data by narrowing down the data to within specified criteria and parameters, and it can also help when searching for specific pieces of data. To filter your data, the first thing you need to do is turn filtering on, which is very simple. On the Data tab, click Filter, and that’s it. You will now see a small filter icon next to each of the column headers. As a sidenote; if you want to only filter on one or more columns, select those columns first, then click Filter. As another sidenote; if you format your data as a table, the columns automatically have filter controls added to them. So now, each column has a filter that can be applied to the data in that column. In the Orderdate column you can filter on the years, in Productline you can filter on the different product types, and in Customername you can filter on each customer by name. Let’s first filter on the year. We’ll select orders from 2004 only, by deselecting the other year. And if we wanted to, we could expand the year and filter by months also, but we won’t do that for now. If you look in the status bar at the bottom of the worksheet, you can see that there are only 50 out of 114 records now displayed. If you want to clear a filter, you can either click the ‘Clear Filter From…’ option, or click the Select All item in the filter list. Now let’s filter on the productline column, to display only the rows that hold data for sales of classic cars. And again, we’ll clear the filter. Lastly, we’ll filter on the customername column, and only display sales to Mini Gifts Distributors Ltd. And then clear that filter. So far, we’ve only applied one filter at a time, but suppose you want to filter down to a greater degree? We can do that too by just enabling all those filters together and now we are only displaying sales of classic cars, to Mini Gifts Distributors Ltd, in 2004. Remember, if you only want to clear one filter, then click its filter button in the column header, and click the ‘Clear filter from’ option but if you want to quickly clear ALL filters, you can use the Clear button in the Sort & Filter group on the Data tab. So far, we’ve used what are commonly referred to as AutoFilters, but you can also use custom filters to specify other criteria, to apply a filter to text or numbers. For example, if you wanted to see sales orders that are over or under a certain value, you can do that with custom filters. For the sales column, let’s add a number filter that only displays sales that are over $2,000. If you look in the status bar, you can see that we are now showing 111 out of 114 records. Then let’s clear that filter and filter it the other way to display the sales orders that are BELOW $2000. We can see that there are only 3 orders that are below $2000. It’s important to note that the data rows that we don’t see have not been removed they are still there they have just been hidden from view by the filters. And this is indicated by the row numbers you see on the left in blue. The row numbers start at 69, and jump in large increments, indicating that there are many more rows of data in our dataset, than are currently being displayed. Let’s clear those filters. If we look at a column filter for a column that contains text, you will see that the menu item changes to ‘Text Filters’ instead of ‘Number Filters’, and you can see there are several text filter options. (And if you want to turn off filtering altogether for a worksheet, just click the Filter button on the Data tab. Now, let’s take a look at the basic sorting capabilities in Excel. Sorting is a very important part of the role of a typical Data Analyst. You might need to organize your text-based data alphabetically, your number-based data numerically, or your date-based data chronologically. When you sort data using these logical parameters, it makes it easier for you to conceptualize and visualize your data in a more meaningful way. When sorting data, the first thing you need to do is select which data to sort. For example, if you want to sort your customers alphabetically, select a cell in the Customername column first and then either sort by A to Z or by Z to A. And if you want to sort your sales figures numerically, select a cell in the Sales column first… and then either sort from smallest to largest or from largest to smallest. And lastly, if you want to sort your customer’s order dates chronologically, select a cell in the Orderdate column first… then sort from oldest to newest or from newest to oldest. But you can also sort your data by more than one column at a time. Simply select a cell in your data, then on the Data tab, click Sort. Then either use the Sort by column suggested, or use the drop-down list to select a different column; in this case we’ll choose the ‘Orderdate’ column as our first sorting criteria, and we’ll choose ‘Oldest to Newest’ in the Order drop-down list. To add a further sorting level, you click ‘Add Level’. Then you choose another sort column in the ‘Then by’ drop-down list, in our case we’ll choose Sales, and for this sort level we’ll choose ‘Largest to Smallest’ in the Order list. If you have a header row in your data – as we do here – then ensure you select the ‘My data has headers’ checkbox, then click OK to sort. So, the data is now sorted to list the oldest orders by order date first, then within each order date, if there are multiple instances with the same order date… then the next sorting level lists data by the largest order values first, down to the smallest order values. In this video, we learned how to use the Filter and Sort tools in Excel to filter and sort our data to enable us to control what information is displayed, and how it is displayed in our worksheets.