Skip to main content

Quickly create a positive negative bar chart in Excel

Author: Sun Last Modified: 2024-11-19

If you need to compare multiple units against the same criteria and display both positive and negative developments clearly, a positive-negative bar chart is an excellent choice. This type of chart visually highlights gains and losses, making it easy to analyze trends, as shown in the example below.

A screenshot showing an example of a positive-negative bar chart in Excel

Create positive negative bar chart step by step (15 steps)
Step 1: Arrange data
Step 2: Create chart
Step 3: Adjust chart

Create positive negative bar chart with a handy tool (3 steps)

Download sample file


Create positive negative bar chart step by step (15 steps)

Step 1: Arrange data

Supposing the original data displayed as below:
A screenshot showing the original data used for creating a positive-negative bar chart

Firstly, add some helper columns:

1. Calculate the difference between two columns (Column C and Column B)

In cell D2, type this formula

=C2-B2

Drag fill handle down to calculate the differences.
A screenshot showing the calculated differences between two columns

2. Calculate the increase or decrease percentage

In cell E2, type this formula

=D2/B2

Drag fill handle down to fill cells with this formula
A screenshot showing the calculated increase or decrease percentage

Then keep cells selected, click Home tab, and go to the Number group, choose Percent Style button to format the cells shown as percentage.
A screenshot demonstrating the application of percentage style formatting in Excel

3. Calculate the positive value and negative value

In cell F2, type this formula

=IF(E2>0,E2,NA())

E2 is the percentage cell, drag fill handle down to fill the cells with this formula.
A screenshot showing the calculated positive values

In Cell G2, type this formula

=IF(E2<0,E2,NA())

E2 is the percentage cell, drag fill handle down to fill the cells with this formula.
A screenshot showing the calculated negative values

Now, select F2: G9, click Home > Percent Style to format these cells as percentages.
A screenshot showing formatted positive and negative values as percentages in Excel

Step 2: Create chart

Now create the positive negative bar chart based on the data.

1. Select a blank cell, and click Insert > Insert Column or Bar Chart > Clustered Bar.
A screenshot showing the insertion of a blank bar chart in Excel

2. Right click at the blank chart, in the context menu, choose Select Data.
A screenshot showing the Select Data option for a blank chart

3. In the Select Data Source dialog, click Add button to open the Edit Series dialog. In the Series name textbox, choose the Percentage header, then in the Series values textbox, choose E2:E9 that contains increasing and decreasing percentages, click OK.
A screenshot of the Select Data Source dialog with the Add button highlighted
A screenshot showing the addition of percentage values

4. Back to the Select Data Source dialog, click Add to go to the Edit Series dialog again, select F1 as the series name, and F2:F9 as the series values. F2:F9 are the positive values, Click OK.
A screenshot showing the addition of positive values series

5. Again, Click Add in the Select Data Source dialog to go to the Edit Series dialog, select G1 as series name, G2:G9 as series values. Click OK.
A screenshot showing the addition of negative values series

6. Back to the Select Data Series dialog, click Edit button in the Horizontal (Category) Axis Labels section, and choose A2:A9 as the axis label names in the Axis Labels dialog, click OK > OK to finish the select data.
A screenshot of the Select Data Source dialog with the Edit button in the Horizontal (Category) Axis Labels section highlighted
A screenshot demonstrating the addition of axis labels

Now the chart is displayed as below:
A screenshot of the positive-negative bar chart before removing blue series

Then select the blue series, which express the increasing and decreasing percentage values, click Delete to remove them.
A screenshot showing the chart after removing the blue series

Step 3: Adjust chart

Then adjust the chart.

1. Right click at the axis labels, then choose Format Axis from context menu, and in the popped Format Axis pane, check Categories in reverse order checkbox in Axis Options section, and scroll down to the Labels section, choose Low from the drop-down list beside Label Position.
A screenshot of the Format Axis option in the context menu
A screenshot demonstrating axis formatting options for the chart in Excel

2. Remove gridlines and X (horizontal) axis.
A screenshot showing gridlines and horizontal axis removed from the chart

3. Right click at one series to select Format Data Point from the context menu, and in the Format Data Point pane, adjust Series Overlap to 0%, Gap Width to 25%.
A screenshot demonstrating the adjustment of series overlap and gap width

4. Right click at left series, and click Add Data Label > Add Data Labels.
A screenshot showing the addition of data labels to the series in Excel
A screenshot showing data labels on the positive-negative bar chart in Excel

5. Remove the #N/A labels one by one.
A screenshot showing the removal of #N/A data labels in Excel

6. Select the chart to show the Design tab, and under Design tab, click Add Chart Element > Gridlines > Primary Major Horizontal.
A screenshot showing added primary major horizontal gridlines in the chart

You can change the series color and add chart title as you need.

This normal way can create the positive negative bar chart, but it is troublesome and takes much time. For users who want to quickly and easily handle this job, you can try below method.


Create positive negative bar chart with a handy tool (3 steps)

With the Positive Negative Bar Chart tool of Kutools for Excel, which only needs 3 steps to deal with this job in Excel.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Click Kutools > Charts > Positive Negative Bar Chart.
A screenshot showing the Kutools Positive Negative Bar Chart option in Excel

2. In the popping dialog, choose one chart type you need, the choose the axis labels, two series values separately. Then click Ok.
A screenshot showing the Kutools dialog for creating a positive-negative chart in Excel

3. Then a dialog pops out to remind you it will create a new sheet to store some data, if you want to continue create the chart, click Yes.
A screenshot showing the confirmation dialog for creating a new sheet

Now a positive negative chart has been created, at the same time, a hidden sheet created to place the calculated data.

Type 1
A screenshot showing a positive-negative chart generated by Kutools in Type 1 format

Type 2
A screenshot showing a positive-negative chart generated by Kutools in Type 2 format

You can format the generated chart in Design and Format tab as you need.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Sample file

Click to download sample file


Other Operations (Articles)

How to create thermometer goal chart in Excel?
Have you ever imaged to create a thermometer goal chart in Excel? This tutorial will show you the detailed steps of creating a thermometer goal chart in Excel.

Change chart color based on value in Excel
Sometimes, when you insert a chart, you may want to show different value ranges as different colors in the chart. For example, when series value (Y value in our case) in the value range 10-30, show the series color as red; when in value range 30-50, show color as green; and when in value range 50-70, show color as purple. Now this tutorial will introduce the way for you to change chart color based on value in Excel.

Conditional formatting stacked bar chart in Excel
This tutorial, it introduces how to create conditional formatting stacked bar chart as below screenshot shown step by step in Excel.

Create an interactive chart with series-selection checkbox in Excel
In Excel, we usually insert a chart for better displaying data, sometimes, the chart with more than one series selections. In this case, you may want to show the series by checking the checkboxes. Supposing there are two series in the chart, check checkbox1 to display series 1, check checkbox2 to display series 2, and both checked, display two series

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!