This is where tables become really useful. Much like flash fill and auto fill, they take what you’ve done and extrapolate it to other cells.
We’ll start by creating a total row that automatically adds (or does other things to) your rows. Go back to the table in the example spreadsheet, then click on one of the cells in the bottom row.
When you do, you’ll see a new tab, called Design, in the Ribbon:
To insert a total row, check the Total Row box.
You’ll see that we now have another row, labeled “Total,” on the table.
In our case, it came up with some strange results:
Excel didn’t total the prices—which would have been useful—but it did total the discounts, which gives us a misleading figure. Let’s change that.
First, click on the cell that’s displaying “42%,” then click the downward-facing arrow next to the sum of the discount percentages.
Then select Average:
That gives us a more reasonable figure. Now, to get the total price.
There’s nothing displayed in the total row under the prices, but if you select the cell, you’ll still see an arrow. Click on the arrow and select Sum:
Now we have the total of the prices from our list, as well as the average discount, automatically calculated.
You’ll notice that there are many options beside Average and Sum—remember these for later! You never know when you might need them.
Excel isn’t limited to auto-calculating rows. It can auto-calculate columns, too.
And it’s very adept at figuring out what you want in your column, too. Let’s take a look.
We have the prices and discount percentages in our spreadsheet—why don’t we find the final prices, too?
Click into cell E4, and type the following formula:
This formula will get us the final price after the discount is applied.
Hit Enter to see Excel work its magic:
Excel not only calculated the result of this formula, but filled it into each row of the table and added the column to the table.
The column header isn’t very descriptive, and the numbers aren’t formatted as currency, but those are easy problems to fix.
And now we can use the total row to add the discounted prices:
It might seem like a lot of steps, but once you get used to working with tables, you’ll be able to complete tasks like these much faster than if you hadn’t defined the table.