Re-arrange the data from columns to rows
Transposing data in Excel is pretty straight forward. Just copy the data, goto Paste Special and check on the transpose
box found in the Paste Special window. The data is immediately re-arranged with the dimensions switched, from rows to columns
and columns to rows. But if you are planning to transpose partially so that you can use the data as a Pivot Table source,
then the solution is not as straight forward. This happens when you are collating sales forecasts, budgets or working on
payroll within Excel.
To do a partial transpose, you need to use the worksheet function INDEX.
- In row 1, label all the columns from 1 to 13 starting from column B.
- In Column A, label the rows starting from 1 in row 2. Note the colours for the labels.
- In Cell B11, enter the formula =INDEX($B$2:$P$7,2,1). The INDEX formula will pick up the value
within the specific range $B$2:$P$7, starting the row count from cell B2, as row 1 and column 1.
The value "product 1" is displayed.
- To get the location in C3, the formula is =INDEX($B$2:$P$7,2,2).
- Instead of manually entering the row and column into
the INDEX formula, you can refer the row and column
using the labels we have set up in Step 1 and 2 and in
the following steps.
- Label the rows and columns as shown below.
- The green range refers to the columns that need to
be repeated for 12 months of records. The purpose range
refers to the row to ber repeated. The pink range refers
to the columns (months) that need to be transposed.
- The 2 INDEX formulas we have created initially can
now be modified.
- The month is in row 1 column 3. So the formula is
- The amount can be picked up using the formula
- Once you have these formulas set up, you can copy
the 4 formulas down to the rest of the 11 remaining
- To repeat the whole process, enter the formula
=I11+1 into the cell I23. This will increase the row by
one unit. If you copy the formula down, the row number
will change after every 12 rows. 13. Enter the formula =
J11 into the cell J23. The formula will repeat the
column numbering from above and reset the number every
12 rows. Copy the formulas to as many rows as required
and use the data for Pivot Table.