Generate Invoice Numbers in Excel With a Simple Formula

Formulas & functions · Updated November 20, 2026

To generate invoice numbers in Excel, enter ="INV-"&TEXT(ROW()-1,"0000") in cell A2 when row 1 is a header and your first invoice is on row 2, then fill the formula down. It returns INV-0001, INV-0002, and so on; the important caveat is that formula-generated numbers can change when rows are inserted, deleted, or rearranged.

What the invoice-number formula does

The formula combines three parts: the fixed text prefix INV-, the current row number minus 1, and a four-digit format. In the stated layout, A2 is the first invoice row, so ROW() returns 2 and subtracting 1 produces 1. TEXT formats that value as 0001, and the ampersand joins the prefix and formatted number into INV-0001. When you copy the formula to A3, it evaluates to INV-0002.

With a header in row 1 and invoice records beginning in row 2, enter this formula in A2 and copy it down the invoice-number column: ="INV-"&TEXT(ROW()-1,"0000"). If your first record is on a different row, adjust the offset. For example, when the first record is in row 5, subtract 4 so that row 5 produces 0001. The formula’s result is text, which is useful because it preserves the prefix and leading zeros.

The 0000 format specifies a minimum of four digits. Numbers below 10,000 display with leading zeros; once the sequence reaches 10,000, Excel displays five digits rather than cutting off the number. Choose a longer mask, such as 000000, if you expect more than 9,999 invoices and want six-digit numbers from the start.

Set up an invoice register before filling down

Keep one row per invoice and use a clear header row. A practical register might have Invoice No. in column A, Invoice Date in B, Customer in C, Due Date in D, and Amount in E. Enter the first formula in A2, then copy it into the rows where you expect invoices to be recorded. In Microsoft 365 for Windows, you can drag the fill handle at the lower-right corner of the selected cell, or select the formula cell and the cells below it and press Ctrl+D. On a Mac, use Command+D to fill down.

For a cleaner register, convert the range to an Excel table with Insert > Table and confirm that the table has headers. A table can extend formatting and calculated-column formulas as you add rows. However, a table does not make a row-based invoice number permanent: the formula still depends on the row position and can recalculate if the records are reordered.

  • Use one consistent prefix, such as INV-, rather than changing the format from row to row.
  • Keep the invoice number in its own column, separate from customer names and dates.
  • Check that every issued invoice has a number before sending or saving it as a final record.
  • Keep a backup or a separate issued-invoice register if the workbook is part of your recordkeeping process.

Make the number appear only when an invoice exists

If you pre-fill formulas down many rows, the number column may show IDs beside blank invoice records. You can make the formula return a blank until a required field, such as the invoice date, is entered. With invoice dates in B2:B and invoice numbers in A2:A, enter the following in A2 and fill down: =IF(B2="","","INV-"&TEXT(ROW()-1,"0000")). When B2 is empty, A2 stays blank; when B2 contains a date, the first record in row 2 displays INV-0001.

This variation only checks whether the date cell is empty. If you sometimes enter a customer before the date, or use another field to signal that a record is ready, check that field instead. For example, checking a customer-name cell would make the number appear as soon as a customer is entered. Choose a trigger that matches your workflow, and avoid placing unrelated notes in that trigger column.

A blank-looking formula cell is not truly empty: it contains a formula that returns an empty text value. That distinction can matter when filtering or counting records. If you need to count completed invoices, count a field that is genuinely filled in for each completed record, such as the invoice date, rather than assuming every formula cell with a blank display is empty.

Add a year or another prefix

Some businesses prefer the year to be visible in the invoice ID. With invoice dates in B2:B, the first invoice record on row 2, and the number formula in A2, use: ="INV-"&YEAR(B2)&"-"&TEXT(ROW()-1,"0000"). If B2 contains a date in 2026, the result is INV-2026-0001. Fill the formula down to use each row’s date and row-based sequence.

This example includes the invoice year in the label, but it does not restart the sequence at the beginning of a new year. It might produce INV-2026-0037 followed by INV-2027-0038 if the next record is on the next row. That can be a useful way to make dates easy to recognize, but it is different from a yearly sequence that starts over at 0001. If your process requires an annual reset, plan the numbering logic deliberately and test it against invoices entered out of date order before relying on it.

You can also replace INV- with another fixed label, such as BILL-, or remove the prefix by using a numeric format. A prefix can help distinguish invoices from quotes, purchase orders, or credit notes, but it does not by itself guarantee uniqueness. If several people create invoices in separate workbooks, each workbook could generate the same ID.

Formula-generated IDs versus permanent invoice numbers

