Free demo · Microsoft Excel · Lesson 7 · Basics · Sort and filter

Sort and filter a list with an Excel Table

Press Ctrl + T and a plain list becomes a Table: every heading gets an arrow that sorts and filters, and whole rows always stay together.

[b]Child | [b]Course | [b]Fee Asha | Art | 1500 Kabir | Sport | 1200 Riya | Computers | 1800 Imran | Sport | 1200 Tara | Music | 1400 Dev | Art | 1500
  • Sort A to Z
  • Sort Z to A
  • Sort by Color

  • Clear Filter From "Course"
  • Text Filters

  • (Select All)
  • Art
  • Computers
  • Music
  • Sport

  • OKCancel
  • [b,fill=#107c41,color=#ffffff,d]Child | [b,fill=#107c41,color=#ffffff,d]Course | [b,fill=#107c41,color=#ffffff,d]Fee [fill=#d6ebdf]Imran | [fill=#d6ebdf]Sport | [fill=#d6ebdf]1200 Kabir | Sport | 1200 [b]Total | | [b]=SUBTOTAL(109,[Fee])→2400
    1. 1Click any cell in the list, press Ctrl + T, then OK
    2. 2It is a Table now: banded rows and an arrow ▾ on every heading
    3. 3Arrow on Child › Sort A to Z: whole rows move together
    4. 4Arrow on Course: untick (Select All), tick Sport, OK
    5. 5Only the Sport rows stay. The others are hidden, not deleted
    6. ✓Table Design › Total Row adds up only the rows you see: 2,400
    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 8
    9. 9
    10. Enrol

    Tip: press → or Space to step through, ← to go back, R to replay.

    About this lesson

    A list in Excel is easier to work with once it is a Table. Click any cell in the list and press Ctrl + T (or choose Home › Format as Table), confirm that the table has headers, and Excel adds banded rows, a filter arrow on every heading and a Table Design tab. The arrow on a heading opens one menu for both jobs. Sort A to Z puts the list in order, and whole rows move together, so a name never ends up next to the wrong number. The tick boxes below filter the list: untick (Select All), tick the items you want and only those rows stay visible. The other rows are hidden, not deleted, and Clear Filter brings them back. Ticking Total Row on the Table Design tab adds a total that counts only the visible rows. On a plain list, Ctrl + Shift + L adds the same filter arrows. The full course goes on to multi-level sorts, number, text and date filters, structured references such as =SUM(Sales[Total]) and slicers.

    Key points

    • Ctrl + T turns a list into a Table with filter arrows
    • Sort A to Z from a heading: whole rows move together
    • Filter with the tick boxes; a Total Row adds only visible rows