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.
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 which 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.
Select this cell (where the sum is showing), and you see a drop down appears right after the cell.
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 from.
Table of Contents
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.
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.
“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.
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.
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.
Differences between a standard range and an Excel Table
Here are some differences between a standard range and an Excel table:
- 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.
- 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.
- You can use the slicer to Filter data easily in a table. But there is no slicer available for a standard range.
- When you scroll down, table headers replaces the regular column headers and becomes visible all the time. But a standard range does not show the headers anymore and make it difficult for the users to read data.
- 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.
- 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.
- Table supports structured references which we shall discuss in great details in a later post.
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!