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
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