What Is Column Sort In Excel?
Column sort in Excel helps rearrange columns in the specified logical order. The feature allows one to display the data in a particular order, quickly assess it, and find the required values in a worksheet.
Users can sort columns in Excel in ascending or descending order by conditions such as alphabetical order, date, color, font, and conditional formatting.
For example, the below table contains the top scores in the listed subjects in 2019-2022.
However, the years are in descending order. And the requirement is to show the data in ascending order of years, from 2019-2022.
So, we can perform a column sort using the Sort feature from the Data tab to achieve the below result.
In the above example, the Sort option rearranges the columns with the 2019 data in front, and the remaining years’ data is displayed in the increasing order of years. However, the scores’ order in each column remains unchanged.
Table of contents
Key Takeaways
- The column sort in Excel organizes the given columns in the specified logical order. The sorting can be by value, color, font, or conditional formatting.
- Users can use the feature to rearrange columns from left to the right, analyze the given data, or find a specific value in the worksheet more straightforwardly.
- Also, we can access the column sort feature by navigating the path Home → Sort & Filter → Custom Sort or Data → Sort.
- We can customize the Order using the Custom List option in the Order drop-down list.
Column Sort() In Excel Shortcut
We can select the columns we require to sort and then apply the following shortcuts to perform column sort in Excel:
- Alt + H and then S and U, one after the other, to access the Custom Sort function under the Sort & Filter feature in the Home tab.
- Alt + A and then S and S, one after the other, to access the Sort function in the Data tab.
The above shortcuts will open the Sort window, where we can apply the necessary settings to perform column sort.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use Column Sort In Excel?
We can use column sort in Excel in 2 ways, namely:
- Access from the Home tab in the Excel ribbon.
- Access from the Data tab in the Excel ribbon.
Method #1 – Access From The Home Tab In The Excel Ribbon
Select the columns to sort and click the Home tab → click the Sort & Filter option drop-down → click the Custom Sort option as shown below.
Alternatively, applying the keyboard excel shortcut, press Alt + H + S + U will also help us access the Sort window.
The Sort window opens, where we must click Options.
The Sort Options window opens up. Here, we must select the second option as the Orientation to ensure column sorting happens from left to right, and then, click OK.
The field changes from Column to Row, implying that we can perform column sort.
Next, set the Sort By, Sort On, and Order fields in the Sort window.
Finally, click OK to view the selected columns sorted in the required order.
Method #2 – Access From The Data Tab In The Excel Ribbon
Select the columns to sort and click the Data tab → click the Sort option shown below.
Alternatively, applying the keyboard shortcut Alt + A and then S and S will also help us access the Sort window.
The Sort window opens. And the remaining steps to conduct column sort in Excel are the same as explained in the Method #1 section.
The following example shows how to sort column in Excel by value.
The below table contains a list of box types and the total count of each box type in each category of the specified side length in inches.
Suppose the requirement is to sort by column to get the specified side categories in ascending order (10, 20, 30, 40, 50, and 60 inches).
We can perform column sorting in excel using the following steps –
- First, select the cell range B2:G7 and click Data → Sort to open the Sort window.
- Next, in the Sort window, click Options to set the Orientation to do column sort from left to right, and click OK.
- As we need to do column sort based on the side category, select Sort by as Row 2 using the drop-down button.
- Then, set Sort On as Cell Values as we require to sort column in Excel by value.
- As we require to sort the columns in ascending order, set the Order as Smallest to Largest.
- Finally, click OK to view the sorted columns.
Thus, we can see the categories of the specified sides sorted in ascending order, with the columns B to G order rearranged accordingly.
Alternatively, we can select the cell range B2:G7 and go to Home → Sort & Filter → Custom Sort.
The Sort window will open, and the rest of the procedure to perform the required column sort is the same as steps 2 to 6 explained above.
Examples
Check out the following column sort in Excel examples to use it effectively.
Example #1
The below illustration explains how to sort column in Excel alphabetically.
The following table contains the prices of items across different stores.
Suppose the requirement is to sort the items in alphabetical order.
Then, here is how we can use the feature, sort column in Excel alphabetically and arrange the columns so that the items appear in ascending order alphabetically.
- Step 1: To begin with, select the cell range B1:E6 and then, click Data → Sort to access the Sort window.
- Step 2: In the Sort window, click Options to open the Sort Options window.
- Step 3: In the Sort Options window, select the second option under Orientation to perform column sort in Excel, and click OK.
- Step 4: Set the Sort by option as Row 1 using the drop-down button, as we require to arrange the columns based on column headings alphabetically.
Next, set Sort On as Cell Values using the drop-down button.
And pick the Order as A to Z using the drop-down button.
- Step 5: Click OK to close the Sort window and view the sorted columns.
The item names get sorted alphabetically, and the corresponding columns get rearranged. But the columns contain the data in the same order.
Example #2
We shall see how to sort column in excel by date.
The following table shows the per day total site visitors data for four sites.
But the data is in descending order, with the date having the maximum site visitors shown in the front.
And the requirement is to display the data from the oldest to the newest date. Thus, we need to sort column in excel by date.
- Step 1: Select the cell range B2:F6 and click Home → Sort & Filter → Custom Sort to open the Sort window.
- Step 2: In the Sort window, click Options to open the Sort Options window.
- Step 3: In the Sort Options window, pick the second option as Orientation to execute column sort in Excel from left to right.
- Step 4: We require to conduct the column sort by date. So, set the Sort by field as Row 2.
Next, pick the Sort On as Cell Values.
And set the Order field as Oldest to Newest.
- Step 5: Click OK to close the Sort window and view the rearranged columns.
Example #3
Let us see how to perform multiple column sort in Excel.
The below table shows the price and order details of six laptops.
Suppose the requirement is to arrange the columns by price in ascending order and then by the order dates, ranging from the oldest to the newest.
Then, we can conduct multiple column sort in Excel and achieve the required outcome.
- Step 1: Select the cell range B1:G4 and click Data → Sort to access the Sort window.
- Step 2: In the Sort window, click the Options button to access the Sort Options window.
- Step 3: In the Sort Options window, pick the second option as the Orientation to apply column sort in Excel, and click OK.
- Step 4: As we need to rearrange the columns by price in ascending order, set the Sort by field as Row 2. And update the second and third fields as Cell Values and Smallest to Largest using the respective drop-down lists.
- Step 5: Click the Add Level option to add the next sorting level.
We will see another sorting level below the first one. Fill in the fields as depicted below. The reason to choose the Then by field as Row 4 is that we need to rearrange the columns by the order dates, from the oldest to newest.
- Step 6: Click OK to close the Sort window and view the rearranged columns.
The multiple-column sorting is visible in columns C and D. As both laptops have the same price, the one with the older order date comes first, followed by the column data with a newer order date.
Important Things To Note
- Use the shortcut keys Alt + H and then S and U to open the Sort window from the Home tab.
- Similarly, use the keyboard Alt + A and then S and S to open the Sort window from the Data tab.
- Ensure to choose the second option as the Orientation in the Sort Options window to perform column sort in Excel.
Frequently Asked Questions (FAQs)
We can rearrange more than one column in Excel by following the below steps:
1) First, ensure our source data is accurate.
2) Select the cell range that covers the columns to sort and click Data à Sort to open the Sort window.
3) In the Sort window, click Options to open the Sort Options dialog box.
4) In the Sort Options window, pick the second option as the Orientation to perform column sort, and click OK.
5) Next, update the three fields in the Sort window for the first level of sorting.
6) Next, click Add Level in the Sort window. We will see three new fields. Update them for the second level of sorting.
7) Repeat step 6 if we have more sorting levels to perform.
8) Finally, click OK to view the rearranged columns.
The above steps will help in multiple-column rearranging as we apply the required sorting conditions in the specific rows to sort columns from left to right.
Customized column sorting is possible in Excel. Let us see the steps with an example.
The following table contains the results of six tests.
Each column header shows the test names and the respective result ranges, based on which the category gets decided.
Suppose the requirement is to arrange the columns, based on the Result (in mg) in descending order and category from Good, Average, to Poor.
Then, the steps are as follows:
• Step 1: Select the cell range B1:G3 and click Data → Sort to access the Sort window.
• Step 2: Click Options in the Sort window to open the Sort Options window.
• Step 3: In the Sort Options window, choose the second option as the Orientation to ensure we can sort by column, and click OK.
• Step 4: Update the sorting fields using the respective drop-down lists, as depicted in the below image.
As the first requirement is to sort the columns based on the result values, we set the Sort by field as Row 2.
And as the column sort is by value, the Sort On setting will be Cell Values. However, the result values must be in descending order. So, set the Order as Largest to Smallest.
• Step 5: Click the Add Level option to add a second sorting level.
• Step 6: In the second sorting level, set the Then by field as Row 3, as we must rearrange the columns based on the categories.
• Step 7: The second field remains unchanged, as explained in step 4. However, as the default Order settings do not meet our requirements, select the Custom List option from the Order drop-down list to open the Custom Lists window.
• Step 8: Click on NEW LIST under Custom lists in the Custom Lists window.
We will see a cursor in the List entries box, where we must enter the required customized order and click Add.
On clicking Add, the custom list gets added to the existing list. And click OK to close the Custom Lists window.
• Step 9: Click OK to close the Sort window and view the rearranged columns in the required order.
In the resulting table, the columns get sorted with the maximum result value in front and the remaining values in descending order. On the other hand, the categories appear in the specified custom list order.
However, in row 3, the Poor category comes before the Good category. The reason is that the Test_2 and Test_4 results are the same, 9 mg. And the column sort feature maintains the specified category order for the two tests, with the test in the Good category appearing before the one in the Poor category.
The columns do not sort in Excel, perhaps because of choosing the incorrect columns and rows. Otherwise, the selected cell range does not cover the information we aim to sort.
Download Template
This article must be helpful to understand the Column Sort In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Column Sort In Excel. Here, we learn how to sort columns along with examples and downloadable excel template. You can learn more from the following articles –
Leave a Reply