How to create unique square pie charts

Pie charts are notoriously difficult to read.  That being said, I still see plenty out there.  Since I’m not a fan, I tend to replace them with bullet charts, thermometers etc.  Another way to replace them is with a “Square” pie chart:

square pie chartThese 2 charts only show one data point, but you can show multiple data points as shown below:

social media sqaure pie chart

Obviously, this type of chart isn’t a standard excel chart (at least not in Excel 2010).  There are a number of different ways of manipulating Excel worksheets to do this though.  I’ll show you my way, and you can let me know if you have a better one! You can download my sample worksheet here.

Table 1 – coloured squares with 1 data point to show

Step 1: Set up your worksheet

Imagine that you have a normal worksheet with a table in cells A1:B2.

basic worksheet

Adjust the width of columns D to N to 26 pixels wide.  (You can do this by highlighting the required columns, and moving your cursor over one of the trailing edges of the columns.  The cursor will change to the change width cursor, you can then drag the column width across to the required width. As you drag a small popup will appear showing the current width.  Stop when it shows 26 pixels.)

Repeat for the row height of rows 1 to 12, stopping when the height is also 26 pixels.

Using the ribbon, click on View > Show > Gridlines – or in other words, turn off the gridlines by deselecting the check box in the Show section of the View tab of the ribbon.

gridlines

In cell D11 type in the figure 0. In cell D10, type in the figure 10. In cell D9, type in the figure 20, and so on going up in multiples of 10 until you reach cell D2 which should contain the figure 90.

In cell E11 type in the figure 1. In cell F11, type in the figure 2. In cell G11, type in the figure 3, and so on going up in multiples of 1 until you reach cell N11 which should contain the figure 9.

You should now have a table like the one shown below:
table 1

Highlight these cells and make the font colour white.  Then highlight cells E2 to N11 and change the fill to a light blue (for example). Your table should look like the one below:

table 2

Step 2: Add your calculations

In cell E11 write the following calculation:
=IF((E$12+$D11)<=($B$2*100),1,"")
We are going to use this IF statement will help us to create a coloured brick.  In essence it will calculate the sum 0+1 (cells E12+D11) and if that is less than the value we want to display in the chart (cell B2*100), then it will return a value of 1, if not the cell will be blank.

Once you’ve entered the calculation into cell E11, copy and paste it across the row, ending at column N.  Then copy and paste the entire row up, to fill the table.  Your table should now look like this:

table 3

Step 3: Add the conditional formatting

Highlight cells E2 to N11 and click on the small down arrow of the conditional formatting button on the Home tab of the ribbon.

conditional formatting

Click on New Rule…

new rule

Click on Format only cells that contain.  Change the second drop down box (between) to say equal to and in the final box add the figure 1.

equals toClick on the Format button.  Adjust the fill to a dark blue using the fill tab of the pop up menu that appears.  Change the font colour to the exact same blue using the Font tab.  Then press the OK button.

format cells

Press the second OK button and then the Apply button to add the formatting.  Your table should now look like this:

table 1 finished

Table 2 – coloured symbols with 1 data point to show

Step 1: Set up your worksheet

Set up your worksheet exactly as per table 1.  Please note, to show both tables in the sample worksheet, I’ve put my second table in cells P2:Z11, but the principle is exactly the same.

Step 2: Add your calculations

This table doesn’t actually need calculations!  Instead, simply highlight the whole table and change the font to wingdings. Then type in the figure « into each cell of the table. (Or type it once and then copy paste the value across the rows and columns).

Your table should now look like this:

star table

Step 3: Add the conditional formatting

Repeat the same process as table 1 to add another conditional rule.  This time, instead of choosing to format only cells that contain, choose to Use a formula to determine which cells to format.

formula

In the rule description type in the following calculation:

=(E$12+$D2)<=($B$2*100)

Note: My calculation is =(Q$12+$P2)<=($B$2*100) since both tables are in my worksheet!

Click on the Format button and adjust the font colour to white using the font tab of the format cells pop up menu box.  Then press the OK button and the Apply button to add the conditional formatting.  Your table should now look like this:

star chart finished

Table 3 – coloured symbols with 4 data points to show

Step 1: Set up your worksheet

Set up your worksheet exactly as per table 1, except that you will have four rows to your data table.

social media data table

Please note, to show all three tables in the sample worksheet, I’ve put my third table in cells E14:N23, but the principle is exactly the same.

Download the font “Social Font Face” from dafont.com or similar.  Highlight your table and make sure that the font “Social Font Face” is selected.

Step 2: Add your calculations

Just like table 1, you are going to add calculations.  First we’re going to calculate the running sum:

social media table adjusted

The first cell (cell C15) of the new column is equal to your first value (0.47).  The second (cell C16) is equal to the first value + the next (0.47+0.33 = 0.80). And so on.  The total (normally) should add up to 1 (100%).

Once you’ve calculated the running total, you are ready to add the calculations for your table.

In the first cell of your table type in the formula:

=IF((E$24+$D23)<=($C$15*100),"F",(IF((E$24+$D23)<=($C$16*100),"T",(IF((E$24+$D23)<= $C$17*100),"y","G")))))

In essence this is a sumulative IF statement:

  1. If the sum of E24+D23 is less than our first data point, then the cell will show the value F.  If not it carries out the next IF statement.
  2. If the sum of E24+D23 is less than the sum of the first 2 data points, then the cell will show the value T.  If not it carries out the next IF statement.
  3. If the sum of E24+D23 is less than the sum of the first 3 data points, then the cell will show the value y.  If not it will show the value G

third table calculations

Once you’ve added the calculation, copy and paste it across the rows and columns to fill the table.  Your table should now look like this:

third table with no formatting

Step 3: Add the conditional formatting

To add colour to this table you can set up conditional formatting, one rule for each symbol in the same way as for table 1.

  • If the cell value = F, make the fill dark blue
  • If the cell value = T, make the fill light blue
  • If the cell value = y, make the fill red
  • If the cell value = G, make the fill green

Note: Conditional formatting doesn’t recognize the difference between Y and y.  The font “Social Font Face” however will, so make sure you put a lowercase y in the calculation, even though it doesn’t matter for the formatting.

final table conditional formatting

Once applied, your table should look like this:

final tableAnd that’s it.  Hope you find it useful.  You can download the sample worksheet here.

+Alesandra Blakeston

Advertisements

3 thoughts on “How to create unique square pie charts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s