The Benefits Of Tables In Over Data Ranges In Excel

Here are several benefits of using tables over data ranges in Excel:

You can download the workbook here to follow along. There are two tabs: one with the original data range and one converted to a table.

Here is a data range A1:H110. It is set up in the proper format – no blank rows or columns, and it is bordered by either a blank row or column, or the column and row indicators:

Table1

To convert this to a table, click anywhere in the data range and use the keyboard shortct Ctrl + T. This will bring up a dialog box identifying the range with a checkbox for headers:

Table2

Click OK and the data range is now converted into a Table. As long as you have a cell of the table selected, a contextual tab appears and provides several options available with the table:

Table3

There is also a section of Table Styles. Click the arrow at the bottom right corner and a broad list of styles appears. Hover over any of these and see how it changes the style of the table. Click on the one you prefer and it will change the look of the table:

Table4

From the Design tab of the contextual menu, you can choose any of the following options:

Header Row – this will identify the headers of the table (default)
Total Row – this will add a total row to the bottom of the table
Banded Rows – this will alternate the colors of the rows of the table (default)
First Column – this will bold the data in the first column of the table
Last Column – this will bold the data in the last column of the table
Banded Columns – this will alternate the colors of the columns of the table
Filter Button – this will add the Sort & Filter drop downs to the header row (default)
(Note: if you uncheck the Header Row option, your headers are not lost. Just check it again and they will reappear)

Some benefits of using a Table in Excel:
– If you add a column adjacent to the table, it will automatically format it and include it in the table range
– If you add rows the to table, Excel will automatically enter any formulas that are in that column from above
– If you make a change to a formula, it will change all the other formulas in that column automatically

If you add a total row to the bottom of the table, you can choose from any function Excel has available to calculate the data of that column:

Table5

Table6

If you have any cell selected within the table and you scroll down, Excel will replace the column letters with the headers from the table so you can easily see what column you are in:

Table7

There are many other benefits of using Tables in Excel, but these are the most common and useful ones. Go through the various fields of the Design contextual tab and see what other features there are.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, ,

Trackbacks/Pingbacks

  1. How To Create Dynamic Charts Using Filtered Tables In Excel | Excel Bytes - July 21, 2015

    […] I want to create a chart and have it automatically modify as I filter my data. The first think that I want to do is convert my data range into an Excel Table. There are many benefits of using tables rather than data ranges. You can view my post on the benefits of Tables here. […]

Leave a Reply