Tables might be the best feature in Excel that you aren't yet using. It's quick to create a table in Excel. With just a couple of clicks (or a single keyboard shortcut), you can convert your flat data into a data table with a number of benefits.
The advantages of an Excel table include all of the following:
- Quick Styles. Add color, banded rows, and header styles with just one click to style your data.
- Table Names. Give a table a name to make it easier to reference in other formulas.
- Cleaner Formulas. Excel Formulas are much easier to read and write when working in tables.
- Auto Expand. Add a new row or column to your data, and the Excel table automatically updates to include the new cells.
Filters & Subtotals. Automatically add filter buttons and subtotals that adapt as you filter your data.
In this tutorial, I'll teach you to use tables (also called data tables) in Microsoft Excel. You'll discover how to use all of these features and master working with data tables. Let's get started learning all about MS Excel tables.
What is a Table in Microsoft Excel?
A table is a powerful feature to group your data together in Excel. Think of a table as a specific set of rows and columns in a spreadsheet. You can have multiple tables on the same sheet.
You might think that your data in an Excel spreadsheet is already in a table, simply because it's in rows and columns and all together. However, your data isn't in a true "table" unless you've used the specific Excel data table feature.
In the screenshot above, I've converted a standard set of data to a table in Excel. The obvious change is that the data was styled, but there's so much power inside this feature.
Tables make it easier to work with data in Microsoft Excel, and there's no real reason not to use them. Let's learn how to convert your data to tables and reap the benefits.
How To Make a Table in Excel Quickly (Watch & Learn)
The screencast below is a guided tour to convert your flat data into an Excel table. I'll teach you the keyboard shortcut as well as the one-click option to convert your data to tables. Then, you'll learn how to use all the features that make MS Excel tables so powerful.
If you want to learn more, keep reading the tutorial below for an illustrated guide to Excel tables.
To follow along with this tutorial, you can use the sample data I've included for free in this tutorial. It's a simple spreadsheet with example data you can use to convert to a table in Excel.
How to Convert Data to a Table in Excel
Here's how to quickly create a table in Excel:
Start off by clicking inside a set of data in your spreadsheet. You can click anywhere in a set of data before converting it to a table.
Now, you have two choices for how to convert your flat, ordinary data to a table:
- Use the keyboard shortcut, Ctrl + T to convert your data to a table.
- Make sure you're working on the Home tab on Excel's ribbon, and click on Format as Table and choose a style (theme) to convert your data to a table.
In either case, you'll receive this pop-up menu asking you to confirm the table settings:
Confirm two settings on this menu:
- Make sure all of your data is selected. You'll see the green marching ants box around the cells that will be included in your table.
- If your data has headers (titles at the top of the column), leave the My table has headers box checked.
I highly recommend embracing the keyboard shortcut (Ctrl + T) to create tables quickly. When you learn Excel keyboard shortcuts, you're much more likely to use the feature and embrace it in your own work.
Now that you've converted your ordinary data to a table, it's time to use the power of the feature. Let's learn more:
How to Use Table Styles in Excel
Tables make it easy to style your data. Instead of spending time highlighting your data, applying background colors and tweaking individual cell styles, tables offer one click styles.
Once you've converted your data to a table, click inside of it. A new Excel ribbon option called Table Tools > Design appears on the ribbon.
Click on this ribbon option and find the Table Styles dropdown. Click on one of these style thumbnails to apply the selected color scheme to your data.
Instead of spending time manually styling data, you can use a table to clean up the look of your data. If you only use tables to apply quick styles, it's still a great feature. But, there's more you can do with Excel tables:
How to Apply Table Names
One of my favorite table features is the ability to add a name to a table. This makes it easy to reference the table's data in formulas.
Click inside the table to select it. Then, click on the Design tab on Excel's ribbon. On the left side of this menu, find the Table Name box and type in a new name for your table. Make sure that it's a single word (no spaces are allowed in table names.)
Now, you can use the name of the table when you write your formulas. In the example screenshot below, you can see that I've pointed a new PivotTable to the table we created in the previous step. Instead of typing out the cell references, I've simply typed the name of the table.
Best of all, if the table changes with new rows or columns, these references are smart enough to update as well.
Table names are a must when you create large, robust Excel workbooks. It's a housekeeping step that ensures you know where your cell references point to.
How to Make Cleaner Excel Formulas
When you write a formula in a table, the formula is more readable and cleaner to review than standard Excel formulas.
In the example below, I'm writing a formula to divide the Amount Billed by the Hours Spent to calculate an Hourly Rate. Notice that the formula that Excel generates isn't "E2/D2", but instead includes the column names.
Once you press enter, the Excel table will pull the formula down to all of the rows in the table. I prefer the formulas that tables generate when creating calculations. Not only are they cleaner, but you don't have to pull the formula down manually.
Time-Saving Excel Table Feature: Auto Expand
Tables might evolve over time to include new columns or rows. When you add new data to your tables, they automatically update to include the new columns or rows.
In the example below, you can see an example of what I mean. Once I add a new column in column G and press enter, the table automatically expands and all of the styles are pulled over as well. This means that the table has expanded to include these new columns.
The best part of this feature is that when you're referencing tables in other formulas, it will automatically include the new rows and columns as well. For example, a PivotTable linked to an Excel data table will update with the new columns and rows when refreshed.
How to Use Excel Table Filters
When you convert data to a table in Excel, you may notice that filter buttons appear at the top of each column. These give you an easy way to restrict the data that appears in the spreadsheet. Click on the dropdown arrow to open the filtering box.
You can check the boxes for the data you want to remove, or uncheck a box to remove it from the table view.
Again, this is a feature that makes using Excel tables worthwhile. You can add filtering without using tables, sure—but with so many features, it makes sense to convert to tables instead.
How to Quickly Apply Subtotals
Subtotals are another great feature that make tables worth using. Turn on totals from the ribbon by clicking on Total Row.
Now, the bottom of each column has a dropdown option to add a total or another math formula. In the last row, click the dropdown arrow to choose an average, total, count, or another math formula.
With this feature, a table becomes a reporting and analysis tool. The subtotals and other figures at the bottom of the table will help you understand your data better.
These subtotals will also adapt if you use filters. For example, if you filter for a single client, the totals will update to only show for that single client.
Recap and Keep Learning More About Excel
It doesn't matter how much time you spend learning Excel, there's always more to learn and better ways to use it for controlling your data. Check out these Excel tutorials to keep learning useful spreadsheet skills:
- How to Create Your First PivotTable in Microsoft ExcelAndrew Childress11 Apr 2017
- How to Use PivotTables to Analyze Your Excel DataAndrew Childress22 Apr 2017
- How to Sort Data in Excel Spreadsheets (The Right Way)Andrew Childress07 Jun 2017
If you haven't been using tables, are you going to start using them after this tutorial? Let me know in the comments section below.