Doughnut Chart that Changes Colors | Positive Green, Negative Red | How to Excel

Introduction

This tutorial will guide you through creating a dynamic doughnut chart in Excel that changes colors based on the values it represents. Positive values will appear in green, while negative values will turn red. Using the IF function in Excel, you can automate this color change, making your charts not only visually appealing but also informative for presentations and reports.

Step 1: Prepare Your Data

Start by organizing your data in Excel.

• Create a new Excel worksheet.
• Input your data in two columns:
  • Column A: Categories (e.g., Sales, Expenses)
  • Column B: Values (e.g., +100, -50)

Example:

| Category   | Value |
|------------|-------|
| Sales      | 100   |
| Expenses   | -50   |

Step 2: Create a Helper Column

You will need a helper column to define the colors based on your values.

• In Column C, create a formula using the IF function.

• Enter the following formula in cell C2:

  =IF(B2>0, B2, 0)
  

• This formula will return the value if it's positive; otherwise, it will return zero.

• In Column D, enter the formula for negative values:

  =IF(B2<0, ABS(B2), 0)
  

• This formula returns the absolute value of the negative number or zero if the value is not negative.

• Drag both formulas down to fill in the rest of the rows.

Step 3: Insert the Doughnut Chart

Now it's time to create the doughnut chart.

• Select the range that includes your categories and the two helper columns (C and D).
• Go to the "Insert" tab on the ribbon.
• Click on "Doughnut Chart" in the Charts section.
• Choose the 2D Doughnut option.

Step 4: Format the Chart

Next, you will format the chart to display the colors based on the values.

• Click on the chart to select it.
• Right-click on the doughnut chart and select "Format Data Series."
• In the "Fill" options, choose "Solid Fill" for each segment.
• For the positive values (helper column C), select green.
• For the negative values (helper column D), select red.

Step 5: Add Data Labels

To enhance your chart, add data labels for clarity.

• Click on your doughnut chart.
• Go to the "Chart Elements" button (the plus sign next to the chart).
• Check "Data Labels" to display the values.

Conclusion

You have successfully created a dynamic doughnut chart in Excel that changes colors based on positive and negative values. This chart can be a great addition to your presentations and reports, making data visualization more effective.

Next steps could include experimenting with other chart types in Excel or further customizing your chart with additional data series and formatting options.