Pivot Charts

Resolved · Low Priority · Version 365

Thu 16 Jan 2020, 16:04 Delegate Adam said...

Adam has attended:
Excel Intermediate course

Pivot Charts

Is it possible to display a threshold line within a Pivot Chart automatically (ie without having to draw a line within the chart)

Thu 16 Jan 2020, 16:53 Trainer Jens said...

RE: Pivot Charts

Hi Adam,

Thank you for the forum question.

The only line you automatically can add to a Pivot chart is a trend line. If you want a threshold line the threshold values must be in your Pivot Table.

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

Thu 16 Jan 2020, 17:15 Delegate Adam said...

RE: Pivot Charts

Hi Jens - thanks for the clarification.
I don't think a 'trendline' is what i was after since wouldn't this alter depending on the data and what I wanted to display was a static line against which the data related bars/columns would be either above or below.

I'll try playing around with add a static 'threshold' value to my pivot-table but i'm not sure how i'd indicate to the pivot-chart that this one item of data is a static line whilst the other items are variable columns/bars.

Kind regards
Adam

Thu 16 Jan 2020, 17:39 Trainer Jens said...

RE: Pivot Charts

Hi Adam,

If you have the threshold value in your Pivot Table and create a column chart, you will get a column for each series. A column for your other value and a column for your threshold value. If you right click on one of the columns and click change chart type and scroll down in the dialog box which appear, you can change the threshold series to a line chart.

This will give you the line in your column chart.

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:

Hide data in Excel Worksheets

Let's say you have some data in cell 'C5' you would like to hide from the casual viewer.

Click cell 'C5' to select it.

Click the 'Format' menu, select 'Cells'. When the 'Format Cells' dialogue box opens, click the 'Numbers' tab (if necessary), then select 'Custom' from the 'Category' list.

Double-click the 'Type' entry box and type three semi-colons: ";;;"

Click 'OK' to close the dialogue box and accept the new formatting.

The data in cell 'C5' disappears. It's still there and will work in calculations, but it isn't visible.

If you need to check the data, just click the blank cell and the contents appear in the 'Formula bar'.

View all Excel hints and tips