An in cell chart in excel is a chart that has been reduced to fit in a single cell. In cell charts have been around for a long time but have gained significance in the past few years as projects and companies become more complex forcing analysts to pack more information per inch and more importantly, per second of the decision makers time.
In this article we will discuss quick tips on how we can create in-cell charts and then ponder upon some limitations that they pose.
How to create in-cell charts in Excel?
Step 1 – Turn on the drawing toolbar by clicking the ‘View’ -> ‘Toolbar’ -> ‘Drawing’ from the menu.
Step 2 – Click on the ‘Draw’ -> ‘Snap’ -> ‘To Grid’ on the drawing toolbar.
Step 3 – Create a chart – it can be any chart that you want to show in your report or dashboard.
Step 4 – Format the chart to eliminate ‘chart-junk’. This includes eliminating redundent gridlines, borders, labels and colors from data series, axis and the plot areas.
Step 5 – Reduce the boundaries of the chart area and the chart’s plot area so that they can fit within a single cell.
In cell charts can also be made using conditional formatting. You can find an example of gantt chart using conditional formatting here.
In cell Charts – What are they Good for?
Well a lot actually. In cases where the screen real estate is in short supply, in cell charts can be great in packing quite a punch in a small amount of space. Their small size also makes it easier to put different charts series (say for example two chart series, the first containg a list of managers with this month’s sales and the second one with their past 12 months performance) alongside each other and thus providing a 2-dimensional (or a 3-dimensional view) of a single entity (manager’s performanace in this case). Aesthetically, in cell charts look good too and can really stand out if you have to make a presentation to a client. Finally, they allow both the analyst and the reader to focus on information rather than charting.
….And where they miss…
The first problem is that you really can’t provide labels to the axis in an in cell chart which makes it a bit of a guess work as to what si the range of values that you are looking at. And since you can’t label anything else either (there’s no space within a cell for showing both a chart and the label without making it look ugly), your in cell charts can be a bit ‘misleading’. In the absense of axis and other chart lables, one has so ensure that all charts start have the same min and max value scales. Secondly, charts such as the dot and waterfall charts may be difficult to fit in a single cell. And then while you can plot a line chart beautifully using in cell charting, you can’t really plot more than two series without making a mess of it. Finally they are good to use when there are large variations in figures….make the variations small and the reader is likely to miss them altogether.
So, well……..the verdict is mixed with a slight tinge of positive. They are a welcome addition to the charting world but still have some way to cover before they become truly mainstream.