Explanation
The TRANSPOSE function is fully automatic and can transpose cells vertical to horizontal, and vice versa. The only requirement is that there be a one to one relationship between source and target cells.
In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a table that is 7 columns by 2 rows (14 cells).
Note that this function creates a dynamic link between the source and target. Any change in to data in the source table will be reflected in the target table.
One-off conversion with Paste Special
If you simply need to do a one-time conversion, and don’t need dynamic links, you can use Paste Special. Select the source data, copy, then use Paste Special > Transpose.
Explanation
This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:
type,G$4
where type is the named range D5:D14, and G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down.
To sum by group, the range/criteria pair is:
group,$F5
where group is the named range B5:B14, and F5 is a mixed reference with the column locked in order to match group names in column F when the formula is copied across.
Variance formulas
To calculate a variance amount in column I, the formula simply subtracts the forecast from the actual:
=G5-H5
The variance percentage formula in column J is:
=(G5-H5)/H5
with percentage number format applied.
Notes
- The data shown here would work well in an Excel Table , which would automatically expand to include new data. We are using named ranges here to keep the formulas as simple as possible.
- Pivot tables can also be used to calculate variance. Formulas provide more flexibility and control at the cost of more complexity.
