anything in Excel...

It's Excel-o-lozy...

TrickPro - Transpose formulae, the super trick

Ever thought of "Transposing Formulae" through paste special? The error is that cell reference also gets changed.

Let me elaborate:
  • Select a range that contains formulae.
  • Copy the range.
  • Go to destination cell.
  • Go to paste special, select "Transpose" then "Ok".

What happens?

Data is transposed but cell reference changes accordingly. Hell...

Super Trick is so simple:

Before the data is copied, as elaborated supra, do as follows:

  • Select the range.
  • Find and replace "=" with "vv" (or any text you like) and do a "Replace All"
  • Go to destination cell.
  • Go to paste special, select "Transpose" then "Ok".
  • Find and replace "vv" with "=" (only "=", not any text you like) and do a "Replace All"
Woho, magic...
and I think I need not explain how it works...

0 comments: