Two Excel tricks

These are tricks I found in Excel. I'm writing them here in case I want to use them again.
Say you have data like:

These tricks will teach you to:

• Invert the data i.e., 55, 34, 21 ...
• Select only even cells, odd cells, cells 3, 6, 9 ... or any pattern that can be described mathematically

Invert the data

Functions used

=ROW()

Returns the row number of the current cell. E.g.

=INDIRECT()

Returns the value in the cell referred to.

E.g.

INDIRECT("D51") returns the value in cell D51 - in this case, MBNI.

However, INDIRECT(D51) returns the value in the cell in the cell in D51. It's a bit confusing.

It jumps twice: INDIRECT(D69) → D69 contains E71 → E71 contains bounce → bounce

If D69 contained "E69", it would result in a circular formula. The best way to use this function is with the function ADDRESS.

=ADDRESS()

Converts row and column numbers into an address. E.g.

Obviously, a cell can address any other cell, not just itself.

Putting it all together

Can you see where all this is heading? If you can express the sequence of cells in a mathematical formula, you can refer to them with a combination of the above functions. To rearrange cells, we want:

Or, thinking in row and column numbers:

But that still requires a different entry for each cell. We need to represent this using each cell's row number, so we can use the same formula for each cell:

Let's check using cell B5. The current row is 5. 7-current row = 2. Compare with the previous picture, yes! We want row 2. Now all we have to do is express this in Excel language.

If you're wondering how to figure this out, start from the inside out. Look at cell B1. ROW() = 1. 7-ROW = 6. So ADDRESS becomes ADDRESS(6,1), which is A6. INDIRECT("A6") gives the value in cell A6 = 55.

If you're one step ahead, you've already realised you don't have to invert rows side by side. I can invert rows anywhere in the spreadsheet, invert columns or convert rows to columns, or even convert diagonals to rows with a bit of manipulation!

Selecting patterns of cells

Selecting cells is just an extension of the previous. Let's start with a longer list and the cells whose values we want in column B:

Make it into a formula:

I'd much appreciate other uses for these often overlooked but highly useful functions!