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 negative values
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?
19 Responses to “Improve your in-cell charts with colors and negative values”
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).
@Jon... thanks, yeah, I had the border, but it kinda looked odd, so removed it.
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.
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?
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
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
@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.
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 😉
[...] 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 [...]
[...] 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. [...]
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
[...] 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 [...]
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.
@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.
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.
@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)))
[...] 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. [...]
Has anyone ever considered using the Excel Add In "Tiny Graphs"?
Dears,
just wanted to know how did you get the symbol in Q7. I have installed the Albertus Medium font but still I can't find how to get the symbol. Appreciate your assitance