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.

Excel Chart: Customize Axis Label to Create Personality Charts

Download Office Tab

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

---

We've created some useful charts in our previous posts. This time, let's do a small summary about axis labels. Even though axis labels are very common, you can customize them to promote your chart. I find 3 different usages from my collection.

Case 1: Put Axis Labels among Bars

When the axis labels are long, it's not a good solution to display them below axis. Professional magazines usually put them among bars. The following chart comes from the Business Week. Axis label is above bar items. Excel does not support this, but we can implement it very easy.

We need an auxiliary data series and the "Data Label Updater" tool for this case. The first column of the following data table is the raw data. The second column is axis label and column C & D will be used to control label position.

1.1 Select column A and insert a clustered bar into your workbook.

1.2 Select column C & D and insert a "scatter with only marks" into your chart.

1.3 Enable data label for scatter chart.

Your chart will look like this:

The above chart looks urge. How to control data label's width and display them in single line? Excel has no configure for this. But I find a solution for it. For scatter charts' data label, the width is controlled by the width of chart area. But other charts such as bar or column, the data labels' width is controlled by bar chart itself and plot area. This is the reason I choose scatter as my data labels controller. So if you expand the chart area, the data label will become single line at last. Notice you must keep the width of plot area when you expand the chart area. After that, the chart looks like:

1.4 Beautify your chart and add title and cut useless part away, your chart will become this one.

Case 2: Put Axis Label at random position of column chart

We can use the similar technology to put axis label at random position of a column chart. The following chart put axis label left edge of column bar.

Data Table: The raw comes from internet.

Draft Chart: Select column A & B to create a clustered column chart and add column D & E as scatter chart on secondary plot area. Enable data labels for the scatter chart.

Result Chart: Maybe you've found the axis labels are put at the left edge of column items. You can even move data label out of the column items now. But by default, you cannot control data labels' position in Excel directly.

Case 3: User Icon as axis label

When the chart has relation with country, we usually use national flag as part of axis label. The following chart comes from the Economist.

Let’s get all data from the above chart. The column C & D will be used for data label.

3.1 Select Column B and insert a bar chart into workbook

3.2 Select Column C & D and insert a scatter chart into workbook onto the secondary plot area.

3.3 Enable data label and update data label to countries

3.4 Select each mark in scatter chart and paste a national flag and label icon.

3.5 Apply different color and theme; Add title and sub-title; Append foot and comment.

Excel Chart: How to Add Broken Icon at Low Part of Y Axis

Ad Office Tab

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

---

I've reviewed all my collection and found we usually used broken icons for 3 scenarios. Last week, I posted an article for the first scenario "Broken bar chart to avoid browning from very large values". Today let's practice how to put a broken icon on Y axis at low part. The following chart gives us an example why we need broken icon at the low part. All data of the chart is a little large and the whole line chart looks a lamp suspended from the ceiling.

How to solve this issue? We can limit Y axis to [20-50]. But the Bloomberg uses a different method. The following chart comes from its website. A broken icon is added at the low part of Y axis to stand for [0-40]. Even though this chart looks complex, it gives us a different choice to solve this issue.

How to create this chart in Excel? I tried it in Excel 2003, 2007 and 2010 and found broken icon can be added in all version of Excel but if you want to fill with broken icon with color like the above chart, You need more work. The following chart is created in Excel 2010.

Pre-request: Suppose we have the following raw data.

Column A includes all raw data. We can find they are a little large for a normal line chart. We create an auxiliary data sequence for it and save then into column B. Column C will be used as background.

=$A2-20

Step 1: Create an Area Chart as background.

Select column C, column B and insert an Area chart into your workbook. Limit your primary axis to [0-40]. This is a basic background chart.

Step 2: Add broken icon.

As we all know, Excel does not support free shape filling. Or in other words, you cannot create a closure scatter line chart and fill it with solid color. But we have to add a small triangle as our broken icon. We can use a picture as a broken icon. But picture cannot resize itself according to chart. I find another solution for this issue. Use lot of scatter line to simulate filling. This solution is only suitable for small shapes, or the performances will not be high.

For triangle's filling, there are many auxiliary data. I just list part of them. If you want more, please drop me a mail. Please see the following data table. 

Select your chart and right click to launch "Select Data Source" dialog. Click "Add" button to add "broken" data into your chart. Right click the new added data and change its chart type to "Scatter with Straight Lines". You also need limit your secondary vertical axis to [0-40]. After that, your chart should like this.

Step 3: Add grid line, border and axis.

Grid line and borders will be a series of scatter chart. The following tables list axis data and grid line data. Add them one by one into your chart. You'd also notice this tip: Axis should be added as the last scatter line chart so that Excel can draw the axis over other charts.

Axis Data

Grid line & Border

After create all scatter line chart, your chart should be:

Step 5: Add real line chart

It's very easy. Select column B from the raw data table and insert as a "Scatter with straight line" into your chart. Cool, we've finished most of working.

Step 6: Beautify your chart

Your chart should be meaningful and attractive. Both are very important. You’d collect lots of examples or consult some experts.

6.1 Change background

6.2 Change line style and line color of grid line, border and axis

6.3 Change line style of real data line

6.4 Add chart title and chart footer