How to Sort Column by Value in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

How to Sort Column by Value in Excel (7 Easy Methods)

For ease of understanding, we are going to use a List of Employees. This dataset includes the ID, Name, and their corresponding Income in columns B, C, and D respectively.

Now, weâ€™ll use this dataset to sort columns by value using numerous approaches in Excel. Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

1. Using Quick Sort Commands

Most of the time, we need to sort our data in ascending or descending order. This can be said from Smallest to Largest (A to Z) or from Largest to Smallest (Z to A) in the case of numbers and text respectively. Similarly, it will be oldest to newest and newest to oldest in the case of dates. Here, weâ€™ll discuss this in two different sections. So, letâ€™s see below.

1.1 Applying the Sort A to Z Button

At the very beginning, we want to sort columns by value from smallest to largest in Excel. So, follow the simple steps below.

ðŸ“Œ Steps:

• First of all, select cells in the D5:D14 range.
• Then, go to the Data tab.
• Next, click on the Sort A to Z icon on the Sort & Filter group of commands.

Immediately, the Sort Warning dialog box appears before us.

• In the box, click on Expand the selection under the What do you want to do? section.
• After that, click on Sort.

Our columns are sorted from smallest to largest.

1.2 Applying the Sort Z to A Button

Now we will sort the columns from largest to smallest (Z to A). Itâ€™s simple and similar to the previous one. So, letâ€™s follow us.

ðŸ“Œ Steps:

• Again, select the whole data of the Income column.
• Following this, move to the Data tab.
• Then, click on the Sort Z to A icon.

• Later, do the same on the Sort Warning dialog box.

And the final result is before our eyes.

2. Utilizing Filter Option

Sorting columns by value is easy and time-consuming if you use the Filter option. So, without further delay, letâ€™s dive in.

ðŸ“Œ Steps:

• At first, select all the column headings in the B4:D4 range.
• Then, click on the Sort & Filter drop-down on the Editing group.
• From the drop-down list, select the Filter option.

Now, the filter button is available beside each column heading.

• At this time, click on the filter button of the Income column.
• Then, click on Sort Smallest to Largest from the options.

Instantly, we can see the columns get sorted from smallest to largest value.

Similarly, you can sort them from largest to smallest using the same filter button.

3. Employing Custom Sort Tool

You can use the Custom Sort command to sort columns by their values in an ascending or descending manner. Letâ€™s see it in action.

ðŸ“Œ Steps:

• Firstly, select cells in the B4:D14 range.
• Then, proceed to the Data tab.
• Now, click on the Sort icon on the Sort & Filter group.

Suddenly, the Sort wizard pops up. Here you can customize your sorting styles.

We will Sort by the Income column, Sort On the Cell Values, and the Order is Smallest to Largest.

• As usual, click on OK.

Hereafter, we can see the column sorted from smallest to largest in our worksheet.

Also, you can sort them from largest to smallest in a similar way.

4. Engaging Multi-level Sorting by Value

To understand this method clearly, we added a new column titled Expenditure and we have changed the income of Mike, Adam, and Hayden to \$95,000.

Letâ€™s explore the method step by step.

ðŸ“Œ Steps:

• Now, we will customize this level by the Expenditure column, sort on Cell Values and the Order is Largest to Smallest.
• Lastly, click OK.

Here, we can see that 4 persons have the same Income amount and their Expenditures are also sorted from largest to smallest order.

5. Inserting SORT Function

The SORT function is an amazing function to sort a wide range of data. So, without further delay, letâ€™s see how we can use it in this method.

ðŸ“Œ Steps:

• Initially, copy all the column headers and paste them in the F5:H5 range.

• Then, select cell F6 and enter the following formula into the Formula Bar.
`=SORT(B6:D15,3,-1)`
• Later, press ENTER.

The dataset is sorted by value from largest to smallest.

6. Implementing SORTBY Function

The SORTBY function is kind of similar to the previous function. Letâ€™s follow the steps below.

ðŸ“Œ Steps:

• Primarily, select cell F6 and insert the following formula.
`=SORTBY(B6:D15,D6:D15,-1)`
• Secondarily, press ENTER and see the result below.

7. Applying VBA Code

Have you ever thought of automating the same boring and repetitive steps in Excel? Think no more, because VBA has you covered. In fact, you can automate the prior method entirely with the help of VBA. So, letâ€™s begin.

ðŸ“Œ Steps:

• At first, right-click on the worksheet name VBA.
• From the context menu, select the View Code option.

Immediately, the Microsoft Visual Basic for Applications window appears. Also, we can see a code module is inserted for Sheet9 (VBA).

• Afterward, paste the following code into the module.
``````Sub Sort_Column_by_Value()
Range("B4:D15").Sort Key1:=Range("D5"), Order1:=xlDescending
End Sub``````

• Thenceforth, Run the code.

As a result, we can see the column sorted from largest to smallest by value.

Problems You Might Face While Sorting Column by Value

Though sorting columns by value in Excel is quite easy, you might face some problems if you have Blank or Hidden cells or rows in your data range. See the screenshot below to understand it clearly.

In this case, letâ€™s see what happens when we sort the columns by values.

As a result, we can see that only a portion of our data until the first blank cell is sorted, while the remainder remains unsorted.

Solution:

We can solve this blank or hidden cell problem easily. If the dataset contains such cells. We have to select the whole dataset and then sort it by values.

ðŸ“Œ Steps:

• Firstly, select cells in the D4:D14 range instead of just selecting cell D4.
• Secondly, advance to the Data tab.
• Thirdly, click on the Sort A to Z icon.

Now, Excel is showing the correct result.

Things to Remember

âž¤ The SORT and SORTBY functions are only available for Excel 365. You wonâ€™t be able to use this function unless you have this version of Excel.

âž¤ If you have a blank cell in your data table, select the whole data table to sort by value.

âž¤ You can auto-sort columns by their value when you use the SORT function.

âž¤ Always select the column header cell when you sort your data.

Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it by yourself.

You may download the following Excel workbook for better understanding and practice yourself.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF