Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to create bullet chart and horizontal bullet chart in Excel?

When you want to make a presentation of the actual performance against a target, the bullet chart will be a good choice. This article will introduce the tutorial to create a bullet chart in Excel, and a workaround to create a horizontal bullet chart as well.


Create bullet chart in Excel

This method will introduce the tutorial to create a bullet chart in Excel. Please do as follows:

1. Select the table you will create a bullet chart based on, and click Insert > Insert Column or Bar Chart > Stacked Column. See screenshot:

2. Keep the new chart selected, and click Design (under Chart Tools) > Switch Row/Column. See screenshot:

3. Now simplify the new chart as you need. In my case, I remove the gridline, horizontal axis, chart title, and narrow the chart area. See screenshot:

4. In the chart, right click the Target data series, and select Change Series Chart Type from the context menu. See screenshot:

5. In the Change Chart Type dialog, please change the chart type of Target to Stacked Line with Markers, enable corresponding Secondary Axis, and click the OK button. See screenshot:

6. Click to select the secondary axis in the chart, and press Delete key to remove it. See screenshot:

7. Now right click the blue point of the Target series in the chart, and select Format Data Series from the context menu. See screenshot:

8. In the Format Data Series pane, go to Fill & Line > Marker, and then:
(1) In the Marker Options section, check the Built-in option, select dashes from the Type drop-down list, and specify a suited size in the Size box (in my case I specify it as 18 according to t
he chart size);
(2) In the Fill section, specify a color from the Color drop-down list;
(3) In the Border section, check the No line option.

9. Go back to the chart, right click the Actual data series, and select Change Series Chart Type from the context menu. See screenshot:

10. In the Change Chart Type dialog, tick the Secondary Axis checkbox in the Actual row, and click the OK button. See screenshot:

11. In the chart, right click the Actual data series, and select Format Data Series from the context menu. See screenshot:

12. In the Format Data Series pane, please do as follows:
(1) Under the Series Options tab, specify a suited Gap Width based on your chart size;
(2) Under the Fill & Line tab, choose a color from the Color drop-down list.

13. Change the fill color for the Low, Medium, and High data series.
Take the Medium data series for example, right click the Medium data series in the chart, and select Format Data Point from the context menu.

In the Format Data Point pane, under the Fill & Line tab, choose a color from the Color drop down list. See screenshot:

And then change the fill color for the other data series with the same way.
So far, we have created a normal bullet chart in Excel. See screenshot:


Create horizontal bullet chart in Excel

In this part, I will introduce a trick to create a horizontal bullet chart based on an existing vertical bullet chart in Excel. Please do as follows:

1. Create a normal bullet chart in Excel.

2. Select the range containing the whole bullet chart, and press Ctrl + C keys together to copy it.

3. Select a blank cell, right click, and select Paste Special > Linked Picture from the context menu. See screenshot:

4. Keep the linked picture selected, and click Format (under Picture Tools) > Rotate > Rotate Right 90°. See screenshot:

5. Go to the original vertical bullet chart, right click the vertical axis, and select Format Axis from the context menu.

6. Then go to the Format Axis pane, please select Rotate all text 270° from the Text direction under the Size & Properties tab. See screenshot:

7. Select the linked picture, and click Format (under Picture Tools) > Crop, and then crop the linked picture as you need. See screenshot:

Up to now, we have got a horizontal bullet chart in Excel. See screenshot:

Related Articles


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • 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 without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... 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...
  • 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...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • 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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.