Have you heard about the use of infinite ranges in formulas in Google Sheets? To make a formula auto-updating when inserting rows or columns, you must use infinite ranges.
Let’s see how to use infinite ranges in rows, columns, as well as in rows and columns together in Google Sheets.
Assume you have few numbers in the range A1: A10. How to find the total of these numbers?
We are using Google Sheets. So no need to pick the calculator. You can use the basic math function SUM for this.
In cell B1 you may enter the below formula, right?
=sum(A1:A10)
In this formula, we have only used finite ranges. So if you enter any value in cell A11, it won’t update in the formula.
See how to transform this formula to auto-include newly entered values in new rows.
How to Make a Formula Auto-updating When Inserting Rows or Columns in Google Sheets
Make a Formula Auto-updating When Inserting Rows
I am taking the just above SUM formula example. To make the above SUM formula covers an entire column use the range in the formula as below.
Infinite Ranges in Row:
=sum(A1:A)
This formula would cover the entire column A. So you can call it an auto-updating formula.
Make a Formula Auto-updating When Inserting Columns
You have already learned the SUM formula that auto-updates when inserting new rows. Here is column use.
Again I am using the SUM function. This time I have numbers in the cells A1, B1, C1, D1, and E1.
=sum(A1:E1)
This formula would return the total of the numbers in the said cells. This formula won’t update the result when you insert a new value in cell F1.
Similar to Row, you can make this formula auto-updating when inserting new Columns in Google Sheets.
Infinite Ranges in Column:
=sum(A1:1)
This way you can make a formula auto-updating when inserting rows or columns in Google Sheets.
Here is a different scenario. The above two examples show auto-updating formulas in rows/columns.
What about using infinite ranges column and rows in a single formula?
Formula That is Auto-updating When Inserting Rows or Columns
In the real sense, it’s not possible. But there is a workaround. To understand what I am trying to say please read on.
I have a Google Sheets (tab name Sheet1) with 1000 rows and 26 columns. But the rows and columns may increase in the future.
In the same sheet, in tab2 (tab name Sheet2) I want a formula to count the numbers in that whole Sheet. What would be the formula?
=sum(Sheet1!A1:1000)
This formula can auto-update infinite columns. But the rows are limited to 1000 rows. There are two options to make this formula auto-updating when inserting additional rows.
Either use a large number like 50,000 as the last row number. For example;
=sum(Sheet1!A1:50000)
Here is another option. Here instead of putting any large number, the COUNTA counts the values in the cell A1: A in Sheet1 and uses that value as the number of rows.
Since it’s an indirect range, you should use the INDIRECT function to convert it as a normal data range.
=sum(indirect("Sheet1!A1:"&counta(Sheet1!A1:A)))
If you don’t have blank cells in between in this range in A1: A, you can use this formula.
Follow the above tips to make a formula auto-updating when inserting Rows/Columns in Google Sheets.
