If you are working on a huge project causing very complex spreadsheets then it is necessary that you divide your data into multiple sheets so your data remains neat and clean. But it might seem like this will make it harder to do calculations with data on other sheets, but fortunately, Google Sheets makes it easy to reference data on other sheets in your spreadsheet. With Google Sheets, you can quickly access data from a different sheet.
With this feature of Google Sheets, you can relate multiple spreadsheet sheets in Google Sheets. If you are suffering from the complexity of the spreadsheet then this article on how to reference another sheet in Google Sheets is going to be very helpful to you and for your project. So read this article in which we will convey a detailed and easy explanation of referencing data from another sheet In Google sheets.
Why Reference Another Sheet in Google Sheets?
Google Sheets is the platform that produces mega projects in which you may often need to reference data from other sheets in Google Sheets. It’s a simple way to easily compare data from different sources, without having to manually copy and paste between multiple sheets. There are many reasons why you may want to reference data from other Google Sheets. Sometimes reference data from one sheet into another or even merge your spreadsheets together for a master view of your data.
Let’s suppose you are making a project for sale, purchase, and profit, usually a professional makes a master sheet in front and the other individual data on different sheets where referencing data from another sheet is the most useful tool.
Let’s say you have two spreadsheets in different files. The first Google spreadsheet contains the names of customers and the prices of the items they bought. The second sheet contains only the total amount spent by each customer. If you need to see how much each customer has spent, you need to reference the second sheet in the first one. If it is so and you need to reference data from another sheet then here is a tutorial on How to reference another sheet in Google Sheets in the next section.
How to Reference Another Sheet in Google Sheets
There are so many ways to reference data from another sheet in Google Sheets but in this tutorial, we will learn the easiest way to reference another sheet in Google Sheets. You can reference a single cell from another Google Sheet, or multiple cells, and pull data from another sheet into your current sheet according to your requirements. In this procedure, we have some kinds of grocery items on the first sheet and their categories on the second sheet. Now here we will categorize them with the help of using reference data from another sheet in Google Sheets. Let’s see how it will be practically done. The steps are very easy. Let’s move towards them without wasting time.
In this tutorial, you will have to deal with the data in two sheets. So, let’s prepare our both sheets.
Here I have taken a blank file and this is my first sheet.
This first sheet is my sample data of some kinds of grocery items.
Now I will take my second sheet to reference data from another sheet. To add a sheet to your spreadsheet there is an add button at the left bottom corner of the Google sheet as shown in the picture. Click on it to add more sheets to your spreadsheet.
This is my second sheet where I have described some different categories of grocery items.
I will recommend that you reference the sheet in Google Sheets. It is better to first give a name to the sheet.
Right-click on the sheet button at the bottom of the Google sheet window, and a drop-up menu will open where you will find the “Rename” option. Click on it to rename the sheet.
As you can see in the following picture sheet has been renamed.
Now we are ready to reference data from another sheet. First look at your cursor in the cell where you want to fetch the data from another sheet.
Write an equal sign first then write the sheet name in a semi-column as I have indicated in the following picture. As my sheet name is ‘Categories‘.
After writing the sheet name you have to give the cell reference of the data which you want to reference in another sheet. As you can see in the picture I have to reference “Grains and Preserves” whose cell address is “B4“.
So here I have written “B4” after the sheet name but before the cell address does not forget to write an exclamation mark sign. You will see your formula color will be changed which means your syntax is correct. Now just press enter.
As you press enter key, the cell will be automatically referenced with the data on another sheet that we selected. As you can see below in the picture there is data in the cell and reference in the formula bar.
Similarly, your reference will display whatever data is currently in the referenced cell on the other sheet and update just like a regular cell reference
If you are finding difficulty in writing the sheet name and cell address, there is a simple way of referencing data in another sheet which is described in the following steps.
First, just write an equal sign in the cell where you want the reference data from another sheet.
After writing equal signs directly go into the second sheet from where you want to reference data and click on the cell which you want to reference. It will automatically pick the path of the data cell which you want to reference.
Now come back to the first sheet where you just wrote the equal sign, now you will see there is a whole reference that you have picked from the other sheet. You are almost done now just press enter.
As you can see the result has been referenced in the category of grocery items from the categories list on another sheet.
In this method, you have seen that you did not have to write a sheet name or any cell address to fetch the data. Google sheet automatically picked the whole address with just one click. If you don’t want to write a data address then you can prefer the above method.
As we told you there are so many methods of referencing another sheet in Google Sheets such as by IMPORTRANGE function, and by query formula but it depends on our needs. My best opinion to reference another sheet in Google Sheets is the above method which is quite simple and useful. In the above article on how to reference another sheet in Google, we have learned how we can fetch data from another sheet and can relate multiple sheets in Google Sheets.
Hope you have got all the steps clearly as we tried our best to deliver the easiest way. For more best solutions keep connecting with Office Demy.