Tweet

Excel For Noobs Tutorial: How to Sort and Filter with Excel Tables

As mentioned, in the header row of you table are drop-down menus that allow you to sort and filter your Excel table. When you click on the drop-down arrow you will see that you have options to sort your rows from A-Z, Z-A or you can sort by color if you have formatted your table cells will fill colors. If the values in the column are numeric values then you will have the option of sorting values from largest to smallest, smallest to largest or by fill color. Sorting can be very useful, suppose we needed to sort our agents alphabetically, or our homes by prices from most expensive to least expensive. The sort and filter feature of Excel tables makes this possible and efficient.

How to Sort and Excel Table

How to Sort an Excel Table Column by Column

In Excel when sorting columns you should always sort the column of least importance first and the column of most importance last. For example, if we first sorted the List Price column from largest to smallest and the sorted the Agents column from A-Z the then the Agents column would take priority and all of Adams listings would be listed. Then for each agent their listing would be listed from most expensive to least expensive.

Analyze the image below. You can see that the agents are listed alphabetically and for each agent their listings are in order of most expensive list price to least expensive list price. This is because we sorted the list price first and the agent last.

How to Sort an Excel Table Using the Sort Dialog Box

You can also sort your table using the Sort Dialog Box. To do this start by clicking the down-arrow on the column that you want to sort first. When sort your table this way the column you want to sort first will be the most important. Since we want Agents to be the most important column we will start there.

1. In the Agent Column click the Sort down-arrow.
2. From the drop-down menu click on Sort by Color and then click Custom Sort.
3. In the Column category make sure the Agent column is selected. If not the click the drop-down arrow and select Agent from the list.
4. Now in the Sort On category select Values and under the Orders category select A-Z.
5. Now click the Add Level button. A new level will appear that is labeled Then By. Under column select List Price, under Sort On select Values and under Order select Largest to Smallest.

Now your table is sorted by Agents alphabetically and then by List Price from largest to smallest. You could continue adding levels to filter by. Just remember when using the Sort dialog box the most important column is sorted first.

How to Filter Excel Tables

How to Use Filters Based on the Tables Data in Excel Tables

Suppose we had a customer that was only interested in homes that had 3 bedrooms or more. We could use the filter button to remove any listings from the table that have less than 3 bedrooms. To do this we would click the down arrow in the bedrooms column and from the check box list we would uncheck any box that is less than 3 and click OK. Now only homes with 3 bedrooms or more will show in our table.

How to Use Custom AutoFilters in Excel Tables

Now suppose our customer only wanted homes less than \$150,000. To do this we would click on the drop-down arrow in the list price column, and then from the menu select Number Filters. A new menu will appear offering you different options to filter your numbers. For our example we will select Less Than or Equal To… and the Custom Autofilter dialog box will appear. In this dialog box you can see that there are different options to filter. For our example we are only interested in homes listed for less than \$150,000 so in the box right next to the box that list the type of number filter we are using we will enter 150000 and click OK. Now only homes less than \$150,000 that have more than 3 bedrooms are shown in our table.

Find the next tutorial in the links below.