Excel Sort by Column Without Header (5 Methods)

This article illustrates how to sort by column without a header in excel. Usually, excel tries to identify if there are headers in the dataset before sorting. If excel fails to do so, it sorts the entire dataset including the headers. This creates a problem by changing the actual dataset. Fortunately, there are some ways to avoid this issue. We will explain those ways here. The following picture highlights the purpose of this article. Have a quick look through the article to see how to do that.

How to Sort by Column without Header in Excel (5 Easy Methods)

Imagine you have the following dataset containing employee names and sales made by them. You can sort the dataset by the names or sales.

If Excel can identify that there are headers in this dataset then it will sort the dataset as follows when you sort it by names.

If it fails to do so, then you will get the following result instead which is not desirable at all.

Follow the methods below to avoid this problem.

1. Sort by Column without Selecting the Header

You can select the dataset without the headers and then sort it to avoid the problem.

📌 Steps

• First, select the entire dataset excluding the headers as shown in the following picture.

• Then, select Sort & Filter >> Sort A to Z or Sort Z to A from the Home tab.

• Do not select the dataset partially as shown below.

• Otherwise, you will see the following warning. If you select Expand the Selection then the headers will be sorted too. If you select Continue with the current selection then it will sort the names only altering the respective sales values for each employee. But you can do this if you have data in a single column only.

• Finally, you will see the dataset sorted as follows.

Read More: Sort Multiple Columns in Excel Independently of Each Other

2. Use Custom Sort Option

You can also get the same result using custom sort in Excel.

📌 Steps

• First, select anywhere in the dataset as shown below.

• Then, select Sort & Filter >> Custom Sort as follows.

• After that, the following dialog box will pop up. Select Options in the Sort dialog box.

• Now the Sort Options dialog box will pop up. Mark Sort top to bottom to sort by column. It is the default setting. Then, select the OK button.

• Now check the My data has headers checkbox.
• Then choose the Names column to Sort by and A to Z for Order using the dropdown arrows.
• Next, select OK. After that, you will get the same result as earlier.

Read More: How to Sort Multiple Columns in Excel

3. Hide the Header Row and Sort by Column

You can also hide the header row while sorting to exclude it from the sort operation.

📌 Steps

• First, select the row number of the header at the left of the row.
• Then right-click on it and select Hide.

• After that, the header row will be hidden. Now you can sort the data and then unhide the header row.

Read More: Sorting Columns in Excel While Keeping Rows Together

4. Unmark the Header and Sort Data Using Excel Filter Button

You can add filter buttons to the header cells which will exclude them from being sorted.

📌 Steps

• First, select the entire dataset as shown below.

• Then, press CTRL+SHIFT+L to add the filter buttons.
• After that, you will see the filter buttons at the bottom-right corners of the header cells.

• Now, select the filter button for Names. Then select Sort A to Z or Sort Z to A to sort the dataset by names. If you want to sort the dataset by sales then you can do that too.

Read More: How to Sort Column by Value in Excel

5. Use Excel Table and Sort by Column

Creating an Excel table with your dataset can be another alternative method to sort by column without headers.

📌 Steps

• First, select the entire dataset including headers. Then press CTRL+T to create the table.
• Next, check the My table has headers checkbox in the popped-up dialog box and click OK.

• After that, your dataset will be converted to an Excel table. The dropdown arrows in each header cell of the table will allow you to sort the respective columns.

Things to Remember

• Whenever you try to sort any data, excel will sort it by column if you do not instruct it to do otherwise using the custom sort options. So you need to change the sort option if you want to sort your data by rows.
• Excel identifies the headers in your dataset based on some guidelines. It doesn’t consider blank cells as headers. Try to format the header cells differently than the actual data. It will help Excel easily recognize the header cells and exclude them while sorting.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to sort by column without a header in Excel. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.

Related Articles

• How to Sort Alphabetically in Excel with Multiple Columns
• How to Sort Alphabetically in Excel and Keep Rows Together
• How to Sort Two Columns in Excel to Match
• How to Sort Columns in Excel Without Mixing Data
• How to Sort Rows by Column in Excel
• How to Sort Data by Row not Column in Excel

<< Go Back to Sort in Excel | Learn Excel