How to Sort in Excel - Compute Expert

How to Sort in Excel




In this tutorial, you will learn how to sort in excel completely.

When working in excel, we sometimes need to sort data in order so we can analyze them much easier. Different types of data in excel may require different kinds of sorting method. With that in mind, we will discuss these sorting methods so you can sort what you need when working in excel.

Want to master ways to sort various data in excel? Read this tutorial until its last part!







How to Sort Alphabetically in Excel

We will start this tutorial by discussing the way to sort data alphabetically in excel.

Sorting alphabetically is one of the basics when we talk about the process of sorting data anywhere. We can do this process in excel quite easily by following these steps.

  1. Highlight the column cell range you want to sort alphabetically

    How to Sort in Excel - Screenshot of How to Sort Alphabetically, Step 1

  2. Go to the Data tab and click the A to Z button or the Z to A button

    How to Sort in Excel - Screenshot of How to Sort Alphabetically, Step 2

  3. In the dialog box that shows up, there are two choices of sort process. Choose “Expand the selection”Choose “Continue with the current selection” if you want to only sort the column you highlight.

    Click the Sort button after you choose

    How to Sort in Excel - Screenshot of How to Sort Alphabetically, Step 3

  4. Done!

    How to Sort in Excel - Screenshot of How to Sort Alphabetically, Step 4




How to Sort Numbers in Excel in Ascending/Descending Order

Want to sort numbers in excel in ascending/descending order? The steps to do that are quite similar to how you sort your data alphabetically.

Follow the previous steps to sort your data alphabetically to sort your numbers in excel. Highlight the cell range containing your numbers, go to the Data tab, and click one of the two sort buttons there. Excel changes the button names into “Sort Smallest to Largest” (ascending) and “Sort Largest to Smallest” (descending) when you want to sort numbers.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

When you click the button, choose either “Expand the selection” or “Continue with the current selection”. Click the Sort button and you have sorted your numbers in the order you want!

How to Sort in Excel - Screenshot of the Number Sort Result Example



How to Sort Names in Excel

Sorting names in excel are the same as sorting data alphabetically. You can either sort it from A to Z or Z to A.

Just follow the previous steps to sort your data alphabetically in excel to sort your names. Doing that will make you able to organize your names in order!



How to Sort by Date in Excel

A date is a type of number data in excel. Therefore, the way to sort by date is quite similar to the way to sort numbers in excel.



Highlight the cell range containing your dates, go to the Data tab, and click one of the two sort buttons there. You can either click the “Sort Oldest to Newest” or “Sort Newest to Oldest” button if you sort dates.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

After that, choose either “Expand the selection” or “Continue with the current selection” in the dialog box that shows up. Click Sort and you have sorted your dates in the order you want!

How to Sort in Excel - Screenshot of the Date Sort Result Example



How to Sort Time in Excel

Just as the date is, time is also a type of number data in excel. Therefore, you can also sort time in excel by using the method to sort numbers.

Highlight the cell range containing the time, go to the Data tab, and click one of the two sort buttons there. You can click either the “Sort Smallest to Largest” or “Sort Largest to Smallest” button.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

Choose “Expand the selection” or “Continue with the current selection” after that and then click Sort. You have sorted the time in your column in the order you want!

How to Sort in Excel - Screenshot of the Time Sort Result Example



How to Sort Number IDs in Excel

To sort number IDs in a column, just use the numbers sorting method as number IDs are (obviously) numbers too.

Highlight the number IDs column, go to the Data tab, and click one of the sort buttons there. You can either click the “Sort Smallest to Largest” or “Sort Largest to Smallest” button.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

Choose “Expand the selection” or “Continue with the current selection” and click Sort. In the example, we choose “Continue with the current selection”.

Do all of those steps and you will immediately sort your number IDs in excel!

How to Sort in Excel - Screenshot of the Number ID Sort Result Example



How to Sort Highlighted Cells in Excel

Need to sort data in some isolated, highlighted cells in excel? Just do it using the similar method we have discussed to sort data in excel.

Keep highlighting the cells, go to the Data tab, and click one of the two sort buttons.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

