PowerShell Out-ExcelPivotTable – Quickly Create Pivot Tables in Microsoft Excel

The Data – People.csv

Use Import-Csv and type the data with Select

 

Create the Excel Pivot Table

$people | .\Out-ExcelPivotTable

 

The easiest way to get a Pivot Table.

Out-ExcelPivotTable inspects the piped data, sets up the numeric properties as data fields and string properties as row fields (nested).

 

 

$people | .\Out-ExcelPivotTable name dept salary

 

Same data.

You control what goes in the different pivot fields. Row (name), Column (dept) and Data (salary).

 

 

 

$people | .\Out-ExcelPivotTable -values YearsEmployeed

 

You control the pivot data field with the –values parameter letting  Out-ExcelPivotTable do the rest.

Name is nested in Department.

 

 

 

 

ToDo

• Set up Out-ExcelPivotTable parameters to take arrays
• Formatting of value fields
• Naming of Row and Column Labels
• Enable Types of aggregates for Value field
• Improve data layout performance

I have only tried this on the latest version of Excel.

Download It