Excel is one of the top choices for complex data analysis and reporting. It allows users to organize massive datasets in tabular form. Data can easily be pulled from other sheets or workbooks to establish a dynamic connection that updates information automatically.
References to cell or cell ranges in other spreadsheets are called external references.
It is a powerful feature that simplifies complex tasks and lets you automate various tasks. The main goal is to save users time and effort updating the latest data from multiple sheets.
Welcome to this guide, where we will learn how to reference data from another sheet in Excel. Let’s examine various methods of referencing data from different workbooks.
Please read the article to the end so you understand the critical steps to referencing data in Excel.
Reference Data From Another Sheet
There are two main methods for referencing data in Excel, depending on whether you want to pull data from the same or a different workbook.
A Workbook refers to a file that contains multiple sheets. You can reference data from locally saved and cloud-based workbooks on OneDrive.
Note that you don’t need expert knowledge to reference data in Excel. You can do it even with basic knowledge of Excel.
Let us start by learning to reference data from the same workbook.
Reference Data from different sheets of the same workbook
This is the most common scenario where you are asked to reference data from different sheets within the same workbook. It can be beneficial when working with large datasets spread across multiple sheets.
For instance, consider the following workbook with two sheets named “Sheet1” and “Sheet2”.
Our task is to pull data from Sheet2 to Sheet1.
There are three methods to reference data from different sheets within the same workbook.
- Using the Direct Cell Reference
- Using Named Ranges
- Using the INDIRECT function
METHOD #1 – Using the Direct Cell Reference
This might be the easiest method of pulling data from one sheet and populating it in a different one.
To understand it more clearly, let’s consider the following table.
We have employee data, including their name, age, and city.
As you can see in the above image, the fourth column, State, is empty. Our task is to fill it with the required details.
Let us assume that the state data is available in “Sheet2”.
Now, we will use the Direct cell reference method to import the State from column B of Sheet2 to the main employee data table in Sheet1.
Here is the formula,
=(SheetName!CellRef)
Where,
- “SheetName” is the name of the sheet from where you wish to reference the data
- “CellRef” is the cell reference holding the data you want to pull.
Remember to use the “!” between the Sheet name and the cell reference, without which the formula will return an error.
Let’s start,
- Open the desired Excel file and go to the “Sheet1” tab
- Click on the cell “D2”
- Type the equal “=” symbol to insert a formula in the selected cell
- Next, type ”Sheet2” so that the formula will refer to the second worksheet
- Type “!”
- Enter the cell reference as “B2”
- Press the “Enter” key
So, the final formula will be as follows,
=(Sheet2!B2)
Note that the above formula includes two different sections: SheetName and CellRef. The “!” symbol separates both of them.
To apply this formula to the rest of the cells of column D,
- Click on cell “D2”
- Hover to the lower-rightmost corner
- You will see a “+” icon
- Click and drag the “+” icon to the end of the table
The formula will get populated into the rest of the cells automatically. You can refer to the above GIF for detailed steps.
Method #2 – Using Named Ranges
Before referencing data using named ranges, it is important to understand that a named range is a label assigned to a group of cells. Such a name makes it easier to refer to a range without manually selecting the cell range every time.
The method works best, especially when working across multiple sheets within the same workbook.
Let us consider the below example to understand it better.
The first sheet, “Sheet1,” contains the employee details, such as their name, age, city, number of hours worked on weekdays, and number of hours worked on Weekends.
There is a second sheet named “Sheet2” on which all total working hours on Weekdays and Weekends are to be calculated.
Our task is to calculate the working hours of employees working on both Weekdays and Weekends. Note that we are supposed to use the Named Ranges feature for this task.
Let’s start by naming the cell range that holds working hours on weekdays:
- Go to the desired sheet
- Select all the cells from “D2” to “D8”
- Hover to the main menu and go to the “Formulas” tab
- Click on “Define Name” in the “Defined Names” group
- A dialog box will appear as shown below
- Type the desired name in the “Name” field
Make sure your name does not contain any spaces. As you can see in the following image, we have named the range of cells as “WeekDaysHours” without any space) - Click the “OK” button once done
Next, we need to name the cell range, E2 to E8, as “WeekendHours”.
Once you properly name the cell ranges, you can calculate the working hours using the following steps.
- Open the second sheet where we need to calculate working hours
- Click on the cell “B2”
- Type “=SUM”
- Select the first option from the popup or press “Tab” key
- Type “WeekdaysHours“
- Complete the formula using “)“
- Press “Enter” on your keyboard
When using the SUM function in Excel, you need to tell it exactly which cells to add up. However, if you’ve created named ranges for groups of cells, you can just use the name instead of typing out all the cell references.
Our final formula is as follows,
=SUM(WeekdaysHours)
Similarly, we will use the following formula to find the sum of weekend working hours.
=SUM(WeekendsHours)
Named ranges make your formulas simple and more readable, as we discussed.
Method #3 – Using the INDIRECT Function
As the name suggests, this function is used to reference cells indirectly; cell ranges from other sheets or workbooks.
It lets you create a dynamic cell or range reference. The General Syntax of the INDIRECT function is as follows,
=INDIRECT(ref_text,[a1])
Where,
- “ref_text” – This must be a text string or the cell reference holding the text, or a named range
- “a1” – It is an optional argument that is not useful while referencing data from other sheets or workbooks
Consider the following example where we have employee data in the first sheet named “Sheet1”.
And here is the second sheet, which contains the states to be pulled.
Here are the steps,
- Go to the “Sheet1“
- Click on the cell “D2”
- Start typing “=INDIRECT”
- Select the first option from the popup or press “Tab” key
- Replace the “ref_text” argument with the “Sheet2!B2:B8”
(Make sure to use the double quotation marks; otherwise, the formula will not work as expected) - End the formula with a closed parenthesis “)”
- Press the “Enter” key
The final formula would be like,
=INDIRECT(‘Sheet2!B2:B8”)
Where,
- “Sheet2” is the name of the reference sheet
- “B2:B8” is the range of cells from where the data is being extracted
Do not forget to use the “!” symbol between the sheet name and the cell range. Otherwise, the formula may return an error.
Reference Data from sheets of the different workbook
This is another scenario where we are asked to import data from another workbook.
For example, you may be working on a workbook and wish to pull data from a separate workbook stored on your local drive or OneDrive.
It is a pretty straightforward method similar to referencing data across the sheets of the same workbook.
Let us consider the following example containing a single sheet with the employee details as shown below,
Here is another Excel workbook that contains the employee names and their states.
Our task is to import the states from the second Excel workbook to populate the State field of the first workbook.
- Open both the source and destination Excel workbooks
- In the destination workbook (first workbook), click on the cell “D2” where you want to place the referenced data
- Type “=”
- Navigate to the source (second) workbook
- In the source workbook, select the range of cells you want to reference
(Excel will automatically insert the selected cell or cell range in the formula bar in the destination workbook) - Once you have selected the data, press the “Enter” button
The final formula would look like,
='[Test-Reference Data 2.xlsx]Sheet1'!$B$2:$B$8
Where,
- “[Test-Reference Data 2.xlsx]” is the source workbook
- “$B$2:$B$8” is the data range that is to be imported
There is no need to enter the formula here. For your convenience, Excel will automatically enter the sheet name and the selected cell reference.
Note:
- Always remember to save the workbooks to ensure the reference is maintained when they are reopened. Excel will prompt you to update the links if the source file is renamed or moved.
- If your workbooks are stored in the cloud like OneDrive or SharePoint, Excel can update the references automatically without manually linking the files again.
FAQs
Q. What does reference mean in Excel?
In Excel, reference refers to identifying specific cells or ranges within the same or different workbooks.
With cell references, you can easily pull data and ensure it is automatically updated whenever changes are made to the source file. References can point to a cell (A1) or a range of cells (A1:A10).
Using this, you can manage complex datasets and link information across multiple sheets or workbooks in Excel.
Q. What are the two types of references?
This article discusses the complete steps to reference data from the same or different worksheets.
But do you know that there are two types of references in any spreadsheet application:
- Relative References: This type of reference adjusts automatically when copied or moved. For example, if you have a formula in cell A1 that references data from cell B1, and you copy that formula to cell A2, it will automatically reference cell B2.
- Absolute References: This type of reference remains unchanged when copied or moved. You must use a dollar ($) sign before the column or row reference to create an absolute reference. For example, if you have a formula in A1 that always points to B1, you would use the formula “=B$1”.
Q. How do you pull data from another sheet in Excel with formatting?
Sometimes, we pull data from another sheet of the same workbook and ensure the formatting remains the same.
In this case, Excel’s Format Painter tool is our ultimate option.
Here are the steps,
- Open the Excel file
- Select the data you want to pull from the source sheet and copy it by using “Ctrl + C”
- Move to the destination sheet and paste the data
- Next, go back to the source sheet and select the data you copied in the second step
- Hover to the main menu and go to the “Home” tab
- Select the “Format Painter” as shown below
- Finally, go back to the destination sheet and select the first cell of the table
Conclusion: How To Reference Data From Another Sheet In Excel
That’s all about referencing data from other sheets in Excel. We have explored different, easy-to-use methods.
You can easily create an external reference to leverage data from other worksheets in your calculations.
If you have any issues or questions, please let us know in the comments section. Also, make sure to explore our blog section for more tips and tricks on how to use Excel like a pro.