OmniDocExcel Auto Numbering Formula: Number Rows Automatically
Learn how to number spreadsheet rows automatically with formulas, including numbering that skips blank rows, restarts per group and holds up under filtering.
Numbering rows by hand works until you insert a row in the middle of a list, delete one, or sort the data. Then every number below the change is wrong, and you have to fix them one by one. A formula solves this: the number is calculated from the row's position or from the entries above it, so it updates on its own. This article is for anyone who keeps a list, log, inventory, or attendance sheet in a spreadsheet and wants row numbers that stay correct. You will get the formulas to use, how to fill them down, and how to keep them accurate when you filter or sort.
The formulas here are standard spreadsheet functions, so they work in any spreadsheet program that accepts formulas. You can also use them in UniCell spreadsheets in the OmniDoc desktop client, which you can download at download.omnidoc.top. This is a separate task from numbering equations or captions in a Word document, so the steps below focus only on rows and cells.
How do I auto number rows in a spreadsheet? The short answer
For a simple list with no blank rows, put a header in row 1, then enter =ROW()-1 in the first data cell (A2) and fill it down. If some rows are blank and should stay unnumbered, use a COUNTA formula that counts only the rows with an entry.
| Your situation | What to do | Why |
|---|---|---|
| Simple list, every row is an item | Enter =ROW()-1 in A2 and fill down | The number follows the row's position and updates after inserts and deletes |
| Some rows are blank or are section breaks | Use =IF(B2="","",COUNTA($B$2:B2)) | Only rows with an entry in column B receive a number |
| Numbering should restart for each group | Compare the group column with the row above | The counter resets to 1 whenever the group changes |
| You filter the list | Use SUBTOTAL(103,$B$2:B2) | It counts only visible rows, so the sequence has no gaps |
Before you start
- Keep one header row. Formulas that use ROW() subtract the header's row count, so the first data row must be exactly one row below the header. If your header takes two rows, change the subtraction to match.
- Choose a column that marks real entries. The COUNTA and IF formulas check one column for content. Pick a column that is filled in for every real item, such as a name or description, so the check works.
- Decide whether numbers should follow rows or stay put. Formula numbers are recalculated from position, so after a sort they follow the new order. If each number must stay with its original record, you will need to convert the numbers to values before sorting (covered below).
- Know whether the list will be filtered. Plain counting formulas keep counting hidden rows, which can leave gaps when someone filters the list. If filtering is likely, use the SUBTOTAL version from the start.
- Use absolute references for fixed starting points. The $ sign in a formula such as $B$2 locks the start of a range, so the range grows downward as you fill the formula but always starts at the first data row.
Setting up automatic row numbers, step by step
- Open the file. Open your spreadsheet in UniCell in the OmniDoc desktop client, or in any spreadsheet program you already use. Make sure the header is in row 1 and the data starts in row 2.
- Choose a column for the numbers. Column A is the usual choice. Leave it empty in the header row so the header stays a plain label.
- Enter the formula for a simple list. In cell A2, type
=ROW()-1. The function ROW() returns the row number, which is 2 in this cell, and subtracting 1 turns that into item number 1. - Enter a blank-safe formula if needed. If column B holds the item names and some rows are empty, type
=IF(B2="","",COUNTA($B$2:B2))in A2 instead. It returns a number only when B2 has content, and the COUNTA range grows as the formula moves down, so each filled row gets the next number. - Fill the formula down. Drag the small square at the bottom-right corner of the cell down to the last row of data. Each row now gets its own formula, adjusted to its own position.
- Add group restarts when needed. If column B holds a category, put the sequence in column A with
=IF(B2<>B1,1,A1+1)in cell A2 and fill it down. Change the column letter to match your group column. Check that the first row of data starts at 1, because the formula compares it with the header. - Switch to the SUBTOTAL version for filtered lists. Replace COUNTA with SUBTOTAL, for example
=IF(B2="","",SUBTOTAL(103,$B$2:B2)). The function counts only visible rows, so when you filter the list, the visible items are renumbered from 1 without gaps. - Convert to values before sorting if numbers must stay fixed. Select the number column, copy it, and paste it as values in the same place. The formulas become plain numbers that no longer change, so sorting the list will not renumber the rows.
Check the result
Once the formulas are filled down, check the column before you use or share the file.
| Check | How to see it | If it's wrong |
|---|---|---|
| First number is 1 | Look at the first data row | Your header may be more than one row, or the ROW() offset is wrong. Change the subtraction to match. |
| Blank rows show blank, not 0 | Look at rows with no entry in column B | Confirm the IF test points to the right column and that the "" value is in the false branch. |
| No gaps after a filter | Filter the list and look at the visible numbers | Switch from COUNTA to SUBTOTAL(103, ...) with the same range. |
| Group numbers restart | Look at the row where the group name changes | Check that the comparison points to the group column and the row above, and that the first row starts at 1. |
| Numbers survive a sort | Sort the data and compare a few rows | If numbers changed, they are still formulas. Paste them as values before sorting, or accept that the numbers follow the new order. |
| No broken references | Look for error values such as #REF! | You probably deleted a column the formula depends on. Re-enter the formula with the correct column. |
If you need to confirm that a formula is correct, select one of the numbered cells and read the formula bar. It should match the pattern in your row, with only the row number changing as you move down the sheet.
Keep numbers right when you sort or filter
Formula numbering behaves differently from typed numbers. A formula such as =ROW()-1 always reflects the current position, so after a sort, the numbers are assigned in the new order. That is correct for a list that should be numbered top to bottom, such as an agenda, but it can be wrong for an invoice log where each number belongs to a specific invoice.
For a log where numbers must stay with their records, sort first and then paste the numbers as values. This freezes each number at its current value, and later sorts move the number along with its row. If you need new rows added later, keep one untouched formula column for new entries and freeze the older ones as values.
For a filtered view that someone else will use, the SUBTOTAL formula is the safer choice. It renumbers only the visible rows, so the reader sees 1, 2, 3 without gaps, while the full list keeps its complete sequence when the filter is cleared.
❓ FAQ
1. Why does my formula show 0 or a blank in the first row?
A blank or 0 usually means the formula refers to the wrong row. Check that the cell with the formula is in the first data row, not the header. With the COUNTA version, a blank result is expected for rows where column B is empty.
2. Why do my numbers change after I delete a row?
Formula numbers are recalculated from position, so deleting a row shifts the numbers below it. This is the intended behavior for a continuous list. If numbers must stay with their original rows, convert them to values before you delete or sort.
3. Can I number a list without using a formula?
Yes. Typing 1 and 2 in the first two cells and dragging the fill handle will create a series, and the numbers stay fixed. The downside is that inserted or deleted rows will not renumber automatically, which is the main reason to use a formula.
4. Is numbering equations or captions in a Word document the same task?
No. Equation and caption numbering in a Word document is done in the document, while the methods in this article apply to rows and cells in a spreadsheet. If you need the numbers in a document, copy the finished values from the spreadsheet or use the document's own numbering features.
📝 Summary
For most lists, =ROW()-1 gives you numbering that updates on its own. Use the COUNTA or SUBTOTAL versions when you have blank rows, group restarts, or filters, and paste values before sorting when each number must stay with its record.
