Transposition errors in Excel

BY J. CARLTON COLLINS

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:

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: Economy and health care changes top CPAs’ list

CPA decision-makers’ economic outlook and the House Republicans’ proposed tax changes as part of replacing the Patient Protection and Affordable Care Act received attention recently. See how much you know with this short quiz.