A row-based formula is convenient for a draft register because it creates a readable sequence without manually typing each number. It is not necessarily a dependable way to assign a permanent identifier to an invoice that has already been issued. If you delete an earlier row, the numbers below it can decrease. If you insert a row, later results can increase. Sorting the register can also change which invoice receives each row-based result.

That behavior is a key difference between a calculated display and a stored number. Formulas recalculate from their inputs; they do not automatically remember the value they showed when an invoice was first sent. If an issued invoice must keep the same number, convert its generated result to a fixed value after checking it. Copy the relevant cell or range, then use Home > Paste > Paste Values. The exact Paste Values button can appear in the ribbon’s Paste menu. On Windows, Ctrl+C copies a selection; on Mac, use Command+C. Paste Values replaces the formulas with their displayed results, so the ID will no longer change when the row position changes.

Another approach is to assign numbers manually from a controlled register or invoicing system and use Excel to store and report them. This adds a step, but it can be safer when multiple people issue invoices or when numbers must remain attached to sent documents. Establish who assigns the next number, how voided invoices are recorded, and how duplicate numbers are checked. Requirements and accepted practices vary by location and business context, so ask an accountant or other qualified professional about recordkeeping obligations that apply to your situation.

Common problems and how to fix them

  • The first result is INV-0002. The offset does not match the first data row. If the first invoice is in row 2, subtract 1; if it is in row 5, subtract 4.
  • The formula appears as text. Check that the cell is not formatted as Text, and that the entry begins with an equals sign. Change the cell format to General, then re-enter the formula.
  • The prefix or zeros disappear. The formula should produce text through TEXT and concatenation. If you instead store only a number and rely on cell formatting, exporting or copying data to other software may not preserve the display format.
  • Numbers change after editing the register. That is expected from a formula based on ROW(). Convert issued IDs to values or assign permanent numbers through a controlled process.
  • The invoice number repeats. Check for copied records, duplicate entries, and separate workbooks using the same sequence. A row formula only counts position; it does not search all files or enforce uniqueness.
  • A blank row still shows an ID. Use the IF version that checks a required field, or do not fill the formula into unused rows.

Choosing a numbering pattern

Before settling on a format, decide what people need to learn from the ID and how the workbook will be maintained. A short sequential pattern is easy to enter and read. A prefix can make the document type obvious. A year can help with filing, but it can complicate numbering if the sequence resets annually or if invoices are added retrospectively.

  • Simple sequence: INV-0001 is compact and straightforward for a single register.
  • Year in the ID: INV-2026-0001 makes the year visible, but decide whether the counter continues or resets each year.
  • Separate document types: Use clearly distinct labels for invoices and other records, and make sure each sequence is managed consistently.
  • Permanent issued IDs: Store finalized numbers as values or use a system designed to assign and track them.

Do not put sensitive customer information in an invoice ID. Numbers are often included in emails, file names, and payment references, so a predictable sequence is usually more practical than embedding a name or other private detail. Also avoid assuming that a particular prefix or format makes an invoice valid; the number is an identifier, not a substitute for the other information your invoice or local rules may require.

Frequently Asked Questions

How do I automatically number invoices in Excel?
Put ="INV-"&TEXT(ROW()-1,"0000") in A2 when row 1 contains headers and the first invoice is in row 2. Fill the formula down to create sequential display numbers.

How do I start invoice numbers at 1001?
With the first invoice on row 2 and its number in A2, use ="INV-"&TEXT(ROW()+999,"0000"). Row 2 returns INV-1001; the next row returns INV-1002.

Can Excel create an invoice number only when I enter a date?
Yes. With the invoice date in B2 and the number in A2, use =IF(B2="","","INV-"&TEXT(ROW()-1,"0000")) and fill down. It displays a number only when the corresponding date cell is not blank.

Will an Excel invoice-number formula stay the same after sorting?
Not necessarily. A formula that uses ROW() calculates from the row where it currently sits, so a sort or structural change can alter the result associated with a record. Convert issued numbers to values or use a controlled numbering method when IDs must remain fixed.

How many zeros should I use in TEXT?
Use as many zeros as the minimum width you want. The format 0000 displays 1 as 0001, while larger values can display more digits; choose a wider format if you want consistent six-digit IDs.

Practical takeaway

For a simple invoice register, a prefix joined to a formatted row number is quick to set up and easy to understand. Match the row offset to your sheet, test the first few results, and use a blank-checking IF formula if you pre-fill empty rows. Treat the generated number as a changing calculation until you deliberately save it as a permanent value. That distinction helps prevent a convenient spreadsheet formula from silently changing the reference printed on an invoice already sent to a customer.

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.