On this worksheet, I have two identical sets of order data.
With the data on the left, I’ll use standardformulasand no table.
On the right, I’ll create an Excel Table, and build formulas that usestructured references.
Now I’ll use theSUMIFS functionto sum sales by item.
Thecriteria_rangecomes from column D, and also needs to be locked.
The criteria itself comes from the adjacent column.
When I copy the formula down, we get the correct total for each item.
This is a fairly typical SUMIFS solution.
Now I’ll build the same solution using a table.
First, I’ll create the table.
Then I’ll name the table “Orders”.
Notice with a table, we get the formatting for free.
Now I’ll write the SUMIFS formulas with the table.
As before, thesum_rangeis the Total column, and the criteria_rangeis based on the Item column.
Now that we’re using a table, we’ll see structured references instead of regular cell ranges.
These are definitely easier to read.
The criteria itself comes from the adjacent column, exactly the same as before.
When I copy the formula down, we get identical totals.
The SUMIFS formulas on the left use regular ranges.
The formulas on the right use the Orders table.
The table-based formulas aren’t any harder to write, and they’re definitely easier to read.
However, the key advantage shows up when I paste more data below each table.
Because table ranges automatically expand, the formulas on the right automatically update to show correct values.
In the SUMIFS formulas on the left, however, the ranges are static and don’t change automatically.
I will need to update ranges manually, whenever new data is added.
I’ll also need to apply formatting.
