Sorting, filtering and Excel Tables

Learn to sort long lists, show only the rows you need with a filter, and turn data into a “smart” Excel Table with `Ctrl+T`.

  • Intermediate
  • 14 min
  • 10 questions
  • Lesson 11 of 27

In this lesson you will learn

  • Sort data by one or several columns
  • Use text, number and colour filters
  • Create an Excel Table and know its advantages

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. 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. First level

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

  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.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

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. 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. 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. 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.
Create an Excel TableCtrl+T
Turn the filter on / offCtrl+Shift+L
Open the filter menu on a header cellAlt+↓
Select the current data block (press again for the whole sheet)Ctrl+A

Interactive

Loading simulation…
Practise the shortcuts for working with data. Ctrl+T is missing because it opens a new tab in the browser — try it in Excel.

Remember

  • To sort, select one cell in the column; if you select a whole column, choose Expand the selection.
  • In Data › Sort, Add Level builds a multi-level sort.
  • A filter (Ctrl+Shift+L) hides rows rather than deleting them; SUBTOTAL totals the visible ones.
  • A table made with Ctrl+T grows automatically, fills formulas down by itself and offers a Total Row.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

10 questions. Every correct answer earns XP.

Question 1 / 10

Question 1 / 10. Which shortcut turns the filter on and off?