Skip to main content

Quickly create a positive negative bar chart in Excel

If you want to show how multiple units does comparison to each other based on the same criteria clearly, you can use the positive negative bar chart which can display positive and negative development very good as below screenshot shown.

doc positive negative chart 1

Create positive negative bar chart step by step (15 steps)
Arrange data
Create chart
Adjust chart

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

Download sample file

3 steps to create a positive negative chart in Excel

Here are dozens of chart tools in Kutools for Excel which can help you generate some complex charts quickly. One of them is Positive Negative Bar Chart, it can create a beautiful and editable chart for you in 3 steps. Click to free download this tool.
positive negative chart

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

Arraneg data

Supposing the original data displayed as below:
doc positive negative bar 2

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.
doc positive negative bar 3

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
doc positive negative bar 4

Then keep cells selected, click Home tab, and go to the Number group, choose Percent Style button to format the cells shown as percentage.
doc positive negative bar 5

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.
doc positive negative bar 6

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.
doc positive negative bar 7

Now, select F2: G9, click Home > Percent Style to format these cells as percentages.
doc positive negative bar 8

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.
doc positive negative bar 9

2. Right click at the blank chart, in the context menu, choose Select Data.
doc positive negative bar 10

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.
doc positive negative bar 11
doc positive negative bar 12

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.
doc positive negative bar 13

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.
doc positive negative bar 14

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.
doc positive negative bar 15
doc positive negative bar 16

Now the chart is displayed as below:
doc positive negative bar 17

Then select the blue series, which express the increasing and decreasing percentage values, click Delete to remove them.
doc positive negative bar 18

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.
doc positive negative bar 19
doc positive negative bar 20

2. Remove gridlines and X (horizontal) axis.
doc positive negative bar 21

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%.
doc positive negative bar 22

4. Right click at left series, and click Add Data Label > Add Data Labels.
doc positive negative bar 23
doc positive negative bar 24

5. Remove the #N/A labels one by one.
doc positive negative bar 25

6. Select the chart to show the Design tab, and under Design tab, click Add Chart Element > Gridlines > Primary Major Horizontal.
doc positive negative bar 26

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.

1. Click Kutools > Charts > Positive Negative Bar Chart.
kutools 1

2. In the popping dialog, choose one chart type you need, the choose the axis labels, two series values separately. Then click Ok.
doc kutools 2

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.
doc kutools 3

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

Type 1
doc kutools 4

Type 2
doc kutools 5

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


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


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
this was a super;earning for me.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations