# A long long list I need to reverse

#### Shadowboxer

##### Board Regular
Hi

I'm in possession of a long typed list, which I need to place in the opposite order to that which I received from a colleague.

I'm struggling to find any help instructions on how to take a list and turn it 'upside down' (but only to reverse the order, not to actually turn the writing upside down).

Does anybody know a quick way of doing this, rather than having to retype the list in reverse order?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### njimack

##### Well-known Member
Use a helper column (say column A)
A1=1
A2=A1+1

Copy down as far as necessary, and then copy-paste special-values column A.

You can then sort column a DESCENDING

#### yee388

##### Well-known Member
You might create a column in which you enter 1,2,3, etc... then sort on the new column in decending order...

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Assuming I understand your question correctly, I've quickly used an INDEX() to reverse the order, having indexed all the cells in descending order first.
Book4
ABCD
1ORIGINAL LISTCHANGE ORDER
2text here5text
3some more text4yet some more
4even more text3even more text
5yet some more2some more text
6text1text here
Sheet1

Formula: =INDEX(\$A\$2:\$A\$6,B2,1)

#### Richard Schollar

##### MrExcel MVP

ADVERTISEMENT

If you have the list in an Excel file, you could always use something like:

=OFFSET(\$A\$2000,1-ROW(),0)

assuming that A2000 is the last value in your list and your list starts in row 1.

Best regards

Richard

#### Jon von der Heyden

##### MrExcel MVP, Moderator
slick one Richard!

#### Aladin Akyurek

##### MrExcel MVP

ADVERTISEMENT

Book3
ABCD
1
2dragontiger
3lionpanther
4cheetahcheetah
5pantherlion
6tigerdragon
7
8
Sheet1

C2, copied down as far as needed:

=INDEX(\$A\$2:\$A\$6,ROWS(\$A\$2:\$A\$6)-(ROWS(\$C\$2:C2)-1))

#### Shadowboxer

##### Board Regular
I'm obviously a dummy and doing things wrong as having had an extensive play can't get any of these to work.

I am clearly unable to follow instructions.

Any I shall go for the one that was described by someone else as slick first ...

I've not quite figured out where I should stick the formula ... let's say I have a list sitting in A6 to A4007 and I wish to display the list in reverse order within cells F6 to F4007.

I added the formula to F6. I also tried copying it throughout cells F6 to F4007 which hasn't worked. Then I somewhat pitifully ran out of ideas of how to apply the formula correctly.

Doh!

#### Richard Schollar

##### MrExcel MVP
Hi

=OFFSET(\$A\$4007,6-ROW(),0)

copied down.

Aladin's is actually more robust, but will do exactly the same at the end of the day.

Richard

#### Shadowboxer

##### Board Regular
Cheers for that - I just realised where I was going wrong - failed to spot that 1 represented column A and failed to change the number accordingly.

Oops

Thanks again.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back