Discover the power of array formulas in Google Sheets to perform complex calculations on multiple rows or columns with a single, dynamic formula. This guide will walk you through the basics.
Click on the single cell where you want the results of your array formula to begin. This cell will contain the entire formula.
Begin writing your formula as you normally would, but use a range of cells instead of a single cell. For example, to multiply values in columns A and B, you would type =A2:A10*B2:B10.
To convert your standard formula, you can either manually wrap it with the ARRAYFORMULA() function, like this: =ARRAYFORMULA(A2:A10*B2:B10).
Alternatively, after typing your formula with ranges, press Ctrl + Shift + Enter on Windows/ChromeOS or Cmd + Shift + Enter on a Mac. Google Sheets will automatically add the ARRAYFORMULA() wrapper for you.
Press Enter to confirm the formula. The results will automatically populate, or 'spill,' into the adjacent cells below or to the side. The entire output is controlled by the single formula in the first cell.
Pro Tips for Using Array Formulas
Array formulas are incredibly powerful when combined with functions like IF, VLOOKUP, SUM, or FILTER to perform complex operations across entire datasets.
The results of an array formula update automatically when you change the data in the source ranges. There's no need to drag-fill formulas down a column.
You cannot edit or delete individual cells within the output range of an array formula. To make changes, you must edit the original formula in the first cell.
Use array formulas to create dynamic data sources for charts or to apply complex conditional formatting rules based on multiple criteria.
Explore other Google Sheets guides and shortcuts