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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.