Cell References Messed Up? How to Avoid Wrong Cell Links When Sorting in Excel

This problem is very annoying and not very reasonable: When you sort your Excel table, for example using filters, cell references messed up. Instead of linking to a cell in the same row, they suddenly refer to a cell in a different row. The dangerous part: Often, you want immediately notice it – and something like this might happen… Here is the reason and how to fix broken cell references after sorting data.

Summary

In a hurry? Here is the summary:

Remove links to own worksheet before sorting an Excel table.
Find & replace might be the fastest solution here.

Problem: Cell References Broken When Sorting Excel Tables

Admittedly, this is a curious problem. After you sort a table, the cell references within this table are broken.

Sorting this Excel table leads to... — Sorting this Excel table leads to…

For example, your original table looks like this:

... messed up cell references. — … messed up cell references.

After you have sorted the table by the amount sold in column C, the function for Robert looks like this:

The solution, if cell references are messed up: Remove the worksheet name

The solution for this problem is quite simple: Remove the worksheet name from the cell reference. That way your links will adapt when sorting your table. So, instead of

=Sales!C4*Sales!D4

you just write

=C4*D4

Sounds simple, right? It has the further advantage that your formula is shorter and easier to follow up.

The solution: Remove the sheet name from the cell references so that the cell references don't mess up when you sort the table. — The solution: Remove the sheet name from the cell references so that the cell references don’t mess up when you sort the table.

For our example from above, the solution would look like this.

Methods for removing worksheet references from cell links

Method 1: Removing the own sheet name in the reference manually

As usual, deleting the own worksheet name manually might be a fast solution. When you work with tables, you probably just have to do it once and copy & paste your function to all cell in the respective column.

Method 2: Use Find & Replace

Use the "Find and Replace" function to remove the own worksheet name from cell references. — Use the “Find and Replace” function to remove the own worksheet name from cell references.

The find & replace function is quite powerful. Open it by pressing Ctrl + H on the keyboard. In the “Find what:” field you search for the worksheet name (in this case “Sales!”) and the “Replace with:” field you leave blank. Make sure to include the exclamation mark.

If something looks broken or you receive error messages after replacing the sheet names, you can undo it by pressing Ctrl + Z on the keyboard.

Don't forget to include the apostrophes in the "Find what" field. — Don’t forget to include the apostrophes in the “Find what” field (if your sheet name as any special character in it).

If your worksheet name includes a space (or any other special character – refer to this article for more information), also replace the ‘ characters. Excel automatically inserts the apostrophes when the sheet name has any of these special characters – and also removes them, when you rename your sheet (for example without spaces).