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:

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


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.