Job task guide · Invoices and tax

How to calculate an invoice

An invoice is a list of what was bought, how many, and at what price, ending with the amount the customer owes. Office assistants, small-business owners, and front-desk staff often build or check one in a spreadsheet. The math is simple: multiply, add, and apply tax. Doing it with formulas means a changed quantity fixes every total automatically.

The example

An office orders three items. Each row has the item in column A, the quantity in B, and the price per item in C. Column D will hold the line totals. The sales tax rate, 8%, is typed once in F1 as the decimal 0.08.

Row	A: Item	B: Qty	C: Unit price	D: Line total
2	Printer paper (box)	6	7.50	`=B2*C2` → 45.00
3	Toner cartridge	2	64.00	`=B3*C3` → 128.00
4	Stapler	1	18.00	`=B4*C4` → 18.00
5			Subtotal	`=SUM(D2:D4)` → 191.00
6			Tax	`=D5*$F$1` → 15.28
7			Amount due	`=D5+D6` → 206.28

Step by step

Line total. Click D2 and type =B2*C2. The asterisk * means multiply. Six boxes at 7.50 each comes to 45.
Repeat for each line. D3 gets =B3*C3 and D4 gets =B4*C4. Each row multiplies its own quantity and price. To save typing, drag the small square at the bottom-right corner of D2 (the “fill handle”) down to D4. The spreadsheet adjusts the row numbers for you.
Subtotal. In D5, add the line totals with =SUM(D2:D4). That gives 191.
Tax. In D6, multiply the subtotal by the rate: =D5*$F$1. 191 × 0.08 = 15.28.
Amount due. In D7, add subtotal and tax: =D5+D6. The customer owes 206.28.

What the dollar signs do

When you copy a formula to another cell, the spreadsheet shifts its cell references to match. That is why copying =B2*C2 down one row gives =B3*C3. Usually that is what you want, but not for the tax rate. There is only one rate, in F1, and every row needs it.

A dollar sign locks the part of the reference that follows it. $F$1 locks both the column (F) and the row (1), so the reference never moves when copied. If you charge tax on each line separately, you can write =D2*$F$1 once and copy it down: the D2 changes row by row, while $F$1 stays put. In Excel, pressing F4 while the cursor is on a reference adds the dollar signs for you.

Why put the rate in its own cell? If the rate changes from 8% to 6%, you edit F1 once and every tax figure updates. If you had typed 0.08 into each formula, you would have to find and fix every one.

Common mistakes

Typing the percent as a whole number. 8% is 0.08, not 8. Multiplying by 8 would make the tax eight times the subtotal. If you type 8% into a cell, the spreadsheet stores it as 0.08 for you.
Forgetting the dollar signs and copying. If =D2*F1 is copied down, the next row becomes =D3*F2. F2 is empty, so the tax shows 0.
Adding prices instead of line totals. =SUM(C2:C4) adds the unit prices and ignores the quantities. The subtotal should add column D.
Rounding errors in cents. Tax can produce amounts like 15.2784. Formatting the cell to two decimals only changes how it looks. To make the stored value match what you bill, use =ROUND(D5*$F$1, 2).
Including the subtotal in its own range. If D5 says =SUM(D2:D5), the formula refers to itself and you get a circular-reference warning.

Questions first-time users ask

How do I show a discount?

Put the discount rate in its own cell, for example 10% in F2. Then the discounted subtotal is =D5*(1-$F$2), which keeps 90% of it. Apply tax to the discounted amount if that is your business’s rule.

How do I make the numbers look like money?

Select the price and total cells and choose the Currency or Accounting format. The values stay the same; only the display changes to show a currency symbol and two decimals.

Do these formulas work in Excel and Google Sheets?

Yes. Multiplication, SUM, ROUND, and dollar-sign references all work the same way in Excel, Google Sheets, and LibreOffice Calc.

Practice it

What you will see in the trainer

The invoice example above includes three items and tax. The Prepare an Invoice lesson starts with two items and no tax: quantity 4 in B2 at price 12 in C2, and quantity 3 in B3 at price 8 in C3. Enter =B2*C2 in D2, =B3*C3 in D3, and =SUM(D2:D3) in D4. The guided amount due is 72.

“Your turn” changes the items to 5 at 14 and 6 at 9. Use the same three formulas; the new amount due is 124. The separate Apply a Fixed Tax Rate lesson then uses F1 for a rate and =B2*$F$1 in C2.

Ready? Open Prepare an Invoice or open Apply a Fixed Tax Rate.