Excel - How to reverse cell order?

So, I’ve got this situation in Excel where I have a single row with cells containing P I S M O—basically, every single letter is sitting in its own individual cell. What I'm trying to figure out is how to grab those letters and flip them around so they read backwards as O M S I P. I don't really care if the result stays in the same cells or moves to a neighboring row; I just need the sequence reversed. Any ideas on how to handle this via copying or some other trick?

So, you’ll need to build a custom function yourself by following these steps:

1. Open the VBA editor in Excel (just hit Developer -> Visual Basic)
2. Go ahead and select Insert -> Module
3. Paste this code right in:

Option Explicit
Public Function Reversetext(Text As String)
Reversetext = StrReverse(Text)
End Function

4. Close out of the VBA window
5. Now, just type =reversetext(A1) into any cell, and boom—it flips whatever is in A1 backward.

edit:
Wait, I just realized what you said about every single letter being in its own separate cell.
Seriously? If that's the case, why is it even an issue to just flip the order in the neighboring cells?

I'm done wasting my time on stuff like this.

Benjamin Wilson7 said:such nonsense.

Why call it nonsense? Maybe they just don't know yet (they gotta learn). Heck, maybe they opened Excel for the first time ten days ago. 😉

@Jessica Lopez19/">@@Jessica Lopez19
Do this:

Let's say you typed the word LETTER in the first row across columns.
A1=> L
B1=> E
C1=> T
D1=> T
E1=> E

Put these formulas in the next cells in that same row:
G1: =E1
H1: =D1
I1: =C1
J1: =B1
K1: =A1
Highlight "G1 through K1" and drag it down for as many rows as you need. Now every cell will have those same letters, just reversed.

If you want the whole thing reversed in one single cell, just put =CONCATENATE(E1,D1,C1,B1,A1) into cell G1 and copy it.

Benjamin Wilson7, that was just a quick little example to illustrate the point, obviously. We’re talking about dealing with a massive amount of cells here, where trying to rearrange everything by hand is basically a Sisyphus-level nightmare... imagine if you were staring down fifty rows with about seventy cells in every single one—is that more or less what you think I'm getting at?!?!?

John, that’s actually a brilliant way to look at it—it honestly didn't occur to me 😂 to just rearrange that first row like that and then drag the whole thing down... sometimes you really do just need a Fresh perspective to see the obvious. Thanks a ton! I wish I could just highlight the first two cells in the top row with the fill handle and drag them across to finish the sequence, but unfortunately, Excel is being stubborn; instead of continuing the pattern inward from A1 B1, it keeps pushing outward toward A1 B1, though I have to admit, even that is way faster than what I was doing!!

I was actually hoping to stumble upon some specific copying trick that would pull the data inward, or maybe some kind of double transpose, but since that hasn't materialized, this will definitely work. Thanks again for the Fresh idea....

Ugh, I just realized why that specific workaround wasn't hitting the mark for me (honestly, how did I miss that initially?!)—I tried to hardcode the cell reference since it’s pulling from a different sheet, and I was just too lazy to manually type out "=Sheet1!$A1" (even though, let's be real, it leads to the exact same result)....

Let's clarify the whole situation: what I'm actually trying to achieve is to take a 50 x 70 table on one sheet and have it automatically generate a mirrored version on another sheet. The data should stay in the same rows, but the order within the cells needs to be completely reversed....

The real kicker here, John, is that if there happen to be any empty cells tucked in there, you end up getting a zero instead of a blank space, which is totally useless for my purposes. But looking back, I really shouldn't have dismissed that solution so quickly; it's an easy fix if you just wrap it in an IF function to check whether the cell is empty or not....

Jessica Lopez19 said:Look, let me lay this out clearly. What I need is for a single 50 x 70 table on one sheet to automatically generate on a second sheet. The data needs to stay in the exact same rows, but the actual cells should be populated in reverse order...

I don't see the issue here.
Just run an example based on what you're trying to do.

On Sheet2, set up the formulas (using relative references in reverse)

A1: =IF(Sheet1!AA1<>"";Sheet1!AA1;"")
B1: =IF(Sheet1!AW1<>"";Sheet1!AW1;"")
C1: =IF(Sheet1!AV1<>"";Sheet1!AV1;"")
D1: =IF(Sheet1!AU1<>"";Sheet1!AU1;"")
.....
Or if you want the flipped version
=IF(Sheet1!AA1="";"";Sheet1!AA1)

but unfortunately, it trips up at A1 B1—it won't keep going backward, it just keeps copying forward from A1 B1,

