How to create demographic charts for infographics

Creating charts for infographics is not easy.  You need a chart that is simple and easy to read and one that doesn’t need a lot of text or explanation.  To that end I wondered if I could create this kind of chart using Excel…

It’s actually relatively easy.  Let’s imagine that you want to create an infographic showing the percentage of households owning different pets. You have the data table as shown below:

We can also create a grid of squares 10 x 10 across by altering the width and column size of columns D to M and rows 7 to 16.  I have changed them to approximately 10 pixels across / down.  To make it easier to see, I also put a frame around the grid.  I then repeated this for each of the different types of animal.  I also added internal borders in white.  To do this I pressed Ctrl+1, clicked on the border tab and added black borders on the outsides and white borders on the insides.

I then added numbers along the bottom and right hand side, one for each column / row.

To make the chart look cleaner, I then made these numbers white.

To add the fill, I added a calculation into each of the cells in the grid. The first cell calculation is:

=IF((D\$17+\$C16)<(\$B\$2*100),”1″,””)

The \$ symbols are there to prevent the row or column from changing as I copy paste the calculation across the columns and up the rows.  But basically if the sum of the two numbers in that specific row and column are less than the corresponding figure in the table, then to add a “1”, otherwise, to add nothing.

Copy and paste this calculation so that it fills every cell in the grid.

For the first cell, the calculation adds up the 0 from the left, plus the 1 from below to equal 1), and then compares that to the % household with dogs (36.2%), since 1 is less than 36.2, the figure 1 is put into the cell.  The next cell would add 0 and 2.  The cell above would add 10 + 1 and so on.

I then repeated this for the other grids, substituting \$B\$2 with the corresponding cell reference for each of the animals.  Each of the grids now contain lots of “1”s, however this is not very attractive, so lets add some conditional formatting!

Highlight the first grid and on the home tab of the ribbon, click on conditional formatting > new rule.  Click on “Format only cells that contain” and change the formula to say “equal to” and put the number 1 in the data entry text box as shown in the picture below.  Change the format so that the cell fill is the same colour as the text colour, I have chosen light blue for the dogs grid and press the OK button.

Repeat the procedure for the other grids using a different fill (and text) colour for each animal grid.  Your grids should now look like the pictures below:

All that you need to do now is alter the text colour of the animals in the original data table to match the grids:

You can of course add formatting and titles as you wish to make the charts easier to read, but the basics are there…  You can download the source file here.

Enjoy

+Alesandra Blakeston