Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

You can pivot a column in a table by aggregating matching values in a column to create a new table orientation. The table is first sorted in ascending order by the values in the first column.  

Visualizing the concept of pivoting

When you pivot, you take two original columns and create a new attribute-value pair that represents an intersection point of the new columns:

  • Attributes Identical values from one original attributes column (in green on the left) are pivoted into several new columns (in green on the right).

  • Values from the original unpivoted column (in blue on the left) are distributed as values to the corresponding new columns (in blue on the right). 

Here is the sample data used in this procedure.

Pivot Column Result

  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 column that you want to pivot. In the example, select Date. This column's unique values become the new columns and column headers.

  3. Select Transform > Pivot Column

  4. In the Pivot Column dialog box, in the Values Column list, select Amt.

  5. Select Advanced options, and then select an Aggregate Value Function. In the example, select Sum The available options are: Don't aggregate, Count (all), Count (not blank), Minimum, Maximum, Median, Sum, and AverageTip    If you don’t want to count a cell without a value, select Count (not blank). For example, if there ten cells in a column, and two are blank, the count is eight.

  6. Select OK.

Result

The  Date column from the original table is pivoted to get the sum of all values from the original Amt column at the intersection of Country and each new Date column.

Pivot Column Example

Here is the sample data used in this procedure.

Data example before pivoting with no aggregation

You can pivot columns without aggregation when you're working with columns that can't be aggregated or you don't want to aggregate the values.

  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 column that you want to pivot. In the example, select Position. This column's unique values become the new columns and column headers.

  3. Select Transform > Pivot Column

  4. In the Pivot Column dialog box, in the Values Column list, select Product.

  5. Select Advanced options, and then select an Aggregate Value Function. In the example, select Don't aggregate

  6. Select OK.

Result

Data example after pivoting with no aggregation

The  Position column from the original table is pivoted to get values from the original Product column at the intersection of Country and each new Position column.

In this example, we are using the example from the section, "Pivot and aggregate a column":

Pivot Column Result

If you pivot the Date column, select Amt in the Values Column list, and then in the Advanced options,  select Don't aggregate for an Aggregate Value Function, you get the following error message:

"Expression.Error: There were too many elements in the enumeration to complete the operation."

This error occurs because the Don't aggregate option is designed to work with a single value returned for the intersection of Country and Date, and not multiple values.

You may not see this error right away. When you originally defined it, the conditions of the data created a single value at the intersection point. Later on after a future refresh operation, the conditions of the data changed and now multiple values are possible at the intersection point. If this occurs, you can use the Pivot command with an aggregation.

See Also

Power Query for Excel Help

Unpivot columns (Power Query)

Pivot 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.