Excel Table vs. Excel Range – What’s the Difference?

There is a good amount of confusion around the terms ‘Table’ and ‘Range’, especially among new Excel users.

You will find many tutorials use the terms interchangeably too.

But the two terms have some basic differences that need to be identified.

In this tutorial, we will explain what the terms Table and Range / Named range mean, how to distinguish between them, as well as how you can convert from one form to the other in Excel.

Table of Contents

What is an Excel Range?

Any group of selected cells can be considered as an Excel range.

A range of cells is defined by the reference of the cell that is at the upper left corner and the one at the lower right corner.

For example, the range selected in the image below consists of cells A1 to C7, denoted as A1:C7.

An Excel range does not require cells to be contiguous. You can also add cells to a range that are away from each other.

What is an Excel Named Range?

A named range is simply a range of cells with a name.

The main purpose of using named ranges is to make references to a group of cells more intuitive.

For example, if the name of the following selected range is “Sales”, then you can simply refer to this range by name in formulas (rather than using cell references like B2:B7):

Using Named Range in SUM formula in Excel

To convert a range of cells to a named range, all you need to do is select the range, type the name into the Name Box and press the return key.

You can identify a named range by selecting the range of cells.

If you see a name, instead of a cell reference in the name box, then the range of cells belongs to a Named range.

What is an Excel Table?

An Excel Table is a dynamic range of cells that are pre-formatted and organized.

A table comes with some additional features such as data aggregation, automatic updates, data styling, etc.

You can say that an Excel table is basically an Excel range, but with some added functionality.

Like named ranges, Excel tables help group a set of related cells together, with a given name.

However, they also help users clearly see the grouping through some extra styling.

As Excel is releasing new data analysis features such as Power Query, Power Pivot, and Power BI, Excel Table has become even more important. Since it’s more structured, most of these new functionalities will require you to convert your data/range into an Excel Table.

Also read: XLS vs. XLSX Files – What’s the Difference?

How to Identify a Table in Excel?

You can easily identify a table in Excel thanks to its distinguishable features:

You will find filter arrows next to each column header.

Filter is applied to column headers in Excel table

Column headings remain frozen even as you scroll down the table rows.
The table is enclosed in a distinguishable box.

You will also find the table styled differently from the rest of the worksheet. For example you might find rows of the table styled in alternating colors for easy viewing.
When you click on the table (or select any cell within the table), you should see a Design tab in the main menu.

Design Tab that appears when you select a cell in table

When you click on the Design tab, you should see the name of the table on the left side of the menu ribbon.

What’s the Difference Between an Excel Table and Range?

From the first glance, it is quite easy to differentiate between a table and a range.

Not only do they look different, they are also quite different in the amount of functionality they offer.

Here are some of the differences between an Excel Table and Range:

Cells in an Excel table need to exist as a contiguous collection of cells. Cells in a range, however, don’t necessarily need to be contiguous.
Every column in an Excel table must have a heading (even if you choose to turn the heading row of the table off). Named ranges, on the other hand, have no such compulsion.
Each column header (if displayed) includes filter arrows by default. These let you filter or sort the table as required. To filter or sort a range, you need to explicitly turn the filter on.
New rows added to the table remain a part of the table. However, new rows added to a range or are not implicitly part of the original range.
In tables, you can easily add aggregation functions (like sum, average, etc.) for each column without the need to write any formulas. With ranges, you need to explicitly add whatever formulas you need to apply.

In order to make formulas easier to read, cells in a table can be referenced using a shorthand (also known as a structured reference). This means that instead of specifying cell references in a formula (as in ranges), you can use a shorthand as follows:

=[@Qty]*[@Sales]

Moreover, in a table, typing the formula for one row is enough. The formula gets automatically copied to the rest of the rows in the table. In a range or named range, however, you need to use the fill handle to copy a formula down to other rows in a column.
Adding a new row to the bottom of a table automatically copies formulae to the new row. With ranges, however, you need to use the fill handle to copy the formula every time you insert a new row.
Pivot tables and charts that are based on a table get automatically updated with the table. This is not the case with cell ranges.

How to Convert a Range to a Table

Converting an Excel range to a table is really easy.

Let’s say you have the following range of cells and you want to convert it to a table:

Here are the steps that you need to follow to convert the range into a table:

Select the range or click on any cell in your range.
From the Home tab, click on ‘Format as Table’ (under the Styles group).

You should now see a dropdown menu with a number of styling options. Select the styling option that you want to apply to your table. We selected the option highlighted below:

This will open the ‘Format as Table’ dialog box.
Make sure that the range displayed under ‘where is the data for your table’ is correct.

Confirm range in Format as Table dialog box

You should see a dashed box around the cells that will be part of your table.

If your dataset has headers, ensure that the checkbox next to ‘My table has headers’ is checked.

Click OK.

Here’s what your table should look like if you’ve followed the above steps:

Excel range is now converted to Excel Table

Note: Alternatively, you could use the keyboard shortcut CTRL+T in place of steps 2 and 3.

How to Convert a Table to Range

It is also possible to reverse the conversion, in other words, convert a table back into a range of cells. Here are the steps that you need to follow:

Select any cell in your table.
You should see a new ribbon titled ‘Table Tools’ in the main menu. Select the Design tab under this menu.

In the Tools group, select the ‘Convert to Range’ button.

You will be asked to confirm if you want to convert the table to a normal range. Click Yes.

Alternatively, you could simply right-click on the table and select Table->Convert to Range from the context menu that appears.

Right-click and then click on Convert to Range

You will now find that the table features (like filter arrows and structured references in all the formulas) are no longer there since it’s now just a regular range of cells. Structured references have all turned back into regular cell references.

In this tutorial, we explained with examples how ranges and named ranges differ from tables.

To conclude, a table can be considered as a named range, but with some added functionality, like styling, easy aggregations, structured references, and more.

We hope we have been successful in clearing any confusion you might have had about ranges, named ranges, and tables.

Other Excel Tutorials you may also like: