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.


Download Practice Workbook

You can download the practice workbook from here.


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).

defining range to describe excel table vs range


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.

creating named range to describe excel table vs range

  • 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 has usually a row of text headings that describe the contents of each column. Here is an example of a table.

defining table to describe excel table vs range


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.

changing formatting to describe excel table vs range

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.

changing range formatting to describe excel table vs range

  •  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.


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.

filtering table to describe excel table vs range

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

Home > Editing > Sort & Filter > Filter

filtering range to describe excel table vs range


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.

scrolling down the table to describe excel table vs range

  • But ranges do not have this advantage.


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.

expanding the table to describe excel table vs range

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


6. Using Formula

We use a formula to find out another value from the existing data. Table and Range has 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.

using formula in the table to describe excel table vs range

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

using formula in the range to describe excel table vs range

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


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.


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.


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.


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.

Tags:

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo