Sort Column by Value in Excel (5 Methods)

Using custom sorting

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.

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.

using the A-Z methods

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

using the A-Z methods

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.using the A-Z methods

And our columns are sorted from Smallest to largest.

using the A-Z methods

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

largest to smallestStep-2:

Click on “Expand the Selection”.

largest to smallest

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

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

using filter option sort by valueStep-2:

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

using filter option sort by value

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

using filter option sort by value

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

using filter option sort by value

Click “OK”. Our values are sorted.

using filter option sort by value

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

Using custom sorting

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

Using custom sorting

Step-3:

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

Using custom sorting

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

Using custom sorting

And the sorting is done.

Using custom sorting

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

multi-level-sorting

 

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

multi-level sorting

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

multi-level sorting

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

The “SORT” function is an amazing function to sort a wide range of data. 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.

using sort function to sort column

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

using sort function to sort column

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

using sort function to sort columnStep-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)

using sort function to sort column

Press “Enter”. Our sorting is done.

using sort function to sort column

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.

problems during sorting

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

problems during sorting

Click “OK”.

problems during sorting

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.

problems during sorting

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.

problems during sorting

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo