Google Spreadsheet Sorting Bug

I needed to tally votes from a simple poll for determining priorities of some topics.  Several other people need to analyze the results; since they are from different organizations, I decided to use Google Spreadsheets.

Instead of going through the whole process of how I arrived at the bug, I’ll cut to the chase and just explain the problem:

If a Google Spreadsheet uses references to another spreadsheet, sorting the spreadsheet does not adjust the referenced cells correctly.

To demonstrate the problem, here’s a few screenshots:

Google Spreadsheet - Initial State
Google Spreadsheet - Initial State

Note that cell A3 uses formula =’Topics’!A3 in order to display the value from the corresponding cell of the Topics spreadsheet.

Google Spreadsheet - Incorrect Sort Behavior
Google Spreadsheet - Incorrect Sort Behavior

After sorting on column D, notice that columns C & D (in green rectangle) are changed, but columns A & B (in red rectangle) are not.  The result of the sort is incorrect and useless at this point because Topic A, Choice 1 is not truly the top rated item.

To demonstrate a bit further, I changed hard-coded the values in column A rather than have them reference the values in the Topics spreadsheet.  The result of sorting after this change looks like:

Google Spreadsheet - Partly Correct Sort Behavior
Google Spreadsheet - Partly Correct Sort Behavior

Here we see that columns A, B & C have changed correctly, but column B has not because it still uses references to the Topics spreadsheet. (Note the value of cell A3 shown in the Formula bar just above the spreadsheet)

So, is this a bug or not?  I would count it as “unexpected behavior,” but I guess Google will have to determine whether they want fix this or not.  I got the results I expected from Excel, however, as you can see here:

Microsoft Excel - Correct Sort Behavior
Microsoft Excel - Correct Sort Behavior

If you’re interested to tinker with or investigate this issue, you can copy the Excel spreadsheet, import it into Google Docs and tinker away.  Caveat: You may have to set the reference formulas in the Results spreadsheet to point to the appropriate cells in the Topics spreadsheet.  I don’t think Google converts the references to hard values during import, so verify it beforehand.

One thought on “Google Spreadsheet Sorting Bug”

  1. I have been searching the Internet for 5 days straight trying to find an answer to this issue! It seems to me that Google is being just plain stupid about this. There is absolutely no need to sort cells if they make these following mistakes.

    I hope that you can find a solution to this, or that google themselves fix this horrible problem with their google spreadsheets….till now, i’ll just keep using excel.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.