If you aren't changing most of your data tables into Excel Tables, you're missing out on some major benefits. Converting data to Excel Tables is not only easy and quick to implement, but it will save you tons of time. Let me give you five reasons why.
Video Tutorial
Watch on YouTube & Subscribe to our Channel
Downloads
But first, how do you make an Excel Table? Starting with any cell in your data set selected, just click the Table button on the Insert tab.
Or instead, use the keyboard shortcut Ctrl + T.
Now, here are 5 reasons that Excel Tables rock:
#1. Better Formatting
The rows are banded with alternating colors making it easier for the eye to track along each row.
You can change the format colors and table style with the click of a button from the library of preset formats that Excel offers. These options are on the Table Design tab.
#2. Filter Drop-Down Menus Are Applied
When you create a table, each column automatically gets filter drop-down menus. This allows you to quickly filter and sort your data.
I've got some great keyboard shortcuts that you can use for drop-down menus. Check out this tutorial for seven of them:
7 Keyboard Shortcuts for the Filter Drop Down Menus in Excel (excelcampus.com)
#3. Automatic Formulas
If you add a formula to the right of the table, Excel will automatically extend the table to the right to include it. In addition, it will copy that formula all the way down the new column, applying the formula to the other rows in the table.
There may be times when you don't want Excel Tables to auto-fill formulas. If that's the case, you can learn how to disable that feature here: How to Prevent or Disable Auto Fill in Table Formulas.
You can also turn off structured references as well: How to Turn OFF Structured References in Excel Table Formulas.
#4. Total Row
For every Excel Table, you have the option of turning on the Total Row feature. This option is found on the Table Design tab. The keyboard shortcut for adding the Total Row is Ctrl + Shift + T.
This will add a row that gives you a total for the rightmost column in the table. There are several options to choose from in the drop-down menu for different metrics.
It's good to know how the total is calculated in the Total Row. It uses the SUBTOTAL function, which I explain here: The SUBTOTAL Function for Excel Tables Total Row Explained.
#5. Table Automatically Extends
Finally, if you add data to the bottom of the table, Excel will automatically extend the table to include the new rows.
Excel Tables are the best! Want to learn more about them? I've got everything you need to get started in this tutorial:
Excel Tables Tutorial Video – Beginner's Guide for Windows & Mac
I hope these tips were helpful! Leave a question or comment below with the reasons you love Excel Tables!
A drawback (or at least caveat) to tables I recently learned is that when you have multiple tables on the same sheet, the buttons in the ribbon to clear the Filter do not apply to all tables on the sheet, like they would clear all filters if it was only a cell array. Each table has to be individually cleared. Easy to forget, and it resulted in me mistakenly omitting data. Is there a way to clear all table filters on a sheet en masse? I am not good with VBA yet.
Thank you.
Brian
Jon, I love tables and use them in all of my VBA projects.
Another reason to use tables is each column is now a named range in VBA and you can clear the column range, add formulas, etc.
My major is research of forecasting methods and testing. There is no regular workflow. But EXCEL provides an easy tool for my work!