Format column on PivotTable Sheet & correctly set column range for PivotTable

[MarianiVentura]

Hello,

Would you be able to help me with two things.

Consider my code:

$excel = Import-Csv $output_file -Delimiter ^ | Export-Excel $excel_file -AutoSize -AutoNameRange -TableName Items -FreezePane 3 -Passthru `
-IncludePivotTable  -pivotrows Days -PivotData @{BOvalue='sum'} -IncludePivotChart  -ChartType Pie  -ShowPercent -show
$sheet = $excel.Workbook.Worksheets["Sheet1"]
$sheet.Column(8) | Set-ExcelRange -NFormat "$#,###.##"
$sheet.Column(9) | Set-ExcelRange -NFormat "$#,###.##"
$sheet.Column(12) | Set-ExcelRange -NFormat "$###,###,###.##"
$sheet.Column(13) | Set-ExcelRange -NFormat "$###,###,###.##"
$sheet.Column(14) | Set-ExcelRange -NFormat "$###,###,###.##"
#$sheet2 = $excel.Workbook.Worksheets["Sheet1PivotTable"]
#$sheet2.Column(2) | Set-ExcelRange -NFormat "$###,###,###.##"
Close-ExcelPackage -ExcelPackage $excel 

I would like to learn how to set a range for my pivot table that only includes a range. I have a summary formula at the top of the "BOvalue" column and I do not want to include the summary row in the pivot table data.

Also, I successfully formatted some columns on Sheet1 to format as numbers with commas and periods, but I wasn't able to do this for the second column on my new "Sheet1PivotTable" sheet. The two lines are currently remarked out in my code.

Thanks for any assistance, much appreciated!

M.

[dfinke]

pls could you post the data your are using and the xlsx of how you would like it to look

[MarianiVentura]

Absolutely! Thanks for the assistance.

[MarianiVentura]

Good day. Here is my ExcelFile

I had to manually add the totals on row 1. If I don't, they become part of the table and the pivot table calculations.
Is there a way in Powershell to insert this line? Or specify a range for the table?
Also, How do I format the values in my pivot table? We want to show the commas at least and it would be nice to have $. I can do this on columns on the main sheet, but they are not formatted on the pivot table.
Thanks!