Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You can reference data from other sheets in Google Sheets. You can use a special formula to replicate and copy data from one sheet to another within a single Google spreadsheet. Referencing data from other sheets means replicating data from one sheet to another within a single spreadsheet document. This makes it so you don't have to manually update the spreadsheet or enter data twice. And it has the added benefit of automatically changing the data on multiple sheets when the master data sheet is revised. Let's walk through how to reference data from other sheets using the sales data spreadsheet.
Take a look at this sales data from March 2014. I have three totals here, one for the total items that were sold, in cell C23. One for the total amount of items left in stock in cell D23, and one for the total amount of items on order in cell E23. I'd like to replicate the data for these totals to my totals sheet, which I can access by clicking here. I'd like these totals to be replicated in the March column on the sheet. I'll start with the total items sold for March. To replicate the cell data from the other sheet, I'll first enter an equal sign to start the formula.
Then I'll enter the name of the sheet I'm copying data from. If the sheet name contains a space, or any non alphanumeric symbols, I need to include single quotes around the name of the sheet. I'll take the single quote mark, and the name of the sheet, which is March 2014, and an end single quote. Then I type an exclamation point and the letter and number of the cell I'd like to copy. Now let's look back. I'd like to copy cell C23. So I'll type in C23 here.
My full formula I can see in my formula bar. It reads, equals, single quote, March, space, 2014, end single quote, exclamation point, C23. When I press the Enter key on my keyboard, the contents of cell C23 on the March 2014 sheet will be copied into cell D2 on this total sheet. If I go back to the master sheet and I change the contents of the reference cell, which I'll do here by adding another item to be sold which changes my total to a 159, that number will be revised on the totals sheet as well.
I can reference the other two cells by creating their own formulas here. I can copy the formula, but I need to be careful about referencing the correct cell in the master sheet. Google sheets may try to guess which cell to reference, and sometimes their guess is not correct. I'll copy the formula here by dragging down. And rather than C24, I'll change that cell to D23. And press Enter. Likewise, for total amount left in stock, I'd like to reference cell E23.
So I'll change this to E23. As you can see, the total items on order for March and the total amount left in stock are replicated from the spreadsheet here. Referencing data from other sheets can save you a lot of time. And can help keep you from making costly errors by updating data as it changes. Make sure when you're referencing multiple cells that you carefully check the cells referenced so that your data stays accurate
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 99162 Viewers
80 Video lessons · 141795 Viewers
59 Video lessons · 60163 Viewers
52 Video lessons · 73300 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.