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

Chart Formatting Changes When Using Slicer

A

Aubade

New Member
Hi All


I created a dynamic report with several charts all connected to a single slicer in Excel 2010. The slicer, "Agency", contains about 60 agencies, so when users click on it the charts automatically update with the data just for that particular agency.


For some reason when I create a chart and format it the way I want, when I click on another agency using the slicer, the chart automatically changes back to Excel's default formatting. I've tried creating the chart with no filter applied, using a specific template as the chart selection, and also setting it as the default chart but the problem still occurs.


I really do not want to have to change the chart type to my custom template 60 times in a row for each chart.


Does anyone know how I can make Excel keep my chart formatting for all slicer selections that doesn't require a macro?


Thanks in advance for your assistance!
 
Good day Aubade


All the charts are linked to one slicer so will show the default, you could create your own slicer style but this will update all charts to the one style. The only way around this is to have a slicer for each chart but with over 60 charts rather you than me
 
Hi bobhc, thanks for the reply.


However, maybe I wasn't clear in my original post. There are actually only 4 charts, but 60 agencies within the one slicer. The problem is happening within each individual chart.


In other words, I set each pie chart with the colors I want and make the data labels show numbers and percentages in the font size I like. But then when I click a new agency with the slicer, the charts all go back to the default colors and small number only data labels.


All four charts have the same style. The problem is each one changes back to a different style when I click a new agency with the slicer.


I hope this makes more sense.
 
Aubade


Do you have a Dropbox account, if not can you open one and then upload your file, there are other upload sites but I do not visit those that require a login or make you wait while they show their advertisements or make you install some obscure software, dropbox is free and no hassle
 
No, I don't think I am allowed to do that since it is confidential data for state funded school programs. Each program is located in a different school. The slicer is so that program administrators can send easily send a snapshot report to each individual program.


Maybe it will help to say the Agency slicer is linked to four charts:

-Participants' Race/Ethnicity

-Percent of Participants by Service Type

-Percent of Participants by Service Category

-Average Monthly Attendance


When I click on Agency (School) A, my charts look how I want them. Then when i click on Agency b, they go back to the default instead of my format.
 
Aubade


I have put together a pivot table with three charts running of multi slicers (put it together rather quickly so no work of art) if I select a data table on the left hand side and then alter/filter a slicer then only the chart associated with that data table alters/updates, probably not what you are trying to get over but if you have a look in my Dropbox it may help. if I slicer one table the charts of the others do not change.


https://dl.dropbox.com/u/75495784/Pivot_Table.xlsx
 
Ok mine doesn't look like that at all. I made a simplified example to show you but unfortunately drop box is a blocked site in my office. (practically everything is blocked here!)


I will upload it when I get home tonight.
 
Aubade


By the time you get home I will be in the land of nod. Will have a look in the morning.
 
No problem. Once I hit the upload button I'll be running around with my 2 year old anyway, so I won't look at it again until morning either. have a good night!
 
OK here it is in google docs. If you download it then you can open it in Excel 2010. Hopefully that's ok.


https://docs.google.com/file/d/0ByNBbSD3NjstWkFBRHItbTdBV3c/edit?usp=sharing
 
Good day Aubade


I have downloaded your file and I now see what your problem is, not sure if I have an answer but give me some time.
 
Aubade


From what I have managed to find out pivot tables save all information for the table in one dedicated area of Excel (hidden) so all items are linked, that is why you need to make copies of data and attache slicers to them…..but there is away, if you click on the slicer and then choose the Option tab that has apeared and in the slicer section choose PivotTable Connections you can from there choose to what the slicer is connected to, I have tried it out on your spread sheet and it does as it says but not sure if this will be of help to you.


You may find this link of help !!


http://www.accountingweb.co.uk/article/excel-2010-kpi-tutorial-3-pivottable-dashboards/520772
 
Thanks. I did use PivotTable Connections to connect all of my charts to the Agency slicer, so they all change together to show the data for each individual agency when I click on the slicer. So that part is working fine.


My only problem is that when you click on Agency 1, the chart looks how I want it to - blue & orange with big bold data labels. However, when you click on any of the other Agencies 2 - 10 in the slicer, the charts revert back to the default format with blue/red and tiny data labels.


I've searched all over on the web and haven't found an answer. I'm surprised other people haven't run into this problem!
 
