9 (+1) Benefits of Using an Excel Table

Here’s something that many Excel users don’t know:

You can create Tables within an Excel worksheet.

No, I’m not talking about entering any kind of data into grid of cells, slapping some headings on it, and applying some cool formatting.  That’s not a Table.

I’m talking about a Table that Microsoft Excel identifies as a separate object within your spreadsheet, and can be easily formatted or updated.

Here’s what an example Table looks like:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

At first glance, it looks just like any other range of data in Microsoft Excel.

But there are a few hidden features beneath the surface, which we’ll go through in this post.

And these are features that could massively streamline your Excel experience.

We’ll cover off most of the main features throughout the post, but we’ll also cover how to create an Excel Table and the different types of benefits you get from using Tables.

Why use an Excel Table?

There are three main reasons why you should be implementing Tables in your Excel workbooks:

  1. You want a consistent, uniform set of data
  2. Your data will be updated over time (additional rows, columns over time)
  3. You want a simple way to professionally format your work

In this post, you’ll learn several main benefits for each of these reasons.

How to Create an Excel Table

There are two ways to create a Table in Excel:

  • Either use a current range of data and convert it into a Table, or
  • Create a new blank Table and populate the data afterwards.

Using a current data range

If you already have a data range that you want to convert into an Excel Table, simply highlight the data and use the shortcut Ctrl + T.  A small dialog box will appear, asking you to confirm the range of data for the Table (don’t worry, you can change the size of the Table later!).  If your data range already has headers, make sure you tick the “My table has headers” checkbox.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Creating a blank Excel Table

If you don’t have any data yet, you can still create a Table.

Select the cells that you want to be included in your Table, then use the Ctrl + T shortcut.

In the dialog box that appears, select if your data has headers or not.  If you leave that checkbox unselected, an extra row will be added to your Table.  Hit OK, and you’re done!

Note that Microsoft Excel will automatically create column headings for your table, since all column headings in Tables need to be populated.  Excel will use ‘Column1’, ‘Column2’ etc if any headings are blank.

Data Entry Benefits

Since Excel Tables have a defined list of columns, they become seriously useful when you want to enter data into them.

Here are some of their benefits that relate to data entry.

1. Tables automatically expand when you enter data in the next row/column

One of the biggest visual differences you’ll notice when you’re working with Tables is the L-shaped handle on the bottom right.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

You can resize any Excel Table by dragging this handle around.  And this isn’t only for adding new rows and columns; you can remove data from the Table as well.

Alternatively, if you start typing in the next row or column, Excel will automatically resize the Table to include the new information.

This means that your Table formatting will be maintained, any columns with formulas will be copied down, and any charts or PivotTables will also update as well to cover the whole data (although you will have to manually refresh any PivotTables – use the Alt + F5 shortcut).

Get more PivotTable basics with The 7 Minute Excel PivotTable Tutorial.

If you don’t want the Table to automatically expand, hit the Undo shortcut (Ctrl + Z) and the Table range will go back to it’s normal size.  The data you just entered will still be there.

2. Make data entry ultra-simple with Excel Forms

You can even choose to add new or edit existing records in your Table by using a simple data entry form.

These forms will look just like what you might see on a website, and will make your data entry much more simple and less prone for errors.

Here’s what a form looks like for the Salesperson Table example we saw earlier:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Just enter the details in the relevant boxes, click on “New” and it will automatically be added to your Table!

You can populate your Table purely by using this Form; if you don’t want to enter data directly into the spreadsheet then you don’t have to!

Forms were included as a normal menu option in early versions of Excel, but have been phased out in recent versions.  Fortunately, you can still find the option if you add it to the Quick Access Toolbar at the top of your Excel Window.

How to add the Form option to your Quick Access Toolbar

Forms are still accessible in Excel, but only via the Quick Access Toolbar.

If you’ve never heard about this toolbar, it’s seriously handy and is a great place to put your favourite shortcuts.  It is always visible at the top-left of your Excel window.

Customising the Quick Access Toolbar is just one of the tips in the FREE eBook “10 Simple Microsoft Excel Tips to Save You Time”.  Get the free eBook now!

To change the list of commands in the toolbar, on the right side of it there will be a drop-down menu.  In that menu, select “More Commands…”:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

The next dialog box that appears will contain a list of functions on the left, and the current Quick Access Toolbar commands on the right.  In the “Choose commands from:” menu, select All Commands then scroll down until you see the Form… command (hint: you can press “F” on your keyboard to automatically scroll to where the “F” items are).  Click on “Add > >” to add it to your QAT.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

That’s it!  Now you can select the “Form…” button at any time when using Excel, which will open up a Form depending on which cell is selected.

Using Forms for your Tables

Using a Form is simple.

Just select any cell in your Table and click on the Form button in your Quick Access Toolbar.

Excel will automatically identify the field names, and present you with a simple layout to enter data.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

By default, Excel will allow you to edit the row that you selected at the time, but you can switch to entering new data by clicking on the “New” button.

Hit Tab to cycle to the next item, or Shift + Tab to go to the previous item.

Press Enter to add the data to the table, and start entering in a new row.

3. Add new rows by pressing Tab

In typical Excel usage, pressing the Tab key will move the selected cell across to the right.

But in an Excel Table, if you’re currently on the last column it will move to the first cell on the next row.

And if you’re on the last row, then Excel will automatically create a new row for you.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

So, as long as you’re within the Table, the Tab key will ensure that you keep working within that Table.

If you use tables within Microsoft Word, this functionality is very similar.

4. Column headers are always visible

As we covered earlier, all columns in a Table need to have a heading.  That’s just one of Microsoft’s requirements with Tables.

