Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Recognizing a Header Row when Sorting

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. 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. 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 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.

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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2586) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Recognizing a Header Row when Sorting.

Related Tips:

A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best. Check out Excel Graphics and Charts today!

 

Comments for this tip:

Bruce L    22 Aug 2013, 05:07
@Suzie: "Go to sort/filtering; custom sort; expand the selection - sort; check the box "my data has headers"; sort by; ad run sort. Keep this box checked and when you go back into your worksheet and sort A-Z and it will not sort your header row."

That's exactly what I meant by "tiresome and time consuming". It's fair enough if you're working on a big-ish piece of work which needs to be properly formatted for distribution anyway, but if you're only doing some quick wrangling/reformatting of string data on the side of another project, having to go through all those clicks just to specify a header row adds a significant time penalty.
p    21 Aug 2013, 23:25
Personally, I feel that trusting the sort button is going to eventually lead to tears. I do the selection myself (usually everything), and use the sort dialog.

If you use the button then one day you'll end up not including a column in the sort, and you might not realise till it's too late to fix it.
Bruce L    11 Apr 2013, 09:11
Sorting text data is particularly problematic. Excel 2007/10 won't auto-detect a header row no matter what you do - bold, underline, italic, fill colour, font colour etc. - it doesn't matter what you do, it sorts your header with your data unless you go the long way round and actually use the sort dialog. This gets very tiresome and time consuming when you need to sort text often and quickly.

In fact the phrase "tiresome and time consuming" covers rather a lot of latter-day Excel's foibles IMHO. If anyone has a solution to the text quick-sort problem I'd love to hear it.
Suzie    27 Dec 2012, 18:57
Go to sort/filtering; custom sort; expand the selection - sort; check the box "my data has headers"; sort by; ad run sort. Keep this box checked and when you go back into your worksheet and sort A-Z and it will not sort your header row.
Sibin GS    14 May 2012, 21:37
The better way to sort and make excel recognise your header is by selecting the entire range of cells you want to sort including the headers, then in the Sort and filter option choose "Custom Sort", now at the very top right of the next window place a check mark next to "My data has headers", then add atleast one level by choosing which column you want to sort and click OK. This helps in sorting data and making the excel recognize that we have a header in the sort data.
MattF    19 Jan 2012, 08:48
It seems to help if you add a blank row (which can be hidden if you like) above the last of your header rows.

This doesn't work if you have merged cells spanning from the last header row to the ones above.
awyatt    17 Dec 2011, 10:56
Excel has a hard time recognizing multi-row headers. 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.
brockway    13 Dec 2011, 13:10
You say in the last paragraph:
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.

How do you make changes in the header row to make sure it is recognized by Excel as a header row? To make the spreadsheet look the way I want, I have five header rows above the data. A couple are thin blank rows to improve the overall appearance. How can I make sure Excel recognize all of these as header rows. In Numbers in iWork that is super-easy to do, but I don't know how to do it in Excel.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.