 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Hi all,
Is there a simple way of reversing the order of a set of data in a column (or row) in Excel? eg if the column contained 1 2 3 4 5, can you easily swap them round so it goes 5 4 3 2 1 (which doesnt involve writing them all out again)?
Ta, Duane.
Data tab...
look for this [img] 
[/img]
Ok sorry, I dont want to sort them into order (either ascending or descending), that was just an example. eg if the column is 1 4 5 2, I'd like to flip it so its 2 5 4 1.
What iteration of Excel are you using (2003, 2007 etc)? You need to use the "sort" tool, which in 2007 is accessed by selecting the DATA tab. There is then a window called "Sort & Filter". Click at the top of the column you are interested in and press either A-Z to sort lowest to highest or Z-A Highest to Lowest. You can only sort columns like this.
Do you know about fill? If you enter your smallest or largest number (say 10 as you want to insert 10 down to 1), Click and hold on the cell with the number in and drag and highlight a number of cells. Then on the Home tab of Excel 2007 you will see a Fill button in the Editting field. Click on that and select Series, select your step value and your end value (so for 10 down to one you would enter step = 1, end value = 1). Click Ok.
OR
enter the first 2 values of your series in to two cells (so to do the same count cell 1 would = 10, cell 2 would = 9), click and highlight the two filled cells. You will see at the bottom right hand corner of the lowest/right most cell, a small square. Click on this and drag across the column/row that you want to fill and Excel will auto fill. It will even show you the value of each cell as you proceed.
Ah, now you tell us. Not sure.
add a new column, fill it with numbers 1 to ...let's say 100 (as many as you have rows)
Then sort descending by the new column
Assume the data you want to sort is in cells A1:A8
In cell B1, enter =COUNT(A1:$A$8)
drag-copy this formula down to B8
In column B, you should now have the numbers 8 - 1 in reverse order.
Finally, put the following in cell C1:
=INDEX($A$1:$A$8,B1)
This formula tells Excel to find the [B1]th value in the range A1:A8
Drag-copy this formula down to C8, and you should have all your values in reverse order.
+1 for CharlieMungus. Simple & effective 🙂
Pardon my ignorance, but how do you "sort descending by the new column"?
Using Excel 2003 btw.
add a new column, fill it with numbers 1 to ...let's say 100 (as many as you have rows)Then sort descending by the new column
BORING! 😉
But yes, effective.
Now for homework I want someone to do it using OFFSET
😉
duane:
insert new column
type "1" in top cell of column,
drag down so that column is full of numbers 1 to n all the way down the side of your existing data.
Select all your data including the new index column.
Got to Data menu, Sort
Select sort by your index column, descending.
Haha doh yeah, that works, cheers mate 🙂