Quickly create a positive negative bar chart in Excel

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.

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

Supposing the original data displayed as below:

Firstly, add some helper columns:

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

In cell D2, type this formula

Drag fill handle down to calculate the differences.

2. Calculate the increase or decrease percentage

In cell E2, type this formula

Drag fill handle down to fill cells with this formula

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

3. Calculate the positive value and negative value

In cell F2, type this formula

E2 is the percentage cell, drag fill handle down to fill the cells with this formula.

In Cell G2, type this formula

E2 is the percentage cell, drag fill handle down to fill the cells with this formula.

Now, select F2: G9, click Home > Percent Style to format these cells as percentages.

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.

2. Right click at the blank chart, in the context menu, choose Select Data.

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.

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.

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.

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.

Now the chart is displayed as below:

Then select the blue series, which express the increasing and decreasing percentage values, click Delete to remove them.

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.

2. Remove gridlines and X (horizontal) axis.

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%.

4. Right click at left series, and click Add Data Label > Add Data Labels.

5. Remove the #N/A labels one by one.

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

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.

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.

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

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.

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

Type 1

Type 2

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

Click to download sample file