Good day (morning)Aubade


I changed the style of the charts and no matter what Angency you choose the style remains the same in both charts, please see if the Dropbox files is what you are trying to achive.


https://dl.dropbox.com/u/34893656/Slicer_Example.xlsx


https://dl.dropbox.com/u/34893656/Slicer_Example%202.xlsx
 
Well it is closer - on yours the colors stay the same but I notice that the data labels are % for Agency 1 but not the other agencies.


Do you know what you did differently to keep the colors the same so I could reproduce it?
 
Aubade


The data labels are as upload but it is easy enough to change these.

Click on a chart and from the tabs that are now there choose design.


I have changed some chart colours/styles to show that they are still independent of each other.


https://dl.dropbox.com/u/75495784/Slicer_Example%203.xlsx
 
Thank you so much for continuing to help me with this problem!


However, I think we just may not be communicating well.


I don't want the charts to be independent of each other.


I want them all to have the same style, and that style to stay the same every time I click on a different Agency.


I don't want to have to click each chart ten times for each agency and apply my style. I have 60 agencies in my real dataset, and 4 charts, so that means I'd have to click 240 times.


I have a template saved with my chart style. When I create the charts, I choose that template. It looks fine for Agency 1. But then when I click on the other 9 agencies, it looks different. I just want it to stay as the chart template I originally created for all 10 agencies without clicking on each one individually.


Does that make sense?
 
Aubade


Yes it seems we have crossed wires. The first of today's uploads (https://dl.dropbox.com/u/34893656/Slicer_Example.xlsx) has the same chart styles that do not change when you choose different agency's, you do not need to click on the charts 240 times. Just make your four charts and then apply the same style to them all. Click on a chart and from the tabs that are now there choose design.

I made the other charts to show that whether the charts were the same or individual styles they do not change style as you go through the different agency's on the slicer,which is your original problem. Make your charts with the pivot table for all 60 agencies before you apply the styles. Then save as template.
 
*sigh*

I don't know what I'm doing wrong. I tried what you said and it just won't work for me.


I click on a chart to highlight it, go to the design tab, click Change Chart Type, select my custom template. That individual agency's chart looks fine, but then again, if I click on a different agency it doesn't look like my template.


Excel hates me.
 
Aubade


There must be some thing wrong with the template, as you can see from the workbooks I uploaded when you choose any agencies the charts do not change their formatting. If it was me I would make a new template as I described above...But that's just me but with all the trouble you are having with the templates that is what I would do in your shoes.


Excel hates everybody...that's why we have forums such as Chandoo :)
 
HOOOOOOOOOOORAAAAAAAAYYYYYYYYYY


I think I finally figured it out. The problem was that I had Agency as the column or row labels in my pivot tables, so the charts had each agency as its own series. You can only format the data labels for one series at a time. So this is why I had to reformat the labels for each and every agency.


However, I just discovered that I can remove Agency from the pivot tables themselves, and only connect the Agency Slicer to the charts. That way the data is only one series, but the slicer works to display only the data for each agency, so whatever formatting I apply works for everything. YES!


Thanks for all your help bobhc!!
 
Aubade


I would have liked to have been a fly on the wall to watch you face become covered in on big smile. Pleased YOU figured it out.
 
I had exactly the same formatting issue while using slicers. Thanks to this thread I was able to get around it!!!!
 
Aubade said:
HOOOOOOOOOOORAAAAAAAAYYYYYYYYYY


I think I finally figured it out. The problem was that I had Agency as the column or row labels in my pivot tables, so the charts had each agency as its own series. You can only format the data labels for one series at a time. So this is why I had to reformat the labels for each and every agency.


However, I just discovered that I can remove Agency from the pivot tables themselves, and only connect the Agency Slicer to the charts. That way the data is only one series, but the slicer works to display only the data for each agency, so whatever formatting I apply works for everything. YES!


Thanks for all your help bobhc!!
Click to expand...


Can we expand on this solution? I am having the same problem. My pivot-chart shows "Planned Expenses" by stacked line graph and "Actual Expenses" by stacked bar graph. I have a slicer that breaks down the type of expenses to labor, travel, training etc... but it resets all my data to stacked bar graph whenever I click one.
 
@Mahonei
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.
 
You must log in or register to reply here.
Back
Top