Excel Chart: How to Create Circle Gauge with Pure Excel Elements

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.

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

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.

$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.

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

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.

Excel Chart: How to Clip Picture Background into Non-Regular Shape in Plot Area

Download Office Tab

Enable Tabbed View for Word, Excel, PowerPoint, Project & Visio

---

Yesterday, a friend sent me a message and demonstrated how to display an no-regular picture background in plot area. As we all know, it's easy and quick to fill plot area with a rectangle picture in all version of Excel. But it's very difficult to clip the picture into a non-regular shape. This friend's ideal is some creative even though it's not very complex. I review some magazines and find an example for this scenario.



I collect some data from newspaper and practice his ideal. The and get the following chart.

Prerequisite: Prepare Data.
Suppose we have the following data. Column B is pseudo CPI (Customer Price Index), which becomes higher and higher recently. Column C is our buy capability. It looks mismatch with the increase of CPI. Where is our money?

Step 1: Prepare two auxiliary data as area chart.
Column D is the auxiliary data, which will be created as a shape to cover the picture background. In fact, we all know, we cannot create a non-regular picture background directly. But we can create some no-regular shapes and overlap them over the background picture. The result background will displayed as non-regular.

Select column $A1-$D6 and insert a stacked area into your workbook. The area chart looks like:

Step 2: Plot the Column C & the auxiliary area chart to the secondary axis.
Select the area chart matching with column C and column D and plot them to the secondary axis.




And then limit both the primary & secondary vertical axis to [0-100].Your chart will become the left picture. Select the red one and change its fill to transparent.

Step 3: Add real data into the chart.
The real data will be created as a scatter line chart. Select column B, C again and insert them into the draft chart. Choose series A and change chart's type to scatter line chart.

Step 4: Change plot area’s and all area charts’ background.

Step 5: Add special mark to line chart.
Prepare the following icon. Select the first icon and press Ctrl+C to copy it to clipboard. Select the CPI line and press Ctrl+V to paste the icon as it's mark icon. Do the same operation for the other one.

Step 6: beautify it with Orange + Green theme.
Prepare the following icon. Select the first icon and press Ctrl+C to copy it to clipboard. Select the CPI line and press Ctrl+V to paste the icon as it's mark icon. Do the same operation for the other one.

Excel Chart: Square Pie Charts Make Percent Number More Readable

Download Office Tab

Enable Tabbed View for Word, Excel, PowerPoint, Project & Visio

---

Pie charts are very common used in our daily business, especially on dashboard. But while we can see differences in angles quite well, reading the meaning of the difference is another matter, so readable number is still very important. Data visualization experts replace pies with square one sometimes. A square is divided into 10x10 fields, and for each number, as many fields are filled in as there are percent. Consequently, the numbers remain readable by simply counting the number of fields covered by one color. The following example comes from "Forrester Research".

Can we create this chart in Excel? In fact, it's not very complex and we have many solutions. For example, you can fill 10x10 cells with different color and then use "Camera" button to create a snapshot as a result chart. Here, we use a different solution. The final charts look like:

Let's begin with the second one. The following table is the chart data. Column A is the percentage number. Now it's 32%. Column B is used checked how many fields will be covered with different colors. Column C & D will be used for grid line. You can use the following formula to fill the value of column B if you don't want to input theme one by one.

=MAX(0,MIN($A$1-(ROW(A1)-1)*10,10))

Step 1: Create a draft clustered bar chart.

Select B1:B10 and insert a clustered bar chart into your worksheet. Change gap width of bar chart to 0 and then limit primary horizontal axis to [0-10]. You can get a chart like this.

Chart:

Step 2: Add grid line to create 10x10 fields.

 √ 2.1 (Picture1) Select Column D and insert a new series into the above draft chart twice.
 √ 2.2 Choose the two new added bar charts and change chart type to "Scatter with only marks";
 √ 2.3 Change the X-value of the first scatter chart to column D, and Y-value to column E;
 √ 2.4 (Picture2) Change the X-value of the second one to column E, and Y-value to column D;
 √ 2.5 (Picture3) Enable error bar for these two scatter. For Y-axis, you'd enable X-plus error bar and for Y-axis, you'd enable Y-plus error bar.

Step 3: beautify the result square pie chart.

We can also go further here. If we have many percent numbers, how can we add them into one square pie chart? Suppose we have the following raw data.

Column B is the raw data. And column C is accumulated percent number of them. You can use the following formula to calculate it.
=SUM($B$1:$B2)

Column E - I are the real data for chart. You can use the following formula
Column E=MAX(0,MIN($C$1*100-(ROW(C1)-1)*10,10))
Column F=MAX(0,MIN($C$2*100-(ROW(C1)-1)*10,10))
...
Column K & L is used for grid again.

Step 1. Create draft clustered bar chart.
Select column I and insert a clustered bar chart into your worksheet. Then select column H, G, F, E and repeat the similar operation. You can get a normal clustered bar chart like this.

Step 2: Change bar chart into square pie chart.
Change bar chart's gap width to 0 and series' overlap to maximum. You can get a draft square pie chart.

Step 3: Add grid line.
This step is same as the previous one. You'd create a scatter chart at first and then enable error bars for it.

In fact, your chart has been done until now. Apply a professional theme and promote your chart as a good example.
 √ 1. Add chart title and child title. All these charts from professional magazine usually have two titles.
 √ 2. Add some small decoration to make your chart vivid.
 √ 3. Color is another important thing.