anything in Excel...

It's Excel-o-lozy...

#Execelolzy - Transpose formulas without changing reference - Copy Paste Trick - relative or fixed reference - Column to Row - errors in MS Excel

www.Excelolozy.com - MS Excel changes reference while Copying formulas from horizontal cells (rows) to vertical cells (columns). Transpose command under paste special has this error.

Following are two easiest methods to do it:

  1. Absolute reference trick - Slow method 
  2. Replacing "=" (Equal sign)  trick - Fast method 

These are elaborated as follows:

  1. Absolute reference method: 
    • Edit all cells in range by inserting a dollar sign in front of each row and column reference.
    • It makes all cells as absolute cell references.
    • It will keep formulas static.
    • Now copy the range and paste at destination cell/s through > Paste > Paste Special and now then check the box next to Transpose and it's done.

       
  2. Replacing "=" (equal sign):
    • Select the range to be transposed. 
    • Press Ctrl+H and replace "=" (Equal sign) with any sign say "#" (Hash). 
    • Click Replace All. 
    • It converts the formulas to text.
    • Copy the selected data. 
    • Paste the data through Paste > Paste Special by checking Transpose option.
    • The data is convert from Row to Column or vice versa.
    • Now replace "#" with "=" using Replace All button in the find and replace box.
 Voila.. It's done.. Enjoy.. Cheers.. 


Excelolozy for Excel tips and tricks by Vikrmn author 10 Alone CA Vikam Verma
Excelolozy 
 Anything in Excel, be an Excelolozian... for best MS Excel Tips visit http://www.Excelolozy.com 
Gotta query? Drop us a mail at mail@excelolozy.com  
Posted by Vikrmn: Author of "10 Alone" (CA Vikram Verma) 10Alone.com

0 comments: