Reference a Different Sheet in Excel: The Ultimate Guide for 2024
In this article, we will show you how to reference a different sheet in Excel. Simply follow the steps below!
How to Reference a Different Sheet in Excel
Below we explain how you can reference a different sheet in Excel:
1. Open Your Workbook
Ensure your Excel workbook is open and contains multiple sheets.
2. Identify the Sheet Name
Check the name of the sheet from which you want to reference data. You can find the sheet names at the bottom of the Excel window.
3. Select the Cell Where You Want to Enter the Formula
Click on the cell where you want the referenced data to appear.
4. Specify the Cell Reference
Start your formula with an equal sign. Type the name of the sheet you are referencing. Follow the sheet name with an exclamation mark. After the exclamation mark, enter the cell reference from which you want to pull the data.
For instance, if you are referencing cell B2 from the “Inventory” sheet, your complete formula looks like this:
=Inventory!B2
5. Press Enter
After typing the formula, press Enter to display the data from the referenced sheet in your selected cell.
How to Reference Another Sheet Based on Cell Value
To reference a cell from another sheet in Excel based on a cell value, you can use the INDIRECT function.
For example, if you want to reference cell A2 from the sheet named in cell A1 (e.g., “Inventory”), your formula will look like this:
=INDIRECT(A1 & “!A2”)
How to Reference a Range of Cells from a Different Sheet
To reference a range of cells from another sheet, specify the range using a colon.
Example: To sum all values from cells A1 to A10 on the “Expenses” sheet, you would use:
=SUM(Expenses!A1:A10)
How to Use Sheet References in Functions
You can include the sheet reference as part of the function’s arguments.
Example: To find the average sales from B1 to B5 on the “Quarter 1” sheet, you would use:
=AVERAGE(‘Quarter 1’!B1:B5)
We hope that you now have a better understanding of how to reference a different sheet in Excel. If you enjoyed this article, you might also like our articles on how to reference a worksheet name in Excel and how to remove circular references in Excel.
