Create an invoice in Excel by setting up columns for descriptions, quantities, unit prices, and line amounts, then adding formulas for the line amounts and invoice totals. For example, with a description in A11, quantity in B11, unit price in C11, and amount in D11, enter =B11*C11 in D11; if the quantity is 3 and the price is $40, the amount is $120.
Plan the invoice layout
Start with a simple layout that keeps the information a customer needs separate from the calculations. Put your business name and contact details near the top, followed by an invoice number, invoice date, due date, and customer details. Leave enough space for several line items, then reserve a small block at the bottom for the subtotal, any applicable tax, the total, payments received, and the balance due.
A practical line-item table uses four columns: description, quantity, unit price, and line amount. In this example, those columns are A through D, with line items in rows 11 to 20. Labels and amounts for the totals go in C21:D26. You can adjust the positions to suit your page, but keep the ranges consistent in every formula.
- A: Product or service description
- B: Quantity
- C: Price per unit
- D: Extended amount for that line
Use one row per product, service, or other separately priced item. Avoid merging cells inside the table: merged cells can make sorting, selecting, and copying formulas harder. For a one-off invoice, a straightforward sheet may be enough. If you will invoice repeatedly, make a clean master copy and save a separate file for each customer or invoice.
Enter and copy the line-item formula
With descriptions in A11:A20, quantities in B11:B20, unit prices in C11:C20, and line amounts in D11:D20, enter this formula in D11: =IF(OR(A11="",B11="",C11=""),"",B11*C11). It leaves the amount blank until the description, quantity, and price are all present; when A11 is filled, B11 is 3, and C11 is 40, it returns 120. The formula checks that all three input cells have content before multiplying.
After entering the formula, select D11 and drag the small fill handle at the lower-right corner of the cell down to D20. Excel adjusts the row references as it copies, so the next row uses A12, B12, and C12. The formula prevents unused rows from displaying zero amounts, while still allowing the subtotal formula to add the completed lines.
If you prefer a shorter formula and do not mind seeing zero in unused rows, use =B11*C11 in D11 and fill it down. In either case, enter quantities and prices as numbers, not text such as “three” or “$40 each.” Apply number formatting to show currency without typing currency symbols into the values themselves.
Calculate the subtotal, tax, and total
With line amounts in D11:D20, enter =SUM(D11:D20) in D21 for the subtotal. If the three completed lines are 3 units at $40, 2 units at $22.50, and 1 unit at $30, their amounts are $120, $45, and $30, and the formula returns a subtotal of $195.
For a simple example with one tax rate, put the rate in D22 as a percentage, such as 8%, and label C22 “Tax rate.” With the subtotal in D21 and the rate in D22, enter =D21*D22 in D23. On the $195 example, it returns $15.60. Enter 8% as a percentage or as 0.08; entering 8 as a plain number would calculate tax at 800%.
With the subtotal in D21 and calculated tax in D23, enter =D21+D23 in D24 for the invoice total. Using the example values, the result is $210.60. You can label C21, C23, and C24 “Subtotal,” “Tax,” and “Total.” Keep the tax-rate input visibly distinct from calculated cells so it is less likely to be overwritten accidentally.
Tax treatment depends on the transaction and applicable rules. Some items may be exempt, or different rates may apply. The single-rate setup above is only an illustration, not tax or legal advice; check the rules that apply to your situation or consult a qualified professional. If line items have different rates, calculate tax by applicable category rather than applying one rate to the entire subtotal.
Track payments and the balance due
With the invoice total in D24, enter the amount already paid manually in D25, and label C25 “Amount paid.” Then, with the total in D24 and payment in D25, enter =D24-D25 in D26 for the balance due. If the total is $210.60 and the customer has paid $100, the formula returns $110.60. Format the result as currency.
For an invoice that has not been paid, enter 0 in D25 so the balance equals the full total. If you record multiple payments, either update the amount-paid cell to their sum or list payments in a separate table and sum that range. Do not subtract a payment both in the payment list and again in D25. If a payment exceeds the invoice total, the formula returns a negative balance; review the payment entry and decide how to record any credit or refund in your own bookkeeping process.
Add dates, labels, and readable formatting
Dates can be typed directly into cells and formatted as dates. If B4 contains an invoice date and B5 is the due-date cell, the formula =B4+30 in B5 adds 30 days. For an invoice dated November 3, 2026, it returns December 3, 2026. This is a simple calendar-day calculation, not a statement about what payment terms you should use; enter the due date manually if your agreement uses a different rule.
Make the sheet easy to scan: use bold text for column headings and the final total, align quantities consistently, and give the description column more width than the numeric columns. To apply currency or percentage formatting, select the relevant cells and use the Number Format controls on the Home tab. Currency formatting changes how a numeric value appears; it does not change the underlying calculation. Use a date format for invoice and due dates, and a percentage format for the tax-rate input.
Include clear payment instructions, such as the accepted payment method and where to send payment, if appropriate. Keep invoice numbers distinct and consistent so you can identify records later. A spreadsheet does not automatically establish that an invoice meets every record-keeping or tax requirement, so check what information is required for your business and location.
Make the workbook reusable and check the printout
Save a blank version before entering customer details. For each new invoice, use Save As to create a separate file, update the invoice number and dates, replace the customer and item information, and check the amounts. This reduces the risk of sending a later customer an invoice that still contains another customer’s details. You can also save a copy as a PDF when you need a fixed version to send; retain the editable workbook if you need to revise the invoice later.
Before sending, compare each quantity and unit price with your source records, confirm that the correct tax treatment was used, and verify the total and balance. Check the print layout through File > Print. If the invoice spans pages, use Page Layout > Print Area > Set Print Area to select the invoice, then review the scaling and page breaks. Excel for Microsoft 365 on Windows and Mac has similar core worksheet and print features, though ribbon placement and some controls can look different. Excel for the web may also present print and layout controls differently from the desktop app.
Consider protecting formula cells from accidental edits if other people will use the workbook. A simple alternative is to shade input cells differently and add a note telling users which cells to edit. Protection can help prevent mistakes, but it is not a substitute for keeping a backup. Avoid treating a protected worksheet as a secure way to store confidential information.
Use a template or build your own?
An Excel invoice template can save setup time and may already include a branded layout and common calculations. Before using one, inspect its formulas and input cells rather than assuming every total is correct. Confirm that the currency, tax fields, payment terms, and printed page fit your needs. Templates vary, and a design that looks polished may still require changes for your workflow.
Building a sheet yourself takes longer at first, but makes the calculations and structure easier to understand and adapt. A small business that invoices the same way each time may benefit from a reusable workbook. Someone creating a single invoice might find a suitable template quicker. In either case, test the workbook with a small example like the $195 subtotal above, then confirm the total and balance before relying on it.
Common formula problems to check
- Numbers stored as text: A quantity or price pasted from another source may not behave as a number. Check the cell contents and convert the entry to a numeric value if necessary.
- Wrong range: If a new item is entered below row 20, it will not be included in the example subtotal range D11:D20. Extend the range or add rows within the range.
- Overwritten formula: Typing into a calculated amount cell replaces its formula. Re-enter the formula or restore a clean copy, then check other formula cells.
- Unexpected tax result: Confirm that the rate cell is a percentage and that the subtotal includes only the items to which that rate applies.
- Rounding differences: Displaying two decimal places does not necessarily round the stored calculation to two decimal places. If your process requires a particular rounding method, confirm it with the applicable accounting guidance or professional.
When a result looks wrong, select the cell and inspect the formula bar to see what Excel is calculating. Also check that calculation is set to Automatic under Formulas > Calculation Options. Most invoice totals update automatically when their input cells change, but a workbook set to manual calculation may show an outdated result until recalculated.
Frequently Asked Questions
How do I make an invoice total automatically in Excel?
Put each line amount in one column, calculate the subtotal with a SUM formula for that column’s item range, and add any applicable tax to get the invoice total. Keep the formula ranges aligned with the rows where you enter items.
Can Excel calculate sales tax on an invoice?
Yes. For one applicable rate, multiply the taxable subtotal by a percentage stored in a cell, then add the resulting tax to the subtotal. Tax rules and which items are taxable vary, so verify the correct treatment for the transaction.
How do I stop blank invoice rows from showing zero?
Use an IF formula that checks whether the description, quantity, and price cells are filled before calculating the line amount. If any required input is missing, it returns a blank instead of zero.
Can I save an Excel invoice as a PDF?
Yes. In the desktop app, use File > Save As or File > Export and choose PDF, depending on the available options in your version. Review the print layout first so the invoice is not split or clipped unexpectedly.
Do I need to use an Excel invoice template?
No. A template is optional. You can build a simple invoice from a blank workbook, or adapt a template after checking its formulas, fields, and print layout. Choose the approach that is easiest to maintain accurately.
Related
- Clean an Excel Spreadsheet: Steps for Cleaner Data
- Clean Excel File Online: Safe Steps and Best Options
- Clause 34 of Tax Audit Report in Excel: What to Include
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- AVERAGEIF Excel Between Values: Formula and ExamplesTo average numbers that fall between two limits in Excel, use AVERAGEIFS, not AVERAGEIF, because the range needs…
- Recursive LAMBDA in Excel: Examples That WorkRecursive LAMBDA formulas call themselves to repeat a calculation, such as finding a factorial or stepping through a…
- Excel Formula for Calculating QuarterThe simplest Excel formula for calculating the quarter from a date in cell A1 is =ROUNDUP(MONTH(A1)/3,0), which returns…
- Average Date in Excel: Formula, Examples, and FixesTo calculate an average date in Excel, use =AVERAGE(A2:A10) with dates in A2:A10, then format the result as…
- Date Minus Days Formula in Excel: Examples and TipsTo subtract days from a date in Excel, use a minus sign: if A2 contains a date and…
- Extract Part of Text in Excel: Formulas and StepsTo extract part of text in Excel, use LEFT, RIGHT, or MID for characters in known positions, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.