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.
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:
#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:
I hope these tips were helpful! Leave a question or comment below with the reasons you love Excel Tables!