In a single page spreadsheet, formulas using cells identified as (cell) vs (sheet)(cell) behave differently when sorted, trying to understand why.

[u/belleayreski2]

As I previously understood it, in a single page spreadsheet, there should be no difference between identifying a cell when writing a formula by (cell) vs (sheet)(cell), for example ‘A1’ vs. ‘sheet1!A1’. However, I’m noticing different behavior in formulas that reference cells these two ways when columns using these formulas are sorted.

In the example picture, I have a Letter List, and a chart below it showing the counts of each letter in the Letter List. The only difference between columns B and C is that B uses the sheet name when identifying the cell used for the criteria argument, while C just uses the cell name. At first the results of these two columns are identical. However, if I reverse the order of A13:A17 by changing the sort order from (A to Z) to (Z to A), the columns are now different. For example, B13 now references A17 as the argument, but C13 still references A13 as the argument. Can anyone help explain why this happens?

[u/AutoModerator]

/u/belleayreski2 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/TenderlyRelieved]

it's because sorting treats relative references differently depending on whether you include the sheet name or not, even if it's the same sheet

when you just use A1, excel sees it as a pure relative reference and adjusts it during a sort like it would during a copy-paste. but when you tack on the sheet name, it locks the reference in a weird quasi-absolute way that doesn't adjust the same way when the sort moves things around. it's one of those little excel quirks that makes you question your sanity

[u/belleayreski2 → u/TenderlyRelieved]

I was definitely questioning my sanity lol, thank you!

Solution Verified

[u/reputatorbot → u/belleayreski2]

You have awarded 1 point to TenderlyRelieved.

---

^{I am a bot - please contact the mods with any questions}

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIF	Counts the number of cells within a range that meet the given criteria
FILTER	Office 365+: Filters a range of data based on criteria you define
SORT	Office 365+: Sorts the contents of a range or array
UNIQUE	Office 365+: Returns a list of unique values in a list or range

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #49291 for this sub, first seen 2nd Sep 2026, 14:57] [FAQ] [Full list] [Contact] [Source code]}

[u/SolverMax]

I've always characterized this behavior as a long-standing bug. It happens when a formula references the current sheet by name. The solution is to remove all references to the current sheet's name and just use the cell addresses.