+ Reply to Thread
Results 1 to 9 of 9

Consistent formatting for dynamic charts

  1. #1
    Registered User
    Join Date
    12-22-2014
    Location
    Geneva
    MS-Off Ver
    Excel 2013
    Posts
    5

    Consistent formatting for dynamic charts

    Hi all,

    I have built a dynamic stacked bar chart using named named ranges, with data that changes depending on a country that the user selects. I have modified the formatting of my graph (I changed the color of one of the bars, and used a pattern fill).

    My problem is that when the graph dynamically changes (when the user selects another country from a drop down list), the formatting goes back to the original one (color and fill).

    Is there a way to change the default formatting so that it remains consistent when the data changes dynamically?

    I hope I was clear. Any help appreciated.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Consistent formatting for dynamic charts

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Glenn



  3. #3
    Registered User
    Join Date
    12-22-2014
    Location
    Geneva
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Consistent formatting for dynamic charts

    Hi,

    I have attached a sample workbook. You can change the country from Alpha to Bravo in the BEFORE sheet, and you will see that the formatting of one of the columns changes from orange to grey.

    I couldn't create an AFTER sheet with the problem fixed, because when I change it manually, close and reopen the sheet, the problem appears again.

    Tell me if you still miss information.

    Thanks for the help.
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,863

    Re: Consistent formatting for dynamic charts

    Looks like a bug, where the chart remembers the formatting applied to the different ranges, even if the series formula is a named range.

    To get around the problem you will need to format the series for each choice.

    EDIT: I did a bit more of a test with a new chart.
    So it looks like the chart will use the Chart Styles formatting for all series. If you format the series fill format this is only used on series it was applied to, even if the chart only contains 1 series but the data range changes. So in xl2013 it would appear the formatting of a named range can be different for each range referenced *IF* you change parts of the standard formatting.
    Last edited by Andy Pope; 12-22-2014 at 07:15 AM.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    12-22-2014
    Location
    Geneva
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Consistent formatting for dynamic charts

    I tried that, but it will only work until I close and reopen the workbook. After that, the formatting will still be correct for whatever country I had selected when I saved, but will go back to grey when I change the country.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,863

    Re: Consistent formatting for dynamic charts

    Yep, I see that now.

    You will have to choose a colour combination from the default selection in order to keep the formatting for both bars.

  7. #7
    Registered User
    Join Date
    12-22-2014
    Location
    Geneva
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Consistent formatting for dynamic charts

    Not an option, unfortunately. there are many other graphs in the sheet, and the colors have a specific meaning. And it wouldn't solve the lack of pattern fill.

    You say it's a bug, does it behave differently normally? I've recreated another dummy graph and had the same issue, independently.

    Perhaps with a macro on open ? I wanted to avoid this solution because it makes the file heavier and slower, but it may be the only solution...

  8. #8
    Registered User
    Join Date
    12-22-2014
    Location
    Geneva
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Consistent formatting for dynamic charts

    I found a solution: if I use an intermediate range rather than a dynamic range, the chart formatting will not change when I switch the country.

    For instance, if you have a named range ("Example_Range") set as "=offset(index(A:A,match(Country,A:A,0)),0,1,0,4)"
    What I previously did was setting the series values of the chart as "='Book1.xlsx'!Example_Range",

    Instead, select a range (1x4 in my example), and set it as "=Example_Range", making sure to hit Ctrl+Shift+Enter as it is an array formula. Then choose that range as the series value.

    Not the most elegant of solution (I prefer to avoid intermediate ranges, when possible), but it works, and I can hide my intermediate range below my graph.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,863

    Re: Consistent formatting for dynamic charts

    It's a bug in the sense that excel versions prior to 2013 don't act in this way. The older versions work as expected.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 06-28-2013, 03:02 PM
  2. Dynamic charts - formatting
    By Jonno1 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-15-2013, 05:00 AM
  3. Consistent coloring among multiple pie-charts with same attributes/ segments
    By harsh2209 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2011, 04:34 AM
  4. Replies: 1
    Last Post: 10-27-2011, 03:32 AM
  5. Consistent Colors for Pivot Charts
    By zasskar in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 01-15-2008, 11:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1