Learn how to use the ARRAYFORMULA in Google Sheets to autofill arrays. Drag down formulas automatically!
Have you ever been in a situation where you need to drag down a formula in your spreadsheet? Maybe when adding new data, you also need to adjust the formulas.
Worry no more! With the ARRAYFORMULA, you can make your Google Sheets more dynamic.
The ARRAYFORMULA in Google Sheets makes your calculation for the entire column. It replaces a series of formulas with just one.
ARRAYFORMULA in Short
The ARRAYFORMULA is a function in Google Sheets. It is used in combination with other formulas in order to automatically fill the column, avoiding copy-pasting down the formula.
Using the ARRAYFORMULA in Google Sheets
For example, you might want to calculate the sum of three columns. Then you might add a formula showed below and copy paste it down.
However, this copy-pasting can be avoided by an ARRAYFORMULA. Then you add the ARRAYFORMULA before taking the sum of the columns. Here you need to add the array of the columns - such as "C3:C" - instead of the cell "C3".
It is good practice to add an IF-formula in the beginning of the ARRAYFORMULA. This way you avoid showing zeros when you do not have an invoice name.
See how it can be done in the video below.
🔥Hot tip!
Pressing Ctrl + Shift + Enter while working on a formula in Google Sheets will automatically add the ARRAYFORMULA.
Try it yourself now!
Click here to copy paste the Google Sheet spreadsheet in the video.
Comentários