If you work in FP&A or financial analysis long enough, you will inevitably encounter this problem:

The data looks organized — but it is difficult to analyze properly.

When collaborating with stakeholders, particularly those outside of finance, most datasets are delivered in a pivoted format. Months are spread across columns, while categories such as GL accounts, products, or departments sit in rows.

This is understandable. Pivoted layouts are visually intuitive and easier to digest, especially for those without a strong finance or data background.

It looks clean.

However, it is structurally inefficient for analysis.

A Real-World Example

I once worked with a consultant from Deloitte who was building a five-year forecast model at the request of our CFO.

There was one challenge.

Our company operated multiple subsidiaries, and the default ERP report exported general ledger accounts in rows and subsidiaries in columns. The consultants needed the data structured entirely in rows in order to properly process actuals within their financial model.

Sort of how the data looks like

One of my colleagues ended up spending an entire evening manually restructuring the data.

The task could have been completed in minutes.

The solution was a function that sits quietly inside Excel — largely unnoticed by many users.

That function is UnPivot, located within Excel’s Power Query environment.

Although it is not a native worksheet formula, UnPivot has become one of the most powerful tools available for structuring data efficiently.

The Core Problem with Pivoted Data

Pivoted data is inherently two-dimensional.

To analyze it, you must reference both:

• The row dimension (e.g., product, department, account)

• The column dimension (e.g., month, year, subsidiary, scenario)

For example, consider a general ledger report broken out by GL account in rows and periods across columns.

At first glance, this structure appears logical. However, when you attempt to:

• Calculate dynamic totals

• Apply conditional filters

• Build dashboards

• Perform trend analysis

• Combine the dataset with other tables

you quickly encounter structural friction.

The issue lies in how core variables are spread horizontally across multiple columns. Periods, subsidiaries, or scenarios — which should ideally exist as values within a single field — are instead treated as separate fields.

As a result:

• Any new period requires formula adjustments

• Filtering becomes cumbersome

• Aggregations require multiple conditional references

• Automation becomes fragile

You are forced to analyze across both rows and columns simultaneously.

This complexity makes scalable automation significantly harder.

Why UnPivot Changes Everything

When you apply the UnPivot transformation in Power Query, the dataset is normalized into a row-based structure.

Instead of multiple period columns, you now have:

• A single Period column

• A single Value column

The structure becomes one-dimensional and scalable.

This transformation dramatically simplifies analysis.

You can now:

• Filter by period using a single field

• Group and aggregate dynamically

• Append new months without modifying formulas

• Build Pivot Tables and dashboards that refresh seamlessly

• Combine datasets without structural conflict

UnPivot converts column-based conditions into row-based conditions.

And row-based data is significantly easier to automate, filter, and scale.

How to UnPivot

1. Select your data set, and convert it into a table

2. Go to the Data tab, and press “From Table/Range”.

a. This will open the Power Query UI.

3. Select the columns/fields that you want to convert to rows

4. Under the Transform tab, click UnPivot

Why This Matters in FP&A

In FP&A, data rarely arrives in an ideal analytical structure.

Operational teams often provide reports in pivoted layouts because they are easier to read visually. However, visually friendly does not mean analytically efficient.

UnPivot allows you to restructure the dataset once — and then build scalable logic on top of it.

Instead of writing complex SUMIFS formulas across twelve month columns, you can:

• Filter by a single Period field

• Use consistent aggregation logic

• Build cleaner dashboards

• Centralize reporting models

Most importantly, when new periods are added, your model does not break.

From an automation perspective, UnPivot eliminates repetitive structural adjustments and reduces long-term maintenance risk.

The Bigger Lesson

The true power of UnPivot is not the feature itself — it is the mindset behind it.

Strong analysts do not simply analyze numbers. They design data structures that make analysis scalable.

Pivoted data may look organized.

UnPivoted data is optimized.

Once you begin thinking in terms of normalized, row-based datasets, your analysis becomes:

• More flexible

• More automated

• More resilient

And in FP&A, resilience in data structure is what allows you to spend less time fixing files — and more time driving insight.