• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting changes in pivot chart

maudrenetan

maudrenetan

New Member
Dear all,

I have been searching around for a solution to my problem, but have yet to find a suitable solution.

I have a set of data that has been collected across several groups over 6 quarters. I have calculated the average across the groups and will like to be able to compare against the average, as well as across groups. Hence, clustered columns are the preferred chart-type. However, there is a static target line that I'll like to plot onto the pivot chart to show how the groups are doing relative to the target. The problem starts here. When I try to filter to select the groups that I'm interested in, the formatting that I've initially set gets messed up.

Method 1 (tab mtd 1):
I included the target line as a column in my data set. Then I used a combo chart to select clustered columns for my groups and target line into "line" chart. However, when I tried to filter for specific groups, my formatting gets messed up.

Method 2 (tab mtd 2):
I included the target line as a "group" in my dataset. Again, I used a combo chart. However, the same formatting problem occurs.

How can I keep the "target" in line format and my groups in "clustered columns" and not have this format messed up when I select groups of my choice? Can anyone help me with this? I have attached a sample data that I had created (both mtd 1 and mtd 2) to help everyone understand how my data is like.


Many thanks.


Warmest regards,
Maudrene
 

Attachments

  • sample data.xlsx
    32.3 KB · Views: 4
maudrenetan
You wrote the formatting that I've initially set gets messed up.
What Your the formatting get messed?
What ... how should formatting keep same, if there are automatic settings selected?
Could You take some snapshots, which should keep same after filtering?
 
Dear vletm,

Thanks for the response. Sorry for being unclear in my description. I'll attempt to do a better job here.

In the chart below (1618587851708.png), where I used method 1 to obtain, I am displaying all the groups that I have in my dataset. I manually changed the chart format to be a combo ,where my % are kept as clustered columns and my targets as lines. I also saved the template.

74133

However, when I filtered to only see "Average", "gp2" and "gp4" (1618588003004.png) my target lines changed back to columns instead of the line chart. This happens as long as I change the groups that I want to view in my chart. But, I want my target to be a line instead of column1618588003004.png.



Using Method 2, the same problem happens. I started with all groups and I manually set the target to be a line chart as seen below (1618588154895.png)

74135

However, when I filtered the grouping to only select "Average", "gp2" and "gp4", my target changes from a line chart to a column (1618588244599.png)

74136

What should I do so that my target will always be a line rather than a column?

Can I find out where I can switch off the "automatic settings"?

Really hope you can advice me with how to make this work. Thanks so much!


Warmest regards,
Maudrene
 
maudrenetan
'Automatic settings' ... if You let Excel do what Excel would like to do as default.
With this sample file - I try to explain to Excel - what it should do after You have filtered pivot-table.
Do this sample work something like You wrote?
HInt: Do Filter > Click somewhere in sheet
 

Attachments

  • sample data.xlsb
    39.6 KB · Views: 19
Dear vletm,

This is exactly what I wanted.

Can you please teach me how you "explained the steps to Excel - what it should do after you have filtered pivot table"? I will like to replicate this. Thank you very much.
 
maudrenetan
I wrote two codes for those two sheets which will work after done changes with Pivot-table.
The 1st code sets those series type as line or column as You have wanted.
The 2nd code sets all as column - except 'target'-series.
You can see those code ... do right click over sheet tab and select view code.
You could ... copy & paste those codes to other file as those are now --- and do needed modifications per You other file.
Those are samples - there could be more options which could set like fixed if needed - eg colors.
 
This is amazing! Thanks so much! Appreciate your assistance greatly.

However, I'm really sorry that I have to trouble you more as I'm new to macro codes. I tried to copy and paste it into my dataset (which has 30 groups), however, the charts still keep changing. Can I check where I went wrong?

1) Before I copy and paste the code, into my worksheet, should I already have the pivot table and chart set up?
2) I believe I need to make changes to the numbers. "s" refers to the number of variables that I have in the data series? "t" represents the chart type?

What else do I need to change to allow my charts to automatically update with each filter?

Appreciate your guidance once again and thank you for your patience in guiding me through this.
 
Last edited:
Dear vletm,

I think I figured it out. It seems to work on my chart now. Thanks so much!!!!! Sincerely appreciate your guidance.


Warmest regards,
Maudrene
 
vletm said:
amiteshkapoor
It could be possible
if I could get a sample of Your file
and details - how should it work?
Click to expand...
Thanks you Please see attached sample file. You can see that second pivot chart changes to default rather than combo when I filter using slicer the Line changes to column in second chart
 

Attachments

  • Sample.xlsm
    88.8 KB · Views: 6
amiteshkapoor
... hmm?
#4 reply sample-file has one chart and Your file has ... two charts.
As well as Your file do not have any code.
Above has already two reasons - why happens as it happens - and that's normal.
Did You try to solve this Yourself at all?
 
vletm said:
amiteshkapoor
... hmm?
#4 reply sample-file has one chart and Your file has ... two charts.
As well as Your file do not have any code.
Above has already two reasons - why happens as it happens - and that's normal.
Did You try to solve this Yourself at all?
Click to expand...
I did try this with several codes i found on google but not able to make those work. The second graph keeps changing after filtering from slicer
 
amiteshkapoor
It's 99% sure that something have to modify, if tries to use other files code.
Only sometimes copy and paste will give 99% success.
... and as You've two charts ... then those both charts have to take care.
How did You try 'my code' to work with Your file?
Can You show, what have You done?
 
You must log in or register to reply here.
Back
Top