Create an Invoice in Excel With Formulas

Formulas & functions · Updated November 19, 2026

Yes: create columns for quantities and unit prices, calculate each line total, add the lines for a subtotal, and use further formulas for tax, the invoice total, and any remaining balance. In Excel for Microsoft 365 on Windows, a simple worksheet can handle these calculations while you enter customer, business, and payment details manually.

What invoice formulas do

An invoice spreadsheet combines information you type in with calculations Excel updates automatically. You enter items, quantities, prices, and any applicable tax rate; formulas calculate amounts that would otherwise require repeated arithmetic. If you change a quantity or price, dependent totals recalculate. That makes a formula-based invoice useful for occasional billing, services, and small product orders.

Formulas do not decide what you should charge, whether a tax applies, or what payment terms belong on an invoice. Those are business decisions that depend on the transaction and applicable requirements. Treat the spreadsheet as a calculation and document tool, and confirm tax treatment and other obligations with a qualified professional when needed.

Plan the invoice layout

Before entering formulas, reserve distinct areas for the invoice heading, customer information, itemized charges, and totals. One workable layout places the item table in rows 10 through 20, with headings in row 10 and up to ten item lines below. Use these columns:

  • Column A: item or service name.
  • Column B: description or notes.
  • Column C: quantity or hours.
  • Column D: price per unit or hourly rate.
  • Column E: line total.

Put the subtotal, tax rate, tax amount, invoice total, amount paid, and balance due below the item table. For example, use E22 for the subtotal, B23 for a tax-rate input, B24 for tax, B25 for the total, B26 for payments received, and B27 for the balance. Label those cells in column A or beside them so the figures are understandable when printed. Keep business name, contact details, customer name, invoice number, issue date, due date, and payment instructions in a separate header area.

Make input cells visually distinct from calculated cells. For instance, use a pale fill for cells where you type data and a neutral fill for formula results. This is only a visual aid, not a security feature: users can still edit cells unless you protect the worksheet.

Enter invoice details and calculate line totals

Start a blank workbook and enter your labels and invoice details. Type one item or service per row. Quantities belong in C11:C20, unit prices in D11:D20, and calculated amounts in E11:E20. Use numbers for quantities and prices rather than typing currency symbols into the cells; apply number formatting afterward. That keeps the values usable in arithmetic.

With the quantity in C11 and the unit price in D11, enter =C11*D11 in E11. If C11 is 3 and D11 is 45, the result is 135. Copy the formula through E20 by selecting E11, dragging its fill handle down, or copying and pasting it into the remaining line-total cells. Excel adjusts the relative references for each row, so E12 will multiply C12 by D12.

For an illustrative invoice, suppose the first three rows contain 3 units at 45, 2 units at 18.50, and 1 unit at 60. The line totals are 135, 37, and 60. Enter only the rows you need; unused item rows can remain blank. A basic multiplication formula in a blank row displays zero, so you may prefer to keep formulas only on rows that contain items or use a blank-aware version.

With the quantity in C11 and the unit price in D11, enter =IF(OR(C11="",D11=""),"",C11*D11) in E11. It leaves the line total blank if either input is blank; for 3 and 45, it returns 135. Copy it down the item rows as before. The formula uses Excel’s IF and OR functions, and its quotation marks are required to represent empty text.

Calculate the subtotal, tax, and invoice total

With line totals in E11:E20, enter =SUM(E11:E20) in E22 for the subtotal. For the example lines of 135, 37, and 60, the result is 232. SUM ignores blank cells in the range, which is useful when an invoice has fewer than ten items. Place a clear Subtotal label next to the result.

Enter a tax rate as a percentage in B23 only when it applies to the invoice. For example, type 8.25% rather than 8.25; Excel stores the percentage as a decimal and displays the rate in a readable form. With the subtotal in E22 and the tax rate in B23, enter =E22*B23 in B24. At a sample rate of 8.25% on a subtotal of 232, the result is 19.14.

With the subtotal in E22 and tax amount in B24, enter =E22+B24 in B25 for the invoice total. The illustrative result is 251.14. If no tax applies, enter 0% in B23 or leave the tax amount at zero, according to how you have organized the sheet. Do not assume that a tax rate in this example applies to your location, product, or customer.

Excel may store fractions of a cent internally even when a cell displays two decimal places. Decide how your invoice should handle rounding, and apply the same approach consistently. The example formulas calculate totals from the stored values and rely on formatting to display two decimal places. If you need to round each individual line to cents before summing, use an explicit rounding formula and check that the method matches your invoicing requirements.

Track payments and the balance due

Record payments received in B26, entering zero if nothing has been paid. With the invoice total in B25 and the amount paid in B26, enter =B25-B26 in B27. For a total of 251.14 and a payment of 100, the balance is 151.14. If a payment exceeds the total, this formula returns a negative value, which signals an overpayment or credit rather than hiding it.

