Categories
Life hack

How to sort values in microsoft excel

How to sort values in microsoft excel

The Excel SORT function sorts the contents of a range or array. Values can be sorted by one or more columns. SORT returns a dynamic array of results.

  • array – Range or array to sort.
  • sort_index – [optional] Column index to use for sorting. Default is 1.
  • sort_order – [optional] 1 = Ascending, -1 = Descending. Default is ascending order.
  • by_col – [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.

The Excel SORT function extracts and sorts a list of unique values from a range. The result is a dynamic array of values. If this array is the final result (i.e. not handed off to another function) array values will “spill” onto the worksheet into a range that automatically updates when new uniques values are added or removed from the source range, or when source values change.

By default, the SORT function will sort values in ascending order using the first column. Use the optional arguments sort_index and sort_order to control which column to sort by, by what order (ascending or descending).

Examples

In the example shown, data includes in column B and scores in column B. The SORT formula in E5 is:

The SORT function extracts all values, sorted in ascending order by score, and results “spill” into the range E5:F14.

To sort by score in descending order, set sort_order to -1 in a formula like this:

Updated on January 17, 2021 by Swayam Prakash

While creating a repository of information it is important to organize that data well. When you arrange your data properly, you avoid confusion and mistake while using the info later on. Microsoft Excel is a popular and one of the oldest applications for storing data and records. The app has many in-built functions that help in properly arranging huge volumes of data. One of those is the sorting function which we will discuss in this guide. I have explained how to sort by date on Excel using two easy methods.

You can sort data chronologically in both ascending and descending order. Alternatively, you can sort by months as well. A part of data with certain months can be grouped. Basically, you have to set the dates as a reference point. let’s get into the guide and check out all the methods in detail.

How to sort values in microsoft excel

Sort by Date in Microsoft Excel

We will start by trying to sort by date chronologically.

Chronologically Sorting

The sorting happens with the latest date or earliest date in the record as a reference point. You can use any one of both.

  • Open a workbook on Microsoft
  • Select the rows of data that you want to sort
  • You can even choose the entire dataset on the workbook by pressing Ctrl + A
  • Then in the menu bar click on the Home tab
  • Under that navigate to the far-right corner to see an option Sort & Filter. Click on it
    How to sort values in microsoft excel
  • There will be two kinds of sorting Sort Oldest to Newest and Sort Newest to Oldest
    How to sort values in microsoft excel

The options are pretty self-explanatory. If you choose Newest to Oldest, then the record will display the selected data from the record with the latest date and will go all the way down to the earliest date of the month or year.

Sort by Date [Months and Year]

To sort by months or years you can use the inbuilt MONTH or YEAR functions. This means the reference point will be the month or year. The dataset must have values that consist of different

  • Launch Microsoft Excel
  • Open the workbook
  • Besides the existing data create a new column with the heading Month
  • Under the main dates column, type the formula =MONTH(first cell data column) or =YEAR(first cell of data column)
  • After setting the formula, select the new Month column you created earlier
  • Now, go to the Home tab in the menu bar and click on Sort & Filter
  • Again you get two types of sorting options as we did in the previous section
  • if you have set the year as a reference point, then the sorting will happen in terms of years [newest or earliest year in the dataset]
  • Or if you have set the year as a reference point, then the sorting will occur in terms of months [newest or earliest month in the dataset]

So, these are the two ways by which sort by date can be used on Microsoft Excel. Everyone likes to have their data collection organized properly. Sorting is a great way to ensure that. I hope this guide was helpful.

@howtogeek
Feb 19, 2019, 3:00 pm EST | 3 min read

How to sort values in microsoft excel

Excel has “Sort A to Z” and “Sort Z to A” features that let you sort values alphabetically or numerically, and a “Custom Sort” feature that lets you sort using multiple criteria. Here’s how to use them.

Let’s Look at an Example

In this example, we have chosen nine chemical elements from the periodic table, and we will sort the data based on the cell contents using the “Sort A to Z” feature and the “Custom Sort” feature.

The “Sort & Filter” drop-down is located on the far right of the “Home” tab.

How to sort values in microsoft excelSort & Filter” width=”425″ height=”148″ src=”https://www.howtogeek.com/pagespeed_static/1.JiBnMqyl6S.gif” onload=”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);” onerror=”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);”/>

The drop-down has several features listed, but we are focusing on the first three.

How to sort values in microsoft excel

Here is our example data, taken from the periodic table. Each “Element” has information that is unique to it, such as its “Symbol” and “Atomic Number.” But each element also belongs to other categories, such as its “Phase at STP” (i.e., whether it is a solid, liquid, or gas in a room at standard temperature and pressure) and its “Element Category” on the period table.

How to sort values in microsoft excel

Let’s start simple and sort the data by the “Atomic Number” column. Start by selecting the column (you can include the header row if your data has one).

How to sort values in microsoft excel

Now navigate to the “Sort & Filter” drop-down and select either the first or second option. In this example, we will sort from the lowest to highest numerical value using the “Sort A to Z” feature.

How to sort values in microsoft excel

A “Sort Warning” box will appear if there are other columns of data Excel thinks you might want to include in your sort. Let’s examine the results of the “Continue with the current selection” option, first. Click that option and press the “Sort” button.

How to sort values in microsoft excel

You can see that the “Atomic Number” column has been sorted from lowest to highest, but that the corresponding information in the other columns has not changed. This could be problematic if you wanted to sort all of the columns. So, before moving on, look at some of the rows to see if they make sense. In this case, it is obvious that the other columns have not been sorted because Hydrogen, the element with the lowest “Atomic Number,” is shown as having the “Atomic Number” of Lead.

How to sort values in microsoft excel

Now let’s try the other option to see how it works. Select the “Expand the selection” option and click the “Sort” button.

How to sort values in microsoft excel

You can see that the data in the “Atomic Number” column has been sorted and the data in the other rows followed it. (You could also just highlight all the data and Excel won’t show a “Sort Warning” box.)

How to sort values in microsoft excel

Now let’s see how we can use the “Custom Sort” feature to sort the data by several different columns at once.

Select all of the data, include the header row if your data has one.

How to sort values in microsoft excel

Now navigate to the “Sort & Filter” drop-down and select the “Custom Sort” command.

How to sort values in microsoft excel

This brings up the Sort window.

How to sort values in microsoft excel

We will sort our data first by using the “Phase at STP” column so that all of the gasses will appear higher on the list than the solids. Select the “Phase at STP” column.

How to sort values in microsoft excel

Now press the “Add Level” button.

How to sort values in microsoft excel

A new level appears. Note that the level says “Then by.” This means it will be the second column used to sort the data. We will choose the “Element Category” as the second level so that the gasses are sorted by their category and noble gasses will appear higher on the list than the reactive gasses. Select the “Element Category” column.

How to sort values in microsoft excel

Notice that there are other options. Let’s take a look at the “Sort On” drop-down options. There are a variety of options, but we will use the “Cell Values” option.

How to sort values in microsoft excel

Add another level by pressing the “Add Level” button again.

How to sort values in microsoft excel

Select the “Element” column. This means that the “Element” values in each “Element Category” will be sorted alphabetically so that the noble gas Argon will come before the other noble gas, Neon.

How to sort values in microsoft excel

Let’s look at the “Order” drop-down. There are a few options, but for this example, we will choose the default “A to Z” sort order.

How to sort values in microsoft excel

You can add as many levels as will fit in the “Sort” box. When you’re finished, click the “OK” button.

How to sort values in microsoft excel

The data has been successfully sorted. The sort order was first by “Phase at STP,” then “Element Category,” and then by “Element.” You can see that the “Phase at STP” column is sorted alphabetically and within each “Phase at STP” type, the “Element Category” values are sorted alphabetically. And finally, the “Element” values are sorted alphabetically within each “Element Category” type.

How to sort values in microsoft excel

Note: Be sure to select all of the columns you need when preparing to sort your data. Excel will not warn you that you may be overlooking columns when you use the “Custom Sort” feature.

@bryanclark
Jan 14, 2021, 10:24 am EST | 2 min read

How to sort values in microsoft excel

Sorting and filtering data offers a way to cut through the noise and find (and sort) just the data you want to see. Microsoft Excel has no shortage of options to filter down huge datasets into just what’s needed.

How to Sort Data in an Excel Spreadsheet

In Excel, click inside the cell atop the column you want to sort.

In our example, we’re going to click cell D3 and sort this column by salary.

How to sort values in microsoft excel

From the “Data” tab on top of the ribbon, click “Filter.”

How to sort values in microsoft excel

Atop each column, you’ll now see an arrow. Click the arrow of the column you wish to sort to bring up a menu that enables us to sort or filter the data.

How to sort values in microsoft excel

The first and most obvious way to sort data is from smallest to largest or largest to smallest, assuming you have numerical data.

In this case, we’re sorting salaries, so we’ll sort from smallest to largest by clicking the top option.

How to sort values in microsoft excel

We can apply the same sorting to any of the other columns, sorting by the date of hire, for example, by selecting the “Sort Oldest to Newest” option in the same menu.

How to sort values in microsoft excel

These sorting options also work for the age and name columns. We can sort by oldest to youngest in age, for example, or arrange employee names alphabetically by clicking the same arrow and choosing the appropriate option.

How to sort values in microsoft excel

How to Filter Data in Excel

Click the arrow next to “Salary” to filter this column. In this example, we’re going to filter out anyone who makes more than $100,000 per year.

How to sort values in microsoft excel

Because our list is short, we can do this a couple of ways. The first way, which works great in our example, is just to uncheck each person who makes more than $100,000 and then press “OK.” This will remove three entries from our list and enables us to see (and sort) just those that remain.

How to sort values in microsoft excel

There’s another way to do this. Let’s click the arrow next to “Salary” once more.

How to sort values in microsoft excel

This time we’ll click “Number Filters” from the filtering menu and then “Less Than.”

How to sort values in microsoft excel

Here we can also filter our results, removing anyone who makes over $100,000 per year. But this way works much better for large data sets where you might have to do a lot of manual clicking to remove entries. To the right of the dropdown box that says “is less than,” enter “100,000” (or whatever figure you want to use) and then press “OK.”

How to sort values in microsoft excel

We can use this filter for a number of other reasons, too. For example, we can filter out all salaries that are above average by clicking “Below Average” from the same menu (Number Filters > Below Average).

How to sort values in microsoft excel

We can also combine filters. Here we’ll find all salaries greater than $60,000, but less than $120,000. First, we’ll select “is greater than” in the first dropdown box.

How to sort values in microsoft excel

In the dropdown below the previous one, choose “is less than.”

How to sort values in microsoft excel

Next to “is greater than” we’ll put in $60,000.

How to sort values in microsoft excel

Next to “is less than” add $120,000.

How to sort values in microsoft excel

Click “OK” to filter the data, leaving only salaries greater than $60,000 and less than $120,000.

How to sort values in microsoft excel

How to Filter Data from Multiple Columns at Once

In this example, we’re going to filter by date hired, and salary. We’ll look specifically for people hired after 2013, and with a salary of less than $70,000 per year.

Click the arrow next to “Salary” to filter out anyone who makes $70,000 or more per year.

How to sort values in microsoft excel

Click “Number Filters” and then “Less Than.”

How to sort values in microsoft excel

Add “70,000” next to “is less than” and then press “OK.”

How to sort values in microsoft excel

Next, we’re going to filter by the date each employee was hired, excluding those hired after 2013. To get started, click the arrow next to “Date Hired” and then choose “Date Filters” and then “After.”

How to sort values in microsoft excel

Type “2013” into the field to the right of “is after” and then press “OK.” This will leave you only with employees who both make less than $70,000 per year who and were hired in 2014 or later.

How to sort values in microsoft excel

Excel has a number of powerful filtering options, and each is as customizable as you’d need it to be. With a little imagination, you can filter huge datasets down to only the pieces of information that matter.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to sort cells by absolute values in Excel?

Supposing you have a list of data which contain both positive numbers and negatives need to sort, if you apply the Sort function in Excel, all the numbers will be sorted by ascending or descending. But you want to ignore the sign when you sort your data, that means sort the cells by absolute values. Is there any way to sort the list of numbers by their absolute values in Excel?

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Sort cells by absolute values with a helper column

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

You can convert the values to absolute values first, and then use the Sort function to sort the values, and the cells will be sorted by the absolute values. Please do as follows:

1. In an adjacent blank cell, B2 for instance, enter this formula =ABS(A2) , see screenshot:

How to sort values in microsoft excel

2. Then press Enter key, and select cell B2, and then drag the fill handle to the range that you want to contain this formula, and all the numbers have been converted to absolute values. See screenshot:

How to sort values in microsoft excel

3. Then click Data > Sort A to Z or Sort Z to A as you need, see screenshot:

How to sort values in microsoft excel

4. And a Sort Warning dialog box will pop out, check Expand the selection option. See screenshot:

How to sort values in microsoft excel

5. Then click Sort button, and all the numbers have been sorted by the absolute values. See screenshot:

How to sort values in microsoft excel

6. After getting the sorted result, you can delete the helper column B as you want.

Sort cells by absolute values with Kutools for Excel

If you have installed Kutools for Excel, the feature of Advanced Sort can help you sort cells by absolute values more directly and simply.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

Step 1. Select the cells you want to sort by their absolute values, and click Enterprise > Advanced Sort. See screenshot:

Step 2. Specify the setting in the Advanced Sort dialog. See screenshot:

  • Select the column you want to sort and if the column has header, check My data has headers box;
  • Select Absolute value from Sort On drop-down list;
  • Choose ascending or descending order from Order part.

How to sort values in microsoft excel
Step 3. Click Ok, the cells are sorted by the absolute values. See screenshot:

Microsoft Excel is a powerful spreadsheet app. It lets you process a substantial amount of data. You can sort it, apply formulas, and use conditional formatting to make it easier to read. Add to this the many types of graphs it supports and you can get a lot done with it. Data sorting options apply to the columns in an Excel sheet. Just about everything that you can do to sort and filter data, you apply to columns. That’s how data is entered in a spreadsheet. The sorting works with columns by default. That said, you can sort data in rows in Excel. The feature is there and it’s not too hard to use either.

Open an Excel file. Enter data however you like. We’re assuming here that you want to sort data in rows instead of in columns. You can sort data in rows from highest to lowest value and in alphabetical order.

How to sort values in microsoft excel

On the ribbon, go to the Data tab. Click the Sort button. A new window will open with several tabs at the top. At the right-end of the tabs is an Options button. Click it.

How to sort values in microsoft excel

In this new window, you choose how the data is sorted. Since you want to sort data in rows, select the ‘Sort left to right’ option. Click Ok and return to the previous window.

How to sort values in microsoft excel

The previous window will show you three columns; Row, Sort On, and Order. Under each column is a dropdown. Open the dropdown under the ‘Row’ column and select the row you want to sort data in. Open the Order dropdown and select how you want to sort the data in the selected row.

You can sort the data by values, cell color, font color, and cell icon.

How to sort values in microsoft excel

Click Ok to sort the data.

This will not change the default sorting criteria in Excel. You will have to repeat the entire process each time you want to sort data in rows. This unfortunately doesn’t work with the Filter feature. You can only apply filters to columns and there’s no option to change it and apply them to rows.

If you need to apply a filter to rows instead of columns, you’re going to have to use the transpose feature and turn your rows into columns. There’s unfortunately no way around this. The filters are neat because you can change them and update the sorted data on the fly. You don’t have to reapply a sorting criteria over and over. Additionally, the filter feature lets you sort the data according to its value and not predetermined criteria like alphabetical order or numeric order.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

There are several methods to sort multiple columns, please see the following 3 common methods.

1. Custom Sort

Step 1: Click any cell in the data range;

How to sort values in microsoft excel

Step 2: Click the “Home” tab from the ribbon;

How to sort values in microsoft excel

How to sort values in microsoft excel

Step 4: Click “Custom Sort” from the drop-down list;

How to sort values in microsoft excel

Step 5: In the “Sort” window,

1. Select the first sort column in the first box (Column A in this example);

2. Select “Cell Values” in the second box;

3. Select “A to Z” in the third box;

4. Click the “Add Level” button on the top.

How to sort values in microsoft excel

Step 6: In the second row from the “Sort” window,

1. Select the second sort column in the first box (Column B in this example);

2. Select “Cell Values” in the second box;

3. Select “A to Z” in the third box.

How to sort values in microsoft excel

Step 7: Click the “OK” button and the data will be in the proper order.

How to sort values in microsoft excel

2. CONCAT (CONCATENATE) Function

Another method is to sort multiple columns with the CONCATENATE Function (or CONCAT Function) to create a helper column.

Step 1: Move your mouse to the last column and create a helper column;

How to sort values in microsoft excel

Step 2: In Cell C2, type the formula =CONCAT(A2,B2) and press Enter;

How to sort values in microsoft excel

Step 3: Copy the formula down to the bottom of the data range;

How to sort values in microsoft excel

Step 4: Sort the data range by the new column C “A to Z” or “Z to A“.

How to sort values in microsoft excel

3. The Ampersand (&) Operator

You can also sort multiple columns using the Ampersand (&), which is similar to the CONCAT Function.

Step 1: Move your mouse to the last column and create a helper column;

How to sort values in microsoft excel

Step 2: In Cell C2, type the formula =A2&B2 and press Enter;

How to sort values in microsoft excel

Step 3: Copy the formula down to the bottom of the data range;

How to sort values in microsoft excel

Step 4: Sort the data range by the new column C “A to Z” or “Z to A“.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to sort a column but keep intact rows in Excel?

Suppose you have multiple columns in a worksheet, now you want to sort a column and keep its intact rows order as below screenshot shown. Here I will tell you some ways to solve this problem in Excel.

How to sort values in microsoft excel

Sort a column but keep rows by Sort function

In Excel, you can use the Sort function to sort a column and keep rows.

1. Select the column data you want to sort, and then click Data > Sort. See screenshot:

How to sort values in microsoft excel

2. In the Sort Warning dialog, keep Expand the selection option checked, and click Sort.

How to sort values in microsoft excel

3. In the Sort dialog, specify the criterion that you will sort on and the sorting order. See screenshot:

How to sort values in microsoft excel

4. Click OK. Now the selected column has been sorted and the intact rows are still kept.

How to sort values in microsoft excel

If you want to sort the column data based on the absolute values, the Sort function of Excel cannot directly help you sort them. Now I introduce the Advanced Sort of Kutools for Excel for you.

Sort a column but keep rows by Advanced Sort function

With Kutools for Excel’s Advanced Sort function, you can sort data by absolute value, month, day, frequency, last name and so on.

After installing Kutools for Excel, please do as below: (Free Download Kutools for Excel Now!)

1. Select all the data range and then click Kutools Plust > Sort > Advanced Sort. See screenshot:

How to sort values in microsoft excel

2. In the Advanced Sort dialog, select the column you want to sort, and then select the criterion you want to sort on, then select the sorting order. See screenshot:

How to sort values in microsoft excel

3. Click Ok. Now the selected column data has been sorted with intact rows kept.

How to sort values in microsoft excel

If you are interested in Kutools for Excel’s Advanced Sort utility, you can click here to know more detail about this utility.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to sort chart by value in Excel?

In general, we can sort data by value or other criteria easily in Excel, but have you ever tried to sort chart by value? Now this tutorial will talk about the method on sorting chart by value in Excel, please read the following details.

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Sort chart by value

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Because the chart series order will change automatically with the original data changing, you can sort the original data first if you want to sort chart.

1. Select the original data value you want to sort by. See screenshot:

How to sort values in microsoft excel

2. Click Data tab, and go to Sort & Filter group, and select the sort order you need. See screenshot:

3. Then in the popped out dialog, make sure the Expand the select is checked, and click Sort button. See screenshot:

How to sort values in microsoft excel

Now you can see the data has been sorted, and the chart series order has been sorted at the same time.