In the previous section, we created an Excel table. One of the features of an Excel table is the built-in drop-down menus that make sorting and filtering quick and easy.
Available Files
The following files are available for download:
Sorting data
Currently, the data in the Sales by Location worksheet is in alphabetical order by location. Let's change this to be organized in ascending order by date.
- To open the Date drop-down menu, in the Excel table,
Click
- To sort by date, at the top of the drop-down menu,
In Microsoft Excel for Windows, Click Sort Oldest to Newest
In Microsoft Excel for macOS, Click Ascending - In Microsoft Excel for macOS, to close the drop-down menu,
Click in a blank area of the worksheet
The data in the table is now in ascending order with the oldest date (6/5/2022) at the top. No data has been removed, so the average sales in the total row hasn't changed.
Filtering data
The drop-down menus can also be used to filter data. When data is filtered, it is hidden but not removed, so no data is lost.
Let's filter the data to see just the sales that will benefit the food pantry.
- To open the Beneficiary drop-down menu, in the Excel table,
Click
The Beneficiary drop-down menu opens:
The drop-down menu has the sorting options at the top. The filtering options are at the bottom of the menu. Each unique value, in this case, Beneficiary, is listed. Unchecking the box next to a value hides that value.
We want to remove all of the beneficiaries except the Food Pantry. We could uncheck each box. Or we can uncheck (Select All) and then check just the Food Pantry entry.
- To deselect all of the beneficiaries, in the drop-down menu,
Click the (Select All) checkbox
- To select just the Food Pantry, in the drop-down menu,
Click the Food Pantry checkbox
- To close this filter,
In Microsoft Excel for Windows, Click
In Microsoft Excel for macOS, Click in a blank area of the worksheet
The table has been filtered and only shows the dates and locations when the Food Pantry was the beneficiary.
Notice the row numbers. Only the rows containing the Food Pantry are visible. The other rows are hidden. The average of sales in the Total Row has changed to show the average of the visible cells.
- To open the Beneficiary filtering options,
Click
- To remove the filter, in the middle of the drop-down menu,
Click the Select All checkbox
- To accept this change,
Click
- To save the workbook, on the keyboard, press:
Control key + S
If you have been working through this course in sequential order, you have reached the end of the materials.