Excel doesn't work like that; it won't drag a formula backward.
For that first row on Sheet2, you’ve gotta do it manually—cell by cell, formula by formula. Go slow and be careful, because when you're doing the same repetitive thing over and over, that's usually when mistakes happen.
Once that's done, just select the first row and drag it down to row 70.

Another way is to go cell by cell using Copy => Paste Special => Paste Link from Sheet1 to Sheet2.
If you do that, everything on Sheet2 will update whenever you change Sheet1, but keep in mind: if a cell is empty on Sheet1, it’ll show up as a zero (0) on Sheet2.

Kyle Johnson2 said:Honestly, I don't see where the issue lies here.
Just run through an example based on your specific problem.

On Sheet2, set up the formula (using relative references) going backward
:
A1: =IF(Sheet1!AA1<>"";Sheet1!AA1;"")
B1: =IF(Sheet1!AW1<>"";Sheet1!AW1;"")
C1: =IF(Sheet1!AV1<>"";Sheet1!AV1;"")
D1: =IF(Sheet1!AU1<>"";Sheet1!AU1;"")
.....
Or, if you prefer the reverse logic
: =IF(Sheet1!AA1="";"";Sheet1!AA1)

Look, Excel just doesn't work that way; it won't "copy backwards" for you.
For that first row on Sheet2, you’re going to have to do it manually—cell by cell, formula by formula—slowly and carefully. In scenarios like this, where you're repeating the exact same action over and over, most mistakes happen simply because you slip into autopilot.
Once that's done, just select the first row and drag it down to row 70.

The second option is to go cell by cell using Copy => Paste Special => Paste Link from Sheet1 to Sheet2.
If you go that route, everything on Sheet2 will update whenever you change something on Sheet1, with one little catch: if a cell is empty on Sheet1, it’ll show up as a zero (0) on Sheet2.

That is exactly what I ended up doing last night; took me about 10 minutes tops. 👍

Jessica Lopez19 said:Ugh, it just hit me why that workaround didn't sit right with me (honestly, how did I miss that initially?!), so I tried linking the cells instead since they're moving to another sheet, and I just couldn't be bothered to type out "=Sheet1!$H$1" by hand—though, let's be real, it's basically the same thing anyway....

Let’s step back and actually clarify what I'm trying to achieve here. I want to take a single 50x70 table on one sheet and have it automatically generate on a second sheet where the data stays in the same rows, but the actual cell order within those rows is completely reversed....

The real headache, John, was that if there are any empty cells in that range, you end up with a bunch of zeros instead of blanks, which is totally useless for my purposes. I really should have realized sooner that I could just toss that idea aside because it's such an easy fix using an IF function to check if a cell is blank before doing anything else....

If you don't want to mess around with complicated formulas, you can just turn off zero displays entirely. Just go into the Tools menu and uncheck "zero values." However, if your tables actually contain legitimate numbers, then using an IF statement is definitely the smarter way to go.

@Jessica Lopez19/">@@Jessica Lopez19

Alright, alright, my bad! I honestly thought you were just messing with me, but I see you're being serious now. My mistake.
Next time, though, could you maybe give me a bit more detail about what's bothering you? It helps!

Benjamin Wilson7 said:@Jessica Lopez19/">@@Jessica Lopez19

Alright, alright, my bad—I honestly thought you were just messing with me, but I see I was wrong.
Next time, though, maybe give us a bit more context so we actually know what's going on.

Ugh, sorry! I was trying to be quick about asking, and I didn't want to write a whole novel explaining why I needed this (or rather, bore everyone with the details), but I realize now that the way I phrased it sounded pretty ridiculous... What I'm actually struggling with is a school schedule that I've built in Excel, and the stupid software I use to print everything out doesn't have the option to flip the layout when the following week needs to be read "from the bottom up," and I really need to print both 🙂

Hey there!

Walter Jackson48 said:If you want to avoid making your formulas overly complicated, you can just turn off the zeros entirely. Just go into the Tools menu and uncheck "zero values." Of course, if your spreadsheet actually needs to display specific numerical data, then using an IF statement is probably your best bet.

Honestly, that didn't even cross my mind—I mean, we used to do that ages ago, but it hasn't occurred to me in forever. This definitely makes troubleshooting a lot easier. Thanks!

Edit:
Spoke too soon, though. I obviously can't use that trick when I actually need to show 0 hours in the sheet, so I guess I'm stuck with the IF check for empty cells after all. 🙂

Kyle Johnson2 said:Let’s say you have the word PISMO typed out in the first row across these columns:
A1=> P
B1=> I
C1=> S
D1=> M
E1=> O

