In this post, we will dig deeper, to discover why an Excel Table is much more powerful than just formatting.
What is a table?
Let's start with some basic definitions.
In Excel, you can freely structure content within cells; cell A3 below is just data.
When you highlight cells A3:C5, the data becomes part of a range. 
The range becomes a table when every column has a header (note the lower case "t"), such as A3:C5.
In a table structure, a user might understand that row 3 contains headers, but Excel doesn't know that. Excel also doesn't understand that cell B4 is a Qty of Succulents.
Converting a table to a Table
A table can easily be converted to an Excel Table by selecting the table range and then pressing Ctrl + T. It can also be created from the Home menu by selecting "Format as Table", or from the Insert menu by selecting Table, then Table. The fact that Excel gave us 3 ways to create a Table signals its power.
Once the Table is created, you'll notice cosmetically that the data has been formatted. My default layout changes the rows to blue and white, alternating between each row (i.e, banded rows).
Most importantly, when you select anywhere in the Table, a new Table Design menu becomes available on the ribbon. This is an easy way to confirm that all your data has been picked up in the Table. By using End + arrow keys, you can quickly scroll around the edges of the Table and ensure all data which should be included has been.
What is a Table?
Now that you've created a Table, let's explore some of the key features of a Table.
Structure
An Excel Table is structured. The first row is always the Header row. Everything beneath a header relates to that header; for example, beneath the Product header are types of products.
No cell references
Since Excel understands a Table's structure, you can reference Header names directly when writing formulas. For example, you could write a formula for Total Sales as =[@Qty]*[@[Unit Price]].
This is a significant deviation from the traditional formulas of =B4*C4, but results in much more intuitive formulas which explain what's happening (Qty * Price). When the data is located separate from the formula such as on another worksheet, the efficiency created by using Excel Tables is even more evident.
Formula auto-calculation
When a formula is entered in to a Table, the formula is automatically copied down to the remainder of the Table rows and gets Power-calculated. To me, this is personally the best benefit of using Tables. I work with lots of large data sets containing lookups, SUMIFs, and nested IFs. Copying down a new formula can take a few minutes, or cause Excel to stop responding; but, when using the exact same data formatted as an Excel Table, I rarely ever have issues and formulas take a couple of seconds, no matter how complex.
New rows
When adding a new row to a Table, any formulas automatically copy down. Give it a try by downloading the exercise file below, then copy the additional data and paste in cell A10.
↑
Headers
Since Excel knows the Headers, there's no need to Freeze Panes. When you scroll down in the Table, the Headers float to the top replacing the traditional A:Z column headers.
Ranges defined
Since Excel knows how a Table is structured, as long as your cursor is anywhere inside Table, a few things are easier than ever:
- Sorting: Just select Sort; no need to highlight a range.
- Pivot Tables: Range is automatically the entire range of the Table.
- Power Query: When loading from the current workbook, Power Query needs the data as a Table.
In this lesson, you learned:
- The difference between a table and an Excel Table;
- How to create a Table; and
- The functions of Table including a structure Excel can read, no cell references, formula auto-creation, auto add formulas for new rows, scrolling headers, and a defined named range.
Prior post: Error Checking
Next post: Query Dependencies
No comments:
Post a Comment