WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Sort Data in Excel

Sort Data in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Madhuri Thakur

What is Sort in Excel?

SORT in excel means arranging the data in a determined order. Sometimes we need to arrange the names alphabetically, sort the numbers from smallest to largest, largest to smallest, dates from oldest to latest, latest to oldest etc. In excel we have an inbuilt tool called SORT option. This sort option can help us to sort the data based on the condition we give.

In excel SORT option is located under the Data tab.

sort option - step 1

  • In order to organize the data, the SORT option plays a key role in it. If the monthly sales data is not in order from Jan to Dec, it may not be a proper organizing way of the data structure.
  • Excel SORT option will help us to solve all kinds of data to make life easy. In this article, I will demonstrate the usages of the SORT option in excel and save a lot of time.

How to Sort Data in Excel?

You can download this Sort Options Excel Template here – Sort Options Excel Template

Example #1 – Single Level Data Sorting

This will sort the data based on only one column, nothing else.

Now, look at the below data. I have sales of different products by Segment and Country.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

sort option in excel - Example 1

I have the data until the 700th row. Download the excel file to follow along with me.

Now I want to sort them based on the Country column from A to Z.

  • Step 1: Select the data that we want to sort. You can use the shortcut key to select the entire data, select the first row, and then click Ctrl + Shift + Down Arrow.

sort option in excel - Example 1-1

  • Step 2: Go to Data tab > Sort. The shortcut key to open the sort option is ALT + D + S.

sort option in excel - Example 1-2

  • Step 3: Now make sure the “My data has headers” checkbox has ticked. If this box has ticked means, the selected data has headers otherwise;, your will header will be treated as the data only.

sort option in excel - Example 1-2

  • Step 4: Click on Sort by drop-down list and select the word Country.

sort option in excel - Example 1-3

  • Step 5: Click on Sort On. Here we can sort based on values, Cell Colour, Font Colour, and Cell Icon.

Click on Sort On

  • Step 6: Finally, select the Order. Here you can sort A to Z, Z to A, and custom list.

select the Order

  • Step 7: Final SORT of dialogue box should look like this.

Final SORT of dialogue box

  • Step 8: Click on OK it will sort the data country-wise alphabetically A to Z.

sort option in excel - Example 1-7

Example #2 – Multi-Level Data Sorting

In the previous example, we have learned single level sorting. In this example, I will explain to you the process of Multi-level sorting.

Previously I sorted the data country-wise. Assume I want to sort the data Segment-wise, Product-wise, and Units sold from largest to smallest. This requires multi-level sorting.

  • Step 1: Select the data. (I am using the same data from the previous example)

sort option in excel - Example 1-1

  • Step 2: Press ALT + D + S (shortcut key to open the SORT box)

Press ALT + D + S

  • Step 3: First select Segment heading.

select Segment heading

  • Step 4: Now, click on Add Level to insert one more layer.

click on Add Level

  • Step 5: From the second layer, select the Product.

second layer select Product

  • Step 6: Now, click on Add Level to add the third layer.

click on Add Level

  • Step 7: From the third layer, select the Units Sold header.

select Units Sold header

  • Step 8: by default, the Order will be from Smallest to Largest. Under Order, click on the dropdown list and select Largest to Smallest.

 click on the dropdown list

  • Step 9: Both the layers will be sorted alphabetically, and Units Sold will be sorted from the largest value to the lowest value.

sort option in excel - Example 2-6

Firstly, it will sort the data based on the Segment column. They will sort by Product and finally by Units sold (largest to smallest).

Example #3 – Sorting Dates Data

I have a sales table country-wise at different dates. I want to sort the data country-wise first and then date-wise (oldest to newest)

  • Step 1: Select the data first.

sort option in excel - Example 2-7

  • Step 2: Open sort of option. (ALT + D + S)

Open sort of option

  • Step 3: In the first drop-down list, select a Country-wise header.

drop-down list select Country-wise header

  • Step 4: Click on Add Level to insert one more layer.

Click on Add Level

  • Step 5: From the second layer, select the Date header.

sort

  • Step 6: by default, orders selected Oldest to Newest. Our objective is to sort from oldest to newest.

Click on OK to sort.

sort option in excel - Example 3-4

Things to Remember

  • We need to select the entire to sort. Otherwise, left out columns will be as it is.
  • Make sure my data has a header check that has been selected to sort the data.
  • We can sort the colored cells, colored font, etc.…
  • We sort the data by applying the filter in excel as well.
  • We can sort from A to Z, Z to A in case of alphabetic.
  • We can sort from Largest to Smallest, Smallest to Largest in case of numbers.
  • In the case of dates, we can sort from Oldest to Newest, Newest to oldest.

Recommended Articles

This has been a guide to Sort in Excel. Here we will demonstrate the SORT option in excel (Single Level and Multi-Level Sorting) along with an excel example and a downloadable template. You may also look at these useful functions in excel –

  • Sort by Number in Excel
  • Sort by Color in Excel
  • VBA Sort Range
  • REPT Function in Excel
52 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel Tutorials,  Excel, VBA & Power BI

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Sort Options Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More