Tables Basics

Creating a table

Table is a general term, but it also describes a specific Excel functionality. These calculations could for example be called a table, but they are not yet a Table according to Excel.

To create a table first select the corresponding cell range, then click on Home > Format as Table.

You can pick any table style. They all offer the same functionalities and can be changed afterwards.

The cell range can be changed if necessary. If you already selected the correct cell range, click directly on OK.

Main advantages of tables

Easier formulas

The header of a table is recognized automatically and can be used directly in formulas. A formula created this way is much easier to follow.

Formula with table

Formula without table

Automatic sort and filter

A table creates automatically a filter for each column.

This is the same kind of filter which can be created over Home > Sort & Filter > Filter. It is recommended to create a table as soon as a filter is needed. This provides directly all the advantages of a table.

Table Design tab

When selecting a table, an additional Table Design tab is shown on the ribbon.

Table name

Each table automatically gets a name, which is show together with other cell names on the Name Manager. Differently from other kind of names the field Refers to cannot be updated, as this is tied to the table range.

The table name can be changed over Table Design > Properties.

Total Row

You can add a Total Row over Table Design > Table Style Options.

A different operation can be defined for each column on the total row, e.g. sum, average, max.

When applying a filter on one or more columns, then the operations on the total row are only calculated for the filtered values. Here is shown an example with the average of the net unit price and the sum of the total gross price.

All rows

Filtered by year 2022

Download

• Example