ExcelDailyDrills

ARRAYFORMULA, IMPORTRANGE and GOOGLEFINANCE in Google Sheets

Three functions make Google Sheets different from Excel: one applies a formula to a whole range, one pulls data from another spreadsheet, and one fetches market data.

ARRAYFORMULA: one formula for a whole column

=ARRAYFORMULA(A2:A*B2:B) multiplies every pair of cells in the two columns and shows the results in the cells below, without copying the formula down. Using open-ended ranges such as A2:A makes it pick up new rows as they are added.

While editing a formula in Google Sheets, pressing Ctrl+Shift+Enter wraps it in ARRAYFORMULA for you. The output cells below must be empty, otherwise you get a #REF! error.

IMPORTRANGE: pull data from another spreadsheet

=IMPORTRANGE(spreadsheet_url, range_string) imports a range from another Google Sheet. The range string has the form "Sheet1!A2:B6"; without a sheet name the first sheet is used. Both arguments are text, in quotes or in cells.

The first time you connect two spreadsheets, Sheets shows a #REF! error with an Allow access button. Click it to connect the sheets. If you don't own the source, you need to ask its owner for access. Once allowed, any editor of the destination sheet can read from the source.

GOOGLEFINANCE: market data

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval]) returns market data. With only a ticker, and the attribute "price", it returns the current price. Quotes can be delayed by up to about 20 minutes, so it isn't suitable for trading.

Common mistakes

  • Typing the output range by hand under an ARRAYFORMULA. Any value in the way blocks the result with #REF!.

    Fix: Clear the cells below the formula.

  • Forgetting the quotes around the URL or range in IMPORTRANGE.

    Fix: Both arguments are text: put them in quotes, or reference cells that hold them.

Frequently asked questions

Is there an Excel equivalent of IMPORTRANGE?

Power Query (Data > Get Data) can load data from another workbook and refresh it. A plain cell link to another file also works, but is more fragile.

Does ARRAYFORMULA exist in Excel?

No need: formulas spill automatically in Excel 2021 and newer. Older versions needed Ctrl+Shift+Enter for array formulas.

Related functions