Now, just drop these formulas into the next available cells in that same row:
G1: =E1
H1: =D1
I1: =C1
J1: =B1
K1: =A1

It's basically the same logic for writing formulas... but here's an automated way to do it:

=INDEX($A$1:$A$1000,COUNTA(A1:$A$1000))

hiddennomad13 said:It’s basically just writing formulas... Here's how you automate it...
=INDEX($A$1:$A$1000,COUNTA(A1:$A$1000))

"Automate it"? What does that even mean? How is that supposed to work for her massive 50-column by 70-row setup?

hiddennomad13 said:=INDEX($A$1:$A$1000;COUNTA(A1:$A$1000))

@hiddennomad13/">@@hiddennomad13
was waiting for you to jump in with an explanation, but whatever.

That formula caught my eye—you were definitely on the right track. The way you wrote it, though, it’s locked to one column (A), so you can't just drag it right to get what you need. Still, solid idea.

@Jessica Lopez19/">@@Jessica Lopez19 has 50 columns and 70 rows and needs to flip the data in a single row. So instead of A:AA, she needs AA:A covering the whole range to get those results (if I'm reading this right).

Your formula actually works great for auto-filling if you tweak it like this:

=INDEX($A1:$AA1;COUNTA(A1:$AA1))

Or specifically for her setup since the data is on Sheet1:

=INDEX(Sheet1!$A1:$AA1;COUNTA(Sheet1!A1:$AA1))

Just a heads up—this only works if there aren't any empty cells in the range (they gotta be zeros).

Basically, this kind of formula can be dragged across rows or columns easily because the cell references are set up with absolute columns and relative rows.

My bad, I didn't go through the whole thing super carefully—I just jumped straight to your solution.

But, if you’re looking to drag this formula to the right, you can just tweak those column references like this: =INDEX(A$1:A$1000,COUNTA(A1:A$1000))

Basically, if you type your target word into each column, the result will follow suit (the logic works for rows, too).

The only little hiccup is that it'll spit out a zero for any empty cells. To fix that, we need to add a check to see how many characters are actually in the column:

=IF(COUNTA(A$1:A$1000)>ROWS(A$1:A1), "", formula)

hiddennomad13 said:My bad, didn't read everything super closely—just looked at your fix... Only issue is it’ll spit out a zero for empty cells, so you gotta add a check to see how many letters are actually in the column.

Pretty sure those formulas you posted aren't quite hitting the mark again. They're based on columns and they skip the first cell.
If the second cell is blank, it shifts everything else and misses the first one entirely. Try leaving every other or third cell blank across a few rows or columns to test it.

The goal is to get all cells reversed, whether they have data or they're just empty.
I put a file for you to download ON THIS LINK so give it a shot and let me know what you think.😉

Well, there you go. Just tweak the columns or rows however works best for you..

Everything is laid out pretty clearly in that link, too...
If I can just go slightly off-topic for a second—instead of using the current formula where it would return a #VALUE error if you typed "Springfield" instead of "Springfield City," I’d probably swap in this version to make sure it just returns the word itself: =TRIM(RIGHT(A1;LEN(A1)-FIND(" ";A1&" ")+1)&" "&LEFT(A1;FIND(" ";A1;1)))

hiddennomad13 said:It’s basically just writing formulas... Here’s an automated way to do it...

=INDEX($A$1:$A$1000,COUNTA(A1:$A$1000))

I actually grabbed this formula and just stripped out the absolute references (the $) on the column. Once you do that, you can grab the first cell in column A, drag it across to the other columns, and then just use the fill handle to pull it down through all those other columns—and boom, you're done. Honestly, the INDEX function is pretty fascinating; I hadn't really played around with it before. I definitely know what COUNTA does, but INDEX is still a bit of a mystery to me. I'll have to keep scrolling down to see if anyone has figured out how to stop it from spitting out a zero when it hits an empty cell.

Thanks, this function is a total lifesaver!

hiddennomad13 said:The only real headache is that it’s going to spit out a zero for any empty cells, so you’ve got to add a check to see how many characters are actually in the column:

=IF(COUNTA(A$1:A$1000)>ROWS(A$1:A1); ""; formula)

I think you meant for the full formula to look like this:
=IF(COUNTA(A$1:A$1000)>ROWS(A$1:A1000); ""; INDEX(A$1:A$1000;COUNTA(A1:A$1000)))
I gave it a shot myself and realized you probably had a typo or a little copy-paste slip-up—if someone were to try using this, they’d need to go up to A1000, not just A1.

Anyway, it does exactly what I needed it to do... thanks a million!