Do all that and you have sorted the data in your highlighted cells!

How to Sort in Excel - Screenshot of the Highlighted Cells Sort Result Example




How to Sort by Color in Excel

Have done some color coding in your data table and want to sort the table using the color as the basis? Follow the steps below to do the sorting process.

  1. Put your cell cursor in the cell range where you want to sort based on color

    How to Sort in Excel - Screenshot of How to Sort Color, Step 1

  2. Go to the Data tab and click the Sort button there

    How to Sort in Excel - Screenshot of How to Sort Color, Step 2

  3. In the dialog box that shows up, input the details of the color sorting process you want for your table. For each additional detail, you need to click the Insert button there.

    How to Sort in Excel - Screenshot of How to Sort Color, Step 3-1

    In each detail, you need to input these:
    • The column which color you want to base your sorting process on in the “Column” dropdown
    • The basis of the sorting process in the column in the “Sort on” dropdown. As you want to use color as the basis here, choose either “Cell Color” or “Font Color”
    • The color you want to determine the sorting order of in the “Color/Icon” dropdown
    • The order you want for the color you chose. On top or bottom

    Give the details for all colors you want to base your sorting process on

    How to Sort in Excel - Screenshot of How to Sort Color, Step 3-2

  4. Click OK. By doing these steps, you have sorted your data table by color!

    How to Sort in Excel - Screenshot of How to Sort Color, Step 4




How to Sort Duplicates in Excel

If you have duplicates in your data, you may want to sort them to the top to identify them much faster. To do this in excel, you can combine the usage of conditional formatting and the sorting process by color.

We use conditional formatting to mark our duplicates first by color. To do that, highlight the cell range that contains data with duplicates you want to sort. Then, go to the Home tab, click the Conditional Formatting dropdown button, and choose Manage Rules….

How to Sort in Excel - Screenshot of the Home Tab, Conditional Formatting Button Dropdown, and Its Manage Rules... Button

In the conditional formatting dialog box, click the New Rule… button.

How to Sort in Excel - Screenshot of the New Rule... Button Location in the Conditional Formatting Dialog Box

In another dialog box that shows up, choose “Format only unique or duplicate values” in the Select a Rule Type box. Then, in the part below the box, make sure you select “Duplicate” in the dropdown.

How to Sort in Excel - Screenshot of the Format only unique or duplicate values Choice and Duplicate Value Dropdown Locations

Next, click the Format… button.

How to Sort in Excel - Screenshot of the Format... Button Location in the Conditional Formatting Dialog Box

As we want to color the duplicates, go to the Fill tab in the dialog box that shows up. Then, choose the color you want as the duplicates mark.

How to Sort in Excel - Screenshot of the Fill Tab Location in the Format Dialog Box

After you pick a color, click OK and then OK and then OK again. Now, the cells containing the duplicates in the cell range you highlight should have the color you picked.

How to Sort in Excel - Screenshot of the Conditional Formatting Result Example to Color Cells with Duplicates

Next, we just need to sort our cell range based on the color of the duplicates. Put your cell cursor in the cell range with your data, go to the Data tab, and click the Sort button.

How to Sort in Excel - Screenshot of the Data Tab and Sort Button Locations

In the Sort dialog box, create a sorting detail that will make cells with the duplicate color go to the top. In the example, we choose the “Name” column, sort on “Cell Color”, “Yellow” color/icon, and “On Top” order.

How to Sort in Excel - Screenshot of the Detail Input Example to Sort Duplicates

Click OK after you finish inputting the sorting detail. Now, your duplicates should be at the top of your cell range!

How to Sort in Excel - Screenshot of the Duplicates Sort Result Example



How to Sort Multi-Level in Excel

Need a multi-level sorting process for your data in excel?

When you have this kind of needs, you might have multiple variables as the basis of your data table sorting process. There is a prioritization, of course, as we sort based on one variable first before we sort based on other variables.



We can do this sorting process by using the excel sort dialog box. Place your cell cursor in the data table where you want to do the multi-level sorting process. Then, go to the Data tab and click the Sort button there.

How to Sort in Excel - Screenshot of the Data Tab and Sort Button Locations

