Sort and Filter an Excel Table: Slicers, Total Row and Tips

Part of the free Module 4: Sort and Filter · Lesson 7 of 11 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted. Slicers on tables need Excel 2013 or later.

To sort and filter an Excel Table, press Ctrl+T to convert your range into a Table, then use the permanent drop-down buttons in the header row. A Table keeps its filter buttons, grows automatically as you add rows, supports one-click slicers and adds a Total Row that only counts visible cells. It is the most reliable way to sort and filter data that changes often.

What an Excel Table is and why it beats a plain range

A plain range is just cells. Excel does not know where your data starts or ends, so every filter, formula and chart has to be re-pointed when you add rows. An Excel Table (Insert > Table) is a named object that Excel manages for you. Once you create one you get these benefits straight away:

  • Permanent filter buttons. The header row always shows sort and filter arrows and stays visible in the column letters when you scroll.
  • Auto-expanding range. Type in the row directly below the Table and it joins the Table, picking up formatting, formulas, filters and any chart or pivot table built on it.
  • Structured references. Formulas use column names such as =SUM(Sales[Amount]) instead of =SUM(C2:C500), so they never need editing when data grows.
  • Calculated columns. Enter a formula once and Excel fills the whole column, including new rows.
  • Slicers and a Total Row. Visual filter buttons and filter-aware totals with two clicks.

Filtering a normal range is covered in the AutoFilter lesson. Everything there still works inside a Table; this lesson adds what only Tables can do.

How to create an Excel Table

  1. Click any cell inside your data. The data needs one header row and no fully blank rows or columns.
  2. Press Ctrl+T, or go to Insert > Tables > Table.
  3. Check the range in the Create Table dialog and make sure My table has headers is ticked.
  4. Click OK. Excel applies a banded style, adds filter buttons and shows the Table Design tab (called Table Tools > Design in Excel 2016 and 2019).
  5. On the Table Design tab, replace the default name in the Table Name box (for example Sales). Names cannot contain spaces.

Sort and filter from the Table header buttons

Every header cell carries a drop-down arrow. Click it, or select the header and press Alt+Down Arrow, to open the same menu you see in AutoFilter: Sort A to Z, Sort Z to A, Sort by Color, the Text Filters, Number Filters or Date Filters sub-menu, a search box and the tick-list of values.

Two things are different from a plain range. First, the filter buttons never disappear, so nobody can accidentally sort the header row into the data. Second, when you add rows below the Table they are filtered and sorted with the rest of the data, whereas rows added below a plain filtered range are ignored until you reapply the filter. For a multi-column sort, use Data > Sort & Filter > Sort and add levels exactly as in the sorting lesson; Excel will sort the whole Table without asking you to select it.

To clear every filter at once use Data > Sort & Filter > Clear or press Ctrl+Shift+L twice. Note that the second press switches the filter buttons back on, because a Table always shows them unless you untick Filter Button on the Table Design tab.

Add a slicer to an Excel Table

A slicer is a panel of clickable buttons, one per unique value in a column. It is the friendliest filter for a report that other people will use. Slicers on Tables require Excel 2013 or later.

  1. Click anywhere in the Table.
  2. Go to Table Design > Tools > Insert Slicer.
  3. Tick one or more columns, for example Region and Product, and click OK. Each column becomes a separate slicer.
  4. Click a button to filter. Hold Ctrl and click, or switch on the Multi-Select toggle at the top of the slicer (Alt+S), to choose several values.
  5. Click the Clear Filter icon in the slicer corner (Alt+C) to show everything again.

Right-click a slicer and choose Slicer Settings to rename its caption, sort its buttons, or hide items that have no data. The Slicer ribbon tab lets you set the number of button columns, the button height and a colour style. You can move a slicer to any sheet in the workbook by cutting and pasting it; it keeps filtering the Table it was born from, so the slicer and the Table do not have to sit on the same sheet.

One limit to remember: a slicer built from a Table connects to that Table only. It cannot control a second Table or a pivot table. If you need one set of buttons to drive several reports, build those reports as pivot tables and connect them through Report Connections, as shown in the pivot table slicers lesson.

The Total Row and SUBTOTAL

Tick Table Design > Table Style Options > Total Row (Ctrl+Shift+T) and Excel adds a row under the Table with a total in the last column. Click any cell in that row to get a drop-down of Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var and More Functions.

