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 🙂
Excel Factor 6 Auto Refresh PivotTables
Gordon Miller
After many formatting attempts, I can’t get rid of the padding between the “|” symbols. The chart looks like… |||||||||||||||||||||||
Mynda Treacy
Hi Gordon,
What formatting did you try? Different fonts, different font sizes etc. Have you tried the Playbill font at 12pt?
Mynda
MZ
Playbill font did the trick for me, thanks.
Paul Martin
If the ‘|’ character is too thin, consider using ‘g’ with Webdings font. It’s a solid cube, and gives solid colour, though much wider than using ‘|’.
Mynda Treacy
Thanks for sharing, Paul!
Bryon Smedley
You can also use a variety of ASCII characters to create some interesting bar types. Simply replace the “|” pipe symbol with an ASCII code. This is accomplished by holding the ALT key and typing in the applicable code number, i.e. ALT-254 (make sure to place this code in double quotes just like the pipe symbol.) Using a standard font like Arial or Calibri tends to work best.
EXAMPLES:
ALT-177 ▒ ▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
ALT-178 ▓ ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
ALT-219 █ ███████████████
ALT-220 ▄ ▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
ALT-221 ▌ ▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌
ALT-222 ▐ ▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐
ALT-223 ▀ ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
ALT-240 ≡ ≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡
ALT-247 ≈ ≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈
ALT-251 √ √√√√√√√√√√√√√√√√√√√√√√
ALT-254 ■ ■■■■■■■■■■■■■■■■■■■■■■■■
BONUS: If you really want to make the bars change colors based on their lengths (ex: short bars RED, medium bars YELLOW, long bars GREEN), apply a conditional format logic to the bars.
EXAMPLE:
Suppose all the data is between 0 and 10 and you wish to apply the following color designations (first bar resides in cell A2):
Numbers less than or equal to 3 = RED
Numbers greater than 3 but less than 7 = YELLOW
Numbers greater than or equal to 7 = GREEN
Set the Conditional Format to “Formula Is” (2003 or earlier)
or
Set the Conditional Format to “Use a formula to determine which cells to format” (2007 or later)
“=A2<=3" (set to RED) "=AND(A2>3,A2<7)" (set to YELLOW)
"=A2>7″ (set to GREEN)
Mynda Treacy
Hi Bryon,
Thanks for those brilliant examples. I especially like the conditional format idea 🙂
Cheers,
Mynda.
Nobi Sawyer
Love this! I need to show this off to someone at work!
Mynda Treacy
🙂 Cheers, Nobi.
Earl Dickerson
The chart looks great. I don’t know how make these sparklines but maybe because I don’t have yet use Microsoft Excel 2010. Anyway, thanks for sharing your ideas.
Mynda Treacy
Cheers, Earl 🙂
Aejaz
So simple, yet so appealing. Ready with a graphical representation in a jiffy. Fantastic, very powerful feature. Thanks for bringing out such features.
Mynda Treacy
Cheers, Aejaz 🙂