Whenever you create a new google spreadsheet it is better to have series numbering listed for each row in column 1. We already have numbering before the 0th column but, if you have a huge pile of data and if somebody asks to check the 1000th row then you can directly search for 1000 in the search to highlight the particular row. Below, I have mentioned different ways to add series numbering in google spreadsheets.
1. Manual: Entering numbers in each row.
2. Use the drag action: Enter the number 1 in the first row of the column(A1) and then add number 2 in the second row of the same column (A2), then point the cursor to the right bottom corner of the number 2 cell to get a + symbol. Now click on it and drag the cursor below to automatically add the number.
3. Incremental Formula:
Add number 1 in the first row of the first column (A1), now in the 2nd row of the first column add the formula as below.
Consider you have a scenario where you have to count the number from in between say 1000 then, just change A1 to 1000, it will auto-increment from 1000. Try it.
4. ARRAYFORMULA: Consider you want to add numbers till 1000 rows, it will be quite tiresome to use the dragging cursor action or incremental formula dragging. Although you can do it if you have an ample amount of time, there is an easier way to add the auto numbering to any row. You can use ArrayFormula formula to automatically add the numbers in the sheet. Below is the example to add 5 rows with incremental numbers using ARRAYFORMULA
If you want to add numbering till 100 then you just need to change the formula to below
1. If you delete any row from the numbered row then you can observe that the numbering will not be affected, this is not the case when using manual numbering or drag action. Also observe that the formula dynamically changes to A1:A99. Try it.
2. If you want the series numbering to happen for columns then you need to change the formula as
Fun Fact:
1. You can add 5000000 rows keeping only 1 column in the sheet. I was extremely bored on a Quarantine Sunday and I tried adding 5000000 entries in the sheet to check if the browser would crash. It did a couple of times. Find the google spreadsheet below. Don't blame me if your system gets hanged :D
https://docs.google.com/spreadsheets/d/1TbF7t1Vg18LzHQjGjA72a0-oMbFiwULNG4svVLuSXKg/
2. You can add 18278 columns in one spreadsheet if you keep only 273 row in the sheet. As mentioned previously I tried adding the columns to test how it behaves. Click the link below.
https://docs.google.com/spreadsheets/d/11Yl4Fpg7UXvhNVq9iXVPy6t1UQbi_qK0U9Eh5LVVq8I/
I will make another post for filling sequential text and much more in the coming blog posts.
1. Manual: Entering numbers in each row.
2. Use the drag action: Enter the number 1 in the first row of the column(A1) and then add number 2 in the second row of the same column (A2), then point the cursor to the right bottom corner of the number 2 cell to get a + symbol. Now click on it and drag the cursor below to automatically add the number.
3. Incremental Formula:
Add number 1 in the first row of the first column (A1), now in the 2nd row of the first column add the formula as below.
=(A1+1)Now drag the cursor of the 2nd row below to automatically add the numbers.
Consider you have a scenario where you have to count the number from in between say 1000 then, just change A1 to 1000, it will auto-increment from 1000. Try it.
4. ARRAYFORMULA: Consider you want to add numbers till 1000 rows, it will be quite tiresome to use the dragging cursor action or incremental formula dragging. Although you can do it if you have an ample amount of time, there is an easier way to add the auto numbering to any row. You can use ArrayFormula formula to automatically add the numbers in the sheet. Below is the example to add 5 rows with incremental numbers using ARRAYFORMULA
=ARRAYFORMULA(ROW(A1:A5))Add the above formula to row A1 and hit enter.
=ARRAYFORMULA(ROW(A1:A100))Note:
1. If you delete any row from the numbered row then you can observe that the numbering will not be affected, this is not the case when using manual numbering or drag action. Also observe that the formula dynamically changes to A1:A99. Try it.
2. If you want the series numbering to happen for columns then you need to change the formula as
=ARRAYFORMULA(Column(A1:1))
Fun Fact:
1. You can add 5000000 rows keeping only 1 column in the sheet. I was extremely bored on a Quarantine Sunday and I tried adding 5000000 entries in the sheet to check if the browser would crash. It did a couple of times. Find the google spreadsheet below. Don't blame me if your system gets hanged :D
https://docs.google.com/spreadsheets/d/1TbF7t1Vg18LzHQjGjA72a0-oMbFiwULNG4svVLuSXKg/
2. You can add 18278 columns in one spreadsheet if you keep only 273 row in the sheet. As mentioned previously I tried adding the columns to test how it behaves. Click the link below.
https://docs.google.com/spreadsheets/d/11Yl4Fpg7UXvhNVq9iXVPy6t1UQbi_qK0U9Eh5LVVq8I/
I will make another post for filling sequential text and much more in the coming blog posts.