Tell at a glance the relative size of your data

[N1GHTEYES]

If you have a whole bunch of numeric data in a long column, and you want to be able to see the relative size of your data points quickly - where the big ones are and the small ones - it would obviously be easiest if you simply sorted them according to size. But sometimes that is not practical, perhaps because the layout is fixed, or they have to be in some specific order, or they are already sorted by some other column.

Here is a quick and simply way to be able to see at a glance the relative size of the data in each cell compared to the whole range.

To take an example:

in a blank sheet, create a named variable called maxsize where:
maxsize = max(Sheet1!A:A)

in cell A1 type =rand()
in cell B1 type =REPT("X",CELL("width",B1)*A1/maxsize)

Now select A1:B1 and copy down as far as you like.

Now increase the width of column B and hit f9.

What you should see is that however far you copy, each cell in the B column will contain a number of Xs which is proportional to the relative size of the value in the A column on that row compared to the max of the values in the A column. If the target cell has a value equal to the max of the set, then the X's should fill the cell.

Hopefully you can see how this might be adapted to be used with pretty much any dataset to quickly provide a rough visual indicator of the relative size of each of the values in a given column.

If you don't like X's, simply replace X in the line:
=REPT("X",CELL("width",B1)*A1/maxsize)
with whatever letter or symbol you like. Note however that some symbols may not fill the column width when displaying their "maximum" value, so you may need to use a different font or use a scaling factor. Experiment.

I hope you find this useful.

Tony

 

[SkipVought]

Tony,

A much simpler method is the select the column(s) of interest and observe your selected aggregation(s) in the Status Bar.

For instance, I have pre-selected Average, Count, Min, Max & Sum.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Skip, thanks for the comment. As usual it sounds like your knowledge of Excel's little niceties far exceeds my own. To be honest until your post I did not realise you could display a live average of the selected data in the status bar.

However, if that is what you mean, then I'm not sure how it relates to what I was saying.

The point of my post was that you effectively get what amounts to a bar chart aligned to your data in which you can instantly see at a glance the relative size of the data in every cell. How would the status bar info tell me this?

Did you actually try what I suggested? It only takes a minute to set up. Maybe I did not explain very well what the point was, but if you try it out, I'm sure you'll see that there are circumstances in which it might be handy, given how simple it is to accomplish.

However, if I really am using my usual "sledgehammer to crack a nut" technique, and you do have a much simpler way of achieving the same result, please forgive me and take pity by explaining a bit more - I'm always very happy to learn.

Tony

 

[N1GHTEYES]

OK, I lied. I just timed it myself and it took 1m 10s.

These are the instructions I was following:

in a blank sheet, create a named variable called maxsize where:
maxsize = max(Sheet1!A:A)

in cell A1 type =rand()
in cell B1 type =REPT("X",CELL("width",B1)*A1/maxsize)

Now select A1:B1 and copy down as far as you like.

Now increase the width of column B and hit f9

Tony

 

[SkipVought]

If you have Excel 2007+, you can use Conditional Formatting on your data in column A, using the Data Bars feature and display a similar effect. So your XXX bar might be used in Excel 97-2003, although I'd rather use a Column Chart to represent this instead.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

I've not tried to include a picture in a post before, so if I've got it right, here is an example of how it might look with a slight mod

http://www.mediafire.com/?wl38ca1nb284e

This was done using this formula instead:
=REPT("I",CELL("width",B1)*A1*1.8/maxsize)

The cells were formatted as bold, red, Arial, 10pt.

I did not know about the Data Bars in 2007, so thanks for the info Skip, but we use 2003 at work, and I'm still creaking along with 97 at home.

To be honest I think it looks OK as it is, and the advantages over a chart are that it is automatically aligned to the cells, it takes very little effort and it automatically uses the full width of the available column space.

Tony

 

[SkipVought]

If you use a dynamic named range, then the chart will automatically adjust to new or fewer rows. Try BAR chart.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Yes, I frequently do use dynamic ranges in charts, but it was not just the data range size I was talking about. I meant that to have the bars actually coincident with the cells from which each is derived, you'd have to fiddle with the size every time the size of the range changed. Using the method I suggested however, if you simply copy down the cells alongside the expanded data range - which can be done with a single click - they must automatically match the cells they relate to.

Also, using a chart, you need to faff about losing the axes, etc to get the bars to maximise the space in the column, whereas the method I suggested again automatically maximises the use of the space, so a fairly narrow column of bars will still show a reasonable amount of visual information. See the example I posted if the link works. This means you could use it in a situation where visible space is at a premium, say in some kind of crowded dashboard-type setup. Or just any old spreadsheet where you have lots of columns and you want to be able to see as much as possible at once.

Don't get me wrong, it sounds like I'm saying this method is better than charts, which is obviously nonsense. The point I'm trying to make is that I can envisage situations where a quick'n'dirty indication of relative values, in a manner which visually relates directly to the cells in question, and which can be shown in as small a space as possible - especially if it is easy to set up and adjust - would be an ideal tool. It is not the display methodology of choice for every circumstance, but I think there are times it might be quite useful. (Which is presumably why they included the Data Bars CF in 2007).

In fact that is exactly how I came to use it. I am currently using a model I've made which asses the overall performance of
a system, wrt several key metrics, based on a user-selected set of input variables. Every time I run it I generate a new line of results specific to a particular combination of input variables. These need to be compared and contrasted. I wanted a quick and easy method to see at a glance how the various results compared relative to each other. So today this method occurred to me & I tried it out. When I found it both very easy and very useful (at least for what I'm doing) I thought it would be a nice idea to share it.

I now know (thanks) about the DataBars conditional formatting in 2007, but at the time I posted it, the idea was new to me and I could not recall ever having seen anything similar. So, because I know that you and the other TTers tend to like new tools and techniques, I just thought it might be nice to give something back after all the help you folks have so generously given me over the years. It may not be brilliant, but it's the best I've got ;D.

So if anyone out there, stumbling around in the pre-2007 dark ages finds it useful, I'll be happy.

Tony

 

[N1GHTEYES]

Oops, I think I messed up sharing the folder holding the image. Try this link instead:

http://www.mediafire.com/i/?2lavysvf7m1hq5s

Tony

 

[lionelhill]

The data-bars are wonderful and are definitely the way forwards (two clicks and no formula needed. How much simpler can it get?).

The improvements in conditional formatting are one of the best reasons to upgrade to Excel2007 if you're still using previous versions.