Excel Sorting Problem?

Question

I'm an Excel expert but I can't figure out what's causing this inconsistent sort result. I have a table with headers at the top of each column. When I select a cell (just one) in one of the columns, and then hit the sort icon that I've placed in the Quick Access Toolbar, I get one of two results:

1. The table sorts like it is supposed to, based on the column containing the selected cell. Excel leave the header line as is, which is the correct result.
or
2. The entire table sorts, including the header as if it were part of the table. In other words, Excel forgets that there is a header, and moves the header into the data.

Usually the header only moves if the table isn't set up properly, such as missing a header, or if there is adjoining data that is contiguous with the table. Neither of those are the case here.

I know how to use the Data ribbon and the Sort dialog, but I'd rather just use the Quick Access Toolbar icon. I sort a lot, and that comes in very handy. If I go through the dialog box, Excel seems to remember that there's a header for a while, but eventually if forgets again.

Is this a bug in Excel, or might there be some subtle issue I'm missing.

I'm using the latest version of Office 365 and it just updated yesterday. The OS is Windows 8-64 bit.

Answer 1

When you use the sort from quick access, you are asking Excel to make some assumptions about your data. When you do this, it happens on occasion that it will make incorrect ones. This is the nature of the beast. You can be explicit and guarantee the results you want, or you can tell Excel to make a guess, and sometimes it guesses wrong.



I think there are things you can to do increase the odds of a correct guess. For example:



- freezing the header row in the view

- making the header row bold, leaving everything else normal

- any datatype mismatches should increase your chances (a header that is text but the values are all numeric implies the first row doesn't belong)



Or, if you really want, make your own icon to replace the default sort and write the VBA to handle it, forcing it to retain the header row. The VBA, of course, would implement Excel's native sort. You would have to be a real bit twitcher to do better than the built in sort.

Answer 2

I dunno about current bugs in office 365, I have office 2010. I use the sort dialog because I almost always have a header and I usually have a particular way I want things sorted.

Answer 3

It's not a bug. it has been like that since excel came out.