Reverse data order in Excel especially with dates

00:00 Excel sorts dates badly
00:30 Create an index
00:42 Sort on the new index

Images on website

If you receive data, especially from the internet, it may be in the wrong order. So in the example we use, the monthly exchange rates go down from the latest to the oldest. How can we reverse data in Excel so that, in this case, it goes from the oldest to the newest. The method used depends on what you are reversing and whether you want it to repeat the process or whether it is a once off.

Once Off Data Reversal
The first and easiest way is just to sort the rows based on something in the data e.g. date order. For dates in particular this doesn’t always work BECAUSE Excel may not recognize the data as valid dates and you end up with a crazy sort order. In other cases you may not have a column that you can conveniently just change the sort order.

Reverse Data in Excel

The easiest way is to create a helper column. In column G we will create a column that just increases by 1 in each row. So below in column G we have entered a 1 in cell G3 and then created a formula that adds one to the cell above. You could also use the Autofill to create these numbers. If you use the formula method, it is better to copy/ paste as values over the formulas before you do the next steps.

Reverse Data in Excel

Now simply sort column G from Largest to Smallest as shown below and the data will be reversed. The other benefit is that, if you keep this column, you will also be able to get back to the original sort order.

Reverse Data in Excel

Reverse data each time you receive the file
Excel’s new PowerQuery tool (you have it and it is free, even if you don’t know about it), has a reverse data feature built in. If you don’t know about PowerQuery have a look at the online course.

As shown below, once you have the data in PowerQuery, you can just go to the Transform tab and click the Reverse Rows button.

Reverse Data in Excel

The major benefit of this is that not only does it reverse the rows, but the step is remembered for next time. So when you update your information, you can just refresh the table and it will automatically reverse the rows for you. PowerQuery (now known as Get & Transform) is something you must learn about as soon as possible (click here for an introduction to PowerQuery)!

[denryuu]

Thank you very much, I can't believe excel doesn't have this in built function and I've been looking through internet for hours and you helped me solve this in a minute

[callumcawley]

you are the biggest saint of all time thank you