There are two ways you can sort information in Excel: using the Sort Ascending and Sort Descending tools on the Data tab of the ribbon, or by using the Sort dialog box. Using the ribbon tools allows you to do the sort more quickly, but Excel makes a few assumptions in the process.
First, Excel assumes that you want to sort only by the column of whatever cell you have selected. If you want to perform secondary and tertiary sorts on more than one column (or row), you need to use the Sort dialog box.
The second assumption affects exactly what Excel sorts. If you have a single cell selected, Excel extends the selection to select a range (much like pressing Ctrl+Shift+8) bounded by one or more blank columns and rows. It then examines the first row in the selected range to determine if it contains header information or not.
This is where sorting with the ribbon tools can become tricky—your header (assuming you have one) must meet some rather strict guidelines in order for Excel to recognize it as a header. For instance, if there are any blank cells in the header row, Excel may think it isn't a header. Likewise, if the header row is formatted the same as the other rows in the data range, then it may not recognize it.
Only after selecting the range and determining if there is a header row will Excel do the actual sorting. How pleased you are with the results depends on whether Excel got both the range selection and the header row determination right. For instance, if Excel doesn't think you have a header row, and you do, then your header is sorted into the body of the data; this is generally a bad thing.
To make sure that your data range is recognized correctly, use the Ctrl+Shift+8 shortcut to see what Excel selects; this is what will be sorted. If it doesn't match your expectations, then you need to either modify the character of the data in your table, or you need to sort by selecting the data range before using the Sort dialog box.
To make sure that your heading is recognized correctly, use the Ctrl+Shift+8 shortcut to select the data range, then look at the first row. If your header has blank cells among those selected in the first row, or the first row is formatted just like the second row, or you have more than one header row selected, then Excel assumes you have no header row at all. To correct this, make changes in your header row to make sure it is recognized properly by Excel.
Comments for this tip:
Connie 17 Jan 2014, 11:54
I want to let you know this article helped me. I froze the first row but it would still sort. I do agree with "Silly" that if you freeze rows one would think the row would not sort. Anyway - I then checked "My Data Has Headers" in custom sort. But if I closed the program it defaulted back to being unchecked. I wanted to find a way to keep it check even after closing. When reading this article it said to make the header appear different than the data. So I centered the words in the header row columns, and the program now recognizes it as a header and the "my data has headers" now is checked even after closing the program and bringing it back up. Thanks for the tip.
CT 09 Jan 2014, 18:14
Just merge the column A cells for the rows that are header rows (insert a blank Column A if you want).
Excel recognizes all the Column A merged cells as the header.
Willy Vanhaelen 08 Nov 2013, 09:27
Silly, if Excel's guess isn't to your liking you can always instruct it how you want the job done:
- Select your data without your 3 rows header.
- Select 'Sort & Filter' and 'Custum Sort...'.
- Make sure 'My data has headers' is unchecked.
- Select your sort options and click OK.
Silly 07 Nov 2013, 10:24
Completely useless article, because it fails to specify WHAT changes need to be made in my 3-row header to get Excel to treat rows 4 and onward as the data rows to sort. You'd think Excel would understand that if you've frozen rows 1 through 3, then those are the headers. But NOOOOOO. Microsoft hires programmers from the loony bin. Come on. It's 2013, not 1993!
Luke Allen 31 Jul 2013, 15:08
One "fun" quirk is that when I'm trying to sort exactly two cells in the same vertical column, sometimes Excel assumes I have a header row... on the one cell I'm sorting.
James Cameron 23 Jul 2013, 23:27
Sorry for length of previous post. It did not seem so long when I was typing it.
James Cameron 23 Jul 2013, 23:25
I tested this out on three lists and found that things are slightly more complex.
The first list had text headings and numerical data. The second list had text headings and text data. The third list had text headings and a mixture of text and numerical data.
In all cases the headings were formatted differently to the data, by being in bold text and with a coloured shading. The first header cell of all lists occupied cell A1, and all lists consisted of three columns with nothing to the right or below either list. Initially, all three lists were on different worksheets.
On initial testing, using Ctrl+Shift+8 then using either the Sort Ascending or Sort Descending tools, everything worked as described by Allen.
I then sorted the third list using the Sort dialog box, and leaving the "My data has headers" box selected. Subsequent testing of the first and second lists showed the same results as before.
I then sorted the third list using the Sort dialog box, and delselecting the "My data has headers" box option. Subsequent testing of the first and second lists showed the same results as before. Obviously the third list was messed up as the heading was sorted into the body of the data.
I then restored the third list and copied it into the first and second worksheets, leaving several columns between the first and second lists that were on their respective worksheets. I then deleted the third sheet and the list it contained.
Using the Sort dialog box, but without doing an actual sort, I determined that, for the first and second lists on their respective worksheets, the "My data has headers" box was still selected, but for the third list that had been copied to both worksheets this box was still unselected.
If I now re-ran the tests on the first and second lists, using Ctrl+Shift+8 then using either the Sort Ascending or Sort Descending tools, everything still worked as described by Allen.
I then sorted the third list on one of the remaining two worksheets, using the Sort dialog box and ensuring the "My data has headers" box option was still deselected. This caused the third list to be corrupted in the same manner as before.
I then re-ran the tests on the first and second lists, using Ctrl+Shift+8 then using either the Sort Ascending or Sort Descending tools.
For the worksheet on which I had corrupted the third list, Excel was no longer able to detect the Header row for the test list, and sorted the Header row into the data. Using the Sort dialog box, but without doing an actual sort, showed that the "My data has headers" box for that list was now also deselected.
For the worksheet on which I had not corrupted the third list, Excel was still able to detect the Header row for the test list, and everything worked as described by Allen. Using the Sort dialog box, but without doing an actual sort, showed that the "My data has headers" box for that list was still selected.
From this I deduce that the information about sorting provided by Allen in this article is, as usual, correct. However, readers need to be aware that if they have more than one list in a worksheet, and they change the paramenters in the Sort dialog box for one of those lists, then use of the Sort Ascending or Sort Descending tools may be affected for the remaining lists on the same worksheet.
Dave Ono 23 Jul 2013, 09:07
2 things. First, make sure there is a blank column right of your last field, there is nothing in the row above the SINGLE row of headers, and a blank row after your last data row. Second, make a column (call it sort) and number it sequentially before you sort. This way, you'll always have a way to get the dataset back to it's original order.
John 23 Jul 2013, 08:29
Secondary and tertiary sorts can be accomplished without resorting to the Sort dialog box by performing sequential sorts using the Data tab sort command (or the AutoFilter sort feature).
Sort first by the tertiary column, then by the secondary column and lastly by the primary column.
I find this method to be easier and faster than using the dialog box. It's also useful if you wish to sort by more than three columns.
Bryan 23 Jul 2013, 07:14
I think that data type has something to do with it too. The majority of my work is done with numerical data with text headers, and I don't usually have problems using the sorting buttons. When I have a text header with text data I almost always have problems. (Note, that Excel is smart enough to know that you have numerical data even if the cell formatting is set to "general").
Another good option for sorting is to use the Autofilter.
Leave your own comment: