Using the Original Replacement Hack
Our sample data is arranged in rows, but we need it to be arranged in columns like a traditional table.
The idea was to write the formulas as traditional cell-to-cell links (i.e. =B2) but use a bit of fixed text in place of the equals sign, such as “lg” – or your initials. (After we fill it down we would replace the “lg” with an equals sign to put everything back the way Excel expects it for proper cell-to-cell links.)
After we create a pattern for Excel to follow, we fill the pattern down.
The number sequences would continue when filling in a left-to-right direction, but it does not work when filling in a downward direction.
This is because when transposing from vertical to horizontal, we need the numbers in the cell references to change, and Excel is smart enough to figure that out.
When filling in a downward direction, we need the letters that reference the columns to change. Excel cannot distinguish our placeholder text (“lg”) from the column letter reference so it just repeats the original set of references.
Enter the Better Hack
When researching this problem, I came across a method by 25-year Excel MVP Bob Umlas.
This hack requires we change one of the Excel program options.
Select File (tab) to enter the Backstage. Select Options – Formulas – Working with formulas and place a check in the option labeled R1C1 reference style.
Take a look at the column headings. Everything is using numbers.
Select an empty cell (such as D8) to point to cell B2. Enter the following formula.
=R2C2
Notice that the result is the same as it was when we entered =B2. This notation is telling Excel to point to the cell located in Row 2 and Column 2.
Another way of creating the cell-to-cell reference is to type an equals sign and click cell B2 with the mouse.
This will produce the following formula.
=R[-6]C[-2]Think of this a relative reference; we’re telling Excel to travel back 6 rows and back 2 columns in the spreadsheet (when entered in cell D8).
Implementing the “LG” Trick
Now we’ll utilize the trick where we enter some placeholder text in lieu of the equals sign.
Below the APP title, enter the following formula.
lgR2C2Below the SALES title, enter the following formula.
=lgR3C2
Select both formula cells and pull the Fill Series handle down the columns.
Excel updates the column reference numbers in sequence.
We will now select our newly created placeholder data cells and perform a Find/Replace operation; searching for all instances of “lg” and replacing them with equals signs.
Take a look at our updated column list.
Set Excel Options Back to Normal
Unless you have more lists to transpose, it’s a good idea to reset the Excel option in charge of notation style. Select File (tab) to enter the Backstage. Select Options – Formulas – Working with formulas and uncheck the R1C1 reference style option.
Notice that our cell-to-cell reference formulas are displayed as traditional absolute references.
Hiding the Zeroes
If you created formulas to accommodate longer lists, you are most likely presented with several zeroes at the bottom of your transposed data.
You can hide these zeroes many ways with creative formulas, but a simple technique is through a custom number format.
Select the output of the transpose operation, right-click on the highlighted area and select Format Cells…
In the Number Formatting dialog box, select the Custom category on the right and enter a custom number format that would hide the zeroes.
Because we are using large numbers, we wish to display commas every third place position and we do not wish to display any fractional values. Enter the following custom number format.
#,##0;-#,##0;
Now the zeroes will be hidden, but additions to the original horizontal list will appear in the transposed vertical list.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
