Store an explicit sequence value, format it into an invoice number, then preserve the assigned number as a fixed value. Keep an invoice-level register to check uniqueness; repeated numbers in a line-item table can be intentional.
1. Create an invoice-level register
Use one row per invoice in your register, even if the invoice itself has several services. Keep columns for sequence, invoice number, client, issue date, and status. That gives you a place to see which numbers have already been assigned.
The example below uses an agency prefix and a four-digit sequence. It is a practical identifier format, not a rule about what your business must print on its invoices. Keep the numbering convention already used by your accounting process.
| A: Sequence | B: Invoice # | C: Client |
|---|---|---|
| 41 | AG-2026-0041 | North Studio |
| 42 | AG-2026-0042 | Harbor Works |
Enter the sequence as an explicit value. Choose the next available value from the register rather than restarting at 1 when you create a new spreadsheet.
2. Build a number with a prefix and padded sequence
With the sequence in A2, enter this formula in B2:
="AG-2026-"&TEXT(A2,"0000")If A2 contains 41, the result is AG-2026-0041. Fill the formula down for the other sequence values. Use your own prefix and period. The fixed year in this example stays fixed; it does not change when you open the file next year.
Microsoft's TEXT function documentation explains how a number format becomes text. Here, the zero placeholders pad a short sequence. Keep monetary values in separate numeric columns so formatting an identifier does not affect your calculations.
If your Excel locale uses semicolons between function arguments, replace the comma with a semicolon. Localized versions of Excel may also use a translated function name.
3. Freeze assigned numbers before sorting or exporting
After checking the numbers, copy the invoice-number cells and use Paste Special → Values over those cells. Keep the original sequence in the register. Verify that the assigned identifiers now remain the same when the full table is sorted.
A formula based on ROW() ties the number to a cell's position. Inserting or deleting rows can then change an identifier. A formula based on the current date can also change when the workbook recalculates. Fixed values avoid those two problems once a number is assigned.
Sort the complete table, not just the invoice-number column. Otherwise a stable identifier can still end up paired with the wrong customer. When several people prepare invoices, assign one person or system to allocate the numbers so two separate files do not reserve the same sequence.
4. Check duplicates in the right table
In an invoice-level register with numbers in B2:B1000, this helper formula flags a repeated number while ignoring an empty cell:
=IF(B2="","",IF(COUNTIF($B$2:$B$1000,B2)>1,"CHECK DUPLICATE","OK"))Fill it down and extend the range to include your complete register. Review flagged entries before generating invoices. A check covering only this month's file cannot detect a number used in an earlier file.
A line-item table is different. Two service rows with AG-2026-0041 can legitimately belong to the same invoice. Confirm that the client and invoice-level details agree, then use that number as the grouping identifier in LinkoPDF. Do not remove valid service rows just to make every cell in that column unique.
5. Preserve the identifiers through import
If you use entirely numeric codes with leading zeros, format their input column as text before typing or pasting them. Microsoft describes this behavior in Format numbers as text. Changing a column's format later does not reliably recover characters already lost.
After exporting, inspect the file and compare several IDs with the register. Upload it to LinkoPDF, connect the invoice-number field, and generate a small batch. Confirm that the printed identifiers and client names match the source.
For repeated service rows, continue with the itemized invoice setup. For shifted columns or altered values, use the CSV import troubleshooting guide.