Md. Shamim Reza
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.
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.
- 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.
- 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 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.
- 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
- Advantages of Sorting Data in Excel (All Features Included)
- How to Sort Alphanumeric Data in Excel (With Easy Steps)
- How to Remove Sort by Color in Excel (With Easy Steps)
- Difference Between Sort and Filter in Excel
- How to Auto Sort in Excel without Macros (3 Methods)
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 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.
- 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.
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.
Thanks for the information – exactly what I was looking for!
Hi, Irene!
Thanks for your appreciation. To get more helpful information stay in touch with ExcelDemy.
Regards
ExcelDemy