In Excel 2007, you have the ability to create these data bars.
I wouldn't call them the 'bee's knees', but at least they give you some ability to apply in-cell visualization.
In 2003, no such functionality exists, so some Excel users have turned to the REPT function to simulate data bars.
Here is my alternative to that.
Place this function into a standard module in Excel:
Function DataBar(lngValue As Long)
DataBar = String(lngValue, ChrW(9608))
End Function
This essentially uses the VB String function to repeat a defined character a given number of times.
Here, the character is defined by the ChrW function (the Unicode equivalent of Chr). 9608 just happens to give us a block character.
Once that's in place, you can pass a number to the function in any given way.
Here, I'm passing the results of a calculation that compares each value to the max value in the range. This allows me to limit the length of the visualization to 20 bars.
Granted, it's not exactly high science, but it does give you a relatively clean way to get a high-level visualization within your cells.
Great tip! I've never seen that character before and it sure makes a difference in making the bar look good.
I'm thinking it might be nice to pass the max value and scaling factor as optional parameters.
Hi Mike,
Using Webdings font CHAR(103) will also give you a continuous bar.
I don't like the fade effect on the built-in data bars.
You could also use the Excel add-in "Sparkline" (available here : http://sparklines-excel.blogspot.com/).
It allows you to create built in data bars and other great features (linechart, gauge, scaleline…)
Hello,
That's a nice tip, but how do you make these incell bars in 2007 ?
Or you could put the scaling in the function:
Function DATABAR2(lngValue As Long, Optional barscale As Long = 1)
DATABAR2 = String(lngValue / barscale, ChrW(9608))
End Function
Function DATABAR3(lngValue As Long, Optional maxvalue As Long = 100, _
Optional maxchars As Integer = 10)
DATABAR3 = String((lngValue / maxvalue) * maxchars, ChrW(9608))
End Function
although this doesn't adapt to the data automatically.
Andy: I never did like Excel 2007's databars. The gradient at the end just kills me. I never did notice char(103) before. Interesting option for non-vba.
Florent: Seems to be a dead link?
Cyril: This function works in 2007 as well.
Roy: I like your DATABAR3 alternative. It does serve to take a bit of work off the user. That is, I think it would be bit easier for a user to enter =DATABAR3(A1, Max(A1:A9), 20) than my original function.
>DataPig: Thanks, but I was wondering about the first "gradient" ones. Now I've found this is a "conditional format thing".
For the link, just remove the ending parenthesis. This work fine with this one : http://sparklines-excel.blogspot.com/
Problem with rept is if you change the zoom as you will notice the scale will change
This is a great function, but I've noticed that it doesn't work for negative values. How would we adjust the formula to compensate for negative values?
Joe: You could put negative numbers in one column and positive numbers in another column (next to each other). Pass the negative values to the function as absolutes. Right justify the negative column, and left justify the positive column.
Hi Mike
Like you say in 2007 you can use data bars but if you send your sheet in the body of the mail you will lose the data bars and other new stuff from conditional formatting.
If you use your way you will see the data bars in the mail.
Ron: Very true. This is one reason I can't get completely excited about the new features in Excel 2010 (such as sparklines). If they aren't backwards compatible, it becomes virtually useless to 50+ percent of Excel users. I've yet to see the new conditional formatting tools being used at the clients I work for. They purposely stay away from them for the very reason state.
Hi Mike :
It is a cool tip.however ,both this databar and the rept func,will produce a bar that is not ccurate, and when printed ,zoomed ,they are not continuous.
I perfer to create a true bar chart ,and align it to the cell .
Databars are a poor data representation, and databars in 2007 with that dumb fade-off are an abomination. Yours at least don't suffer the fade-off, but they still fail to convey information accurately IMO.
Shouldn't the Excel (Office?) designers be made to study Tufte and Few and get to understand good visualisation techniques (or even read 'Dashboards for Dummies' … plug, plug! Send Jensen a copy Mike.)
[I never did like Excel 2007’s databars. The gradient at the end just kills me.]
On the up side, they can coexist with text in a cell. I just printed off a very column heavy data dump for my boss on a very tight timeframe. Databars helped highlight some numbers for her, in almost zero time. Granted, it wasn't a long term reporting solution. But I found the feature useful.