Sort, filter, and a simple table

Sorting rearranges rows so you can read a list in order — A to Z, Z to A, smallest number to largest, or the reverse. Microsoft's basic-tasks page describes two approaches. For a sort with specific choices, select a single cell anywhere in the range, then on the Data tab, in the Sort & Filter group, choose Sort. A Sort dialog appears. In Sort by, pick the first column to sort on. In Sort On, choose Values (you can also see Cell Color, Font Color, or Cell Icon — this course stays with Values). In Order, choose A to Z or Z to A for text, or smallest to largest / largest to smallest for numbers.

Before you sort, select a range that includes every column of the list, or click inside a well-formed block that has headers. Microsoft's example ranges include several columns at once (such as A1:L5) as well as a single column. For the Household sheet, that means keeping Item, Store, Amount, and Category together. If you sort only the Amount column, the prices can detach from the item names. That is one of the easiest ways to ruin a list. If a sort looks wrong, use Undo if you still can, or go back to the sample layout at the start of this course.

On Hours, you might sort by hours (largest first) to see which day was busiest. Wednesday's 7.5 hours should still travel with the note “Market stall.” After you finish looking, you can sort Day back to the original week order if you need Monday through Sunday again — or keep a copy of the file before you experiment.

Filtering hides rows that do not match your choice. The rows are still in the workbook; they are just not shown until you clear the filter. Microsoft's basic-tasks steps are:

1. Select the data you want to filter.
2. On the Data tab, in the Sort & Filter group, click Filter.
3. Click the arrow in a column header to see the choices.
4. To select by values, clear the (Select All) check box, select only the values you want to see, and click OK.

On Household, click inside A1:D6, choose Data > Filter, then click the arrow on Category. Clear Select All, tick Groceries, and click OK. Only Milk 2 litre and Bread should remain visible (5.49 and 3.29). Fuel, feed, and the phone card are hidden, not deleted. To see everything again, open the same arrow and choose Select All, or turn Filter off from the Data tab.

You can set a filter on more than one header. For example, you might first show only the Co-op in Store, then look at Category. Each extra filter hides more rows. If the sheet looks “too empty,” open each header arrow and see what is still ticked.

Filters are helpful before a meeting: “Show me only Vehicle costs” or “Show me only the Co-op.” They are not a good place to hide private information you should have removed. Hidden rows can still travel with the file when you share it (Lesson 10). Use filter to read a list, not to conceal something sensitive.

Microsoft's basic-tasks page says a simple way to use Excel's power is to put data in a table, which lets you quickly filter or sort. One official Windows desktop path uses Quick Analysis:

1. Select your data by clicking the first cell and dragging to the last cell (or hold Shift and use the arrow keys).
2. Click the Quick Analysis button in the bottom-right corner of the selection.
3. Click Tables, preview the Table button, and then click Table.
4. Use the arrow in a table header to filter (clear Select All, then tick what you want) or to sort A to Z / Z to A.

Microsoft notes Quick Analysis in that article in connection with Excel 2016. If you do not see the Quick Analysis button (common on some Mac setups and on Excel for the web), use the path on Microsoft's create-and-format-tables page instead:

1. Select a cell within your data.
2. Select Home > Format as Table.
3. Choose a style.
4. In the Create Table dialog, check the cell range.
5. Mark whether your table has headers.
6. Select OK.

A table keeps header arrows ready for sort and filter and applies a readable style. For this basics course, that is enough. You do not need PivotTables, slicers, or Power Query. If Excel asks “Does your table have headers?”, say yes when row 1 already contains words such as Item, Store, Amount (CAD), and Category. If you say no, Excel may add names like Column1.

1. Sort Household by Amount, largest to smallest, and check that each item kept its store and category.
2. Use Data > Filter (or table header arrows) to show only Category = Groceries, then show all rows again.
3. Format the Household block as a table with headers.
4. Optional: on Hours, sort by Hours worked to see the busiest day, then confirm Wednesday is still “Market stall.”

If two people sorted the same hall sign-up list — one by last name, one by town — what would you check before you trusted either print-out?