Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all similar values are in one column. This is necessary, for example, to create a chart or a report.

Visualizing the concept of unpivoting

When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the new columns and re-orient them into flattened columns:

  • Values (in blue on the left) are unpivoted into a new column (in blue on the right).

  • Attributes (in green on the left) are unpivoted into a new column (in green on the right) and duplicates are correspondingly mapped to the new Values column.

Here is the sample data used in this procedure.

Pivot Column Example

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select one or more columns. In this example, select all the columns except Country. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column.

  3. Select Transform > Unpivot Columns.

Result

Pivot Column Result

 This command unpivots unselected columns. Use this command in a query when not all columns are known. New columns added during a refresh operation are also unpivoted.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the columns you don't want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column.

  3. Select Transform > Unpivot Other Columns.

Use this command when you don’t know the number of columns in the data source, and you want ensure the selected columns remain unpivoted after a refresh operation.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select the columns you do want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column.

  3. Select Transform > Unpivot Only Selected Columns.

See Also

Power Query for Excel Help

Pivot columns 

Unpivot column (docs.com)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×