Excel Factor 7 In Cell Charts

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:

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 SparklinesOf 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.

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 GotliebDr. 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 :)

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. Bryon Smedley says

    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 says

      Hi Bryon,

      Thanks for those brilliant examples. I especially like the conditional format idea :)

      Cheers,

      Mynda.

  2. says

    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.

  3. Aejaz says

    So simple, yet so appealing. Ready with a graphical representation in a jiffy. Fantastic, very powerful feature. Thanks for bringing out such features.