Excel Chart: Duplicate Data Source to Create Special Effected Pie Chart

Download Office Tab

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

---

1. Duplicate Data source to create an enlarged pie to emphasize a special data series.

In Excel, we usually explode one pie from the whole pie chart to emphasize a special data series. But I also find a different solution, which enlarges that pie and highlight data series by size. The left of the following picture is the normal scenario and the right one is a different one. (Please see the green arrow!)

In fact, we can duplicate the data source and add a new chart series (I use doughnut here) to create the right one quickly and easily. Suppose we have the raw data 14 and 86.

A. Insert a blank doughnut chart into workbook. Select data 14 and 86 and press Ctrl+C to copy data to clipboard.

B. Pick new chart and press Ctrl+V twice.

C. Right click on the inner doughnut chart and change its type to pie. (We've done this in the gauge chart)

D. Adjust the outside doughnut chart's "Hole Size" and enlarge the size of pie chart until it satisfies you.

E. Select the data point for 86 from the outside doughnut chart and change its fill to "No Fill".

F. Apply the color and theme you've prepared.

Add decoration and title to your chart. At last you can get the result chart

2. Duplicate Data source to create the second edge for a pie chart.

In fact, take the similar operation as the above chart, you can also create personal chart like the following one, which comes from a popular magazine. The special feature of the chart is the edge. Even though it's not very useful, it improves the whole chart.

 A. Insert a blank doughnut chart into workbook. Select data 14 and 86 and press Ctrl+C to copy data to clipboard.

B. Pick new chart and press Ctrl+V twice.

C. Right click on the inner doughnut chart and change its type to pie.

 We can also create other interesting chart via duplication of data source. Here are some other examples:

1. Area chart with highlight edge.

We've used this technology in our previous post. "Always markup Min & Max point of line chart". You'd insert an area at first and then insert a line chart or scatter line chart over the area chart. Then highlight the line chart.

2. Simulate "double" compound typed line chart.

Line chart with "double" compound type is easy to create. But you can also add two line charts into your workbook and then widen one line chart but keep the other one thin, which looks like a hollow of the thick one. This post just gives you a different solution. Maybe, you can create cooler chart base on this ideal.

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.