Piece of Pie – Dynamic Pie Charts using Data Merge
October 17, 2011 5 Comments
Hot on the heels of my last article “Power Merging“, I’ve been trying to do things in Data Merge which I shouldn’t be able to do, such as graphs and charts. In the previous article, I demonstrated how bar graphs could be made using inline graphics and some very clever spreadsheeting in InDesign.
Well on this occasion, I’ve managed to create dynamic pie charts using Data Merge. Before getting too excited, the charts are accurate within 1% and once again relies on clever spreadsheeting, but this time rely on an array of images which make up the pie chart.
Unlike bar graphs, pie charts can’t use inline text as the pie needs to become a complete circle, so the “Binary solution” used for the bar graphs was not possible. Instead, an array of pie charts from 0% (an illustrator file containing an empty box) through to 100% (a solid circle) and all whole numbers in-between. Slices 1-99% are coloured white but once in InDesign, these images will be assigned a screen effect to go over the top of the 100% circle.
Again, Excel becomes the engine that turns the useless into the useful. The spreadsheet contains the actual raw data values; but in the next immediate column is a formula which takes that data value, divides it by the total, multiplies it by 100 (to make a percentage), rounds it off using standard rounding, and then is assigned the illustrator suffix. What this formula effectively does is tells InDesign to grab the appropriate picture on file for the piechart based on the value in the data. I’ve attached the dynamic piegraphs PDF (containing all links and original workings) so that anyone interested can reverse-engineer what i’ve done.
Ultimately, these latest forays into making InDesign’s Data Merge feature more useful was to actually shift the onus of “usefulness” away from InDesign and towards Excel which prepares the text-only data ultimately imported into Excel. This has opened my eyes to the many functions available in Excel to make data more useful, such as If-Else statements.
The next challenge? Line graphs maybe?