Filters, conditional formatting, pivot tables and multi-sheet work for summarizing real data.
6 articles
Conditional formatting colors cells automatically when they meet a rule. Select the cells, go to Home > Conditional Formatting, and pick a rule such as Highlight Cells Rules > Greater Than. To color whole rows, use New Rule > Use a formula, with a $ before the column letter: =$E2>5000. Rules update whenever values change.
To filter data in Excel, click any cell in your table and press Ctrl+Shift+L (Mac: ⌘⇧F), or choose Data > Filter. Arrow buttons appear in the header row. Click an arrow, untick (Select All), tick the values you want and click OK. Hidden rows aren't deleted: clear the filter and they come back.
To reference another sheet, type the sheet name, an exclamation mark, then the cell: =Prices!B3. If the name has a space, put single quotes around it: ='Last Year'!B5. The easiest way is to type =, click the other sheet's tab, click the cell and press Enter, and Excel writes the reference for you.
To show a percent of total in a pivot table, click the ▾ on the field in the Values area, choose Value Field Settings, open Show Values As and pick % of Grand Total. Each number then shows its share of the total, and the parts add up to 100%. You can also right-click a value and choose Show Values As.
A pivot table summarizes a long table in a few clicks, without formulas. Click a cell in your data, choose Insert > PivotTable, and click OK to put it on a new sheet. Then drag fields into Rows, Columns and Values: Region in Rows and Sales in Values gives total sales for each region.
SUBTOTAL calculates a total that skips rows hidden by a filter: =SUBTOTAL(109,D2:D11) adds only the visible amounts, while SUM adds every row. The first argument picks the calculation: 9 or 109 sums, 1 or 101 averages, 3 or 103 counts non-empty cells. The 100s also skip rows you hid by hand.