Transposition errors in Excel
Q: Is there an easy way to copy formulas that are arranged horizontally across an Excel worksheet and paste them vertically down the page? When I try this using Excel’s Paste-Transpose command, the cell references change and no longer reference the correct cells.
A: I can think of three ways to accomplish your task, as follows:
1. Copy using absolute references. Edit each formula to be copied by inserting a dollar sign in front of each row and column reference. This action creates absolute cell references, which prevent the formulas from changing when they are copied and pasted. Next, copy the row of edited formulas and paste the formulas to the desired location by selecting Paste, Paste Special from the Hometab, then check the box next to Transpose, and click OK.
2. Replace equal signs. A slightly faster way to copy-transpose formulas is to highlight the formulas to be copied and replace the equal sign (=) with a number sign (#) as follows. From the Home tab, select Find & Select, Replace, enter an equal sign (=) in the Find what box and enter a number sign (#) in the Replace with box. Make sure that the Within box is set to Sheet, and click the Replace All button. This action temporarily converts the formulas to text so they can be copied and pasted without changing the cell references. Next copy the row of formulas and paste-transpose the formulas to the desired location from the Home tab by selecting Paste, Paste Special, then check the box labeled Transpose, and click OK. To complete the process, highlight the pasted formulas and replace the number signs (#) with equal signs (=) by selecting Find & Select, Replace from the Home tab, enter a number sign (#) in the Find what box, enter an equal sign (=) in the Replace with box, then click the Replace All button.
3. Using the OFFSET method. My preferred option for solving this problem involves using the more advanced OFFSET function, as follows: In the cell where you want your vertical formulas to start, write a formula using the OFFSET function as pictured below.
In the OFFSET formula =OFFSET($A$1,3,ROW(B1)), the first criteria ($A$1) references the top-left corner of the data range; the second criteria references the number of rows down from the top row where the data to be copied resides; and the third criteria references the number of columns across from the first column where the data to be copied resides.
To complete the process, copy the newly created formula down the appropriate number of rows. As the formula is copied downward, it references cells across the page. (Note that inserting the phrase ROW(B1) as the third criteria instead of the number “1” allows the formula to be copied down vertically while referencing horizontal data across the worksheet.)
More from the JofA:
