Freeze Panes vs Pinned Headers
When working with large tables, you might often resort to freezing panes to pin the headers as you scroll down. However, with Tables, the headers automatically stay visible when you scroll, even if they don't start in the first row.
Filters
The filter buttons added to each column header are significant time-savers. With just a click,
you can sort and filter your data to find exactly what you need in seconds.
Filters make it simple to narrow down your data to the most relevant information without manually searching through rows and columns.
You'll have different filter options depending on the type of data in the column, and you can even use
the search feature within the filter drop-down:
Structured References and Dynamic Ranges
Adding calculated columns is one of the most common tasks you'll perform when working with data. For example, in my dataset I have columns for sales and cost of goods sold (COGS) but no column for Gross Profit.
Inserting a column header in the first empty column to the right of the table will result in the table automatically expanding to include this new column in
the table range.
The formula for Gross Profit is (Sales - COGS) and when you insert this in a table is uses the table's structured references instead of regular cell references. Structured references consist of the column name prefixed by the @ symbol, which tells Excel to refer to the cell on the current row. When you press ENTER, the formula is copied down the remaining cells in the
table:
Not only is having the same formula in a column best practice, but it also makes
it easy to understand what the formula is calculating because the structured references display the column names.
Referencing Tables in Formulas
You can also reference tables in formulas from outside the table. For example, to get some headline figures like Total Sales, you can use your mouse to click in the column header and select the Sales column.
You can also use IntelliSense to write formulas. For example, to calculate Total Profit, you can type in the table name, and Excel will
auto-complete it for you:
Then to insert the column, type an opening square
bracket:
Use the arrow keys to move through the list or type in the start of the column name to narrow down the
options. Tab to insert the column name.
You can also reference the table with its structured references from any sheet in the workbook, making it easy to write and understand formulas.
Not only are formulas that use the table's structured reference quick and easy to write, they're
also easy to understand what and how the formula is calculating. Plus, they automatically update when new data is added to the table, so you never have to edit cell ranges in formulas again!
Absolute and Relative Table References
Unfortunately, you can't use the F4 key to apply absolute references to Structured References in formulas. They have some quirky rules depending on
whether you're copying and pasting a formula or left-clicking and dragging it. You'll find more on working with absolute references for Table Structured References here.
Automatically Update Reports
If you create any type of report that gets updated regularly, the most time-consuming task is editing the formulas, PivotTables, and charts to include the new data. But when you format your data in a Table, you don't have to do this repetitive and laborious work.
For example, if your source data table has data up to November, and you receive new data for December,
you can simply copy and paste the December data into the next blank row below the table:
The
formatting and formulas will automatically update to include the new data. We can see below the table range pull handle is now in the last cell of the December data:
To update your report, go to the Data tab and click Refresh All:
With one click, all your PivotTables and charts will include the new data:
Slicers
You might be familiar with Slicers from PivotTables, which make filtering your reports super easy. But did you know you can also use Slicers with Tables? Select a cell in the table, then on the Table Design tab, click Slicer:
Choose the columns you want slicers for, and you can resize and position them as needed.
Slicers make it easy to focus on the data you're interested in and visually indicate what items are included in the table.
Total Rows
Another built-in feature of Tables is total rows. You can turn them on in the Table Design tab
by checking the Total Row box:
It automatically adds a total to the last numeric column, but you can
add totals to other numeric columns by clicking the drop down list button and choosing the aggregation type.
The nice thing about these totals is that they use the SUBTOTAL function, which excludes rows hidden by filters.
As you select different items in the Slicers, the total row
automatically updates accordingly:
By integrating Excel Tables into your workflow,
you can significantly streamline your data management tasks, making your processes more efficient and less error-prone. Give it a try, and you'll see just how transformative this overlooked tool can be.
Automate Your Workflow with Power Query
Above, I showed you how to update tables by pasting in your new data. If you want to learn how to automate this process, including merging
multiple files in seconds and transforming messy data into Tables effortlessly, check out my tutorial on How to Easily Automate Boring Excel Tasks with Power Query.
It will show you how to save
hours on data preparation, making you the go-to Excel guru in your office.
Next Steps
We've just covered a few of Excel's powerful features. But there's much more to discover.
If you've found this information valuable, you'll be interested to know that my Excel courses dive
even deeper. Including everything from advanced formulas to advanced data analysis techniques designed to significantly enhance your Excel proficiency. Click here to check out our complete Excel Course library.
Right Now - All Courses Are 20% Off
Learn With Us
