What is the Difference between a Table and a Range in Excel?

With this post, we shall be starting our Excel Table series. Today’s post, in particular, will give you an overview of the topic at hand and walk you through the basics. Do let us know in the comments what in particular would you like to learn about the Excel Tables next!

This is the Example of an Excel table.

An Excel Table

A table is a rectangular range of data; this table (in the image above) is rectangular in size. It has usually a row of text headings that describe the contents of each column.

Also, notice how the Contextual Design Tab is open. Here you will get all the tools to format, add and delete elements from a table. Just, for example, click on this tab (Design contextual tab) and click on the Total Row check box. You see there is a new row appearing under the table and intelligently shows the Sum of the “Sales” column.

Adding Total Row to an Excel Table

Select this cell (where the sum is showing), and you see a drop-down that appears right after the cell.

Total Row in Excel Table

So, there are many options available to perform general calculations on the numbers in that column. It is to be understood that sum here is calculated by default. For functions not in the list, there are “more functions” to explore.

Creating a table from a range

Creating a table is just one click away from your decision to make a range into a table. Just when you have a thought that you want to make a table from a range of data, just click and it becomes a table. It is that easy. And this is why Excel is widely used, for its simplicity.

Convert a range to a table

You see this range has a heading row that describes the columns of this range. I want to turn this range into a table. Do keep in mind that there are no blank rows in your range. If present, delete them before beginning.  Select any cell within the range. Then click on the “Table” command in the Insert Ribbon or you can press CTRL+T on your keyboard.

Convert a table to a range Image 2

“Create Table” dialog box appears, there is a checkbox in the dialog box, “My table has headers” checkbox. If it is checked, then Excel is assuming that the first row of the range is the table’s headers. If it is not ticked, then Excel takes the first row as data and creates default headings for the table. Our range has headings, so I tick it and click OK.

Excel Table Image 2

You see the range has turned into a table. And your data has got a professional look.

You even can create a table from a blank range. Just select the range (blank range) and click CTRL + T on your keyboard. Excel has got that my table has no headings. I just click Okay.

Creating Excel Table from Blank Range

You see a table has been created with default column headings, column1, column2, column3, and column4. You can change the headings. Just select any heading and type something and click Enter.

Default Headings of Excel Tables

Differences between a standard range and an Excel Table

Here are some differences between a standard range and an Excel table:

  1. Activating any cell in the table gives you access to the “Tables Tools” Design contextual tab on the Ribbon. Just click on any cell in the Table and see there is a contextual tab on the Ribbon. Using this contextual tab, you can easily format the table data. When you click or select a range this design tab doesn’t show up.
  2. Each column header contains a Filter button. Clicking on this drop-down button, you can sort the data or filter the data to display only rows that meet up certain criteria. Say I may want to sort according to the largest amount of Sales. Just click on the Sales Filter button, and then select Largest to Smallest option. There are no such options with a standard range, you will have to manually filter it.
  3. You can use the slicer to Filter data easily in a table. But there is no slicer available for a standard range.
  4. When you scroll down, table headers replace the regular column headers and become visible all the time. But a standard range does not show the headers anymore and makes it difficult for the users to read data.
  5. Within a table in the lower right corner of the lower right cell contains a small control that you can click and drag to extend the table’s size, either horizontally or vertically.
  6. If you enter a formula in a cell in the column of a table, the formula is copied to all the cells in that column.
  7. Table supports structured references which we shall discuss in great detail in a later post.

Difference between an Excel Table and a range

So you see these are what tables are and they have the aforementioned advantages over a regular range of data in Excel.  Stay tuned to learn more about tables!

Read More…

Excel Pivot Table Auto Grouping by Date, Time, Month, and Range!

How to Multiply Columns, Cells, Rows, & Numbers in Excel

How to Compare Two Columns in Excel For Finding Differences

Excel Table Formatting Tips – Change the Look of the Table

How to Use Slicers to Filter a Table in Excel 2013



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