Problem with Excel Pivot chart formatting

Abzy123

New Member
Joined
Oct 27, 2017
Messages
1
Hi,

I am hoping someone can help me with a problem that I am having with formatting on a 2 pivot charts that I have set up.

I have set up 2 pivot tables that are showing trend data over a whole year for 15 different areas. I have used the slicer to flick through the different areas. The chart that I have set up is a Clustered column and a line Combo chart.

Problem number 1:

The first problem I am having is that I am measuring 4 factors in relation to these 15 areas and not all areas are applicable to all the areas. Therefore, for one factor one area has no data entered in. When I use the slicer to select that particular area and when I click on another area the whole chart loses the formatting and reverts back to the original formatting. I have to manually select change chart type again. Does anybody know why this is happening and can suggest a solution? I have tried inputting dummy data for that area and the problem disappears.

Problem number 2:

The second problem that I am having is that when I select multiple areas using the slicer it shows the data altogether i.e there is only one column for both areas and one line for both areas. I think it is adding the values together when I don't want it to do that. I can manually change the formatting to make it show the data in the way that I want however, once I click away and select only one area the formatting reverts back to its original setting and loses the combo chart. Can anyone suggest a solution whereby the charts retain the formatting that I want regardless of which areas I choose using the slicer?

If anyone has any solutions I would be eternally grateful.

I am using Excel 2016

Many thanks

Abdullah
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top