1 Filter arrows in the headings. 2 Check only what you want to see. 3 Conditional Formatting colors past due dates red.
These tools work best on a neat list: one heading row at the top, one row per thing, and no empty rows in the middle.
Sort: put the list in order
Click any one cell in the column you want to sort by, like the Name column.
Click the Data tab.
Click A to Z (the small button with A on top and Z under it). For numbers, it goes smallest to biggest. Click Z to A for the other way.
Excel moves the whole row together, so nothing gets mixed up.
Tip: Never select just one column before sorting. Click one cell only. If you select one column, Excel may ask; choose Expand the selection.
Tip: To sort by two things (like date, then time), click Data, then the big Sort button, then Add Level.
Filter: show only some rows
Click any cell in your list.
Press Ctrl+Shift+L, or click Data, then Filter.
Small arrow buttons appear in every heading.
Click the arrow in the Status heading.
Uncheck Select All, then check only Paid. Click OK.
Now you see only the Paid rows. The others are hidden, not deleted. Click the arrow again and choose Clear Filter to see everything.
Mac: On a Mac, the filter shortcut is Cmd+Shift+F.
Remove Duplicates
Click any cell in the list.
Click Data, then Remove Duplicates.
Check the columns that must match for two rows to count as the same, like Name and Phone.
Click OK. Excel tells you how many it removed.
Tip: Save a copy first, or be ready to press Ctrl+Z. Removed rows are gone.
Find and Replace
Press Ctrl+H. (Or Home tab, Find and Select, Replace.)
In Find what, type the old words, like Ave.
In Replace with, type the new words, like Avenue.
Click Replace All.
To just find something, press Ctrl+F, type it, and click Find Next.
Mac: On a Mac, Find is Cmd+F and Replace is Ctrl+H.
Format as Table: the best trick in this lesson
A table makes a plain list smart. It adds colors, filter buttons, and grows by itself when you add rows.
Click any cell in your list.
Press Ctrl+T. (Or Home tab, Format as Table, then pick a style.)
Make sure My table has headers is checked. Click OK.
Tip: Type in the row right under the table, and the table grows to include it. A formula typed in one row of a table copies down the whole column by itself.
Conditional Formatting: colors that warn you
Conditional Formatting colors a cell only when something is true. It is perfect for late payments.
Color dates that are already past:
Select the Due Date cells.
Home tab, click Conditional Formatting.
Point at Highlight Cells Rules, then click Less Than.
In the box, type =TODAY()
Pick Light Red Fill with Dark Red Text. Click OK.
Now every date before today turns red. Tomorrow it checks again by itself.
Color the 3 biggest amounts:
Select the Amount cells.
Home tab, Conditional Formatting, Top/Bottom Rules, Top 10 Items.
Change 10 to 3. Click OK.
Tip: To take colors off, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.
Tip: Data Bars (also under Conditional Formatting) draw a little bar in each cell. Bigger number, longer bar.
Try it
Go to the sheet 6 Appointments. Turn it into a table with Ctrl+T. Sort it by Date, oldest first. Filter the Status column to show only Waiting, then clear the filter. Use Remove Duplicates on Name and Phone (there are 2 repeats). Use Find and Replace to change every "Wash" to "Wash and Set". Color all Due Dates before today in red, and the top 3 amounts in green.