On this worksheet, I have two identical sets of order data. I’m going to walk through the process of constructing a summary of sales by item for both sets of data. With the data on the left, I’ll use standard formulas and no table. On the right, I’ll create an Excel Table, and build formulas that use structured references. To start off, I’ll add a bit of formatting to the data on the left to make it easier to see. Now I’ll use the SUMIFS function to sum sales by item. The sum_range comes from data in column F. I need to lock this reference so it won’t change when I copy it down. The criteria_range comes 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, the sum_range is the Total column, and the criteria_range is 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. And, as a bonus, there’s no need to lock these ranges – they automatically function as absolute references. 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. As you can see, using a table with SUMIFS is a straightforward process, and provides a number of nice benefits.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
