When we work on a sheet with a lot of numeric data, we tend to often perform calculations using formulas. For next calculations, we copy and paste same formula into consecutive cell but what happens when something goes wrong in between and you need to make changes to your data like inclusion or exclusion of data? You would have to change every formula now for that purpose. But, this hustle can be avoided if we start using array formulas.
Array formulas are those formulas that work for the entire range of cells i.e., when such situations like change in data happens while using array formula function, you will just be in need to make a change once and that change will automatically be done for other rows and cells. Syntax for this formula is –
=ARRAYFORMULA( array_formula ), here
array_formula, can be anything from a range of cell or a mathematical expression of single cell range or multiple cell range or a function that return results greater than one cell.
Another shortcut method to apply this formula is –
by using Ctrl + Shift + Enter after entering a non-array function.
How to use ARRAYFORMULA Function
The usage of this function just includes using basic function’s formula under the syntax of an array formula to convert this simple formula into an array formula. This can be best understood with an example, so let’s go.
Here, we a table and we need to calculate the total sum of B, C & D for each product and total of them all further. For this –
- Click in the cell where you want to represent your total.
- In the formula bar, put the formula as : =ARRAYFORMULA(sum(B2:B5 + C2:C5 + D2:D5))
- And that’s it! you will have your total for all.
But the real point of this function will come out when we will make any change in data and this function will facilitate automatic change in total.
Now, if we add another range in our sheet, array formula will detect it and will change whole calculations accordingly.
And now if you will look the formula would get changed to =ARRAYFORMULA(sum(B2:B6 + C2:C6 + D2:D6)) on its own.
Using IF Statement
In a spreadsheet that keeps changing, IF statement is useful when any cell remains empty. Such empty cells can lead to a lot of zeros, thus to edit these zeros, we have to just change the formula at one place and it will edit zeros across the whole range. For example, formula can be made to –
=ARRAYFORMULA(IF(A2:A<>””,B2:B+C2:C+D2:D+E2:E,””)) to tell function to leave total column blank if there is no data or data is removed from column A.
Apart from using this function separately for ranges of rows and columns, this function can also work multidimensionally for both rows and columns. For example, if we look at following data sheet,
The formula to use array formula function multidimensionally can be –
ARRAYFORMULA over text
Such amazing feature can be useful also while we are dealing with text in our sheet. Yes, array formula can be used over text too. For example, if in given data, we need to combine the names
we can use this formula
=ArrayFormula(A2:A5&” “&B2:B5) and our work will be done.
Since, this function works for the batches of data, learning to use this function is a much needed task for saving the time while working regularly on large databases that keeps changing frequently.