How to create a Bullet chart in Excel
![A Bullet chart in Excel for Microsoft 365 A Bullet graph in Excel 365](/images/tips/641/1.png)
The author of the bullet graph used varying intensities of a single hue for the qualitative ranges, but you can use any colors you like (see rainbow thermometer charts).
To create a bullet chart like the one above, do the following:
1. Prepare the data:
1.1. To create a bullet graph, you will need several different data ranges:
- The target
- The actual or current value
- The qualitative ranges
For example:
![Data for a Bullet chart in Excel for Microsoft 365 Data for a Bullet graph in Excel 365](/images/tips/641/2.png)
1.2. Create the qualitative ranges (see rainbow thermometer charts for more details):
For example, using the "poor", "satisfactory", "good", and "excellent" value ranges which are distributed as follows:
![qualitative ranges for a Bullet chart in Excel for Microsoft 365 qualitative ranges for a Bullet graph in Excel 365](/images/tips/641/3.png)
2. Create qualitative ranges:
2.1. Select the data (in this example, B6:B10).
2.2. On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button:
![Insert Bar or Column Chart in Excel for Microsoft 365 Insert Bar or Column Chart in Excel 365](/images/tips/charts365/column_bar.png)
From the Insert Column or Bar Chart dropdown list, select 100% Stacked Bar:
![100% Stacked Column in Excel for Microsoft 365 100% Stacked Column in Excel 365](/images/tips/charts365/100stacked_bar.png)
Excel creates the chart with an odd look:
![100% Stacked Column chart in Excel for Microsoft 365 100% Stacked Column graph in Excel 365](/images/tips/641/4.png)
2.3. Modify the chart:
2.3.1. Do one of the following:
- On the Chart Design tab, in the Data group, choose Select Data:
- Right-click in the chart area and choose Select Data... in the popup menu:
2.3.2. In the Select Data Source dialog box, click the Switch Row/Column button:
![Select Data Source dialog box in Excel for Microsoft 365 Select Data Source dialog box in Excel 365](/images/tips/641/5.png)
Add any other adjustment that you find useful to the qualitative ranges:
![100% Stacked Column chart in Excel for Microsoft 365 100% Stacked Column graph in Excel 365](/images/tips/641/6.png)
3. Add the data with the current value and the Target to the chart:
3.1. Prepare the new data:
The Target and the Actual (current) value, in Microsoft Excel, can be added in different ways:
- create another chart and place it above this one (see Combining several charts into one chart),
- add the new bar chart which contains both of these values:
- The Target defined at the point of 90%, in the interval between "good" and "excellent" zones. Use 2% to display the Target as a thicker line.
- The Actual (current) value is a percentage of the Target.
- Zone 1 is equal to the Actual value if the Actual value is less than the Target.
- Zone 2 is equal to the difference between the Target and the Actual value if the Actual value is less than the Target.
- Zone 3 is the difference between the Actual value and the Target if the Target is less than the Actual value.
3.2. Select the newly added values (in this example, D6:E10).
3.3. Copy then to the Clipboard (by pressing Ctrl+C).
3.4. Select the chart.
3.5. On the Home tab, in the Clipboard group, select the Paste dropdown list and then choose the Paste Special...:
![Paste Special into the chart from Clipboard in Excel for Microsoft 365 Paste Special into the chart from Clipboard in Excel 365](/images/tips/charts365/paste_special.png)
3.6. In the Paste Special dialog box:
- In the Add cells as group, ensure that the New series option is selected,
- In the Values (Y) in group, select the Rows option,
- Select the Series Names in First Column checkbox,
- Unselect the Categories (X Labels) in First Row checkbox:
![Paste Special dialog box in Excel for Microsoft 365 Paste Special dialog box in Excel 365](/images/tips/641/8.png)
Excel updates the chart after adding the new data series, and you need to fix the formatting:
![Stacked Column chart with 2 data series in Excel for Microsoft 365 Stacked Column graph with 2 data series in Excel 365](/images/tips/641/9.png)
3.7. On the Chart Design tab, in the Type group, click the Change Chart Type button:
![Change Chart Type button in Excel for Microsoft 365 Change Chart Type button in Excel 365](/images/tips/charts365/change_chart_type-tab.png)
3.8. In the Change Chart Type dialog box:
- Select the Combo tab,
- Check the Secondary Axis checkbox for all newly added data series:
![The Change Chart Type dialog box in Excel for Microoft 365 The Change Chart Type dialog box in Excel 365](/images/tips/641/10_365.png)
4. Format new data series:
4.1. Select any of the new data series and choose Format Data Series... in the popup menu:
![Format Data Series in popup menu Excel for Microsoft 365 Format Data Series in popup menu Excel 365](/images/tips/charts365/format_data_series.png)
4.2. On the Format Data Series pane:
- On the Series Options tab, set the appropriate value in the Gap Width field
to display these data series narrower than the qualitative ranges:
- To hide data series, select them and on the Fill & Line tab, hide this data series:
- In the Fill section, choose the No fill option,
- In the Border section, choose the No line option:
Add any other adjustment.