This note describes another problem with sorting in a column containing formulas. This one can be tricky to solve especially if you have a lot of a data. This is where a formula within a column, for what ever reason, is inconsistent.
The previous article was about a spreadsheet where a column wouldn’t sort because its formulas all consistently pointed to the following row rather than the same row of data. We showed how useful the trace precedents formula auditing tool was in highlighting where the problem was occurring. The example shown here is from the Scores Again sheet of the Bad Marks example spreadsheet (XLS 49 KB)
A harder to solve sorting problem is shown here. Same data as before, but this time the formula in the first row is correct. When this list is sorted, in descending order by the Average column, the results are wrong again but there is also a hint in row 6 that something is wrong. (This is, providing the error checking feature, available from Excel 2003 onwards, is enabled).
The green triangle indicates that Excel has found that “the formula in this cell differs from others in this area” as hovering over the information icon will show. Clicking on the arrow gives the range of options shown in the screen shot. 
There will be other green triangles further down the column if the formula changes again.
An obvious thing to do at this stage is perhaps to check the formulas in the cells adjacent to H6. Arguably an even more obvious thing to do is to undo the sort, while you still can, and this is what we did! Then we checked the formulas using the Trace Precedents tool (Found on the Auditing Toolbar pre Excel 2007 and on the ribbon under Formulas | Formula Auditing in Excel 2007). 
The traces shown here illustrate the problem. The formula is indeed inconsistent! Somehow it is correct at the start of the column but not later on. It is hard to figure out how formulas end up like this because you would normally enter the formula at the top of the column and copy it down the rest of the column and it would be consistent the whole way down. Having seen it several times, in quite different spreadsheets, we realise that humans do sometimes make mistakes that are hard to explain! Once the problem is identified it is easy to fix it by copying the correct formula down the column.
Don’t worry if your spreadsheet ends up with the rows in a different order to what is shown here. This is likely to be a consequence of its buggy, inconsistent (!!) formulas. You can still use the auditing tools – you will just get a different display.
There is another technique that may actually be faster than tracing the precedents, of individual cells, for solving this problem. Excel has a Go To Special feature available from the Edit menu in the pre 2007 versions. (Where has the Edit, Go to Special feature got to in Excel 2007? It is a bit hard to find but look under Home | Editing | Find & Select and you will see it.) An alternative, quick way to access it is via the Go To shortcut [F5].
This feature has been part of Excel for quite sometime but it is not used nearly as much as it should be. It enables you to identify the selected cells that match some special criteria.
If you suspect that some cells are not referenced by formulas when they should be, then selecting the entire formula column and then using Go to Special, Precedents option will highlight the cells that are actually referenced. This is illustrated using this problem here. Column H cells were selected first and then the Go To Special, Precedents option (see dialog on previous page) was applied. The picture below shows the result. It has the cursor in the first cell of the resulting selection.
The scores of row 9 are not selected, clearly suggesting that the formula in H9 is incorrect. This method doesn’t automatically tell you which cells have incorrect formulas but it should help in debugging the spreadsheet. You would certainly realise that there was an inconsistent formula in column G!