Sorting, filtering and Excel Tables

Leyla's sales log has 300 rows: date, branch, product, amount. Her manager asks: “Which sales were the biggest? Show me only the Ganja branch's sales in March.” To answer in seconds you need two tools: sorting and filtering. An Excel Table makes both even easier.

Sorting

For a simple sort, click one cell in the column you need and choose Data › Sort & Filter › Sort A to Z or Sort Z to A. For numbers this means smallest to largest and back; for dates, oldest to newest and back. Excel moves whole rows together, so each row's information stays together.

1. 1
   Open the Sort dialog

   Click a cell inside the data and choose Data › Sort & Filter › Sort. Check that My data has headers is ticked.

2. 2
   First level

   In Sort by, choose the Branch column and set Order to A to Z.

3. 3
   Add a second level

   Click Add Level, pick Amount in Then by with Largest to Smallest, then click OK. Each branch's sales are now listed from largest to smallest.

Filtering

Data › Sort & Filter › Filter (Ctrl+Shift+L) adds drop-down arrows next to the headers. Click an arrow and tick only the values you need, e.g. Ganja. Text columns offer Text Filters › Contains…, number columns Number Filters › Greater Than…, Top 10… and Above Average, and coloured cells Filter by Color. Rows that don't match are not deleted, only hidden: their row numbers turn blue and the header button shows a funnel icon. To bring everything back, choose Data › Sort & Filter › Clear.

Excel Tables (`Ctrl+T`)

1. 1
   Create the table

   Click a cell inside the data and press Ctrl+T (or Insert › Tables › Table). Check the range, tick My table has headers and click OK.

2. 2
   Give it a name and a style

   On the new Table Design tab, type a name such as Sales in Table Name and pick colours from the Table Styles gallery.

3. 3
   Turn on the Total Row

   Tick Table Design › Table Style Options › Total Row. A total row appears under the table; each of its cells has a list with Sum, Average, Count and more.

• Filter buttons appear in the headers automatically, and rows get alternating colours (banded rows).
• A new row typed directly below the table joins it, together with the formatting and formulas.
• A formula typed in one cell fills the whole column by itself (a calculated column), e.g. =[@Price]*[@Qty].
• Formulas use names instead of addresses: =SUM(Sales[Amount]) — as the table grows, the formula grows with it.
• When you scroll down, the table's headers replace the column letters at the top.