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.
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.
Read More: How to Sort Two Columns in Excel to Match
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.
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.
- 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.
Read More: Excel Sort by Column Without Header
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:
- Equivalently, open the Sort dialog box.
- Then, select the sorting style like the previous method.
- After that, click on Add Level.
- 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.
Read More: Sorting Columns in Excel While Keeping Rows Together
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.
Read More: How to Sort Columns in Excel Without Mixing Data
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.
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.
