This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel – 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: I have a column with values such as Monday, Wednesday, and so on. When I sort this column in ascending sequence, Friday comes before Monday. The same problem happens with month names, which sort as April, August, December, and so on.
- Friday is alphabetically before Monday.
Strategy: Excel has custom lists built in for months and days. To use them, follow these steps:
- Select a cell in your data.
- Select Data, Sort.
- Choose Sort by Day and Sort on Values. In the Order dropdown, choose Custom List.
- Choose Sunday, Monday, Tuesday from the Custom List dialog. Click OK.
Excel will sort the data correctly.
For more resources for Microsoft Excel
- Excel VBA Book
- Ctrl + Shift + Enter Array Formulas
- Learn Excel from MrExcel
MrExcel.com & related websites debuted on November 21, 1998.
ExcelArticles.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided “as is” and we do not guarantee that they can be used in all situations.
This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com and ExcelArticles.com running.
You can earn a commission for sales leads that you send to us by joining our affiliate program.
Excel® is registered trademark of the Microsoft Corporation.
MrExcel® is a registered trademark of Tickling Keys, Inc.
© 1998 – 2021 by MrExcel Publishing. All rights reserved.
A lot of my Financial Clients send out Birthday cards to their customers. This is an awesome practice but sometimes hard to carry out. The challenge is sorting the birthdays by the day of the month rather than by the age of the person. There is a great way to do this in Excel!
The birthdays have been inputted as the Date of Birth (for the customer), complete with year of birth. When this is exported to Excel and then sorted you get a list sorted by the youngest to oldest person.
| Birthday | Sorted Birthday |
|---|---|
| January 1, 1970 | January 14, 1968 |
| January 14, 1968 | January 1, 1970 |
| January 20, 1980 | January 20, 1980 |
| January 9, 1987 | January 9, 1987 |
When sending out birthday cards you want to sort this birthday list in order of the days of the month (not by age). This can be done if you use the right Function in Excel.
Formula for Sorting Birthdays in Excel
Use this Microsoft Excel function to extract only the month and day. Then do a sort to sort in order of the day of the month.
=Text (A2, “MMDD”) where A2 is the birthday, copy this formula down the sheet
| Sorted Birthday | Excel Formula added |
|---|---|
| January 1, 1970 | 0101 |
| January 9, 1987 | 0109 |
| January 14, 1968 | 0114 |
| January 20, 1980 | 0120 |
If you are using a CRM (Client Relationship Management) program like Maximizer and have entered in the birthdays for your clients this can be a handy tip for you to use.
Free Excel Resource for you
Do you need to get better with Excel? Or do you have a Team in your office using Excel and would like to help them be more productive?
Click on Button below to download free PDF on the 5 Ways Managers Can Make Their Team More Productive with Excel…
Mission Computers enjoys teaching Excel and Maximizer programs. We have standard introductory, intermediate and advanced levels, plus we customize courses often for our clients.
Reverse Order of Excel Data
The reverse order in excel is nothing but flipping the data where the bottom value comes on top and top value goes to the bottom.
For example, look at the below image.
As we can see, the bottom value is at the top in the reverse order, and the same goes to the top value as well. So, how do we reverse the order of data in excel is the question now.
How to Reverse the Order of Data Rows in Excel?
In excel, we can sort by following several methods; here, we will show you all the possible ways to reverse the order in excel.
Method #1 – Simple Sort Method
You must already be wondering is it possible to reverse the data by just using a sort option. We cannot reverse just by sorting the data, but with some helper column, we can actually do this.
Step 1 – Consider the below data for this example.
Step 2 – Next to this, create a column called “HELPER” and insert serial numbers.
Step 3 – Now, select the entire data and open the sort option by pressing ALT + D + S.
Step 4 – Under “Sort by” choose “Helper.”
Step 5 – Then, under “Order,” choose “Largest to Smallest.”
Step 6 – Now click on Ok, our data will be reversed.
Method #2 – Using Excel Formula
We can also reverse the order by using formulas as well. Even though we don’t have any built-in function to do this, we can make use of other formulas to reverse the order.
To make the reverse order of data, we are going to use two formulas, i.e., INDEX and ROWS. INDEX Function can fetch the result from the mentioned row number of the selected range, and the ROWS function in excel will give the count of a number of selected rows.
Step 1 – Consider the below data for this example.
Step 2 – Open the INDEX function first.
Step 3 – For Array, select the city names from A2: A9 and make it absolute reference by pressing the F4 key.
Step 4 – Next, to insert Row Num open ROWS function inside the INDEX function.
Step 5 – For the ROWS function, select the same range of cells as we have selected for the INDEX function, but this time only makes the last cell as an absolute reference.
Step 6 – Close the bracket and hit the enter key to get the result.
Step 7 – Drag the formula to get the full result.
Method #3 – Reverse Order by Using VBA Coding
Reversing the order of excel data is also possible by using VBA Coding. If you have good knowledge of VBA, then below is the code for you.
Code:
Copy this code to your module.
Now run the code to get the reverse order list in your excel worksheet.
Let me explain to you how this code works for you. First, I have declared two variables, “k” & “LR” as a LONG data type.
“k” is for looping through cells, and “LR” is to find the last used row in the worksheet.
Next, I have used finding the last used row technique to find the last value in the string.
Next, I have employed FOR NEXT loop to loop through the cells to reverse the order.
So when we run this code, we get the following result.
Things to Remember
- There is no built-in function or tool available in excel to reverse the order.
- A combination of INDEX + ROWS will reverse the order.
- Of all the available techniques, the sort option is the better and easy way to sort.
- To understand VBA code, you need to have prior knowledge of VBA macros.
Recommended Articles
This has been a guide to Excel Reverse Order. Here we discuss how to reverse the order of data using 1) Sort Method, 2) Excel Formula 3) VBA Code. You can learn more from the following articles –
