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

Arrow up
Arrow down
Menu

How to create a Bullet Chart in Excel

A bullet chart is a different type of a Gauge chart, developed by Stephen Few. A bullet chart is a data visualization tool that can be used to compare the performance of a metric. It is an ideal chart if you want to compare total sales to a target...

In this workbook we have a table with product items that have sold. We can see the revenue of 2014, the target revenue and a range with 3 metrics: poor, good and excellent. Let’s start

 

Highlight your data, from B1 to F6 and go to insert tab. We want to insert a stacked column chart. The first thing is to switch row to column. You will find this selection from design tab. Here it is. The next step is to change the color of our range, red for poor, yellow for good and green for excellent. Right click on this and choose format data series. The dialog box appears and we are looking for the fill. So click the bucket, select solid fill and we are gonna do this a red color. I’ll do exactly the same steps with the other 2…..The ranges are ready.

 

Now we need to move the 2014 revenue to secondary axis. From series option select the secondary axis. Because it’s covering up all the other columns, we have to change the gap width. Let’s move the width to 500%. Now we can see the other columns.

 

The next step is to change the target revenue. Right-click on it and change series chart type. From combo charts we want to change the target revenue to a line chart with markers. Click OK. We want to isolate the markers and delete the lines. So right-click on the line and from format data series click on fill and line options. Choose no line. Now from marker options we will change the type and the size. From fill, select solid fill and change to black color. Also select no line from border options. From marker options choose Built-in and change the type of the marker to a line and the size to 20. Close the dialog box. We don’t want the secondary axis, so highlight it and press delete key. The final step is to format the chart, for example type a title and delete the legend. If you want you can change the chart style. From format tab you can apply a visual effect to the chart, such as Bevel.

Finally let’s test the chart, changing values from the data table. All works fine.

Thanks for watching.

You can see the detailed video tutorial below or on YouTube


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, 13 December 2015 11:51
Rate this item
(0 votes)