Download Office Tab
Enable Tabbed View for Word, Excel, PowerPoint, Project & Visio
Gauge is a frequent used chart in business report. We can find them in many magazines. There are also many 3rd party tools to create it. The following is an example from a popular magazine.
Excel does not support gauge directly. But we can create it by combining some basic elements. The following chart is a basic circle gauge.
Prerequest: Prepare the following data
Three values can define a gauge: minimum, maximum and current value. Current value should be between the minimum and the maximum. Let's suppose them as 0, 100 and 59. We'd also prepare the following data for gauge tick. $B$1 is the current value. And the range $B$3:$C$4 will be used as pointer.
Three values can define a gauge: minimum, maximum and current value. Current value should be between the minimum and the maximum. Let's suppose them as 0, 100 and 59. We'd also prepare the following data for gauge tick. $B$1 is the current value. And the range $B$3:$C$4 will be used as pointer.
Step 1: Create gauge edge and tick
Insert a blank "Doughnut" into your workbook. And follow the guide from steps 1.1 to 1.3. You can get a normal "Doughnut" chart.
1.1 Select $A$1 (Only one cell); Press Ctrl+C to copy it to clipboard and then press Ctrl+V after you choose chart using mouse.
1.2 Select $A$1-$A$13 (Multiple cells) and repeat the operation of step 1.1
1.3 Repeat 1.1 again
1.4 Repeat 1.1 again
Insert a blank "Doughnut" into your workbook. And follow the guide from steps 1.1 to 1.3. You can get a normal "Doughnut" chart.
1.1 Select $A$1 (Only one cell); Press Ctrl+C to copy it to clipboard and then press Ctrl+V after you choose chart using mouse.
1.2 Select $A$1-$A$13 (Multiple cells) and repeat the operation of step 1.1
1.3 Repeat 1.1 again
1.4 Repeat 1.1 again
Step 2: Add pointer
Here we use a scatter chart to emulate pointer. Let's suppose the radius of doughnut chart is 1. So the center point has the value (0.5, 0.5). We've also supposed the length of pointer is 60% of doughnut radius. If the current value is 59, you have to use a little math knowledge to calculate the end point. I've prepared the formula, you can input it directly. Please see the cell $B4 & $C4.
2.1 Select $B3 (the cell has the value 0.5) and copy it to chart as another doughnut. (The red one in the following picture list)
2.2 Change the most outside doughnut to "Scatter with Straight Line and Markers"
2.3 Change the scatter chart data. Force the X values to $C$3:$C$4 and Y values to $B$3:$B$4
Here we use a scatter chart to emulate pointer. Let's suppose the radius of doughnut chart is 1. So the center point has the value (0.5, 0.5). We've also supposed the length of pointer is 60% of doughnut radius. If the current value is 59, you have to use a little math knowledge to calculate the end point. I've prepared the formula, you can input it directly. Please see the cell $B4 & $C4.
$B4 =0.5+COS(PI()*($B1/100*270-135)/180)*0.3
$C4 =0.5+SIN(PI()*($B1/100*270-135)/180)*0.3
2.1 Select $B3 (the cell has the value 0.5) and copy it to chart as another doughnut. (The red one in the following picture list)
2.2 Change the most outside doughnut to "Scatter with Straight Line and Markers"
2.3 Change the scatter chart data. Force the X values to $C$3:$C$4 and Y values to $B$3:$B$4
[Notice: We have supposed the radius is 1 so you'd also need limit both X-Axis and Y-Axis to (0-1)]
Step 3: Add gauge face
Let's enlarge the last chart and add a face to current doughnut chart. Select the cell $B3 and copy it to chart. There will be additional scatter point in chart. (The yellow dot of the first picture of the following list). Change it's type to "Pie" and force the pie's data to both 0.5. You will get the second picture from the following picture list. Drag the pie using your mouse and expand the pie until the size of the pie can fill the doughnut hole. Re-drag the pie again and put them into the center of chart.
Let's enlarge the last chart and add a face to current doughnut chart. Select the cell $B3 and copy it to chart. There will be additional scatter point in chart. (The yellow dot of the first picture of the following list). Change it's type to "Pie" and force the pie's data to both 0.5. You will get the second picture from the following picture list. Drag the pie using your mouse and expand the pie until the size of the pie can fill the doughnut hole. Re-drag the pie again and put them into the center of chart.
Step 4: Beautify your chart now.
4.1 Fill the most outer circle with metal color.
4.2 Remove all grids from the chart.
4.3 Apply black color as the basic color for all doughnuts.
4.4 Format the center point
4.5 Apply tick for the second circle. Here you need "Data Label Updater" tool
Result
4.1 Fill the most outer circle with metal color.
4.2 Remove all grids from the chart.
4.3 Apply black color as the basic color for all doughnuts.
4.4 Format the center point
4.5 Apply tick for the second circle. Here you need "Data Label Updater" tool
Result
In fact, you can also customize these charts according to your imaging. I add more doughnuts for some scenarios. For example, the following green to red doughnuts.
没有评论:
发表评论