How to separate colors for positive and negative bars in column/bar chart?
When working with data in Excel, it is common to visualize both gains and losses, profits and expenses, or any dichotomous values using column or bar charts. However, by default, Excel represents all bars in a single color regardless of whether their values are positive or negative. This uniform coloring makes it difficult for viewers to quickly distinguish trends or interpret key differences at a glance.
For better clarity and professional visual presentation, you may want to set different colors for positive and negative bars in your chart. This tutorial comprehensively introduces several effective methods for separating colors of positive and negative values in column or bar charts, helping you to create visually intuitive and easily understandable Excel charts. Each method includes details, scenarios where it is most suitable, potential advantages and limitations, and practical tips to avoid common errors.
Separate colors for positive and negative bar in column / bar chart
VBA macro: Automatically color bars by positive/negative values
Excel formula: Using helper columns and data series for dual-color bar chart
Separate colors for positive and negative bar in column / bar chart
Excel provides a built-in Invert if negative feature, which offers a quick and straightforward approach to visually differentiate positive bars from negative ones in either column or bar charts. This method is suitable for small to moderate datasets and does not require formulas or scripting. If your goal is to create a simple and clear separation with minimal setup, this is the recommended approach.
1. Right-click a data series bar in your chart, then click Format Data Series from the context menu, as shown in this screenshot:
2. In the Format Data Series dialog box, select Fill in the left pane. Then, check the Invert if negative box. This enables Excel to recognize and visually split positive and negative data. See screenshot:
3. Next, apply distinct colors for the data bars: check the Solid fill option, then select preferred colors for positive and negative values under the Fill Color section. This allows full customization for both categories. See screenshot:
4. Once you've chosen the colors, click Close to exit. Your chart will now automatically display positive and negative bars using the colors you set, making distinctions much clearer for analysis and reporting.
Notes and Tips:
1. In Excel 2013 and later, after right-clicking the data bars and selecting Format Data Series, the interface opens as a side pane. Here, click the Fill & Line icon, check Invert if negative, then specify colors for positive and negative bars under the Solid fill option. See screenshot for guidance:
2. This approach works for both column charts and bar charts.
3. If you have a chart with multiple series, or need to individually color bars according to specific logic (beyond positive/negative), advanced options such as VBA or helper columns may be required.
4. If you find that the "Invert if negative" option is grayed out, double-check that you are editing a single series and not a stacked chart, as the feature does not work with stacked column/bar charts.
This solution is favored for its simplicity and quick results, especially for single-series column or bar charts. For more control or automated batch changes, consider the following advanced methods.
VBA macro: Automatically color bars by positive/negative values
If you need to set bar colors according to value (positive or negative) programmatically, or want to handle charts with multiple data series or more complex color logic, you can use a VBA macro. This method provides flexibility for batch processing and consistent formatting, especially if you regularly update your charts or manage large datasets.
1. Press Alt + F11 to open the Visual Basic for Applications editor. In the VBA window, click Insert > Module, then copy and paste the following code into the new module:
Sub ColorBarsPositiveNegative()
Dim cht As Chart
Dim srs As Series
Dim iPoint As Integer
Dim vValue As Variant
Dim posColor As Long
Dim negColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Set your preferred RGB colors below
posColor = RGB(91, 155, 213) ' Blue for positive
negColor = RGB(192, 80, 77) ' Red for negative
' Use currently selected chart
If ActiveChart Is Nothing Then
MsgBox "Select a chart first.", vbExclamation, xTitleId
Exit Sub
End If
Set cht = ActiveChart
For Each srs In cht.SeriesCollection
For iPoint = 1 To srs.Points.Count
vValue = srs.Values(iPoint)
If vValue >= 0 Then
srs.Points(iPoint).Format.Fill.ForeColor.RGB = posColor
Else
srs.Points(iPoint).Format.Fill.ForeColor.RGB = negColor
End If
Next iPoint
Next srs
End Sub2. Close the VBA editor. Go back to your Excel workbook, select your chart, then press Alt + F8 to open the "Macro" dialog box. Select ColorBarsPositiveNegative and click Run. The macro will loop over all bars and apply blue to positive and red to negative values automatically.
Tips & Notes:
- You can customize
posColorandnegColorto any color of your choice using theRGBfunction (RGB(R,G,B)). For Excel’s standard palette, refer to the color picker for precise RGB values. - This solution works for both clustered column and bar charts, single or multiple series. Stacked charts may require additional modifications.
- If the macro produces no effect, double-check that you have the chart actively selected before running the code.
- For bulk automation across multiple charts or dynamic datasets, you can loop through all charts by modifying the code.
This VBA approach is ideal if you routinely generate complex reports or require highly customizable coloring rules not supported by default chart options. However, macro security settings may restrict code execution, so enable macros if prompted.
Excel formula: Using helper columns and data series for dual-color bar chart
Another practical solution—especially useful if you want full manual control over color assignment or want to visualize more than just positive versus negative (such as different shades for magnitude)—is to create two helper columns using Excel formulas. One column represents positive values only, the other negative values only. Each is then plotted as its own data series, and unique colors are set for each series. This method works well even when you need to apply advanced conditional formatting logic or include data labels only on specific bars.
Below is how you can organize your data and configure your chart:
Suppose your original values are in column B (B2:B11).
1. Insert two helper columns next to your original data: "Positive" and "Negative".
2. In the new "Positive" helper column, enter the following formula into the first cell (e.g., C2):
=IF(B2>=0,B2,NA())This formula transfers the value from B2 if it is positive or zero; if it is negative, it returns NA(), which Excel charts will ignore (showing no bar).
3. In the "Negative" helper column, enter the following formula in the first cell (e.g., D2):
=IF(B2<0,B2,NA())This formula displays negative values and hides non-negative ones.
4. Drag the formulas in both helper columns down the entire range to match your dataset.
5. Hold down the Ctrl key, select your category labels and both helper columns, then insert a clustered column or bar chart. You’ll see two data series: one for positive numbers (showing bars only for positive values), one for negative numbers, both with two different bar colors.
Tips and Notes:
- Using
NA()ensures that there won't be empty/zero-height bars at undesired positions, preserving the visual clarity of the plot. - This approach can be generalized for additional conditions—e.g., coloring bars based on custom thresholds or value ranges by further expanding the number of helper columns and conditional logic.
- If you need to update your chart automatically, be sure to expand the chart range or convert your data to an Excel Table.
- Use this method where the "Invert if negative" option does not provide sufficient flexibility, or when working with stacked/complex charts.
With this structure, chart updates and formatting adjustments are straightforward, and you retain detailed control over individual series formatting.
Related articles:
How to insert a chart with data non-contiguous each other?
How to add total labels to stacked column chart in Excel?
How to create Gantt chart in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in