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.