Excel Table vs. Range: What Is the Difference?

There is a great deal of confoundment regarding Table and Range in Excel. Especially, the new Excel users often mistake a table for a range and vice versa. So, in this article, we will talk about Excel Table vs. Range.

What Is Range in Excel?

An Excel Range is a set of two or more cells. A Range is represented by the combination of the reference of the first cell of the selection and the last cell of the selection. In the following example, the range is (B4:D10).

What Is Excel Named Range?

You can refer to a set of cells by name rather than by range using Excel’s “Named Range” function. It could be an entire column, row, or simply a few specific cells. Any operation on those cells can be performed after the designated range has been defined by invoking the name of the identified range.

In order to create a named range. Follow these steps.

Steps:

• Firstly, choose the cells that you want to make a named range of.
• In this case, we select the range (D5:D10).
• Then, go to the Formulas tab.
• From the Defined Names group, select the command Define Name.

• As a result, a dialogue box named “New Name” will appear on the screen.
• Set a name in the Name box.
• Then, set the selected range from the Refers to option.
• Finally, click OK.

• Consequently, we will have a range of data with a named range.

What Is Table in Excel?

A table is a rectangular range of data. The table is defined and named in a distinctive way. This table (in the image below) is rectangular in size. It usually has a row of text headings that describe the contents of each column. Here is an example of a table.

Difference Between Table and Range

There are many differences between table and range. Some are obvious, and some are subtle. We will discuss some of the key differences between those two in this section. Follow the points below to get familiar with Excel table vs. range.

1. Changing Formatting

Formatting a cell is a key aspect of presenting data in Excel. There is an obvious difference between tables and ranges regarding formatting cells.

• One can change the format of a table with ease. You can choose any cell in the table, and the Table Design tab will open up. From there, you will choose the Table Style Options to make any changes to the table.

Or you could choose the Quick Style option from the Table Design tab to format your table according to the preset styles available.

• But in the case of a range, you have to format the range manually. You will have to right-click any cell from the range and then select the Format Cells.

•  Then, from the Format Cells dialogue box, you will have to change the Number, Alignment, Font, Border, Fill, and Protection for each individual cell.

 Or you could do it by selecting those from the ribbon as well.

Read More: How to Make Excel Tables Look Good

2. Filtering

Filtering data is a good way to show the data that we desire to present in front of our clients. Table and range use different methods to filter data.

• Table headers have filter buttons. The small box at the top right corner of the header row of the table lets you filter the data.

• Whereas, in the case of filtering range, you will have to follow the steps below:

Home > Editing > Sort & Filter > Filter

Read More: Does TABLE Function Exist in Excel?

3. Slicer Command

The Slicer command allows users to filter data from the table.

• In the case of Table, you could use the Slicer command to filter a portion of data.

• But the range does not have this feature.

4. Headers

Headers are important to keep track of whether one is entering the data in the right column.

• Table headers replace regular column headings when you scroll down.

• But ranges do not have this advantage.

Read More: Navigating Excel Table

5. Expansion

We often need to add another set of data or columns with our existing data. Table and Range handle it in different ways.

• If you want to add a new dataset or column to your table, it automatically becomes a part of the table. For example, if we want to add “Sales” columns in the table. We will just have to write it beside the existing headers and hit Enter.

And we will see that Excel has included it in the table.

• The range does not expand that easily. We will have to manually customize the column.

Read More: How to Convert Table to List in Excel

6. Using Formula

We use a formula to find out another value from the existing data. Table and Range have different ways to deal with formulas.

• In the table, if you use a formula in a cell, the formula will automatically be copied to all the cells in that column. For example, if we try to find the revenue of the sales, we will just have to write the formula and hit Enter.

Excel will automatically fill the rest of the cells.

• In the case of ranges, we will have to write the formula first.

• Then, we will have to move the cursor down manually to fill the rest of the cells.

Read More: How to Make a Comparison Table in Excel

7. Structured Reference

For referencing Excel Tables, a structured reference is a unique syntax. Structured references in formulas function similarly to conventional cell references, but they are simpler to read and comprehend.

• Table supports Structured Reference.
• Range does not support this feature.

Creating Table from Range

In this instance, we will create a table from a range.

Steps:

• Firstly, select any cell on the dataset.
• Then, go to the Insert tab in the ribbon.
• Finally, select the Table command.
• Consequently, the Create Table prompt will appear.

• From the prompt, select the dataset as the table range.
• Then, click OK.

• As a result, we will have our data transformed into a table.

Read More: How to Convert Range to Table in Excel

Converting Table into Range

In this instance, we will convert a table into a range.

Steps:

• Right-click on any of the cells in the table.
• Then, from the options available, select Table.
• Finally, from the outstretched options, select Convert to Range.
• As a result, a prompt will appear.

• Click Yes from the prompt.

• As a result, we will have a range derived from the table.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Excel users find it difficult to differentiate between tables and ranges. They often use it interchangeably. After going through this article, they will have a clear understanding of Excel table vs. range.

Related Articles

• Table Name in Excel: All You Need to Know
• How to Insert Floating Table in Excel
• How to Create a Table Array in Excel
• How to Provide Table Reference in Another Sheet in Excel
• Types of Tables in Excel: A Complete Overview

<< Go Back to Excel Table | Learn Excel