In this guide, we’re going to show you how to set different colors to positive and negative numbers in area charts in Excel.
In charts, the colors and shapes are the most important elements which can express the differences between data series. Unfortunately, Excel's area charts don't have a dedicated option for setting different colors to positive and negative numbers in area charts similar to column/bar charts: How to set different colors to positive and negative in column or bar chart in Excel
However, we can use Gradient fill feature to display two colors for the same data series. The Gradient fill option allows you to select and blend two or more colors to give a visual effect to your Excel objects. With a couple of tricks, we can make the blending sharp at the x-axis to mimic the effect of setting different colors to positive and negative values.
Steps
- Start by creating an area chart.
- Next you need to add some formulas to find the correct point where the color change occurs. Find the minimum and maximum values by using MIN and MAX formulas.
- Next step is to divide the maximum value by the total difference. In our case the difference between maximum and minimum values is 15 (7 - (-8)).
The result will give us the percentage of the above portion which is also where the color will be changed.
- Double-click on the data series or right-click on a data series column and choose Format Data Series from the context menu to display properties pane.
Note: If you do not see Format Data Series option, double-check the selected item. It is common to click and select an unwanted item on charts.
- Activate Fill & Line tab in the right pane and click on Fill to see the options.
- Select Gradient fill option under Fill.
- Make sure the Type is Linear and the Angle is 90°.
- You will see four (4) markers on the Gradient stops slider by default. If there are more or less, use the adjacent buttons to make them 4.
- Select same colors for 1 & 2 and 3 & 4 by clicking the stop marker and use the color picker at below.
- Select the second marker and set its Position to the value you found at the 4th step. Also repeat the action for the third marker as well.
Note: Make sure that the second and the third markers are at the same position.