In the example, we want to sort based on the occupation first (A to Z) before we sort based on the name (Z to A). To do that, in the sort dialog box, we should create the details for both sorting processes.

The top detail in the dialog box is the one that excel will execute first. Thus, we create a sorting detail based on the occupation first here. We input the “Occupation” column, sort on “Values”, and “A to Z” order for the detail.

How to Sort in Excel - Screenshot of the First Detail Input Example to Sort Multi-Level

After we have done the first detail, we add another level to our sorting process based on names. To add that, we click the Insert button in the dialog box.

How to Sort in Excel - Screenshot of the Insert Button Location in the Sort Dialog Box

Clicking the button will add another line where we can input another sorting detail. Here, we input the “Name” column, sort on “Values”, and “Z to A” order.

How to Sort in Excel - Screenshot of the Additional Detail Input Example to Sort Multi-Level

If you have other sorting levels you want to add, then keep clicking the Insert button and filling those lines. You can also move up/down the sorting levels and delete the ones you don’t need anymore in the dialog box.

Click OK after you have finished the sorting details input. Now, your data table should sort itself based on the sorting details you have inputted in the dialog box!

How to Sort in Excel - Screenshot of the Multi-Level Sort Result Example



How to Sort Subtotals in Excel

If you use the subtotal feature in excel, then you can sort the subtotals of your numbers too if you want. The way to do the sorting process is quite simple.

First thing you need to do obviously is to apply the subtotal feature to the numbers in your data table. If you haven’t done that, then place your cell cursor in the cell range where you want to get subtotals. Then, go to the Data tab and click the Subtotal button there.

How to Sort in Excel - Screenshot of the Data Tab and Subtotal Button Locations

Excel will show you the subtotal feature dialog box. Make sure the data variable you want to subtotal already has a checkmark in its check box there. Moreover, make sure the first dropdown places your subtotals in the right place and the second dropdown value is “Sum.

In the example, we want the subtotals of sales quantity and we want to place them below each month. Therefore, our subtotal dialog box becomes like this.

How to Sort in Excel - Screenshot of the Subtotal Dialog Box Settings Example

We click OK after that and excel will subtotal the numbers of the data variable we set in the dialog box!

How to Sort in Excel - Screenshot of the Subtotal Feature Implementation Result Example

What comes with the subtotals is the data grouping settings that you can toggle at the left side of your worksheet. This grouping will become important if you want to sort your subtotals.

How to Sort in Excel - Screenshot of the Data Grouping Part as a Result of the Subtotal Feature Implementation

To start the subtotals sorting process, click all the minus symbols in line 2 on the left side.

How to Sort in Excel - Screenshot of the Minus Symbol Locations in the Data Grouping

Doing that will make your data table only show the rows with the subtotal and total numbers.

Next, place your cell cursor in the column where your subtotals are. Then, go to the Data tab and click either the “Sort Smallest to Largest” or the “Sort Largest to Smallest” button. The button you should click obviously depends on the subtotals order you want in your sorting process.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

Excel will immediately sort your subtotals based on the order you want! In the example here, we sort the subtotals from largest to smallest.

How to Sort in Excel - Screenshot of the Subtotals Sort Result Example

If you uncollapse your data again (by clicking the plus symbols on the left side), you will see that excel only sort your subtotals, not your numbers.

How to Sort in Excel - Screenshot of the Data Display Example in the Subtotals Sort Result

This is great if what you want is to only know the order of your subtotals without breaking your data order!



How to Sort Columns in Excel

Your data in excel is in columns instead of rows and you want to sort it? Although uncommon, you can do that by changing few details to the usual sorting process.

To start sorting your columns, highlight the columns which data you want to sort. Then, go to the Data tab and click the Sort button there.

How to Sort in Excel - Screenshot of the Data Tab and Sort Button Locations

In the Sort dialog box, click the Options… button.

How to Sort in Excel - Screenshot of the Options... Button Location in the Sort Dialog Box

Choose the “Sort left to right” orientation and click OK.

How to Sort in Excel - Screenshot of the Sort left to Right Settings Example in the Options Dialog Box

Now, you can input the details of the sorting process for your columns! Choose the row which becomes the sorting basis (Row), what to sort there (Sort on), order (Order), and the color/icon (Color/Icon) to sort if any. For this example, we input Row 2 (Row), Values (Sort on), and A to Z (Order).

How to Sort in Excel - Screenshot of the Detail Input Example for the Columns Sort

Click OK after you have inputted all the sorting details for your columns. Doing that will sort your columns according to your sorting detail inputs!

How to Sort in Excel - Screenshot of the Columns Sort Result Example




How to Sort a Table in Excel

As an excel table already has its own sort feature, it is quite easy to sort the data in it.

To start doing the table sorting, first make sure your headers already have their filter buttons. They are the buttons with the down arrow on the right side of your headers.

How to Sort in Excel - Screenshot of the Filter Buttons Example in an Excel Table

They usually show up right after you convert a cell range into a table. If they don’t, then you can show them by checking the “Filter Button” check box in the Table tab. Place your cell cursor inside your table so the Table tab shows up in your ribbon.

How to Sort in Excel - Screenshot of the Table Tab and Filter Button Check Box Locations

If the filter buttons are there, click the button on the column you want to base your sorting process on. Then, choose whether you want to sort the column data in ascending or descending order.

How to Sort in Excel - Screenshot of the Sort in Ascending and Descending Order Choices in the Filter Button Menu

Doing that will make your table sort its data according to your preference!

How to Sort in Excel - Screenshot of the Table Sort Result Example



How to Sort a Pivot Table

Want to sort the data you have in your pivot table? You can do it just like when you want to sort data in a standard data table in excel.

Place your cell cursor in the column/row/value that you want to base your pivot table sorting process on. Then, go to the Data tab and click either the “Sort in Ascending Order” or “Sort in Descending Order” button (the names of the buttons depend on the type of data you want to base your sorting process on).

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

How to Sort in Excel - Screenshot of the Pivot Table Sort Result Example

Done! You have sorted your pivot table based on the column/row/value you want!



How to Sort Charts in Excel

Need to sort your chart so it shows your data in an ascending/descending manner? You can do that by sorting its data source as the chart display should follow suit after that.

You can sort the data source by using the standard steps to sort data in excel we have previously discussed. Place your cell cursor in the column you want to base your chart sorting process on. Then, go to the Data tab and click the “Sort in Ascending Order” or “Sort in Descending Order” button.

How to Sort in Excel - Screenshot of the Data Tab and Two Sort Buttons Locations

Do that and you have sorted your chart display!

How to Sort in Excel - Screenshot of the Chart Sort Result Example



Exercise

After you have finished learning how to sort in excel from this tutorial, let’s do an exercise here. This is so you can understand what you have just learned more practically.

Download the exercise file and answer all the questions. Download the answer key file if you have done the exercise and want to check your answers

Link to the exercise file:
Download here

Questions

Answer these questions in the appropriate gray-colored cells in the exercise file!
  1. What is the date, product, and amount sold at the top if you sort the table based on the highest sales?
  2. What is the date, product, and amount sold at the bottom if you sort the table based on the newest sales?
  3. What is the fifth date, product, and amount sold if you sort the table with the following sorting details? Sort the green cells first to the top before you sort the data with red font colors

Link to the answer key file:
Download here



Additional Note

You can also sort by right-clicking the cell range you want to sort or by using the filter button. To activate the filter button, place your cell cursor in the data table you want to sort first. Then, go to the Home tab, click the Sort & Filter dropdown button, and choose Filter.

How to Sort in Excel - Screenshot of the Home Tab, Sort & Filter Dropdown Button, and its Filter Choice Locations

There will be down arrow buttons show up in your table headers. You can click the button in the header on which data you want to base your sorting process on. Then, choose sort in ascending order or in descending order.

How to Sort in Excel - Screenshot of the Sort in Ascending and Descending Order Choices in the Filter Button Menu

By doing that (or by choosing the relevant Sort command in the right-click menu), you have sorted the data in your table using the basis that you prefer!

How to Sort in Excel - Screenshot of the Sort Result Example by Using a Filter Button



Related tutorials you should learn too:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert