You can number rows like 1, 1.1, 1.2, 1.3 in Google Sheets and that seems simple. You can do it with an array formula which expands. But when you reach the level 2, I mean 2, 2.1, 2.2 etc., you may want to slightly adjust the formula reference.
Actually numbered lists are built-in to Document editors with 1 or more levels. It’s not a part of Spreadsheets. With one click you can get your required multi-level numbering in Docs.
Here you can learn a two-level numbering in Google Sheets, the very popular spreadsheet application from Google.
See this screenshot first which is an example to the two-level numbering in Google Sheets.
Now time to explain to you the formula that you want to use to number rows like 1, 1.1, 1.2, 1.3 in Google Sheets as above.
How to Number Rows Like 1, 1.1, 1.2, 1.3 in Google Sheets
Here is the formula to get sequential numbers which I’ve applied in Cell A2 in my example above.
=ArrayFormula({"1";COLUMNS($A$1:A$1) & "." & ROW($A$1:indirect("A"&counta(B3:B13)))})
The above formula that populates multi-level serial numbers is the combination of the functions COLUMNS, ROW, INDIRECT, ARRAYFORMULA, and COUNTA.
In Cell A14, I want to reset the numbering. I mean I want to start the numbering from 2. Here you should adjust the above formula a little bit. See that.
=ArrayFormula({"2";COLUMNS($A$1:B$1) & "." & ROW($A$1:indirect("A"&counta(B15:B17)))})
What are those changes?
As I’ve mentioned above, this is a two level numbering. As per my example, the first level is the “Chapter” and the second level is its “Category”.
In the formula in A2, I have the number 1 in the formula, that changes to 2 in the formula in A14.
That changes you should manually do for each “Chapter” in the formula. Then there is a COUNTA formula range, there select the range of the “Category” in each “Chapter” individually.
You have learned now how to number rows Like 1, 1.1, 1.2, 1.3 in Google Sheets.
Pros and Cons of Multi-Level Serial Numbering in Google Sheets.
Pros:
You can delete any row in the formula applied range. The numbering will auto adjust every time you delete or insert new rows.
Cons:
This formula needs to replicate when each new “Chapter” begins. So it’s not fully flexible. Also, you need to make some changes to the formula.
If your dataset has up to a maximum of 100 rows, you can use it effectively. But you may want to enter more than one formulas.
For example, in my above example, I’ve two formulas. So you should take care of not to delete the row containing the formulas.
That’s all about multi-level serial numbering in Google Sheets.
You May Also Like:
1. Group Wise Serial Numbering in Google Sheets
2. Sort by Custom Order in Google Sheets [How to Guide]
3. Increment Months Between Two Given Dates in Google Sheets
