From The Universe Divided, 10 Worst Microsoft Excel Practices.
4. Using color to indicate meta data
Using a color to indicate something is great if you are the only person using the Excel file. That is hardly ever the case. You would have to explain: ‘well, red means this row is to be deleted, yellow means it’s not checked, and green means it is checked’. That’s nice, thank you, but the sheet looks but ugly, and if I print this on black and white all that information is suddenly lost.
I agree with most of this list, but I’m not sure what the ‘worst practice’ in #6 is. What I really like about this article is the way he formats his formulas in the text – light brown background and small border.
The formatting may be a built-in WordPress thing. That or he’s performed a little CSS editing.
I sort of agree with the points, but I wouldn’t want to say “never” for more than a couple of them. There are good reasons to use most, providing you use them with a level of awareness of pitfalls that most useres, admittedly, lack.
What I really dislike about this article is the way he formats his formulas in the text. Bold type in a mono-spaced font is much less distracting.
I think #1, #3, and #8 could have been combined into one: Organize using lists.
A friend gave me her resume to edit and it was in Excel. Talk about using the wrong application.
You’re right about typos, and now I see one of myself: but-ugly is a very nice way of putting it, but it’s sure misspelled.
@Mike, I didn’t use CSS editing, the template comes like this, and I use the code tag, which is displayed like this on the blog.
@J-walk, I tried bold, but I didn’t like it. Perhaps in a pre tag it’ll look better, I’ll try.
I think the worst Excel practice I see is people using a calculator outside Excel and hard coding a value that should be a formula in Excel
Wow… I really don’t agree with most of the list! I hope it’s just because i dont have the same use for Excel (easy/versatile reporting vs database)
1. 3. 8. 9. I actually do that all the time. If I use an excel spreadsheet as a database (and share it!) then something is probably wrong. I use it for preparing presentation, dumping/aggregating data from Business objects, SAS or whatever. Interestingly I can’t stand when people use the zoom feature to make their spreadsheet fit on the screen :) And I can’t remember why!
Heh.. Hui, I use calc.exe outside of excel sometimes. I don’t know why exactly, I guess it’s easier than typing a formula I don’t really need.
I disagree somewhat with #2 (Using cell references). Defined names ARE useful in my workbooks, but when attempting to understand the formulas in others’ workbooks, I’d rather see cell references than defined names. Either way, I trace back each cell reference to understand the formula’s intent, so creating defined names just makes it more difficult for me.
As far as I know, there is no established, universally agreed upon worst Excel practices as implied in the article. Ask 20 Excel MVPs to create a list of the 10 worst Excel practices and I’d be shocked if all the lists agree, far less the order. A more palatable title for the article would have been “10 Bad Microsoft Excel Practices,” with no particular regard to order.
Agree, It all depends on what you are trying to achieve.
What a load of rubbish, most of those are his/her personal opinion.
A real bad practice list might be:
1) number formatting “;;;;” or White on White cell formatting to hide numbers
2) Bits of harded coded numbers or External links in the middle of formula
3) Sleeping circulars by mistake
4) Unnecessary use of array formula
5) Using a space to name a sheet
6) Playing with vba events on sheets to try to prevent updates / access to cells
7) Using manual calculation mode without understaning how it effects other workbooks
8) Prolific use of UDFs in VBA that could be replaced with simple formula
9) Incorrect use of offset e.g. referencing cells way-off the target
10) Telling everyone that you are a Guru in Excel
[Sorry the smiley was clearly not intended ]
Jan, how do you hide numbers?
@Dick: Not to speak for Jan, but my personal preference is to use a hidden row or column for numbers I don’t want to be visible. Using “;;;;” formatting or white on white text does tend to trip other developers up when they come along later and try to understand the design of the spreadsheet. Sometimes, if it’s been long enough since I last looked at a spreadsheet I’ve designed, it even trips me up. I tend to avoid using “;;;” and white on white text for that reason.
Stephanie (or Jan) – but hidden rows / columns screw up drag-to-fill operations or lead to inconsistent formulas, often a long way removed from the required hidden number (and they also get in the way of cut & pastes, depending on the situation). They can also be a problem if you need a data series – for an NPV / IRR calculation or something like that. In general, I try quite hard to avoid them.
I don’t mind “;;;” although I prefer white-on-white, since it is easier to toggle on and off (or seems that way to me). The most common case is where a data table is the logical way to present results – in that case the required “active” formula at the top of the column (or left or the row, or top-left cell) looks a little odd in presentation-quality output.
YES to Stephanie absolutely and dcardno that’s really bad practice!
Strictly speaking “;;;” hides numbers both positive and negative, zeros, trues and falses and even text. Using the number format of “;;;” may seem smart but it introduces all kinds of potential sleeping errors in spreadsheets that are almost impossible to track. I am not a fan of hidden rows or columns either, and I agree that they can get in the way. Workbooks should always be built with inputs, calcs and outputs. If you don’t want to see something put it on an input or calcs sheet. Your data table can be calculated on another sheet and an output sheet can be prepared for presentation-quality output.
Ps I have just thought of another pet hate that should be number 2 or 3 on my list. MERGED CELLS !
There is no excuse. If you really must then you can use “Center across selection” but no Merged cells please.
I use hidden rows/columns a lot in order to efficiently use the GETPIVOTDATA function. I group columns/rows as it makes it more apparent (at least to regular Excel users) that there are some things not being displayed.
I use, but dislike, white on white text. If used, it’s generally to indicate error conditions. Can be used directly or through conditional formatting.
For me the worst are:
1. Using Excel workbooks as a DBMS and/or a trusted source of data.
2. Using an Excel workbook as a software development platform without using source control.
3. Allowing an Excel workbook to become an enterprise-level mission-critical application.
Couple more donts
a) Share workbook
b) Sum(A:A)
Using the number format of “;;;” may seem smart but it introduces all kinds of potential sleeping errors in spreadsheets that are almost impossible to track.
I don’t intend to be confrontational, but please describe one. Just about the only one I can see is unintentional deletion – and usually the effects will be apparent fairly quickly – unless I just don’t have enough imagination!
Workbooks should always be built with inputs, calcs and outputs.
That strikes me as being awfully pedantic – and I wonder whether the extra effort to create (and maintain) a separate output sheet (or several) is really that much better than dealing with a “hidden” result; I suspect it comes down to personal preference. I agree completely on “merged cells” – and the bonehead at Microsoft who made ‘merged cells’ the default icon rather than ‘centre across selection’ deserves special recognition (and don’t get me started about placing it on the Ribbon where it is virtually impossible for a normal user to avoid)…
I agree with Sam on ‘shared workbooks’ – an absolute recipe for bloat and file corruption.
I disagree with number 2. I regularly have to interact with a very complex file that contains over 2800 names where there are almost no cell references in the formulae. This file is an auditing nightmare and I have to refer to a name database for every argument of a formula in order to understand what it is doing and then propose and/or make changes to it.
Named ranges are invaluable for writing robust VBA code but for more simple spreadsheet applications should be used very sparingly. I think the worst way to use them is to hold input values for a spreadhseet, this makes for models with very poor transparency for users and very cumbersome maintenance.
Of course this all personal opinion!
Re “Center Across Selection” on the ribbon. I filed this as a bug/suggestion early in the Office 12 beta, and was told “Good idea. We’ll consider it for Office 14.”
The problem with the “;;;” number format is it makes cells invisible which may obscure the trail of logic and throw off the user. I prefer to format some cells with a medium to light gray font, so you know they’re less important but can still track them visually if necessary.
Let me add another: Not using styles to format your cells.
In my experience, as soon as you start using styles religiously, you start thinking about your spreadsheet design too.
@Roger
I found your comments about range names confusing. I certainly agree that range names can be overused, but I do not understand what you mean by range names for input variables reducing transparancy and increasing maintenance.
Personally I find that range names for cells off the sheet (such as input variables), or on large sheets but outside of the local area, are very useful and reduces maintenance. When revewing a formula that uses cells on another sheet, I find it difficult to remember which cell it is referring to by just looking at the address (maybe it is my age). Whereas with a range name, the name is useful in helping me recall what the cell is especially if i have looked at it once – at least it reduces the amount of switching between sheets to try to figure out what the formula is attempting to do – which for me reduces, not increases maintenance effort.