How to Reference Another Sheet in Excel (Step-by-Step)
An Excel sheet is quite big with 17.2 billion cells in it alone.
Okay, yes. But this doesn’t mean that you necessarily must fit all your data into one sheet. Even if your data is spread across multiple sheets, you can still refer to it and use it just like it sits on a single sheet.
How? By external referencing to another sheet 📝
An external reference is a reference in Excel that refers to a cell or cell range outside the worksheet. This reference works like an internal references and is simpler than you think to create.
Want to learn how to do that? If yes, get your free practice workbook for this tutorial now, and let’s slide in.
How to reference another sheet by writing the sheet reference
Referring to another sheet is all about writing the sheet’s (and the relevant cell in it) name in a specific format 📚
Write the sheet name followed by an exclamation mark and then the cell name. Like here:
For example, if we want to refer to Cell C4 in Sheet2 in Excel which by the way contains the following data:
We will write it up as follows:
I have only written an equal to sign and then the Sheet + cell reference so Excel has reproduced the exact contents of the referred cell 👉
But you can also use it in formulas as normally as you’d use any internal reference. Like here.
Whenever you’re referring to another worksheet, make sure to begin with an equal to sign (=), or use it in a formula after an operator. Only writing the sheet reference above (without an equal sign or not as a part of a formula) will give Excel the impression of it being a text string.
Kasper Langmann, co-founder of SpreadsheetoThe above guidance deals with sheets that are standardly named by Excel.
However, you can also name sheets in whatever way you want, like I have named a sheet as “Data – 20 May 2024”.
The sheet name “Data – 20 May 2024″ includes space characters and a non-alphabetical character (hyphen). When you name your sheets such that they include a space character or a non-alphabetical character you cannot refer them followed by an exclamation mark.
Excel will fail to recognize such sheets 🤯
These sheet names must be enclosed in a single quotation mark. So the external sheet reference format for sheet names containing space and non-alphabetical characters is:
Referring Data – 20 May 2024 becomes:
This is how you can create a reference to a cell from another worksheet.
And why only a single cell? It can be a range of cells, too.
An easy way to seek data from any, and every part of your workbook, isn’t it? 💁♀️
How to reference another sheet automatically
The things discussed in the above section mostly explain all about creating references to another sheet in Excel.
However since it is a manual method, it might take a lot of time and can be prone to human errors 🏓
Excel will fail to recognize the sheet if the reference is not correctly hardcoded, and you might unnecessarily spend too much time figuring out what went wrong.
You can help with these problems by directing Excel toward the relevant cell of another sheet and Excel will do the rest.
For instance, to refer to Cell C4 from Sheet2:
Step 1) Activate the cell where you want the sheet reference placed.
Step 2) Insert an equal to sign (=).
Step 3) Go to the sheet that you want to create an external reference to (by clicking on that sheet’s tab from the sheets’ panel on the bottom) 🚀
Step 4) Within this sheet, click on the relevant cell.
I have selected C4 of Sheet2.
See in the formula bar how Excel has automatically included the reference of this cell in the activated formula.
Step 5) Press Enter.
Excel will conclude the formula as soon as you press Enter and navigate you back to the original sheet where you started typing the formula.
Fascinates me how Excel automatically draws the external cell reference that you would have to hardcode otherwise.
This is just a simple external reference, but what if you want to use it in a formula?
Like to multiply Cell C4 of Sheet2 to 100? 🙈
Step 6) Once Excel has picked an external cell reference from another sheet, add a multiplication operator (*) to it and then write 100.
Step 7) Only press Enter when the formula is complete.
Until you have concluded the formula in this case, don’t switch the sheets.
Similarly, if you want to add the value in C4 of Sheet 2 to Cell B4 of Sheet 1:
Step 8) Once Excel has picked the cell reference for C4 from Sheet 2, add the addition operator (+).
Step 9) Now go to Sheet1 and click on B4.
Excel will automatically draw the reference for B4 from Sheet1 🏍
Step 10) Press enter to get the results.
Also, if you have differently named sheets, this method works best. Save time to hardcode complex names in single quotation marks.
Step 11) Begin writing your formula and refer to the relevant cell from the relevant sheet.
Excel will look after the syntax of the external cell reference itself.
Conclusion
If you’ve been with me until here, you now know how to reference another sheet in Excel.
The whole science of spread-sheeting is deeply rooted in referencing – referencing other cells, cell ranges, different worksheets, tabs, and even different workbooks 💪
Modern-day Excel offers all the flexibility and compatibility you need to create active references to other parts of your workbook. To make the best use of these versatile features of Microsoft Excel, read out my following Excel tutorials that explain all the ins and outs of doing so.
Written by Kasper Langmann
Hi, I'm Kasper Langmann👋
I'm the co-founder of Spreadsheeto, a certified Microsoft Office Specialist, and a Microsoft MVP.
With over 10 years of experience, I’ve taught Excel to millions of people worldwide.
I spent over 13 hours researching and writing this tutorial.
Last updated on August 29th, 2024.
Before you go, sign up for my free Excel course (+100,000 students) ->
