Here is a quick tutorial on how to get a sequence of decimals in Google Sheets using an array formula.
I am specific on the array formula because we can quickly get the sequence of decimals in a column using the fill handle.
First of all, let’s take a look at the fill handle technique.
To get the numbers 1, 1.1, 1.2, and so on, type 1 in cell A1, 1.1 in cell A2. Then select A1:A2 and drag the A2 fill handle down.
It will generate the required numbers.
That’s without using any formula. If you prefer to use a non-array formula, the same above result you can achieve in the following way.
Please follow the below steps.
Type 1 in cell A1. In cell A2, insert the following non-array formula.
=A1+0.1
Copy cell A2 formula, and paste it down or drag the A2 fill handle down as below.
You can try both of the above two techniques in a row, using the cells A1 and B1, respectively.
Without much effort, we can achieve the same result using an array formula in Google Sheets. Let’s go to that.
Array Formula to Get Sequence of Decimals in Google Sheets
To fill a column with a sequence of decimals in Google Sheets, we can use the SEQUENCE function.
Generic Formula: ArrayFormula(sequence(n,1,10)/10)
The ‘n’ determines the number of sequential numbers that we want.
If you want to fill a column with the sequence of decimals from 1 to 2.9, then the ‘n’ must be 20. Up to 9.9, then use 90 instead.
Example:
=ArrayFormula(sequence(20,1,10)/10)
Additional Tips
The above formula starts the numbering from 1. To begin numbering from 0 like 0, 0.1, 0.2, and so on, set the number from which to start the sequence to 0.
Syntax:
SEQUENCE(rows, [columns], [start], [step])
Formula:
=ArrayFormula(sequence(20,1,0)/10)
Here is one more tip.
We may use the above sequence of decimals in Google Sheets in auto serial numbering. In that case, you might want to filter out some of the decimal numbers.
Here is one such scenario.
How to remove the circled numbers from the output? Here is the current formula in cell B1.
=ARRAYFORMULA(sequence(30,1,10)/10)
The FILTER and MOD combination can work well here.
The following formula will return the sequence of decimals in Google Sheets after applying a filter.
=filter(sequence(30,1,10)/10,mod(sequence(30,1,10)/10,1)<0.6)
Formula Explanation
Syntax: FILTER(range, condition1, [condition2, …])
Part # 1 (Filter Range):
sequence(30,1,10)/10
The filter range is actually the formula =ARRAYFORMULA(sequence(30,1,10)/10)
. I have omitted the ArrayFormula as it’s not a must with FILTER.
Part # 2 (Filter Condition):
mod(sequence(30,1,10)/10,1)<0.6
The use of the MOD is justified as it returns only decimals. The integer part got removed because the divisor in MOD is 1.
For example, =mod(1.5,1)
will return 0.5. I hope you got it.
The FILTER filters the decimal numbers <0.6.
Finally, if you want the array formula output in a row, swap the row number with the column number and the column number with the row number.
I mean the formula =ARRAYFORMULA(sequence(30,1,10)/10)
will change to =ARRAYFORMULA(sequence(1,30,10)/10)
. The same is applicable to the Filter and MOD combo.
That’s all about how to fill a column with the sequence of decimals in Google Sheets.
Thanks for the stay. Enjoy!