What is an Excel Table

In this video, we'll introduce the idea of an Excel Table.

So, what is an Excel Table?

An Excel table is a rectangular range of data that has been defined and named in a particular way.

To illustrate, here I have two rectangular ranges of data. Both ranges contain exactly the same data but neither one has been defined as a table.

Next, I'll convert the range on the right to a proper Table.

By the way, when I say "proper table", I mean Excel Table.

When I click OK, we have a table.

So what are the important differences between a normal range, and an Excel Table?

Well, to start with, all tables have a unique name. You'll see this name in several places.

First, you'll see the name in the name box menu along with any other named ranges and tables.

You can use this menu to navigate directly to the data in a table. Even if a table is in a different worksheet.

Second, you'll see a table name on the Design tab under Table Tools when any part of a table is selected.

Notice this tab disappears if I move the cursor outside the table.

You'll also see table names listed in the Name Manager.

You can open the Name Manager from the Formulas tab of the ribbon. Or you can use the shortcut Ctrl + F3.

Next, notice an Excel table has automatic formatting. By default, we get row striping, and a different header format. 

Table formatting is fully customizable, and you can even switch to None to maintain current formatting.

Tables also have a filter applied by default. You can use this filter to sort and filter data. The filter is optional and you can easily turn it off.

There are 3 key features of Excel Tables.

First, tables automatically expand to handle new data.

If I copy in this data from a second sheet, notice the table expands to include it.

This makes tables ideal as a data source for charts, pivot tables, and other reports where data is frequently updated.

Second, tables provide a special formula syntax called "structured references". Structured references make it easier to write, read, and maintain formulas.

Finally, tables have handy features for working with data in rows and columns.

For example, I can use Shift + Space to select an entire row, and Control + Space to select an entire column.

Then I can simply drag to a new location in the table, and Excel won't complain about overwriting data.

We'll cover all these topics in detail in the course.