6

I have an Excel bar chart that is formatted exactly the way I like it. The bar colors, the label fonts, the grid lines, etc, etc.

Here is an example:

enter image description here

Notice the awesome formatting of the second series. Now, I want to update the chart so that its second series points to the updated numbers. I do this by clicking on the corresponding bars, and dragging the blue and red rectangles onto their correct new ranges. Here's what happens:

enter image description here

Notice how the bars changed their fill to orange! Notice how the labels got removed! Where's that green stroke and awesome 3D bevel effect? All my hard work! For nothing!

Is there a way I can stop this from happening? I just want to change the values that are shown, not the way in which they are shown.

Just a few remarks:

  • I know that, in this case, I could add a row which has a formula to pick out the correct values (e.g. based on the value in some 'controller' cell), and have the series point to that row. In that case the series would always be showing the same cell range, and its formatting wouldn't change. For reasons I won't go into here, this is not an option in my actual sheet.

  • I also know I could create a template for this graph and reapply it whenever I make a change. I also don't want to go this route, because it's cumbersome, especially because I have many of these charts, each with its own unique formatting.

Many thanks!


edit

Solution:

As suggested by @ErikF, this page shows how it can be done, i.e., by clicking File > Options > Advanced > Chart > deselect both 'Properties follow chart data point for current workbook' and 'Properties follow chart data point for all new workbooks'

  • If you change the now orange bars back to blue and then repeat trying to update, do they change orange again? Also how are you changing the bar color when you do this? – Eric F Apr 15 '19 at 12:26
  • If I change the formatting for March from orange to a new cool design, and then move onto a new row again (let's say I added an April row), the formatting gets reset to orange again. Curiously, it seems like each row stores/preserves its own formatting, because moving the range back to Feb reinstates the blue dotted green 3d bordered formatting, even if for March I picked a different one. – ElRudi Apr 15 '19 at 12:36
  • 1
    This is a very good question.. Try looking here techcommunity.microsoft.com/t5/Excel/… and try what is suggested at the bottom. File > Options > Advanced > Chart > deselect 'Properties follow chart data point for current workbook' deselect 'Properties follow chart data point for all new workbooks' – Eric F Apr 15 '19 at 12:48
  • Otherwise others have suggested if this doesn't work then templates are the only alternative – Eric F Apr 15 '19 at 12:53
  • force the data to change not the place the series points to as the location, use a subset for the chart, and an option button to specify which set of data the subset should pull from. i.e if option button 1 is selected index from a specific location, if option button2 is selected, look elsewhere – PeterH Apr 15 '19 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.