Excel: How to make an Excel-lent bull’s-eye chart

Q. As a conclusion to each project, we evaluate our project time and cost estimates for accuracy. Obviously, underestimating is a problem, but over-estimating is also a problem that leads to overbidding jobs, resulting in rejected job bids. To better analyze and present these metrics, I’d like to plot them on a bull’s-eye chart in Excel. Is this possible?

A. Excel does not offer a bull’s-eye chart, but with a little trickery you can coax Excel into producing a reasonable facsimile by creating an Excel Scatter Chart (aka an XY chart) with data plotted to create a series of concentric circles, as follows. (The menu options cited below apply specifically to Excel 2016; while this chart can also be created in Excel 2013, 2010, and 2007, the menu options may be slightly different.)

1. Convert metrics data to percentages. Start by converting your variance data to percentage calculations to better fit your bull’s-eye chart, as follows. Divide the budget variance by the total budget to calculate the percentage amount over or under budget, as pictured in column E above. Next, divide the number of days over or under budget by 365 to calculate the percentage of a year in which the actual completion date is over or under the projected completion date, as pictured in column F below.

2. Plot your data. Select your metric data percentages calculated in step 1, above (as suggested on the left of the screenshot below), and from the Insert tab, select the Scatter chart option (the initial scatter chart will appear). Next, right-click on a data point and select Format Data Series, set the Line format to No line, set the Marker Options to Automatic, set the Marker Border format to Solid line, Black Color, 2pts Width, and set the Marker Fill format to Solid fill, Red Color. Format the vertical and horizontal grid lines to No line. The results should appear as shown below.

3. Create data to form the first bull’s-eye circle. On a separate worksheet, create a new table four columns wide by 360 rows long. In column A, enter the numbers 1 through 360 (these numbers will represent the circle’s thetas, or each of the circle’s 360-degree radius angles). In column B, enter the number 1 on every row (these numbers will represent the radius for each theta). In column D, enter the formula =B5*COS(A5/180*PI()) (this formula calculates the cosine divided by 180 times pi for the two data points entered in columns A and B). In column E, enter the formula =B5*SIN(A5/180*PI()) (this formula calculates the sine divided by 180 times pi for the two data points entered in columns A and B). Together, the calculated numbers in columns D and E will be used to plot 360 data points that will form a circle on the scatter chart. Your Excel table containing the data necessary to plot the first and largest bull’s-eye circle should appear as shown below.

4. Create data to form additional bull’s-eye circles. Create an additional three pairs of columns, the first of which multiplies columns D and E by 75% (or 0.75), the second pair multiplies columns D and E by 50%, and the third pair multiplies columns D and E by 25%. Each of these new column pairs will be used to plot a series of smaller concentric bull’s-eye circles. When plotted on an Excel Scatter Chart, these data points will form a total of four circles, effectively converting a scatter chart into a bull’s-eye chart. Your updated table containing the bull’s-eye circle data should now appear as shown below.

5. Add the concentric bull’s-eye circles to the chart. Plot the outer circle of the bull’s-eye chart by right-clicking on the chart and selecting Select Data, and then in the Legend Entries (Series) section, click the Add button. In the resulting Edit Series dialog box (see below), in the Series X values field, highlight and enter the cosine calculations from column D. In the Series Y values field, highlight and enter the sine calculations from column E, and then press OK. Repeat these steps to add the three additional pairs of circle data to the chart.

6. Format and complete the chart. Finally, right-click each circle one at a time and set the Line format to Solid line, 2 Pts width, with alternating Black and Red colors. Set the Marker formats to No fill, No line. Right-click the chart’s X-axis, and set the Minimum and Maximum Bounds to -1.1 and 1.1, then repeat this step for the Y-axis. Right-click any metrics data point and select Add Data Labels. Right-click any metrics data point again and select Format Data Labels; in the resulting Label Options section, select Value From Cells, and then highlight and select the project names as data labels. With the data points still selected, from the Home tab, click the Decrease Font Size icon three times to reduce the size of the data labels. Edit the Chart Title. The final chart should appear as shown below.

The resulting bull’s-eye chart is ideal for visually analyzing and communicating data, and the resulting quadrant breakdown can be informative as well. While this chart does involve a moderate amount of effort to produce, once your Excel template is completed, you need only save the template and substitute new project data each month to produce a new bull’s-eye chart. You can download this example template at carltoncollins.com/bullseye.xlsx.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.