Excel Yourself: No. 2 with a bullet chart

Excel Yourself

Excel how-to with sample spreadsheet and video.

As a follow-up to the bullet chart article from the July 2014 issue of INTHEBLACK, I was asked about creating a horizontal bullet chart like the ones in the PDF by Stephen Few.

The easiest way to create a horizontal bullet chart is to modify a vertical bullet chart using a special technique that doesn’t take many steps. See Figure 1

Figure 1

Figure 1

This technique is based on one suggested by Michael Alexander in his excellent book, Excel 2007 Dashboards and Reports for Dummies published by Wiley.

1. Right click the vertical Axis of the vertical bullet chart and choose Format.
 

Click the Alignment option on the left and click the Text direction drop down and choose “Rotate all text 270”.
 
Click Close.

Figure 2

Figure 2

 2. Click the Horizontal Axis and press F4. This repeats the last operation.

 

3. As you can see from Figure 2, we need to rotate the chart. Unfortunately, the rotate option is not available for charts. As a workaround, we copy a range that includes the whole chart.

4. Then select a cell away from the chart and on the Home ribbon tab click the large Paste icon drop down and choose Linked Picture (bottom right icon) as per Figure 3.

Figure 3

Figure 3

5. This will paste a picture of the chart.

Because it is a picture we can rotate it using the “rotate” icon above the picture. See Figure 4

If you hold the Shift key down as you use the mouse to rotate the picture it will be easier to get it to perfectly horizontal. See Figure 5





Figure 4

Figure 4

As the data is changed, the chart will update and the linked picture of the chart will also update.

You must leave the chart in the same location for this technique to work.
 
If you move the chart you will need to repeat the Copy > Paste > Linked Picture and change it to horizontal.

Figure 5

Figure 5

Try the sample spreadsheet
 
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].


How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel