Sunday, 28 September 2014

AET Status Bar

Last time I hinted that I'd post about something a little more useful for everyday Excel.

And here it is. (Useful in my opinion)

First - my take on the current status bar and formatting

It's come a long way, but it could be better.

For one thing it ignores numbers in cells formatted as text. I can see the logic behind this. Obviously if somebody went to the trouble of formatting the cells as text, it should be safe to ignore them, right?

Yes in some cases perhaps. But a lot people who don't understand "good" formatting practice will often choose text format to add leading zeroes, etc, and I often see a lot of CSV files imported into Excel with cells formatted as text. Depending on what you are doing, this might be annoying or even dangerous. Your formulas might not work as expected. Consider how many cells you can see on your screen. Is each cell in each column formatted the same way? If you have more than one person collaborating on the same file, do they all use formatting and write formulas the same way? Scary? I might take a closer look in a future post, but for now...

The AET Status Bar
As you can imagine, my version does include all numbers, regardless of how they're formatted. And as I do a lot of work with statistics (just basic stuff, I'm no expert), I added a couple of standard deviation fields (which reminds me, please add STDEV.SIFS and STDEV.PIFS functions in a future version of Excel!)

Finally I added another field to see the number formatting. If all selected cells are the same, you'll see that format shown, otherwise you will see "Mixed".

Some pics...

Below, all selected cells are general format. (My hybrid English/Japanese PC shows a Japanese word meaning "standard" which is the same thing)



Where applicable, my status bar and the built-in status bar show the same values (Average, Count, Numerical Count, Max, Min and Sum)



Which cells are formatted as text? Be honest, you can't tell. Numbers showing on the left of the cell might just be aligned to show that way. Same with numbers on the right - the format might be text, but also aligned to show on the right.

Actually, 1 and 3 both use general format.

But with 3 being

=TEXT(3,"0")
And 2 is formatted as text.

For comparison, the built-in status bar shows

Average: 1.5

Count: 4

Numerical Count: 2

Min: 1

Max: 2

Sum: 3


Which do you prefer?

You can download my status bar here. If necessary, you can increase the width by dragging the right side of the form with your mouse.

(And as a bonus I included a 2003 - 2007 version! Also, the form should be modeless for most Excel versions and will update when you select different cells, change sheets, etc)

See you next time.

3 comments:

  1. Worked great. Definitely the easiest way to get this done. Thanks for sharing.

    ReplyDelete
  2. That does work well. However, I think you may be enabling bad habits :). Although I agree about all the reasons a spreadsheet could have mixed data, I'd argue that it's imperative to try to prevent those things from happening, and then deal with things like numbers-as-text in an explicit manner. Otherwise it's just too error-prone.

    ReplyDelete
  3. Doug, I completely agree about bad habits. But it's an uphill battle, for me anyway, to get other people to change their ways. As it takes time to get their data workable, I made this. (But I'll post about how I do that soon)

    In the meantime, I made a new toy that should make a few testers happy. I'll upload it soon :-)

    ReplyDelete