Reference Data From Another Sheet

There are occasions when displaying a result from a function would be better situated on another sheet within a spreadsheet, so how is it possible to reference data from another sheet?

Believe it or not, this is quite common and straightforward; the function will behave precisely the same way; it simply looks a little different, that's all.

Simply go to the other sheet where you want to display the result (in my case, sheet2) and start typing the formula out =SUM( in a cell on that sheet.

Then click on the original sheet (sheet1), select the range you want, close the parenthesis, and hit enter. The formula looks a little different now; it’s similar, but it also has the sheet's name included in the formula.

The formula would look like this if we did it on the original sheet =SUM(B5:B), but now that we’ve created this result on the second sheet and referenced data from the first sheet, it looks a little different now =SUM(Sheet1!B5:B).

Everything works exactly as before, but since the results are on another sheet, both Google and you will want to know where you can locate the data. Therefore, the name is shown along with an exclamation mark ‘!’ separating it from the range.

You can, of course, just type everything out manually on the second sheet without going back to Sheet1 once you know how this works but be careful since things operate slightly differently if the sheet's name has spaces in it.

I’ve renamed ‘Sheet1’ to ‘Sheet One’ in this example. Now, you can see this also includes single quotes around the name.

The only difference now is these single quotes wrapping around the name. It’s subtle, but it’s certainly one to watch out for when you first start learning, as you can easily overlook something like this.

• Sheet name without spaces =SUM(Sheet1!B5:B)

• Sheet name with spaces =SUM(‘Sheet One’!B5:B)

Without them, the formula wouldn’t work; you would see an error like this below.

Outside of this minor nuance, referencing data from another sheet is not too taxing. We’ll cover many examples throughout these resources, so don’t worry you’ll see this come up fairly often.