Forum Discussion
Excel formulas starting to include current sheet name
Hi
I recently noticed that formulas in excel starting to automatically include the "current" sheet name after leaving and coming back to the sheet (example below). I don't remember this used to be the case but I could be wrong. Does anyone have a similar experience or can confirm if this is correct?
Is there a setting somewhere I can turn this on and off?
Example: I have 2 tables (1 per sheet). After clicking on Sheet2 to select the cell I want to sum, coming back to Sheet1 and referencing any cells will include "Sheet1!" in my formula. I believe previously the formula omits "Sheet1!".
Both ways are useful in different scenarios but wondering if I have missed a trick to "turn on and off" how it behaves.
Thanks!
7 Replies
What you're seeing is normal Excel behavior, and it hasn’t changed across versions. It depends on how you build the formula, not a setting. See Hecatonchire example.
Hello,
For your information, this behavior has never changed. I just checked on Excel version 95 (1995).
I wasn’t claiming Excel behavior changed over time. The issue isn’t about versions at all — it’s about how Text to Columns works:
Text to Columns has always required a consistent delimiter.
The data in the Post does not have one:- Some rows use a comma → New Orleans, LA
- Others don’t → Los Angeles CA
- City names have variable spaces
So this isn’t a version problem — it’s a data structure problem. Even in Excel 95, Text to Columns would behave the same way.
The core rule
Excel can only split cleanly when:
There’s a consistent separator, OR a fixed position is defined.
The data has neither — but it does have one reliable pattern:
The state is always the last word.
I see no link between your answer and NoEggsInBasket's question. I think there is confusion with another question.
You're correct – Excel now automatically adds the current sheet name (e.g., Sheet1!) to formulas after you navigate away and back. This is a default behavior change in newer versions.
No setting exists to turn this on or off, so far I know.
Typing formulas manually is the most reliable way to avoid unwanted sheet names. No toggle available – but deleting them takes 1 second.
My answers are voluntary and without guarantee!
Hope this will help you.
Hello,
The selection order is what causes this difference in behavior.
If you select cell C35 of the current sheet and then cell C35 of sheet 2, you get:
=C35+sheet2!C35.
If you first select cell C35 of sheet 2 and then cell C35 of the current sheet, you get:
=sheet2!C35+sheet1!C35.
It's the sheet switching that causes this.
For your information: the presence of the name (sheet1) in the reference (sheet1!C35) poses a problem when sorting values.
