Pivot Table - adding a percentage column

Resolved · High Priority · Version 2016

Mon 8 Feb 2021, 17:01 Delegate Jane said...

Jane has attended:
Excel Advanced - For Power Users course
Power BI Reporting course
Office 365 End User course

Pivot Table - adding a percentage column

How do we add a column in our Pivot Table that would calculate the percentage variance between the two columns to the left. i.e.

Actuals Budget % Var

Mon 8 Feb 2021, 19:20 Trainer Jens said...

RE: Pivot Table - adding a percentage column

Hi Jane,

Thank you for the forum question.

You will need to create a Calculated Field.

On the Pivot Table Analyse tab click Fields, Items, & Sets click Calculated Field. Give the new field a name and delete what you have in the Formula box. Under the formula box you will find all the fields you have in the Pivot Table. Double click on Actuals field name type / and double click Budget from the field list. Click insert field.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 9 Feb 2021, 15:59 Delegate Jane said...

RE: Pivot Table - adding a percentage column

Hi Jens

Thank you for your fast response. However the "calculated Field" option is greyed out and can't be selected. Do you know why this is happening?

Kind regards
Jane

Tue 9 Feb 2021, 16:10 Trainer Jens said...

RE: Pivot Table - adding a percentage column

Hi Jane,

It can be different issues.

Do you have the data stored in the Excel Data model? or do you have the data in an external source?

If you have the source data stored in a worksheet in the same workbook as your PivotTable, you should be able to create calculated fields.

I am happy to take a look at your file, if you want me to.

You can send the file to:

info@stl-training.co.uk

The link below is to Microsoft's website. It just explain how to great calculated fields.

https://support.microsoft.com/en-us/office/calculate-values-in-a-pivottable-11f41417-da80-435c-a5c6-b0185e59da77?ui=en-us& ;rs=en-us&ad=us

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Multiple Lines of Text in a Cell

As an alternative to the Text Wrapping facility, type a word or two, press Alt+Enter to get a new line, type more text, and continue the process for as many lines as you need. Enter as normal when you have finished.

The line break is not affected by changing the column width, as text wrapping. To remove this you must edit the cell and remove the invisible character and replace with a normal space.

View all Excel hints and tips