For a version of this tip written specifically for later versions of Excel, click here: Recognizing a Header Row when Sorting. There are two ways you can sort information in Excel: using the Sort Ascending and Sort Descending tools on the toolbar or by using the Sort dialog box. Using the toolbar 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. 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 toolbar 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. As well, if your data table consists entirely of text and your header row contains nothing but text, Excel will—virtually all the time—fail to recognize the header row.
The row looks just like another data row to Excel. 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.
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 select the data range before using the Sort dialog box. 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.
Finally, all bets could be off if your data table uses multi-row headers. Excel has a hard time recognizing them.
You compound the problem when you expect it to include blank rows in that header; it just can't do it automatically. You can, however, simply select all the rows you want to sort before doing the sort. In other words, be specific in what you want Excel to sort; don't let Excel make the assumptions for you. ExcelTips is your source for cost-effective Microsoft Excel training. This tip applies to Microsoft Excel 97, , , and You can find a version of this tip for the ribbon interface of Excel Excel and later here: Recognizing a Header Row when Sorting.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author.
He is president of Sharon Parq Associates , a computer and publishing services company. Learn more about Allen Sorting by dates is easy, and you end up with a list that is in chronological order. However, things become a bit more My fat fingers sometimes result in typing letters in the wrong order. Here's a quick tool that allows you to easily When generating an index, Word normally uses a dash to indicate page ranges. You can change the character used for these Program Successfully in Excel!
John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr.
Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Need to do the same sorting operation over and over again? Excel doesn't provide a way to save your sorting criteria, but Sort your data and you may be surprised at what Excel does to your formatting. Some formatting may be moved in the sort Enter your address and click "Subscribe. Your e-mail address is not shared with anyone, ever.
Maximum image size is 6Mpixels. Images larger than px wide or px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.
The header row even when recognized was included in this operation. Yet another Microsoft inept [non]explanation clarified. Your "strict guidelines" saved me. Thanks for what you do. A shame that Microsoft designers are too lame to do it in the first place. I just want to say thank you for helping with how to freeze the header and make it sortable across rows without moving.
I spent an hour trying to figure out what went wrong. I had two columns I had added without titles I tend to get the opposite problem - Excel incorrectly assuming the first row of my selection is a header row and failing to sort it with the rest of the data. What's the fix for this?
Alternative option: use google. Google recognizes a multi-row header and doesn't require you to jump through any hoops. It just works. Thank you Jay! That fixed a very common and frustrating problem for me. Rach: If Jay's solution fixed your problem that's great, but it also means you had a different problem to the one discussed above. Jay had the best answer and was the easiest to follow. Enter some data into column A, scroll to the right, and you'll see the data move with you.
You can also freeze all the rows above the active cell and all the columns to the left of the active cell. These columns and rows remain on the screen at all times, no matter how far you scroll. Select a cell that is below the row that you want frozen and to the right of the column you want frozen. These are the rows and columns that stay visible when you scroll. Two black lines appear on the sheet to show which panes are frozen. The rows above the horizontal line are kept visible while scrolling. The columns to the left of the vertical line are kept visible while scrolling. When you no longer want certain rows and columns to stay in place when you scroll, unfreeze all the panes in Excel.
The data in the frames will remain, but the rows and columns that were frozen will return to their original positions.
Lock specific rows or columns in place by freezing panes, so you can scroll through an Excel spreadsheet and still see the top row or left column. You can freeze. Question: In Microsoft Excel for Mac, I have a spreadsheet with column headings. I need to freeze the first row and also freeze the first column. That way, I.
Share Pin Email. Updated January 23, To freeze the top row of a worksheet:.
If you're using Excel for Mac, skip this step. Select Freeze Top Row. To freeze the first column of a worksheet:.