Excel Factor 7 In Cell Charts

This Excel Factor tip is from Dr Isaac Gottlieb, professor at Temple University in Philadelphia.

Words by Mynda Treacy

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:

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.

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

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:

Or revenues over a time:

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

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.

If you don’t have Excel 2010 or later, you can get an add-in from Bissantz called the Sparkmaker that will create Sparklines in Excel.

I teach In Cell Charts and Sparklines in my Excel Dashboard course.

Thanks to Isaac for suggesting In Cell Charts and Sparklines.

Dr. Isaac Gottlieb is a professor at Temple University in Philadelphia. Over 25,000 students and professionals have taken their Excel workshop with Dr. Gottlieb over the last 15 years. He taught this class at Columbia, NYU and other universities as well as in many corporations. He has written a book “Next Generation Excel: Modeling in Excel for Analysts and MBAs” - Wiley Finance. Dr. Gottlieb has 20 years industrial experience in addition to his academic background.

Vote for Isaac

If you’d like to vote for Isaac's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