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.
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.
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 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.
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)!
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.