Custom Formats Pros/Cons

[Jonmo1]

Hello all, thought I'd pick your brains for a bit..

What are your thoughts on using Custom Formats ?

Personally, I'm against it (for the most part). To me, Using a custom format to make something LOOK like something else is just asking for trouble.
1. It can confuse your End Users..
"But look, A1 Says we had 1000 of them..." When in fact it was only 572 formatted to round up to nearest 1000. Yes that's a very general and simple example, but you should get the point.

2. It can confuse YOU the designer of the sheet.
Say a few months After you created the sheet, something goes wrong or needs updating. It's possible to forget that some cells are custom formatted. And can cause extra time/headache to troubleshoot.


There are of coarse exceptions to my dislike of custom formats. Like I saw a cool one that will hide 0 values. Forgot who I saw use it. And another one to Hide #N/A(which I can't find again). That could be very usefull.


But anyway, just wanted some other thoughts about that.

 

[schielrn]

I am with you for the most part on this. I hardly ever use custome formats as I would rather use the TEXT() function to do 99% of my custom formatting. This may add to the formulas and calculation times, but it allows me when I go to my workbook a month from now that I can see I have formatted something a certain way. I haven't seen the one's to hide 0 or #N/A, but those might be useful, but then again I would probably use a formula.

 

[Jonmo1]

Actually, I'll retract my statement about a custom format to hide #N/A being cool...That would actually be very annoying because #N/A would still be in the cell, and other formulas will not like it...

 

[Joe4]

They certainly have their place and usefulness.

I don't see any problem with using them to give true representation of what is in a cell (i.e. some funky date formats), or to even hide zeroes or errors in certain situations.

However I would be leery about using them to somehow "manipulate" or "misrepresent" the value in a cell, like the 572 rounding up to 1000 example you gave (better to do that rounding in a formula than use formatting). Not only can it confuse users (or yourself), but if you use that cell in a calculation, you could get some strange results (like if you multiplied that cell by 2 and rounded to the nearest 1000, it will look like 1000 * 2 = 1000).