Are your spreadsheets stuck in a rut? Excel Tables can transform your workflow and analysis. Enjoy automatic formatting, easy filtering, and structured references for formulas. This tutorial shows you how to create and use Excel Tables effectively. Discover key benefits and streamline your workflow today. Includes a downloadable practice file.
Knowledge You’ll Gain:
- Transform ordinary spreadsheets into dynamic Excel Tables with a few clicks.
- Effortlessly format your data with automatic styling and banded rows for enhanced readability.
- Simplify data analysis with built-in filtering and sorting capabilities.
- Unlock the power of structured references to create more efficient and intuitive formulas.
Understanding Excel Tables
I think part of the confusion is the word “table,” as people figure that the native state of the spreadsheet is a table. Unlike a standard spreadsheet, an Excel Table is a structured range of data that offers a wealth of features for enhanced formatting, analysis, and data management. Let’s take a look at a simple example to illustrate the difference.
As you can see, it’s a collection of stats about US presidents. Not terribly exciting, but it works well for illustration. Now, here’s the same info as an Excel table. This table conversion was done in 4 clicks.
Excel Table Useful Features
If you compare the two screen snaps, there are 3 immediate differences.
- (A) You have a new tab called Table Design with additional features.
- (B) The top row is locked, so the column heading stays in place as you scroll down like Excel’s freeze panes feature. The columns are filterable. Think of Excel’s auto-filter feature.
- (C) You have alternating banded rows that make reading easier.
Most of these features can be enabled in the Table Style Options group.
How to Make a Table in Excel
Now that we’ve explored the benefits of Excel Tables, let’s learn how to create one. As I mentioned, producing the table took 4 quick clicks. If you prefer keyboard shortcuts, you can use Ctrl + T.
- Click in any populated cell. The cell can’t be blank.
- Click the Insert tab.
- Click Table.
- Click OK in the Create Table dialog box. Notice how the range is automatically entered.
If you don’t like the color combination, you can check out the options in Table Styles.
Table Design & Style Options
After you create your Table, you’ll see a new tab on the Excel Ribbon called Table Design that offers more features.
The key areas are:
- (A) A textbox to provide a table name.
- (B) Various Table Style options for header, rows, and columns.
- (C) A series of color styles and options to create your own.
Easier Calculations and Formulas
Apart from the usability features, an Excel Table makes math easier. To begin, you can add a Total Row by ticking the checkbox in the Table Style Options group.
The nice part about this row is it isn’t limited to Totals. There are many built-in math functions. For example, after adding the row, I can do the additional math on the columns, such as determining the average inauguration age. You’ll see a list of available math options if you click the triangle.
Structured References for Efficiency
Excel Tables also make formulas easier by assigning column names instead of cell references. For example, I can insert a new column called “Electoral %” in column M. Excel dynamically adds that column to the table. I don’t have to define it.
In this example, I’ll use a formula that is based on the existing columns Electoral Votes (F) and Total Electoral Votes (G). Instead of using =F2/G2 and copying the formula down, I simply enter the = sign and click the desired column headings. Excel will automatically use the structured reference names. Excel also added the @ symbol to reflect the current row.
Moreover, when I click Enter, it calculates the values for the entire column. I only have to adjust the formatting if I like.
Instead of referencing Electoral Votes as column F, Excel shows the column name. You could also have typed =[ in the cell and Excel would provide a drop-down menu with choices.
Data Exploration with Slicers
Another advantage to tables is you can create Excel slicers. Slicers only work with Excel Tables and Excel Pivot Tables. A slicer gives you another way to drill down into your data by applying one or more filters. You can create one from any column within your table.
Make sure you are on the Table Design tab. Then click any cell and the Insert Slicer button from the Tools group. Excel will provide a panel where you can check any column. You can then drag the slicer to the side.
As you click one button, other slicer buttons may be enabled. For example, if I clicked California, the Actor button under Occupation would enable, and I’d see Ronald Reagan added.
Troubleshooting Table Formatting
With all my excitement about the benefits, I didn’t immediately see the problem with the header. If you look closely at the picture above, you’ll see the columns have 2 different font colors. This glitch can sometimes happen due to inconsistencies in cell formatting within the selected range. Some are black and some are white. If this happens to you, try this:
- Highlight Row 1.
- Click the Home tab.
- From the Editing group, click Clear.
- Select Clear format.
Your column headings should now be the same color. In my case, I adjusted the line height to accommodate the second line.
As you’ve seen, Excel Tables are a powerful tool for enhancing your spreadsheets and data analysis capabilities. By implementing the techniques outlined above, you can transform your workflows, making them more organized, and insightful. Ready to put your new Excel Table skills into practice? Download the practice file below and start exploring the benefits of this essential Excel feature today. You’re just 4 clicks away.