Why does header row sometimes get sorted in Excel?

Question

In my experience, if one has a table, one can't sort the rows in the table by clicking on one of the cells in the top row.  The top row is treated as a header and doesn't get included in the sort.  But that's not always true.  Sometimes it does get included in the sort.  What controls that?

Answer 1

We appreciate your response. In addition to what James Cone mentioned, the header row is automatically enabled if you created a table by using the Insert Table option in Excel. Thus, making the first row of your table not included in the sorting and filtering.

If you are referring to just placing cell borders when you mentioned “grid of rows and columns”, the header row will not be automatically enabled and Excel will treat your first row as part of the data. Thus, your first row will be included in the sorting and filtering.

Check the difference on how the tables look like on the images below:

A. Table created by using the Insert Table option in Excel. Header Row is enabled.

B. Table created by placing cell borders in Excel. Header Row is disabled.

Let us know if you need additional assistance.

Answer 2

Hi Laurel,

When a user inserted a table in Excel, the first row automatically becomes the Header Row. Since it is the header row, the first row shouldn't be included on the sorting. To isolate your concern, we need you to answer the following:

• Which Windows build and version are you using?
• Can you provide us the detailed description of how you are creating a table in Excel?
• How long have you been experiencing this issue?
• Did you make any changes before this issue happened?
• What troubleshooting steps have you done so far?

We will be waiting for your response.

Answer 3

Don't know how to find out build, but it's on automatic update.

It doesn't seem to matter how I create the table.  When I say "table," I'm just talking about a grid of rows and columns.

Several years.

Too long to know whether a change had been made.

I've experimented with filling in outside rows and columns.  I thought that empty spaces might have had an effect.  But I made a little grid that was completely filled in and it had the problem.  Not all grids have the problem.  It shows up in different sheets.

I gather from your questions that this is unusual behavior, correct?

Answer 4

Since I'm getting some confirmation that this isn't just a "feature" that I don't understand, I'll go look more carefully at grids that do and don't have the problem.

Answer 5

LaurelS,

re:  header row when sorting

Excel looks for differences between the first row and the following rows in deciding whether to sort or not sort the first row.  Also, there is an user option to include/not include the first row in the sort.

That option, however does not stay "stuck" on subsequent sorts.

Excel recognizes...

  text vs. numbers

  bold vs. not bold

  cell alignment differences

  font size differences

In use, just make sure the header row is different.

Also, be aware that Excel continues to be "improved" for better or for worse.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)