In this tutorial, you will learn how to pull data from multiple tabs in google sheets.
Splitting up data onto multiple tabs in a Google Sheet can help organize data and make spreadsheets easier to use and maintain, but what if you want to view data from multiple tabs all in one place? While you could copy and paste the information manually, this process would need to be repeated each time data in the tabs changed. The better method is to use Google Sheets formulas to pull data from multiple tabs into a single tab. Read on to learn more
Single Cell Method
The formula to use to pull the value from a single cell in another tab is quite similar to how you’d reference another cell in the same tab, you simply have to add something to the beginning of the reference to specify what tab to pull the data from. Here’s how:
Locate the cell you want to pull data from and note the standard A1 cell reference. For example, it’s in column D, row 5, that’s D5
You’ll also need to know the exact name of the tab the cell is located in. You can find it in the tab at the bottom of the spreadsheet. The active tab will be slightly paler than the other tabs. For this example, we’ll use Sheet1
Now, go to the tab you want to pull the data into and type the following formula: “=Sheet1!D5”, where Sheet1 is the name of the sheet, followed by an exclamation point (!) and then the A1 reference of the cell in that sheet. If you typed the formula correctly, it will highlight orange as in the screenshot below
Note that if your sheet name has any spaces in it, you will need to wrap the name of the sheet in single quotation marks. For example, if the sheet is called “Sheet Number Two”, the formula would be: “=’Sheet Number Two’!D5”
Hit enter and the data from the other tab will be pulled in by the formula and update automatically whenever the data in the referenced tab is updated
You can pull in data from an entire range in almost the same way as you pull in data from a single cell, but you need to wrap the reference in an array formula so Google Sheets knows to display more than one value.
For example, to pull in data from A1:C10 on the sheet called “Sheet Number Two”, use this formula: “=ARRAYFORMULA(‘Sheet Number Two’!A1:C10)”. In the screenshot below, all the data in the selected cells is pulled in by the array formula
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to pull data from multiple tabs in google sheets. Want more? Check out all the Google Sheets Tutorials.