How to Use Sort and Filter with Excel Table
In this post, we will discuss how you can sort and filter the data of a table.
The following table contains the data of a real estate company. Each item in the Header Row of the table contains a drop down arrow. This drop-down arrow is known as “Filter” buttons.
If you click on a Filter Button, you will see many types of Sorting and Filtering Options.
Sorting means rearranging the rows based on some criteria. For example, you may want to sort a column in alphabetical order. Say I want to sort this Area column in alphabetical order. Click on the Filter Button of Area column. Three sorting options here: Sort A to Z, Sort Z to A, and Sort by Color. I click on Sort A to Z.
A little up arrow has been added to the Filter Button to let you know that this column has been sorted from lower alphabetical letter to higher alphabetical letter.
Now click on the “List Price” column’s Filter button. “List Price” column holds numbers. You see the sorting options have been changed.
So, sorting options actually depend on the contents of a column. I click on the “Sort Smallest to Largest” option. So, the column is sorted in ascending order.
Now I want to show, how you can sort a column using background or text color.
You cannot sort a column using that default table background color or table default text color. Only when you have applied different color in cells or text by yourself, you can sort a column using a background color or text color. For instance, if I applied some background color in some cells and used some text color in some text in this column. Click on the Filter Button in the Area column again, move your mouse on Sort by Color. A sub menu appears. You can “Sort by Cell Color” or you can “Sort by Font Color”. Sort by Cell Color has three options for this Column: Yellow color, Light Blue Color, and the No Fill color.
Sort by Font Color has also three color options: The Red Font color, Blue Font color, and Automatic Font color. There is another option Custom Sort which we shall discuss later.
Let’s see now how you can sort multiple columns.
You can sort multiple columns in two ways. For example, I want to sort three Columns Agent, Area and List Price. When you sort multiple columns, then remember one important thing: At first sort the least significant column, then sort the next priority column and at the end sort the most significant column.
In our case, say our least significant column is the Area column, then the List Price column and most significant column is the Agent column.
So first of all, I sort Area column, click on the Filter button and choose Sort A to Z option, then sort List Price column, click on the Filter button, and choose “Sort Smallest to Largest” option, and finally sort Agent column, in the Filter button, choose “Sort A to Z” option. This is how you can sort multiple columns.
There is another way you can sort multiple columns. Click on any Filter Button, move your mouse over “Sort by Color” option, then click Custom Sort. Sort dialog box appears.
As I want to sort Total three columns and in the dialog box, you can sort only one column. I have to add two more levels. So, I add two more levels clicking twice on the “Add Level” button. In the Sort dialog box, the most significant column comes first, and then the next significant column and finally the least significant column.
Our most significant column was “Agent” column, then List Price column and the least significant column was Area column. So, at first I want to sort Agent column, I select Agent in the Sort by drop-down, you can sort on Values, Cell Color, Font Color and Cell Icon. I choose Sort on Values. Order A to Z. Okay. To get more options click on the drop down, Z to A and “Custom Lists” options are there. Click on the Custom Lists option, Custom Lists dialog box appears.
Then I want to sort List Price, Sort On Values, okay, Order Smallest to Largest, I want to change it, select Largest to Smallest. Finally, I want to sort Area column, Sort on Values, okay, Order A to Z, okay. You can up your level, you can down your level. Just select the Level, click on this Up arrow, again click on the Down arrow. You can copy level and you can delete level. Click on the Options button. The Sort Options dialog box appears with More Options. You can make your sorting Case Sensitive, and you can change the Orientation. Click OK, and click OK. You see the three columns have been sorted according to the criteria.
Okay, let’s now discuss how you can filter a table.
Filtering means displaying only those rows that meet certain conditions, the other rows will be made hidden. For example, in this Real Estate table, you might be interested to see the only the Central Area data. To do that filtering, just click on the Area Filter Button, unmark Select All, and then Select Central. You see only the Central Area related data is available on the table now.
Also, notice that some row numbers are missing, they are actually hidden. Also, notice that the Filter Button of this column is now showing a different graphic which means that this column is filtered.
Complex filtering is also possible. Say I want to see only Adams in the Agent Column, only N. County in the Area column, and only Single Family in the Type column.
You’re your mouse over the “Text Filters” Option, a sub menu appears and so many options are here. Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom Filter. Dig them out by yourself and solve the homework on this topic. I click on Custom Filter, Custom AutoFilter dialog box appears.
This Filtering will show rows where Area is Equals, or you can change, just click on the drop down, you see there are so many options here: equals, does not equal, is greater than, is greater than or equal to; so many options, I select Equals to N. County. I select Or, then again equals and then Central. I click OK. You see only N. County and Central area is showing on the Table now. In the same way, you can filter a number column. When you use filtering, the Total Row of the table is updated to show only the visible rows.
To remove filtering for a column, click on the Filter button, and select Clear Filter for Area. If you’ve filtered multiple columns and you want to Remove all the filtering at one go, just click on the Sort and Filter drop down in the Home ribbon and then select the Clear command from the list.
So, this summarizes our learnings on Excels table sort and filter functions. Questions are welcome! Until next time…