Combine a donut chart and scatter plot to create a custom “gauge and needle” visualization in Excel. Learn the importance of building combo charts to visualize multiple data arrays, using formulas to generate dynamic source data, and applying custom formatting to communicate findings in a clear and intuitive way.
- View Offline
- [Voiceover] Excel features a number of individual chart designs and templates, but joining them into combo charts opens up an entire world of creative data visualization. In this case, I'll show you how to combine a doughnut chart with a scatter plot to create a simple and intuitive gauge-and-needle visualization. Open up exercise file 06_02 and select the Weather Dashboard tab. Here I've got some weather data in columns E through F, I've got accident data in rows 17 through 21, and I've got some dynamic source data in rows 24 and 25.
To create the gauge-and-needle effect, I'll combine a doughnut chart with a scatter plot, where the doughnut chart will simply serve as a background and give some context to the position of the needle. I have the data that I need to create my scatter plot for the needle here in cells E24 through F25. This will create the x- and y-values for the needle. What I need to do is quickly hard code some source data to build out the doughnut itself. So in cell G23, I'll type Donut Slices, and what I'll do is enter 10%, Control + C to copy it, paste that four more times, and then follow it up with 50%.
And what I'm doing here is basically creating a visual that I can use to create half of a circle, which will be the background of my gauge. So one of the slices must be 50%, but the other 50% you can slice and dice however you choose. In this case, I'm choosing five equal segments of 10%. From here, I can go into the Insert menu, go into the Charts section, drill into the pie charts, and choose Doughnut. Now you'll notice that Excel found data in my worksheet and tried to be helpful by inserting it into a chart automatically.
To avoid that, I can just right-click, chose Select Data, scroll down and manually remove each of the series that it just created. Now I can start from scratch, add a new series, name it Donut, and select those values that I just created. And press OK and OK again. Now I'm starting to create my gauge chart with the doughnut background element. Here I can delete the title, I won't need that, and I can delete the legend. And before I do any more formatting, what I'd like to do is insert the data series for my scatter plot.
So I'll right-click, choose Select Data again. Choose Add. I'll call this series Needle, but before I select any values, I'm gonna hit OK, select OK again, and then go up into my Chart Tools and select Change Chart Type. And this is where I have the option to create a combo chart, all the way down here in the bottom-left. So it lists out the two series, Donut and Needle, and allows me to select chart types. So for Donut, I'm gonna scroll down and keep this as a doughnut, and for Needle, I'll choose Scatter with Straight Lines and press OK.
So now when I right-click to select the data, I can edit that needle series and actually input the x- and y-values. So for x-values, I'll select cells F24 and F25, and for the y-values, E24 through E25. Press OK twice. And now I've got all of the data built into this combo chart that I need and it's only a matter of formatting from here on out to turn it into a gauge chart. So we'll start by formatting our doughnut.
So we'll click on the Donut series, right-click, and choose Format Data Series. First thing I can do is change the angle of my first slice. And what I wanna do is just place the segmented section in the top. So I'll rotate 270 degrees, I'll increase the doughnut hole size a bit, just for stylistic purposes. And what this lets me do is select just the 50% segment, go into my Fill and Line options, drop down to Fill options, and select No Fill.
Drop down to Border options and choose No Line. So that essential makes that 50% segment invisible and leaves me with just the top half of the circle. Now with these five segments, I'll just do a little bit of fill formatting to create a spectrum from green to red because the low values on my gauge represent good values, or low accident rates, and the high values on my gauge represent high accident rates, or bad values. So this is a quick way to just create that visual trend here.
So I'll choose a dark green for the lower-left segment. Go into More Colors and select a lighter green here for the second segment. For the third, I'll use a bright yellow. Orange is just fine for the fourth. For the fifth, I'll choose a dark red. So now I have this visual spectrum from good to bad, emphasized by green to red. Next up, let's format our scatter plot, which creates the needle effect.
So I'll start with the y-axis and right-click, choose Format Axis. Here I'm gonna create some custom axis bounds. I'd like the minimum to be negative one, I'll press enter to lock that in, and the maximum to be one. I'll perform the same operation on the x-axis. Minimum negative one, maximum of one. And that essentially centers my needle on that 0,0 origin, which aligns it perfectly with my doughnut chart.
Now what I can do is just delete each of these axes, delete the grid line, and change the formatting of the line or needle itself. So I'll go back into Fill and Line options, and again, you can choose to format it however you'd like. In this case, I'm gonna use a dark grey for the needle. I'll go into the Effects tab and choose a preset shadow option here. The final adjustment that I'll make is to right-click the chart area, choose Format, and I'll eliminate the fill, and choose No Line for no border.
Now I can scroll up and drag this chart into place. And there you go, we've created a new custom data visualization using two existing basic chart types in Excel. And who would have ever thought that you could build something like this using a program like Excel. If you wanna test it out, you can actually just change the current risk levels and see how the needle moves. So as you can see, it progress through the gauge just like you'd expect. And just make sure you undo those manual changes so that we don't use the index match function that we had written.
But there you go. Key takeaway, combine chart types in Excel to plot data from multiple sources and build custom visualizations.
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set