Auto-Totaling Line Items and Tax
Okay. Last lesson we figured out what actually needs to be on an invoice. Business name, invoice number, date, who it's for, what you did, what they owe. Today we make it so you don't have to add anything up yourself.
Because here's the thing. You're gonna hate typing totals by hand, and you should hate it, because that's exactly where mistakes sneak in.
Set up your line items first
Your invoice should have a little table in the middle. Something like:
| Description | Qty | Price | Line Total |
|---|---|---|---|
| Bike tune-up | 1 | 45 | |
| Brake pads | 2 | 12 |
Leave the Line Total column empty for now. We're about to make Sheets do that math instead of you.
The line total formula
Click into the first empty cell in your Line Total column. Type an equals sign, then click the Qty cell, type an asterisk, then click the Price cell. Hit enter.
So if Qty is in cell B2 and Price is in C2, you're typing:
`` =B2*C2 ``
That's it. That's the whole formula. Multiplication in Sheets is just the star key, the one above the 8.
Now grab that little blue square in the bottom right corner of the cell, the fill handle, and drag it down through the rest of your rows. It copies the formula down and adjusts the row numbers for you automatically. You don't have to retype it for every line.
Everybody with me so far? If your number showed up as a weird decimal or looks off, check that you clicked the right cells. Easy to grab B2 when you meant C2.
Now add it all up
Below your table, in a cell that says "Subtotal," you're going to use SUM. This is one of maybe three formulas you actually need for running a small business, and I stand by that. People get nervous about spreadsheets because they think they need to know fifty functions. You need like three.
Click into your Subtotal cell and type:
`` =SUM(D2:D6) ``
Adjust that range to match wherever your Line Total column actually starts and ends. This adds up every line total in one shot.
Tax, the part people mess up
Utah's sales tax varies a little by city, so I'm not gonna tell you a number, you'll want to look up your actual rate for wherever your business operates. American Fork's rate is not necessarily Lehi's rate. Don't guess.
Once you know your rate, put it somewhere visible on the sheet, not buried in a formula where you can't see it. I like a cell labeled "Tax Rate" with something like 0.0725 typed in, formatted as a percent.
Then your tax line formula references both the subtotal and that rate cell:
`` =Subtotal_Cell*Tax_Rate_Cell ``
And your grand total is just subtotal plus tax:
`` =Subtotal_Cell+Tax_Cell ``
Now when you change a quantity or add a line item, everything downstream updates on its own. Nobody's retyping totals at 9pm because a customer added one more thing to the order.
Please, please check it against real numbers
I need to tell you something that still bugs me three years later. Early on, building the inventory tracker at the warehouse, I had a formula that overcounted a whole product line by about 300 units. Just a small reference error, one column off. My boss caught it before it caused real damage, but that afternoon was not fun. I had to walk it back line by line trying to figure out where it went sideways.
Since then I check totals against something real, every single time. For invoices, that means before you send one out, do the math on your phone calculator or just eyeball it. Does $45 plus two brake pads at $12 each, plus tax, roughly land where your total says it lands? A spreadsheet will hand you a confident wrong number without blinking. It's not being sneaky, it just does exactly what you told it, and sometimes what you told it wasn't quite what you meant.
Trust the formula to do the adding. Don't trust it blind.
A word on formatting the money
Highlight your price, subtotal, and total cells, right-click, Format cells, and choose Currency. Nobody wants to hand a customer an invoice that says "127.5" with no dollar sign. Looks unfinished, even if the math's fine.
Before next time
Build out your line item table with the Qty times Price formula, get your subtotal and tax working, and try changing a quantity just to watch the total update on its own. It's a small thing but it never stops being satisfying.