Forum Discussion

Maggi685's avatar
Maggi685
Copper Contributor
Oct 03, 2023

How to autmatically change the bar color for certain chart bar without manually change

I need some help, I have already tried to figure it out by myself for many days, but still, it does not work. I have the below table (Image 1), and I want to make the bar charts (like waterfall chart), but the chart only includes the items when their value is not "0". I built up the formula behind it, and it works all ok (see image 2). I also made the bar colors as specific colors as the items in the table. But when I gives some data for T8 for example, the chart will inlcude the addtional bar for T8, which is good. But the color also change corresponsdingly (Image 3). I would like to keep the T1~T10 in the green color, and 'Pay' bar in green-blue color, and 'Profit' with the dark blue one as the image 2 bar colors. I would like to bar colors change automatically without manual change.  Really appreciate if anyone can help me to solve this challenge. Thank you. 

(Image 2)

(Image 3)

 

 

excel
Formulas and Functions
Macros and VBA

2 Replies

  • ScottJBrown's avatar
    ScottJBrown
    Copper Contributor
    Dec 20, 2024

    Hi Maggi685 

     

    I understand your issue. Using a custom legend is an easy solution to display each data point in the legend. You can then set the color once in the chart. I used a https://chartexpo.com/utmAction/NCtNVEMreGwrUUorTVNPRlRDTVQr for this purpose, and I hope you find it helpful.

     

    You can find a detailed https://www.youtube.com/watch?v=efMDkWUVnEg.

     

     

     

    Thanks,

  • mrjohnsmith's avatar
    mrjohnsmith
    Copper Contributor
    Oct 03, 2023

    Maggi685 

    To automatically change bar colors in a chart based on conditions, use conditional formatting in spreadsheet software like Excel or Google Sheets.

     

    Select the data series and create a rule with a formula that triggers a color change when a condition is met (e.g., =A1=0 for values equal to 0). Set a specific color for bars meeting this condition. For bars not meeting the condition, define your desired default color (e.g., green).

     

    Ensure the conditional formatting rule applies universally, including new data. This way, as you update your chart with different https://lunchtimeresults.com/, the bar colors will automatically adjust, maintaining consistency without manual changes.

Resources