How to show positive / negative colors in area charts? [Quick tip]

editor
May 13, 2020
Articles
0 Comments

Ever wanted to make an area chart with up down colors, something like this? Then this tip is for you.

Area chart with two colors for up & down values - Excel charting trick

How to create area chart with up / down colors?

Simple. You need,

Data with positive & negative values.
optional: A cup of coffee or a cold beverage of your choice.

Start by creating a regular area chart. You will get this:

Now, select the are and go to format settings (use shortcut CTRL+1).

Go to fill color for the area and change it to gradient fill.

Set it to one of the default gradient fills, you will see a screen like this.

The last step: Setting two color gradient

We are nearly there. Take a victory sip out of that coffee cup or cold beverage. First calculate the mid point for our gradient stops. This will be =maximum up value / (maximum up value + maximum absolute down value)

For example, if your data has +5 has maximum positive value and -5 as maximum negative value, then the gradient stop will be 50% since 5 divided by (5+5) is 50%.

Take another sip and set the gradient stops as shown below.

Create 4 gradient stops (most of the default gradient settings have 4 stops)
Set stop 1 & 2 to positive color (say blue)
Stop 3 & 4 to negative color (say red)
Adjust the position of 2 & 3 to the gradient stop calculation you have done earlier.
Make sure Gradient is linear with 90 degrees

gradient stop settings for area chart with up down colors

Done. Your area chart with positive / negative colors is ready. Admire its beauty while finishing your beverage.

Watch video tutorial: Area chart with positive / negative colors

If you are not sure about the whole gradient color trick, check out this video. I explain clearly the idea with few examples (plus there is a bonus trick in there). See it below or head over to my YouTube Channel for it.

Download Example File

Please click here to download a sample file with up down colors for area charts. Play with the data, examine the chart settings to understand this better.