Excel Basics: Filtering and Sorting Data

February 9, 2017

When you’re working with a large data set it can be helpful to filter out the unnecessary items, so you can focus on the important ones. Today we will show you how to filter a list of data, format it, and sort it.

In the example below, we have a table of personnel data that shows employees’ names, their supervisors, work level, and location.

First, let’s apply a filter and show only those employees working in the New York office. To do this, first select the data you want to filter.

Then go to the “Data” menu, and select “Filter”. Notice that when you hover over the filter button, a menu pops up with some instructions as well as the keyboard shortcut for this function.

After you click the “Filter” button, you’ll notice that filter buttons or pull down menus appear in the bottom right corners of each of the column headings. Click on the filter button and un-check everything except for “New York”. Then click the “OK” button.

Now the only rows that remain visible are the ones that have “New York” in the location column. An interesting feature of the filter is that you can format the visible rows, but the hidden rows will remain unaffected. Let’s give it a try. First select all of the visible data, and then change the fill color to be yellow. You can change the fill color from the “Home” menu as shown below.

Now if you click the filter button again and select all…

You’ll see that you formatted only the visible rows; leaving the hidden rows unaffected.

Now that you know how to filter and format a list of data, let’s learn how to sort it. If you click on the filter button again, you will see that there are several options to quickly sort data for that specific column. You can sort alphabetically, by color, or you can even get fancy and use the text filters to filter by key letters or words.

Instead of using one of these options from the filter button, we’re going to use the sort function from the “Data” menu. First click “Data” and then “Sort”. A menu will pop up to help you sort the data.

First let’s sort by “Location” by clicking in the field labeled “Sort by”.

Then let’s add a level by clicking the “Add Level” button at the top left. For the second sorting criteria, let’s sort by “Work Level”.

You’ll notice that once you select “Work Level” as your sorting criteria, the “Order” changes automatically to be “Smallest to Largest”. This is because the sort function recognizes that “Work Level” is given in numbers rather than text. Click the “OK” button, and voila! Your employee data is sorted first by “Location” and then by “Work Level”. You’ll also notice that the yellow formatting you gave to the New York employees has moved along with the data. So, you don’t need to reformat the data.

There you have it. Now you know how to filter data, format it, and sort it using multiple criteria. If you found this training to be useful, you can find more training and several pre-made tools in the Downloads section of our website.



Leave a Reply

Your email address will not be published. Required fields are marked *