For multiple payments, list each payment in a small separate area and sum that range in B26 instead of overwriting the previous payment. Keep payment dates and references where useful, especially when you need to reconcile the invoice with bank or accounting records. An invoice template should show what remains due, but it is not itself a record that a payment has cleared.

Format, review, and print the invoice

Format prices, line totals, tax, and balance as currency using the Home tab’s Number group. Set the tax-rate cell to Percentage, dates to a date format, and invoice numbers to text if they include leading zeros or letters. Formatting changes how a value appears; it does not change the underlying calculation. For instance, a displayed value of 45.00 can still be used in multiplication.

Before sending an invoice, check the customer details, item descriptions, quantities, rates, invoice number, dates, and payment instructions. Confirm that the total equals the displayed line amounts plus any included tax, and make sure the balance reflects payments already received. A second review is particularly useful if you copied a formula or reused a workbook, because a formula can be syntactically valid but point to the wrong cell.

To set the printable area in Excel for Microsoft 365 on Windows, select the invoice cells and choose Page Layout > Print Area > Set Print Area. Use File > Print to inspect the preview and adjust scaling or orientation if the invoice spills onto multiple pages. You can save the workbook as an Excel file for later editing and export or save a PDF for sending a fixed copy. On Mac, the ribbon and print options are similar, though their arrangement can vary; Excel for the web also offers printing, but some page-layout controls differ from the desktop app.

Common formula problems and practical safeguards

If a result looks wrong, check the inputs and references before rebuilding the sheet. Common causes include typing a price as text, including a currency symbol in a text-formatted cell, selecting the wrong row when copying a formula, or overwriting a formula with a typed value. Click a result cell and inspect the formula bar to see which cells it uses. A green error indicator may also flag a value stored as text.

  • A formula appears as text: change the cell’s number format to General, edit the cell, and press Enter. Check that the entry begins with an equals sign and that Show Formulas is not turned on.
  • The total is zero or incomplete: verify that the line-total range includes every item row and that the quantity and rate cells contain numeric values.
  • The total changes unexpectedly: inspect formulas for incorrect references and check whether a row was inserted outside the intended sum range.
  • A displayed amount differs by a cent: check the stored precision and the rounding method. Formatting alone does not round a value used in another formula.

Keep an untouched copy of the original template, and use a separate copy for each invoice or a carefully designed invoice log. Excel formulas can reduce arithmetic errors, but they do not prevent accidental edits, guarantee unique invoice numbers, or provide a complete accounting audit trail. Avoid relying on a formula that generates an invoice number from the current row if sorting or deleting rows could change the result; assign and preserve invoice identifiers according to a consistent process.

When an Excel invoice is enough—and when it is not

A spreadsheet can be a practical choice if you issue a modest number of invoices, need a custom layout, and can maintain the workbook reliably. It is flexible: you control the fields, formulas, and appearance without needing a specialized billing system. Excel is also useful for a one-off invoice or as a starting point for learning how totals are calculated.

A spreadsheet becomes harder to manage when several people edit invoices, when you need automatic reminders or recurring billing, or when you need reliable customer histories, payment reconciliation, audit trails, or integrations. Accounting or invoicing software may offer those features, but capabilities and prices vary. Compare options based on the workflow you actually need, including export, access controls, backups, and fees, rather than assuming a spreadsheet or a paid service is automatically the better fit.

Excel itself is part of Microsoft 365 for many subscribers, while a standalone Excel license or another plan may have a different cost. A formula-based invoice does not require a special invoice add-in. If you download a template, check its formulas and source before entering sensitive business or customer information; a polished appearance does not guarantee that its calculations or privacy practices are suitable.

Frequently Asked Questions

Can Excel calculate an invoice total automatically?
Yes. Put quantity and unit price in separate numeric cells, multiply them for each line, sum the line totals, and add any applicable tax. The invoice total updates when the input values change.

How do I calculate tax on an Excel invoice?
Enter the subtotal and a tax rate as a percentage in separate cells, then multiply them. The rate and whether tax applies depend on the transaction and relevant requirements; verify those details rather than treating a sample rate as guidance.

Can I make an invoice in Excel without tax?
Yes. Omit the tax row or use a zero tax rate and make sure the invoice clearly shows the amount due. The formula for the total can simply add the subtotal and any other applicable charges.

How do I make a reusable invoice template?
Keep labels and formulas in place, clear customer and transaction inputs, and save a separate template copy. Test it with sample quantities and prices before using it for real invoices so you can confirm the formulas and print layout.

Can I send an Excel invoice as a PDF?
Yes. Review the print preview, set the page layout, and export or save the worksheet as a PDF. A PDF is convenient for sharing a fixed version, while the Excel workbook remains editable.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.