First, right-click on the newly created outer chart and select Change Series Chart Type. You need to realign the charts so that the pointer can work the way it should. Click OK one more time to close the dialog box.Delete the default value from the Series value field, leaving only “=” in that field.Type “Pointer” into the Series Name field.Once there, click the Add button, and do the following: Right-click the lovely half-gauge you’ve just created and choose Select Data. It’s time for the pointer to come into play. Step #7: Add the pointer data into the equation by creating the pie chart. Select each slice individually and change the color of the corresponding slices. In the Fill & Line section, click the Fill Color icon to open the color palette and pick your color for the slice. We will use red, yellow, and green colors to represent how well the imaginary sales team did its job-let’s hope nobody gets fired! Step #6: Change the colors of the remaining slices. Again, go to the Fill & Line tab, and in the Fill section, choose No fill to make the bottom part disappear.Double-click on the bottom part of the chart to open the Format Data Point task pane.Think of the dial chart as the tip of an iceberg while the half-circle stays below the waterline, supporting the structure. To turn the circle into a half-circle, you need to hide the bottom part of the chart.
Step #5: Hide the biggest slice of the doughnut chart. You can now close the Format Data Series task pane. Once you’ve positioned the doughnut chart properly, click the Fill & Line icon in the pane, navigate to the Border section, and click the No line radio button to make the chart look nice and neat. In the pane that appears to the right, set the Angle of first slice to 270° and the Doughnut Hole Size value to 50%.Right-click on the doughnut chart body and choose Format Data Series.Next, you need to adjust the position of the chart to lay the groundwork for the future half-circle gauge. Click the Insert Pie or Doughnut Chart icon.Once you have your dataset sorted out, do the following: End: This special formula- =200-(E3+E4)- will help position the pointer properly by adding all the cells from the Value column ( 30+40+30+100) and subtracting from that sum ( 200) the pointer width ( E4) and your actual data value ( E3).In our case, we will display how much money a fictitious company earned from its sales in Arizona. Value: This is your actual data value.You can change the width however you see fit, but we recommend making it smaller than five pixels. Pointer: This determines the width of the needle (the pointer).The more detailed you want to display your data, the more value intervals you’ll need. Levels: These are the value ranges that will split the dial chart into multiple sections.Labels: These determine the intervals of the gauge chart labels.Prepare your spreadsheet as follows:Ī few words about each element of the dataset: Let’s start out our grand adventure by creating a dataset for both charts. The doughnut chart will become the speedometer while the pie chart will be transformed into the pointer. Technically, a gauge chart is a hybrid of a doughnut chart and a pie chart overlapping one another. Step #1: Prepare a dataset for your gauge chart. Without further ado, let’s dive right in. Here’s an example of the gauge chart you will learn to build from scratch: However, Excel’s massive bag of built-in visualization tools unfortunately has no ready-made solution to offer for such a chart.īut with a bit of spreadsheet magic, there is no obstacle you can’t surmount. Gauge Charts (also referred to as Speedometer or Dial Charts) save the day when it comes to comparing KPIs or business results against the stated goals. Bonus Step for the Tenacious: Add a text box with your actual data value.Step #11: Add the chart title and labels.Step #10: Hide all the slices of the pie chart except the pointer and remove the chart border.Step #9: Align the pie chart with the doughnut chart.Step #7: Add the pointer data into the equation by creating the pie chart.Step #6: Change the colors of the remaining slices.Step #5: Hide the biggest slice of the doughnut chart.Step #1: Prepare a dataset for your gauge chart.
0 kommentar(er)
