Celebrate 'The VLOOKUP Book' birthday with us. Get 50% discount on the e-book today & tomorrow (30 & 31 October only).

Click here for details

Improve your in-cell charts with colors and negative values

Posted on July 17th, 2008 in Charts and Graphs , hacks , ideas , Learn Excel , technology - 18 comments

In response to Making Incell charts better article, reader Tony mailed me an excel sheet in which he has modified the charts to display colors and negative values. This is pretty cool. Take a look at the chart below:

MS Excel Incell charts with colors and provision for negative values

MS Excel Incell charts with colors and negative values

Incell charts with negative values by displaying the chart in 2 rows
The trick lies in displaying the charts in 2 rows, one for positive values and another for negative values and then coloring positive row with green and negative row with red. See aside.

Download this example sheet to experiment with incell charts.

Thanks Tony for sharing this with our readers :)

What is your favorite charting trick?

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Improve your in-cell charts with colors and negative values”

  1. Jon Peltier says:

    Interesting idea. You could also use a border on the boundary between the two cells to serve as an axis (though it’s not really necessary).

  2. Chandoo says:

    @Jon… thanks, yeah, I had the border, but it kinda looked odd, so removed it.

  3. tony says:

    It’s always easier to improve on something good rather than invent it from scratch – I didn’t expect Chandoo to post it though! I also wanted to add an x-axis but the point of these charts is simplicity so I left it out.

  4. Speedball says:

    Hi Chandoo,

    Thanks for the excel tips.
    I’ve been trying to create a heatmap on a selected grid of numbers in a spreadsheet (basically select the grid of any size and hit a keyboard shortcut to run a macro which will create the heatmap)…
    The color shades will be from blue to red (blue being the smallest and red being the highest)

    Any ideas?

  5. tony says:

    Speedball, try downloading Poptools (free) as it has that function but in shades from white to red, white to blue etc.
    http://www.cse.csiro.au/poptools/
    It has a heap of science functions but halfway doen the list is the function you are after.

    To do exactly what you are after, try http://www.prodomosua.eu/zips/stile.exe
    It is in Italian and is an add-in (prodomosua is the password) – also free

    While you are at it, have a look at the prodomosua site for some great add-ins http://www.prodomosua.eu/ppage02.html

  6. tony says:

    Speedball, try downloading Poptools (free) as it has that function but in shades from white to red, white to blue etc.
    http://www.cse.csiro.au/poptools/
    It has a heap of science functions but halfway doesn’t the list is the function you are after.

    To do exactly what you are after, try
    http://www.prodomosua.eu/zips/stile.exe
    It is in Italian and is an add-in (prodomosua is the password) – also free

    While you are at it, have a look at the prodomosua site for some great add-ins http://www.prodomosua.eu/ppage02.html

  7. Chandoo says:

    @Speedball : looks like you have few answers from Tony. Let us know if this works for you. Otherwise I can write a macro that can be used to generate heatmaps. Btw, if the heatmap should have 4 or less colors, you can do that with conditional formatting, no need for vba. :) http://chandoo.org/wp/2008/07/09/partition-charts-excel-pie-alternative-visualization-hack/

    @tony.. thanks alot for sharing these links, I really liked the prodomosua site and will test some of these add-ins from my windows laptop.

  8. SpeedBall says:

    Thanks for the links Tony and Chandoo…

    @Tony I think stile from prodomosua is exactly what I’m looking for, but downloading exe’s is blocked at my firm :( I would give it a shot at my home and if the source is available that would be excellent…
    Anyway these plugins are really cool and would definitely like to try some of those…

    @Chandoo unfortunately I want more than 4 colors…rather shades!
    else CF would have done the trick…
    If stile’s source is not available I might have to fall back on your skills ;)

  9. [...] try: Min-max charts to tell the story better, Incell graphs with colors, hot looking thermometer chart Do you like this post? Consider subscribing to my [...]

  10. [...] This technique involves using in-cell charts to prepare the micro chart and then using excel features like cell alignment and conditional formatting to provide additional information, thus making the charts rich. Learn more. [...]

  11. Steven says:

    Looks sweet!
    Could you tell me where to get this “bar” character? I didn’t find it yet, and copy pasting is quite complicated to do!

    Thx

  12. [...] a simple font change, you can make your incell charts magical. What more, combining incell charts with conditional formatting and some awesome alignment, you can make charts like this with [...]

  13. Scaffdog845 says:

    Chandoo,

    Looks good but I think you may want to review the setup a bit. I just started to play around with it and noticed that once any of the values for C7:I7 hits either positive or negative (24) one of two things happen. Either a new bar is added making it a 7 bar chart or GREEN and RED bars appear as values for the same month. As the numbers get further beyond an ABS(24) the chart reacts even weirder.

    I think it has to do with I7:I8 being set to wrap text and only having 7:8 set to 35.25 pixels.

  14. Chandoo says:

    @Scaffdog: It is happening because row height is prevent from the bar to shoot thru roof. You just need to rows 7 and 8 , and double click on the row divider line between number 7 and 8 in the header area (to extreme left). This will increase row height so that the contents can fit inside snugly.

  15. NeverHappyMike says:

    A wonderful example of using in-cell charts. In fact I’m in the process of using it on some datathat has +/- % values. However, I have a problem.

    How can I make the small values like -4.75% or 16% show up using the formula that was in the downloadable sheet?

    =IF($W3>0,REPT($Y$2,ROUND($W3/2,1)),REPT($Y$2,ROUND(-$W3/2,1)))

    I’ve tried usig >0%, or $W3/-10,1, etc but nothing seems to work. It only shows up when it’s around -/+ 200%

    Any pointers greatly appreciated.

    Michael.

  16. Chandoo says:

    @NHM: Since your values are in percentages, you should try to multiply them with 100 before dividing by 3 or 5, like this:

    =IF($W3>0,REPT($Y$2,ROUND($W3*100/4,1)),REPT($Y$2,ROUND(-$W3*100/4,1)))

  17. [...] I’ve a few tables that have % values like -4.5%; -16%; 15%; 6%; 2.5%. I’m interested in creating some in-Cell charts that show positive and negative values. [...]

  18. Scott Kennedyd says:

    Has anyone ever considered using the Excel Add In “Tiny Graphs”?

Leave a Reply