Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper Contributor

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. 

Maggi685_0-1696327803713.png

(Image 2)

Maggi685_1-1696327941994.png

(Image 3)

Maggi685_2-1696328277732.png

 

 

1 Reply

@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 data, the bar colors will automatically adjust, maintaining consistency without manual changes.