Tables, slicers and timelines

A table is a block of data with a name, a header row that names its columns, and a style. Unlike a formatted range, a table knows where its columns are and grows as you add data, so formulas written against it keep covering all of it.

Create a table

1. Select the data, including its header row. If you select a single cell, the table covers all the data on the sheet.
2. Open Format as table in one of these ways:
   • Click the table button in Home › Styles or Insert › Tables (its tooltip is Format the selection as a table).
   • Choose Format as table from the More menu in the title bar.
3. Check the options:

4. Click Create.

The status bar says, for example, Sales created over A1:D20. Its columns can now be named in formulas — Sales[Region].

Headings are tidied as the table is made. A blank heading becomes Column followed by its position, and a heading that repeats an earlier one gets a number, such as Amount2, so every column has a unique name.

If the table can’t be made, the dialog stays open and says why:

The Table tab

While the current cell is inside a table, the Table tab appears on the ribbon.

Rename a table

1. Click a cell in the table.
2. Click the table’s name in Table › Properties.
3. In Name, type the new name. The dialog tells you how many formulas refer to the table by name; they are rewritten to match.
4. Click Rename.

The status bar confirms the new name and how many formulas were updated. The same naming rules apply as when you create a table; if the name breaks them, the dialog stays open and says why, or says ”…” is taken. when another table or a defined name already uses it.

Table styles

Open Table › Style › Style and choose one of: Light1, Light2, Light3, Light9, Light10, Light11, Medium1 to Medium7, Dark1, Dark2 or Dark3. New tables use Medium2.

Styles are drawn with the workbook’s theme colours, so changing the theme recolours every table. Formatting you apply to cells yourself — a fill on one row, say — is drawn over the table style. The style’s name is saved in the file, so Excel shows its own version of the same style.

The totals row

1. Click a cell in the table.
2. Click Totals in Table › Style options.

A row is added under the table. Its first cell reads Total, and each column of numbers gets a sum. Columns of text are left empty.

To change what a column shows:

1. Click Table › Style › Totals….
2. For each column, choose None, Average, Count, Count numbers, Max, Min, Sum, StdDev or Var.
3. Click Close.

Count counts every non-empty cell; Count numbers counts only numbers. The totals row uses the SUBTOTAL function, so rows hidden by a filter or slicer are left out of the totals.

Tables grow as you type

• Type in the row directly under a table and the table grows to include that row. If the table has a totals row, the new row goes above it and the totals move down.
• Type in the column directly to the right of a table and the table grows to include that column.

Formulas that refer to the table by name, PivotTables built on it, and queries that read it all cover the new data.

Calculated columns

When you type a formula into a table column whose other cells are empty, or already hold the same formula, it becomes the column’s formula. New rows that join the table get it filled in. A formula typed into a column that holds other values is left as a one-off.

Refer to a table in formulas

Formulas can name a table and its columns instead of cell addresses:

=SUM(Sales[Amount])
=[@Price]*[@Quantity]
=Sales[[#Totals],[Amount]]

These references follow the table: if you insert a column to the left of Amount, Sales[Amount] still means the amounts. See Writing formulas for every form.

Convert a table to a range

1. Click a cell in the table.
2. Click Table › Properties › To range.
3. Read the warning in Convert table to a range and click Convert.

The data and its formatting stay where they are. The table’s name, its automatic growth and its style options go. The totals row’s formulas are rewritten to point at the cells directly. Any other formula that names the table will show #NAME?; the dialog and the status bar say how many there are.

Slicers

A slicer is a panel of buttons that filters a table by one column. Unlike a filter hidden in a menu, it shows on the sheet what is being filtered.

Add a slicer

1. Click a cell in the table.
2. Open Table › Filter › Slicer and choose a column.

The slicer appears to the right of the table, titled with the column’s name, with one button for each value in the order the values first appear.

Filter with a slicer

The rows that don’t match are hidden, and the status bar says how many. The filter icon’s tooltip says how many values are selected, for example Clear this filter — 2 of 5 selected, or Nothing is filtered out.

When a table has several slicers, they combine. A button whose value has no rows left once the other slicers are applied is greyed out, and its tooltip says so.

Messages you may see: There is already a slicer on Table[Column], or Table has no column called name.

Timelines

A timeline filters a table by a span of dates — “March to June” — rather than a set of values.

Add a timeline

1. Click a cell in a table that has a date column. A date column holds numbers with a date format; text that looks like dates doesn’t count.
2. Open Table › Filter › Timeline and choose the column.

The timeline appears to the right of the table, titled with the column’s name. The status bar says which grain it chose to suit the span of dates, for example Date: months. Drag across the axis to pick a span.

Filter with a timeline

1. Drag across the bars on the timeline’s axis, from the first period you want to the last.

Rows outside the span are hidden. The line under the title shows the span, such as Jan 2026 – Mar 2026, or All periods when nothing is filtered. Each bar’s height shows how many rows fall in that period.

Timelines and slicers on the same table work together.