Using Tables in Excel
Just about all of the data we enter into Excel is structured in table format — meaning that it's organized in a grid of columns and rows. Some of the time, this data takes the form of a free-flowing analysis, where rows, columns, and cells are combined together to perform a calculation, estimation, or prediction. But sometimes, Excel is just used to organize large quantities of data. For example, the below spreadsheet tracks orders placed by SnackWorld's customers from 2014-2016. Every line of the spreadsheet contains directly comparable data: information on one order, like the order date, item name, quantity, and unit price.
In this latter case, Excel provides an incredibly handy feature that helps us organize and manipulate similar rows of data more effectively: Tables. Not to be confused with 'tables'-with-a-lower-case-T, our generic term for data sets organized into rows and columns, Tables-with-a-capital-T are a specific Excel feature. Let's dive in and take a look at what they can do!
Organizing the data
Before we can create a Table, we'll need to make sure that our source data set is formatted properly. Here are the main requirements we'll be looking at before we dive in:
Our source data must:
- Be structured in row-column format, with each row containing information on one line-item that is directly comparable to all other line-items;
- Have a unique, easily understood heading for each column of data;
- Avoid blank rows in between lines of data (e.g., be one cohesive table with no gaps or inconsistencies);
- Have columns that contain similar data types (e.g., each column should describe one property of data that is directly comparable across the entire column); and
- Not contain any subtotal or total rows. Each line should be a comparable piece of data, not a summary line or total.
Our source data set below fits all of the above criteria, so, once we've double-checked to ensure that everything is ready to go, we can get started creating our table.
Making the Table
Once our data is well-structured, creating a table is easy! We'll simply select our entire source data set, head to the Insert tab on the ribbon, and hit the
Since the source data we've selected includes the headers of our table, we'll make sure the "My table has headers" box is checked in the dialogue that appears, then press OK:
The first benefit of structuring data as a Table immediately becomes apparent: Excel has automatically added beautiful headers and row striping to our data so that it's easy to read and looks fantastic:
Additionally, notice that Excel has added drop-down arrows below each header of our Table. Click one of those arrows to bring up a sorting and filtering menu that provides a bevy of built-in functionality for alphabetizing, data filtering, and more — no additional Sort or Filter commands required!
Those are the basics of Table creation, but there are a few other important advantages to structuring our data as a Table that make this feature even more useful. Let's take a look!
First of all, structuring our data as a Table allows us to total it quickly and easily. Simply head to the Design tab on the Ribbon, and check the box that says "Total Row" in the
A grand total row is automatically added to the bottom of our table. Note that there's a dropdown next to each cell on this total row, which can be switched to use various functions including
This makes it very easy to perform
Note that if we scroll to the bottom of our Table and begin typing an additional row of data, Excel automatically formats our input as a new row of the Table in question:
Additionally, if we copy and paste data into a Table, it will automatically be expanded to fit the data that is pasted in.
Same-row references and auto-fills
Our data set currently contains a
Let's start by creating a new column. To do so, we'll position our cursor in Cell H2 and typing a new column name:
Now, with our cursor in Cell H3, let's start writing a formula to multiply
Notice that something unusual happens here: when we navigate our reference to the
This is a type of cell reference that we've never seen before. What's going on here?
It turns out that Tables allow us to make references to column names rather than just cell addresses. These two types of references are functionally equivalent, but referencing column names allows us to write easier-to-read formulas quickly and easily.
These column name references can only be used when referencing a Table cell on the same row as the source cell, and they're completed by preceding a column name with the
The above formula will pull whatever value sits in the
When the column name contains spaces, it must be enclosed within an additional set of brackets:
Column references can also be used when constructing a formula outside of the referenced table like so:
Note that this formula outputs the value in the referenced Table's
With this all in mind, let's complete our formula in the
When we press Return notice that Excel automatically copies our formula down to every cell in the Table. This is another important table feature:
Tables also make referencing entire columns from outside the Table quick and easy. We can do so with the following syntax:
For example, if we wanted to find the
SUM( Table1[Order total])
This approach has a number of advantages:
- First, writing the formula is easy. There are no cell references to deal with, so formulas summing up an entire column of data become extremely quick to construct.
- Second, the formula will automatically update when new rows are added to the Table. If new data is copied and pasted into the Table, or if new rows are added at the bottom, a whole-column referencing formula will automatically update to include the additions. This makes Tables a go-to data input choice when constructing dashboards in Excel. As the dashboards are updated with new data (which is either entered into a new line on the table in question; or copy-and-pasted in from an external source), all formulas will automatically update to include the entire Table — with any new information. Without Tables, dashboard formulas need to reference entire columns of data in Excel (e.g.,
E:Eor E1:E99999), which can lead to massive computation times.
Use whole-column references on Tables whenever you're constructing a tool that needs to be updated frequently with new data.
Finally, headers of a table can be referenced using the
This can be useful for simplifying formulas that reference the header row of a table, like
Save three hours of work a day with these 5 simple Excel hacks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on how to automate your work using Excel.
- Why the TRUE and FALSE functions are so important...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...