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.

Excel Sort by Column without Header


Download Practice Workbook

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


5 Methods to Sort by Column without Header in Excel

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.

Sort by Column without Selecting the Header

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

Read More: How to Sort Data by Row Not Column in Excel (2 Easy Methods)


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. Thes select the OK button.

Use Custom Sort Option

  • 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.

Use Custom Sort Option

Read More: How to Create Custom Sort in Excel (Both Creating and Using)


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.

Hide the Header Row and Sort by Column

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

Read More: Sort Column by Value in Excel (5 Methods)


Similar Readings


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.

Unmark the Header and Sort Data Using Excel Filter Button

  • 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 Add Sort Button in Excel (7 Methods)


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.

Use Excel Table and Sort by Column

  • 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.

Use Excel Table and Sort by Column

Read More: VBA to Sort Table in Excel (4 Methods)


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 headers cells differently than the actual data. It will help excel easily recognize the header cells and exclude them while sorting.

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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo