Sort Column by Value in Excel (5 Methods)

When you add more raw data to your worksheet, balancing, organizing, and searching the 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 four methods to sort your data by values in excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article

Five Ways to Sort Column by Value in Excel

1. Using The A-Z (Smallest to Largest) or Z-A (Largest to Smallest) Method

Most of the time we need to sort our data from smallest to largest (A-Z) or from largest to smallest (Z-A). We can do it by following these easy steps.

Read more: How to Sort Columns in Excel without Mixing Data

i. A-Z (Smallest to Largest)

Step-1:

First, we want to sort columns by value from smallest to largest. We need a data set for that. We can take this picture as an example.

Step-2:

Select the “Income” column, Open your Data tab, go to the “Sort & Filter” ribbon and click this icon

Data→Sort & Filter→A-Z

Step-3:

A new window popped out. Now click on “Expand the Selection” as we want the rest of the columns to be sorted according to our values.

And our columns are sorted from Smallest to largest.

ii. Z-A (Largest to Smallest)

Step-1:

Now we will sort columns from largest to smallest (Z-A). Open your Data tab, go to the “Sort & Filter” ribbon and click this icon

Data→Sort & Filter→Z-A

Step-2:

Click on “Expand the Selection”.

Click on “Sort”.Out data is sorted from largest to smallest.

2. Using The “Filter” Option to Sort Column by Value

Sorting columns by value is easy and time-consuming if you use the “Filter” option. Let’s see, how.

Step-1:

To sort column by value, select your column head and go to the “Editing” ribbon and click on this icon

Editing→Sort & Filter→Filter

Step-2:

The filter option is now in each column. Click on this Filter icon and select “Sort A to Z”.

Click “OK”. The columns are sorted from smallest to largest values.

Step-3:

Sorting from largest to smallest is the same as the previous step. Click on your column Filter icon and select “Sort Largest to smallest”.

Click “OK”. Our values are sorted.

3. Custom Sorting Method to Sort Column by Value

You can use the custom sorting command to sort columns by their values in an ascending or descending manner.

Follow these steps.

Step-1:

Select your data-set and go to “Data” and click this icon  

Data→Sort & Filter→Sort

Step:2

A new window appears. Here you can customize your sorting styles. We will sort by “Income”, sort on “Cell Values” and order is “Smallest to Largest”.

Step-3:

Click “Ok” and the columns are sorted according to our customized style.

You can also set the order as “Largest to Smallest” to sort the values.

And the sorting is done.

4. Multi-level Sorting by Value

Using this method, we can sort multiple columns by their values. Follow these steps.

Step-1:

To understand this method clearly, we added a new column “expenditure” and we have changed the income of “Mike” “Adam” “Hayden” to “95,000”.Now, select your data-set and go to “Data” and click this icon

Data→Sort & Filter→Sort

Step-2:

The soring window appears. Now in this case we will add another level. Click on “Add level” and we will customize this level by “Expenditure”, sort on “Cell Values” and order is “Largest to Smallest”.

Click “OK”. Now we have our multi-level sorting column.

5. Using The “SORT” Function to Sort Column by Value

The “SORT” function is an amazing function to sort a wide range of data.

Read more: How to Sort Data in Excel Using Formula

In this method, we will use this function to sort the columns by value.

Step-1:

Copy the column header and paste it to the cell where you want to get your sorted data.

Step-2:

Apply the “SORT” function.

Here,

• “Array” is your data range (B6:D16)
• [sort_index] is the column you want to sort on (3)
• [sort_order] is where you can specify the order (1)

Press “Enter”.

Press “Enter”. The dataset is sorted by value from smallest to largest.

Step-3:

You can sort your value from largest to smallest using the same function. You just have to change the [sort_order] from 1 (ascending) to -1 (descending)

Press “Enter”. Our sorting is done.

Problems You Might Face

Though sorting columns by value in excel is quite easy, you might face some problems if you have a “Blank” or “Hidden” cell in your data range.

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

Click “OK”.

So we can see that only the portion of our data until the first blank cell is sorted and the rest of the part 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.

Click “OK” now the columns are sorted by their values and the blank cell is at the end of the dataset. That’s how you can solve this problem.

Quick Notes

➤ The “SORT” function is 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 header column cell when you sort your data.

Conclusion

Sorting columns by value in excel is discussed in this article. Hope this article is useful to you and helps you when you are facing problems. If you have any confusion or suggestions, you are welcome to comment.

Further Readings

• How to Auto Sort Multiple Columns in Excel (3 Ways)
• Sort Multiple Columns in Excel Independently of Each Other
• Sorting Columns in Excel While Keeping Rows Together
• How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)
• Sort Rows by Column in Excel (4 Methods)
• How to Sort Rows in Excel (2 Simple Methods)