Power-user helps you automatically UnPivot tables to unlock the true potential of your data in Excel.

TABLE OF CONTENTS

• What is it about?
  • The problem of data organized in tables
  • Organizing data in a structured database unlocks powerful Excel features
  • UnPivotting a table 
• How to UnPivot a table using Power-user

What is it about?

The problem of data organized in tables

A tremendous number of issues with Excel actually come from the same problem: most users tend to organize their data as reports instead of databases. This is natural, as we will eventually need reports, but it makes data much more difficult to analyze.

In the example below, data has been organized as a table: there is a different column for each Year, and the Values are at the intersection of a Country (in rows) and a Year (in columns):

Now if the user wants to create a PivotTable, it's not possible as the Pivot fields will look like this:

Because each Year is in a distinct column, it's considered as a distinct field by the PivotTable. This means we cannot easily drag and drop a Year field to the table as a filter. We also cannot sum Values for all years, etc. Basically, we cannot use PivotTable on this data.

That's too bad, because PivotTables are a super-powerful tool that we definitely need for creating dynamic Excel dashboards.

Organizing data in a structured database unlocks powerful Excel features

When data is organized as a structured database, each column represents a single field, with data of the same nature. In the above example, it means that Years should be in a single column, and Values should be in another column. The same data organized as a database would look like this:

Now using this data source, we can unlock the full power of PivotTables to summarize our data in any way we want:

We can also add Slicers and Timelines for interactivity, and build entirely dynamic reports and dashboards. All of this because the data source is properly formatted as a database instead of a cross table. 

Of course there are many other situations when organizing data this way can be more efficient, as you can more easily compare or fetch data this way with formulas like XLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, etc.

UnPivotting a table 

Now that we've seen why data needs to be organized as a database, it can become clear what UnPivotting means. It means transforming the data from a table (as in the first example above) into data organized in a database.

Manually, this process if virtually impossible to accomplish. It would imply countless copying and pasting, duplicating content, etc. in order to get each column filled with data from the proper field.

This is where UnPivot comes into action.

How to UnPivot a table using Power-user

With Power-user, you can quickly transform a crosstab table into a database. This is called to UnPivot (or flatten) a table. 

• From the Power-user tab in Excel, click UnPivot table. 
• A dialog box will open, asking you to select the table to UnPivot. 
• Select the entire table, including rows and columns headers, and then validate. 
• Power-user will automatically create a new tab with the UnPivotted data. 
• You simply need to add headers to your data, such as Country, Year and Value in the above example.