Look at the formula Excel writes: =SUBTOTAL(109,[Amount]). Function number 109 means “SUM, ignoring hidden and filtered rows”. Because of this, the total updates the moment you filter with the header buttons or a slicer, and it never double-counts. A plain =SUM() would keep adding the hidden rows. The Total Row also stays at the bottom when the Table grows and is excluded from sorting.

Total Row choice Formula written Ignores filtered rows?
Sum =SUBTOTAL(109,[Amount]) Yes
Average =SUBTOTAL(101,[Amount]) Yes
Count =SUBTOTAL(103,[Region]) Yes
Max / Min =SUBTOTAL(104,[Amount]) / =SUBTOTAL(105,[Amount]) Yes
Plain SUM typed by hand =SUM([Amount]) No

The full list of SUBTOTAL function numbers, and the AGGREGATE alternative that also skips errors, is in the SUBTOTAL and AGGREGATE lesson.

Table names and structured references in formulas

Once a Table is named, you can use it anywhere in the workbook. Start typing the name in a formula and Excel suggests the columns.

=SUM(Sales[Amount])                      total of the Amount column
=SUMIFS(Sales[Amount],Sales[Region],"East")   East region only
=COUNTA(Sales[Region])                   number of rows
=SUBTOTAL(109,Sales[Amount])             total of VISIBLE rows only
=Sales[@Amount]*0.18                     same row (inside the Table)
=Sales[[#Headers],[Amount]]              the header cell
=Sales[[#Totals],[Amount]]               the Total Row cell

Structured references adjust automatically when you add rows, rename columns or move the Table. They are also what SUMIFS, XLOOKUP and the dynamic array functions in the SORT, SORTBY, FILTER and UNIQUE lesson work best with, because =FILTER(Sales,Sales[Region]="East") keeps working no matter how many rows arrive.

Convert a Table back to a range

If you need to share the workbook with someone on a very old version, or a feature refuses to run on a Table, click inside it and choose Table Design > Tools > Convert to Range, then Yes. The formatting stays, structured references are rewritten as normal cell addresses, and slicers attached to the Table are deleted. Clear the banded style first with Table Design > Table Styles > Clear if you want plain cells.

Keyboard shortcuts for Tables

Shortcut What it does
Ctrl+T Create a Table from the current region
Ctrl+A Select the Table data; press again to include the header row; a third press selects the whole sheet
Ctrl+Space Select the current Table column data; press again to include its header
Shift+Space Select the current Table row
Alt+Down Arrow (on a header) Open the sort and filter menu
Ctrl+Shift+L Toggle filter buttons on or off
Ctrl+Shift+T Toggle the Total Row
Tab in the last cell Add a new row at the bottom
Alt+S / Alt+C (slicer selected) Multi-Select on or off / Clear the slicer

Worked example

Open the course practice file or type this small data set with headers in A1:D1.

Date Region Product Amount
01-Jul-2026 East Laptop 1,200
03-Jul-2026 West Monitor 350
05-Jul-2026 East Monitor 400
08-Jul-2026 North Laptop 1,150
10-Jul-2026 West Laptop 1,300
12-Jul-2026 East Keyboard 60
  1. Click A1 and press Ctrl+T, confirm the headers and click OK. Name the Table Sales.
  2. Tick Total Row. The Amount total shows 4,460 and the formula is =SUBTOTAL(109,[Amount]).
  3. Choose Insert Slicer, tick Region, click OK. Click East. Three rows remain and the total drops to 1,660.
  4. Click the Product header arrow and choose Sort Z to A. Only the visible East rows are reordered: Monitor, Laptop, Keyboard.
  5. In F2 enter =SUMIFS(Sales[Amount],Sales[Region],"West"). Result: 1,650, unaffected by the slicer because SUMIFS does not ignore hidden rows.
  6. Type a new row under the Table: 15-Jul-2026, East, Monitor, 500. It joins the Table, appears in the East filter and the total becomes 2,160.

Tips and common mistakes

  • No merged cells and exactly one header row. Merged cells stop Ctrl+T, and a two-row header becomes data. Unmerge and flatten the header before converting.
  • Every header must be unique text. Excel renames duplicates to “Column2” and converts numeric headers to text, which breaks lookups keyed on the header.
  • Tables cannot be used in shared workbooks. The legacy Share Workbook feature refuses files that contain Tables. Use co-authoring through OneDrive or SharePoint instead, which supports Tables fully.
  • Do not leave blank rows inside the Table. They sort to the top or bottom and the Total Row counts them in Count.
  • A slicer filters only its own Table. Copying a slicer and pointing it at another Table is not possible; insert a new one from the second Table.
  • Formulas outside the Table that use plain references miss new rows. Replace =SUM(D2:D7) with =SUM(Sales[Amount]).
  • Turn off the AutoCorrect option if calculated columns misbehave. File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type controls “Fill formulas in tables to create calculated columns”.

Errors and how to fix them

Problem Cause Fix
“This operation is not allowed. The operation is attempting to shift cells in a table” Deleting or inserting sheet cells that would cut through the Table Right-click inside the Table and use Delete > Table Rows or Insert > Table Rows Above instead
Insert Slicer is greyed out Excel 2010 or earlier, or the workbook is in .xls compatibility mode Save as .xlsx in Excel 2013 or later
Total Row shows the wrong number after filtering Someone overwrote the Total Row with =SUM() Re-select Sum from the Total Row drop-down to restore SUBTOTAL(109,...)
#REF! in a structured reference A column was deleted or renamed and a formula outside the Table still uses the old name Retype the reference using the new column name
Cannot use Subtotal, Share Workbook or Custom Views These legacy features do not work on Tables Convert to Range for that task, or use a pivot table for subtotals
New row did not join the Table A blank row was left between the Table and the new entry Delete the blank row, or drag the resize handle in the bottom-right corner of the Table down

Practice exercise

  1. Open the practice file, convert the sales data into a Table named SalesData and switch on the Total Row with Sum for Amount and Count for Region.
  2. Insert slicers for Region and Product, place them side by side, and use Multi-Select to show two regions at once. Watch the Total Row change.
  3. Sort the filtered rows by Amount, largest to smallest, using the header button.
  4. In a cell outside the Table, write a structured-reference formula that returns the average Amount for the product “Laptop”. Then add three new rows and confirm the formula updates without editing.
  5. Convert the Table back to a range, note what happens to the slicers and the Total Row, then press Ctrl+Z to restore it.

Key takeaways

  • Ctrl+T turns any clean range into a Table with permanent sort and filter buttons that include new rows automatically.
  • Slicers (Excel 2013+) give one-click, multi-select filtering, but each slicer controls one Table only.
  • The Total Row uses SUBTOTAL(109,...), so totals respect filters; a hand-typed SUM does not.
  • Structured references such as Sales[Amount] replace fragile cell ranges in every formula.
  • Keep one header row, unique text headers, no merged cells and no blank rows.
  • Convert to Range only when a legacy feature demands it; slicers are deleted when you do.

Related lessons

Frequently asked questions

What is the difference between filtering a range and filtering an Excel Table?

The menus are identical, but a Table keeps its filter buttons permanently, includes rows you add later, and lets you add slicers and a Total Row. A filtered range forgets new rows until you reapply the filter and cannot use slicers. Tables also give you structured references, so formulas built on the data do not break when it grows.

Can a slicer filter two Excel Tables at once?

No. A slicer created from a Table is connected to that Table only and the Report Connections option is unavailable. To control several reports from one set of buttons, build them as pivot tables from the same source and connect the slicer to each pivot table through Slicer > Report Connections.

Why does the Total Row change when I filter but my SUM formula does not?

The Total Row uses SUBTOTAL with function number 109, which skips rows hidden by a filter or by slicers. An ordinary SUM adds every cell whether visible or not. If you want a filter-aware total outside the Table, write =SUBTOTAL(109,Sales[Amount]) yourself.

How do I remove a Table but keep my data?

Click inside the Table and choose Table Design > Convert to Range. The data and formatting stay, structured references become normal cell addresses, and any slicers are removed. To drop the banded colours as well, clear the table style before converting.

Do Excel Tables work in shared workbooks?

Not with the legacy Share Workbook feature, which blocks any file that contains a Table. Modern co-authoring in Excel 365 through OneDrive or SharePoint supports Tables, slicers and the Total Row without restriction, so save the file to the cloud and share it from there instead.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems built on Tables and slicers are available at NextGenTemplates.com.

← PreviousAdvanced Filter in Excel: Criteria Range and Unique RecordsAll lessonsSort and FilterNext →Remove Duplicates in Excel and Highlight Duplicate Rows