Layered and in-cell chart performance
Many of my posts have presented examples of layering multiple charts on top of one another and shrinking them down to the size of a cell. I have compared this to other approaches, namely sparklines, in terms of flexibility, appearance, and ease of creation. However, performance also deserves consideration. To examine this, I created three versions of the survey response dashboard with the graphs produced using three different methods: (1) layered charts, as presented in the original post; (2) in-cell formulas that creates a bar [=REPT("|",F3*100]; and (3) column sparklines. I removed the stacked bar chart to simplify the comparison since there is no straight-forward formula or sparkline equivalent. And the formula and sparklines were not layered, i.e. they did no contain reference points. Instead, each row had the same data and number of graphs, i.e. 8, as the original. I examined sorting time and file size with a workbooks containing 100, 200, and 400 rows with each of these approaches. Tests were performed using Excel 2010 on a PC with Windows 7 and 8 GB of RAM. The results are presented in the following table:
Using actual charts results in file sizes that are far larger and sort times that are slower than the alternatives. Interestingly, the file size when using formula v sparklines is essentially the same, although sparklines take roughly twice the time to sort. Clearly at some point the chart/ layering method becomes unwieldy, but it surprising how many charts can be included in a workbook while keeping the size and performance reasonable. Consider test 2: 1,600 graphs and 200 rows, yet the file size is only 2 MB and sorting takes only ~1/4 a second. For many purposes this is reasonable. The same could be said for 400 rows and 3,200 graphs; which results in a file 4 MB in size with a ~1/2 second sort time.
In sum, if the graph you need is available as a sparkline or formula, choosing one of those approaches will result in a smaller and quicker workbook. However, there are plenty of case where it is simpler or perhaps no alternative exists to using actual charts. If that is the cases, it is still possible to pack hundreds or thousands of charts in a single workbook.
