Menu
Again I'm wrestling with Excel 2007! I have some data I'm using to generate a Pivot Table and I'd like to divide all of the data by 1000...
I know I could go in and change the original data by hand but I'd much rather have a formula in the Pivot table itself that does this for me.
Any thoughts?
ps: You guys are awesome!
458
In PivotTables, you can use summary functions in value fields to combine values from the underlying source data. If summary functions and custom calculations do not provide the results that you want, you can create your own formulas in calculated fields and calculated items.
In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. If your pivot table layout won't change, another workaround is to calculate the Subtotals and Totals, outside of the pivot table, in columns to the right.
You cannot create a calculated field or a calculated item in a PivotTable based on OLAP source data. When you first create the Pivot table and it asks you where to put it, notice a box at the bottom called Add this data to data model. If it is checked then it seems Group by and Calculated fields get disabled.
Create your pivot table, then in the Pivot Table options select Formulas/Insert Calculated Field. Assuming your data field is named MonthlySum then the formulat would be =MonthlySum/1000. Change the name of the Calculated Field as desired. The newly named calculated field will now be available to add to the values in your Pivot Table. As a side note you cannot use calculated fields as row labels.
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
