Excel In-Cell Charts

Excel In Cell Charts aren’t actually charts at all, insofar as you won’t find them in the Charts menu in Excel.

They’re actually a formula that you can insert in any empty cell, like you see in column C below:

Excel in cell chart

Yes, that is a formula.

The secret to an In Cell Chart is the REPT function. The following formula is in cell C4:

=REPT("|",B4)

When you format the pipe symbol (|) the right way you get a nice smooth bar.

I like to use Script font, Bold and 9pt, but you can play around with different font types to get different effects.

Scaling In Cell Charts

If you find your bar is too big to fit in the cell you can change the scale of your values by dividing them in half, or by 100, or 1000 etc. depending on their size.

=REPT("|",INT(B4/100))

Note: The INT function rounds the value down to the nearest integer.

For example, the data below is in the thousands but the bar still fits nicely in column C. See the formula in the formula bar.

Excel in cell chart

Likewise, you can increase the scale by multiplying the value in column B by 2, or 10, or 100 etc.

In Cell Charts are a handy tool to use in Excel Dashboard reports as they assist the reader in quickly understanding your data and you can format them quite small.

Excel Sparklines

Excel Sparklines Of course if you have Excel 2010 or later, you may know that you have access to a range of different In Cell Charts called Sparklines.Sparklines were invented by Edward Tufte. He defines them as ‘Intense, Simple, Word-Sized Graphics’.

In Excel, Sparklines are mini charts that allow you to show trends in data, and highlight maximum and minimum values.

Like the trend of currency rates over time:

Excel Sparklines

Or revenues over a time:

Excel Sparklines

And Win/Loss charts which are useful for sporting results:

Excel Sparklines

Unlike charts, Sparklines are not objects that hover above your worksheet. They actually occupy a cell. In fact they are the background of the cell, which means you can also type in the cell containing the Sparkline.

Mind you, I don’t recommend you do that. You could apply cell formats like a coloured fill if you wanted to, but don’t go overboard and dilute your Sparklines with unnecessary formatting.

Learn extensive Charting ; Dashboards here.

Team Excelgoodies – www.Excelgoodies.com

Leave a Reply

Your email address will not be published. Required fields are marked *