One handy benefit of having these column names always defined, is that when you scroll down through your Excel workbook, these column headings will always be visible, even without using the Freeze Panes function which locks rows and columns to the side of the screen.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

In this screenshot, you would normally expect to see A,B,C,D,E,F at the top of the sheet.  But since we are looking at a Table, they are updated to show the column headers instead.

For the column headings to appear, all you need to do is select any cell within the Table.  If you click on any cell outside of the table, the standard column letters will be displayed just like usual.

Data Analysis Benefits

Reviewing and analysing the data inside your Table is straightforward and quick.

5. Displaying Totals is ridiculously easy

If you are selecting any part of an Excel Table, the Excel Ribbon menu will show a “Table Tools” section:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

The keyboard shortcut to get to this section is Alt-J-T, which isn’t a useful shortcut since it requires both hands on the keyboard

There are plenty of straightforward options in this menu, but the checkbox options in the Style Options section are particularly useful:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Select the “Total Row” to add an automated Totals Row.

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Three nice features about the Totals row:

  1. If you filter the Table, the Totals row will only display values based on the visible data.
  2. Totals can be calculated in many different ways.  Amongst other options, you can sum up values, count the number of populated cells, or present the minimum or maximum from that column.
    9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com
  3. Totals can be calculated on any column in the Table – just select the totals cell for that column and choose how you’d like to calculate!
    9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

6. Formulas stay consistent across all of your data

If you want a range of data, and want to add a column which is a formula, most users follow this standard method:

  1. Type your formula in the first row,
  2. Check that it works correctly, then
  3. Copy that formula down to all other rows in the data range

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Even with large sets of data this doesn’t take that long, and if you don’t plan on making any changes to your formula then there’s nothing wrong with this option at all.

But if you find yourself altering the formula a few times, it can be painful ensuring that all rows in your data are using the latest version of your formula.  If you aren’t careful, you might forget to keep the formulas consistent across your whole data set.

That could lead to incorrect results, which could be seriously embarrassing.

Fortunately, using Tables solve this issue.

How Tables Deal With Formulas

If you enter a formula in a Table, Excel will automatically copy the same formula to all other cells in that column.  And any updates you make will be automatically applied as well!

Let’s look at an example – here is a simple table which contains some products that were sold:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

I’ve created a Subtotal column, which I want to be the product of Unit Cost and Units Sold.  For the first row, it should be $1.75 * 25 = $43.75.

If I start creating the formula in the Subtotal column and I select cells using the keyboard arrow keys or the mouse, this is what appears:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

It doesn’t reference the cells (which were D4 and E4, for example) but instead uses the column headers from the Table.

Better yet, Excel automatically applies the same formula to all other rows in the Table when I press Enter:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Let’s say I want to include a 5% commission that reduces the amount in the Subtotal field.  This isn’t too hard to alter; just multiply the formula by 0.95.  As you might guess, if you edit any row in the Subtotal column, the whole field will update:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Never fear having an inconsistent formula again!

Note: you can use absolute cell references such as D4 and E4, but that might cause issues if you move the Table around.

7. Graphs/charts will automatically update to include all of your data

Formulas aren’t the only thing that stay consistent – charts in Excel will add new Table data into your carefully prepared graphs as well!

Let’s have a look at the same example – I’ve created a simple bar chart which shows the subtotal per product ID:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

If I add another row at the bottom of the Table, the chart will include the additional item:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Presentation Benefits

8. Formatting will be consistent for all records within a Table

The automatic Table formatting in Excel is a huge time saver, and will quickly ensure that your data is well-presented.

By default, Excel uses a “banded rows” style which shades every other row.  And if you filter the data, that banded style will still be there to ensure your data is easy on the eyes.

Of course, you can still make changes to individual cells if you like.  For example, if you highlight or bold specific cells then choose to apply a new Table Style, that specific highlighting will still be there:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Changing Excel Styles is easy – just go to the Table Tools menu in the Excel Ribbon and select from any of the default Styles:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

Want to be faster at formatting in Excel?  Read the Shortcut Roundup here.

9. You can revert a Table to a normal data range (and still keep your formatting and layout!)

By now you can see how feature-rich Tables are in Excel.

Tables are especially handy in speeding up data entry and ensuring consistent analysis, but if you’re presenting the data to a customer or your boss it might not be necessary to keep those features.

Use the Convert to Range option in the Table Tools settings:

9 (+1) Benefits of using an Excel Table | ExcelEfficiency.com

The formatting and layout of the Table will remain exactly the same, but you won’t be able to use the Table-specific features.

But if you want to turn it back into a Table again, you can select the range of cells, hit Ctrl + T and click OK.  You’re back in action!

Want more ways to idiot-proof your spreadsheets?  Get the 9 Quick Ways to Idiot-Proof your Excel Spreadsheets checklist.

9+1. PivotTable output will appear as a Table

OK so this isn’t exactly a benefit, but it’s worth mentioning that if you review data from a PivotTable, it will appear in a Table.

Any time you double click on a value in a PivotTable, or right-click and select “Show Details”, the relevant data will appear in a new sheet.  This data will automatically be formatted as an Excel Table.

Get more PivotTable basics with The 7 Minute Excel PivotTable Tutorial.

Conclusion

There you have it – plenty of amazing benefits of using Tables in Excel.  They’re a smart and simple way to ensure your data will stay consistent and organised, and will generally be quicker and easier to use than a normal data range.

What do you use Tables for in Excel?

You may also like...

1 Response

  1. Rob says:

    Thanks for this post. Had no idea you could connect a form to a data table. I’ve found tables to be very useful for storing and reading data, but they can cause issues if formulas outside of the table reference specific parts of the data — everything defaults to absolute references. I usually convert it to a regular range at that point.

Leave a Reply

Your email address will not be published. Required fields are marked *