Best 100+ Excel Hacks & Tips with Excel 2016. Take the course for only $10 !!

Arrow up
Arrow down
Menu

How to create a Bi-directional Bar Chart in Excel - Ideal for comparing data

Hello this is Andreas and in this Article you’ll learn how to create bi-directional bar charts in Excel. There are times that we want to compare some data, for example product sales, for a specified period. In this workbook we have 3 products and the sales of them for the years 2014 and 2015. A great choice is by creating and using bi-directional bar charts in Excel. Let’s get started:

The first step is to highlight the data range, from A1 to C4. From Insert Tab select clustered Bar chart. Now Right click the legend in the bar and select Format legend from the context menu. in the Format Legend pane, check Top option in Legend Options section, and close the dialog box.

The next step is to right-click on the series and select Format Data Series in the context menu. Check Secondary Axis option in Series Options section and close again the dialog box. The bar chart has now two X, two horizontal axes.

 

Right click the secondary axis you create in above steps, and select Format Axis in the context menu. From Format Axis and Axis options specify min value, max value and interval unit in the text boxes based on your needs. Because my data’s maximum is 5600, I type -6000 and 6000 into the Minimum and Maximum text boxes, and 1000 in Major unit text box. Keep in mind that the Minimum is not the min value of your data, but the negative number of the max value Also Check Values in reverse order option and close the Format Axis dialog box.

 

We will do some changes in the primary axis, with similar steps. Right click the primary axis, and then select Format Axis to open Format Axis dialog, then specify the setting as same as above, but uncheck Values in reverse order option.

As you can see the names of the products are almost in the middle of the chart. To move them to the left, right click the vertical axis and select Format Axis in the context menu, then specify Major trick mark type, Minor trick mark type, and Axis labels as None, None and Low successively in the Format Axis dialog/pane. Optional you could disappear the negative sighs, from number options.

You can also delete the gridlines, put a chart title and the b-directional bar chart in ready!

 

Thank you very much and waiting for your questions.


If you want to learn how to make amazing Dynamic Excel Charts, Dashboards and Pivot Charts in a few weeks, take our Udemy Course for only $25 !! (original price: $199 - 87% OFF!)

 


 

 

Last modified onSunday, 07 February 2016 09:25
Rate this item
(1 Vote)