Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

When you add more raw data to your worksheet, balancing, organizing, and searching for specific data becomes more difficult. To get rid of this situation, Excel has an amazing feature called Sorting. Using this feature, you can sort your data by values, dates, or alphabetically. It will help you to quickly find, visualize, and understand your data and will help you take action faster. In this article, we will discuss 7 easy and quick methods to sort your column by value in Excel.


Download Practice Workbook

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


7 Methods to Sort Column by Value in Excel

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.

excel sort column by value

Now, we’ll use this dataset to sort column 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 column 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.

Applying the Sort A to Z Button

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.

Sort Warning Dialog Box

And our columns are sorted from smallest to largest.

Sort Column from smallest to largest in Excel


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.

Applying the Sort Z to A Button

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

And the final result is before our eyes.

Applying the Sort Z to A Button to sort column by value in Excel

Read More: How to Sort Alphabetically and Keep Rows Together in Excel


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.
  • After that jump to the Home tab.
  • Then, click on the Sort & Filter drop-down on the Editing group.
  • From the drop-down list, select the Filter option.

Utilizing Filter Option

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

Filter Button on Each Column Header

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

Utilizing Filter Option to sort column by value in Excel

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

Read More: How to Sort and Filter Data in Excel (A Complete Guideline)


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.

Employing Custom Sort Tool

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.

Working on Sort Dialog box

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.

Employing Custom Sort Tool to sort column by value in Excel

Read More: How to Create Custom Sort List in Excel


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.

Engaging Multi-level Sorting by Value

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.

Adding New Level in Sort dialog box

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

Engaging Multi-level Sorting by Value in Excel

Read More: How to Sort Data by Value in Excel (5 Easy Methods)


Similar Readings


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.

Inserting SORT Function

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

Inserting SORT Function to sort column by value in Excel

The dataset is sorted by value from largest to smallest.

Read More: How to Do Advanced Sorting in Excel (9 Suitable Examples)


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.

Implementing SORTBY Function

Read More: How to Sort Data in Excel Using Formula (2 Easy Methods)


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.

Applying VBA Code

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

Code Module for Sheet9

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

VBA Code to sort column by value in Excel

  • Thenceforth, Run the code.

Run the Code

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

Applying VBA Code to sort column by value in Excel

Read More: How to Sort ListBox with VBA in Excel (A Complete Guide)


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.

Problems You Might Face While Sorting Column by Value

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.

Problem to sort while having blank row

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.

Using Sort and Filter Option

Now, Excel is showing the correct result.

Read More: How to Sort Data by Two Columns in Excel (5 Easy Ways)


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.

Practice Section


Conclusion

This article explains how to convert latitude and longitude to address in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.


Related Articles

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo