10 worst Microsoft Excel practices
I have met a lot of people who know they ‘know’ Excel, even sometimes stating they are experts of some sort. Well, they were not, and I could usually spot it because they make one of the following ‘mistakes’. Of course they get the job done, but in the long run they usually got themselves into trouble with it. I used to be such a person myself, until I became a full time Excel and VBA trainer.
So here it goes, starting with the worst:
1. Using tables
Tables (like years on the left column months on the top row) are nice for a visual report, but the data cannot be used in any other way. If you’ve read my recent article about the TableToListConverter, you know why. Excel is an excellent analyzer of data, as long as this data is organized in rows (lists), much like a database.
2. Using cell references
Don’t you hate those formulas like =C1*B6-H3+F5?. They’re horrible to work with. Wouldn’t it be easier to see =Rate*Hours-Discount+ExtraCost?
Well, that’s possible. Just click on the C1 in the top left corner and type the name ‘Rate’, then press Enter. From then on, C1 has an alias of ‘Rate’, and you can use it in formulas.
3. Using too many worksheets
Again too many people use too many worksheet. As above in #1, they create Worksheets named ‘jan’, ‘feb’, ‘mar’, ‘apr’ and so on. Later on it will become very difficult to get totals or filter this data. Forget about multiple worksheets and put everything in one sheet, as long as the data belongs together of course.
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 butt ugly, and if I print this on black and white all that information is suddenly lost.
The correct way to indicate this information is to add another column or more columns next to your data. Give them descriptive heading values, like ‘deleted, checked’. Notice yellow and green can simply be combined by putting yes or no in the ‘checked’ column.
And did you know about the conditional formatting feature? Based on a value or even a formula you can color or format a cell. But, make sure to only do that for formatting that doesn’t relate to valuable information you should have visible in your sheet. It’s a good feature though for e.g. zebra-stripes (odd rows colored different from even rows).
5. Copy & Paste
Using copy and paste is fine, as long as you don’t copy when you don’t need to. A simple cell reference will point to value without needing a copy. So if you have a price in a C1 in one worksheet, and need it somewhere else, use =C1, or of course =Rate (see #2 in this list). Another way, which even works for many cells, is go to ‘Edit > Paste Special > Paste Link’.
6. Bad formatting
Excel is good at formatting data automatically, if you insert it in the ‘right’ way. E.g. try typing ’1-1′ and instead of ’0′ you will get ’1-jan’ depending on your date format. So the value is automatically converted to a date value. If you would type ‘January 1, 2009′, most likely Excel won’t recognize this and leave it as a text value. You cannot calculate with text values, but you can with Date values. So if A1 has ’1-jan’, and B1 has =A1+1 then B1 will become ’2-jan’.
7. Too complex formulas
Of course, some formulas actually are quite complex, there’s no doubt about it. But there’s no need to make them look complex. Split your formulas in multiple parts, and use named ranges. So instead of =Rate * Hours - Disount + ExtraCost you can use =Rate * Hours in the Amount field and =Amount - Discount + ExtraCost in the Total field.
8. Empty rows and columns
Empty rows are inserted to ‘make a sheet look nice’. However, by splitting e.g. January data and February data this way, Excel will assume you are dealing with two lists, not with one. The totals underneath using autosum will therefor work only on one of these sets. If you wish to have a bit more space, just drag the row height handle, or use Format > Row > Height to set a specific height.
9. Formatting for print
If you enlarge a font to make it look bigger on paper, you are making a mistake. Do it using File > Page Setup > Adjust to…
You can adjust to any size without changing the font. The same goes for displaying on the screen in fact. Use View > Zoom, or use the percentage dropdown box in the formatting toolbar.
10. Not using Pivot Tables.
I intentionally name it this way, since there are so many bad ways of analyzing data, getting totals, count, average and more on a set of data. But there’s only one way to do it extremely efficient, powerful and it brings lots more uses with it. Also, it keeps your data separate from your analysis. It’s called a pivot table. Make sure to have a list of data rows with headings, and then choose Data > Pivot Table. Now just click Finish right away instead of following the wizard. You can now drag in column heading names and be presented with a table with totals.
There’s so much you can do with a pivot table I am going to leave it to Microsoft to explain.
A very practical use of Pivot Tables is e.g. to get only unique values for a certain row. Since these are automatically grouped in a pivot table, you can simply copy and paste the list of unique values from there.
The only one I truly take exception to is #10. Pivot tables are not the panacea of the excel massess. Why not use a database and all the data function (=dxxxxx) to solve items instead of the pivot tables kool aid. Put the glass down
Well, I like pivot tables. Sue me. LOL. I agree data functions are useful, but I hardly have a use for them in my daily practice (which is mostly database dumps, so yes large lists with many columns). The fact that pivot tables don’t update instantly when the data changes is too bad… I did make a macro that solves it, but it should be a feature.
#10 states there is only one way. NOT.
No it says there is only one really efficient way. I know, there are more ways, as you mentioned earlier.
Actually, I think most of these are fairly trivial, because in business, the most important thing is to solve the business problem, and to get it right. Empty rows and columns are hardly ever dangerous.
The worst practices, which actually lead to dangerous errors, include things like
* changing formulae in the middle of tables without any warning
* hard coding inputs in formulae
* poor labelling and documentation
* over complex formulae (I agree with that one)
* not including checks such as making sure percentages add to 100
* hiding cells, or worse, making text white so it is invisible
* mixing inputs with formulae, making it easy to corrupt the spreadsheet
I agree with most points, but e.g. hiding columns and rows, I do that a lot, and it doesn’t bother me when people do, since I notice it right away. It can effectively hide stuff you don’t need to see. Hard coding inputs is a good one, but well, it should be using a named cell range anyway.
Most of what I mention here is tailored to using Excel for organizing lists of data, and analyzing data, not to the complex mortgage/finance stuff. I see your list falls more into that category.
I prefer to make an Outline rather than hiding rows and columns – this is easier to see, and easier to “unhide”. Combining an outline with a Custom View, and putting the Custom Views drop-down on the toolbar (such as “Grand Totals Only” “All Data” etc.), makes things even easier to see.
Just found SUMIFS in Excel 2007 which is way better then SUMIF and can replace the pivot table as the data can then be updated automatically as opposed to refresh whenever a user thinks to do it. I may think that pivot tables are bad in 2007 with it. Especially with your suggest of the pivot table to do “REMOVE DUPLICATES” a command in 2007. Agree with the rest.
Yes Kip, I agree with you. Initially I too hated my organization to have “forced” 2007 on me, until I discovered functions like SUMIFS, COUNTIFS, AVERAGEIFS, etc. Its a blessing.
These new formulas are nice, but not blessings. The redesigned charting interface on the other hand is a curse.
jon: it would *almost* be worth it if the charts looked better, but they don’t – they look horrible, clunky and amateurish and when pasted into other applications (ie, Word, which I do a lot of) they look slightly fuzzy. Somebody at MS signed off on a degraded interface with reduced performance; now that takes some doing!
In general I agree but exceptions can and sometimes should be made. I will comment on two:
2. Using cell references. Yes, they make a formula more readable at first glance, but they make it more difficult to audit. One must assume the named ranges are correct, or go check them first (extra steps). Using cell references allow one to press F2 and immediately verify what the inputs are.
3. Using too many worksheets. I would reword this and say the mistake is not using /the correct number/ of worksheets. Combine like data/like tasks (ala your #1), but separate dissimilar data and tasks. How many of us have tried to parse a worksheet that serves too many purposes, e.g., an input data list, a pivot summary, some free-standing formulas (pointed at the pivot table no less), and a chart or three?
Andy, I agree with #3, but regarding #2 I can see the cell references much easier when I press F2 using named ranges than with row/column references. Excel color codes the border of the cell and shows me the cells involved. If I want to go to the cell I just type the name in the name box or I press F5 (Goto).
I am with you… my issue with named ranges does not concern single cell references (which I think is a good idea) but rather named ranges that span many rows or columns. While I agree using named ranges can demystify a formula in some situations, they can obfuscate the intent in others.
Good post!
Also see:
http://www.sysmod.com/praxis/prax0404.htm#Excel
The Devil’s guide to spreadsheet creation
Re #6:
I don’t know why you think 1-1 would result in zero. 15-14 does not result in 1, it results in the text “15-14″, since it was not first recognized as a date. If you want 1-1 to result in zero, you need to enter it as a formula, =1-1.
If you enter January 1, 2009 in a cell, Excel recognizes it as a date, not as text, unless you’ve already formatted the cell as text.
I agree with the last one, that was a mistake, I meant to give an example of a wrongly formatted date, but much of that depends on the current settings of lists and the Windows date format (international settings). Regarding the first part, I mentioned ‘instead of 0 you will get jan-1′, so I agree with your points, but you misread it.
What exactly did I misread? Your sentence implies that you expect that 1-1 should result in zero, but without an equals sign (or a plus sign, in a strange homage to Lotus), you will not get an arithmetic result.
If Excel recognizes input as possibly formatted as a date, it assigns it the date, hence 1-1 becomes January 1 of the current year. As does 1/1. This is sometimes inconvenient, but you can force Excel to keep it as text by prefixing the cell contents with a single quote.
I meant in that sentence that some people might expect 1-1 to equal 0, which is only natural, and then I explain why it doesn’t. Everything you’re saying is known to me of course. I will try to make my text less ambiguous.
Re #1 and #10:
#10 is the answer to #1. Put the data into a list, records as rows and fields as columns, then create a pivot table, with one field in the columns area and the other in the rows area. You don’t even need one column for months and one for years. Just use regular dates, and let the pivot table group by months and years.
As the only person in my very data-oriented office, I’d be doomed without PivotTables. An additional advantage of them for non-Excel-savvy people is that you can crunch a ton of data quickly and people can’t believe how fast you did it. Perhaps not the most elegant way, but gets the job done.
Sorry, but I have a to be critical here.
In my view, most (all) of these points are misleading and/or wrong. The topics that are addressed require significantly more commentary that one or two sentences and a sledge hammer conclusion.
If I were to apply these “rules” to a real world financial model, I would end up with:
- one worksheet;
- no empty columns/rows;
- all black font color for every cell;
- every cell in the worksheet being named (both individual cell and range names);
- heaps of rows/columns to break out formula; and
- heaps of Pivot Tables (which most Excel users don’t understand).
These combined attributes would make for a horrifically “bad practice” model in my view unless the worksheet was nothing more that a two dimensional data dump (when Excel isn’t an appropriate application for “database” purposes.
I agree with dbb’s comments.
- one worksheet
Not true, I said ‘too many’, I didn’t mean you have to crunch everything into one sheet. That doesn’t make sense to me either.
- no empty columns/rows
Exactly. None of my sheets have empty columns nor rows as I don’t see the point of them. If I need separate information it usually goes into a separate sheet, not on a ‘new line’. See previous point.
- all black font
Exactly. That’s what most of my sheets look like, and they work. However, I do use occasional conditional formatting, and then the color shows up, but only to format, not to convey information you’d have to guess.
- every cell named
No, not every cell, but the ones used in formulas, yes.
- heaps of rows/columns to brak out formula
You have to stay sensible in this. Putting together four or even five values is fine for complex sheets, but as soon as it becomes more then 8 it becomes very difficult to read. I stick to this point.
- heaps of pivot tables
One pivot table can suffice to analyze in so many ways, there’s usually not even a need for more. You can quickly change the pivot fields to get different information.
I find the opposite to #3: people get into problems by trying to put everything on one worksheet: data, totals, multiple tables, … I advocate making each worksheet do a single job: front page / control panel, data input, ‘database’, analysis, summary output, user guide, design notes. Your example of a worksheet for each month is fair: you could have a single list with a Month column. However, one often needs to collate data from (say) different workbooks, where the worksheets are not simple lists.
Regarding #4, I favour using colour in a disciplined manner. Obviously this is best if it follows an established convention. I mainly use colour to indicate worksheet structure – principally where the user-input cells are. Also, conditional formatting is useful to indicate status.
/Roy
How about Merged Cells? They almost always get in the way of manipulating a worksheet, and almost always are merely used to center text across multiple columns (which can easily and properly be done with Format… Center Across Selection). Yes, I am assuming that a worksheet is to be manipulated and altered.
KIM W.
Michiel, I agree with you! However, each man to his own. I hate cell references in formulae. Especially when it refers to a cell on another sheet. Most people use a number of standard cells to refer to. It is a lot easier to refer to them by name, than by reference! Colour? I hate it when I get a multicoloured sheet. It does’t look nice, it looks garish to say the least, and normally only serves to increase the size of the sheet.
Empty lines depend on the purpose of a specific sheet. Not all sheets are full of data, but sometimes these are form letters, invoices, quotes and such like. Obviously there will then be open lines and even columns, but mostly, I agree with you.
I must also agree with Kim Wennenberg as far as merged cells are concerned! What a stupid way of doing things!
Too many sheets, each containg a little bit of info, very much like the other sheets, except maybe, as said before, another month, is stupid. They all belong in one sheet! Much easier to analyse that way! And Is understand that you do not mean one sheet only, because that would also be ridiculous!
The bulk of people out there using Excel are not necessarily economists and financial guru’s. Most people cannot even think of filling an entire worksheet, but most people I know, use Excel for data storiage and manipulation. And then I would rather not mention the mess I have seen accountants make of an Excel workbook!
I think your comments are valid, although it definitely do not apply to each and every Excel user. It should not, otherwise we are living in a very dull world!
toby o’brien and dbb: you guys are the ones he’s talking about, and i don’t think you don’t realize it.
I agree that names make a spreadsheet more readable, and also can make ranges work better, but overuse of names in large spreadsheets can and will make calculation slower!
use names for constants (that could change!), and ranges of cells (overhead of lookup is not significant over a large range calculation), and where it makes sense to do it!
In a small spreadsheet it doesnt matter, but loosing names when a sheet calculates slow can produce a speedup!
using colors can be ok, but PUT A KEY on the sheet so others know what you mean! I use colors to indicate things – RED = BAD, GREEN = GOOD!, I also know if the sheet is going to be printed in Black and white or color, so I try to make the colors show up as different in either case!
Good use of color makes a sheet instantly readable, color for function = good, color for pretty=bad!
And as for #1 bad practice, I disagree the worst mistake people make is to take every piece of information and put it in a spreadsheet, even if that information doesnt affect the result!
I tell people, figure out what you need to know, and if you are doing the sheet for someone else, mock up your output, (hopefully you know you can achieve that result) and get aproval before doing the legwork inbetween!
Then work on the input, make the input match the input source where possible (item orders on page match item orders you are reading and typing!).
Most spreadsheets should have 3 pages, input, output and calculation! (where possible, sometimes its good to put all on one page (automated form for example)), sometimes its good to have multiple pages, i just finished a timesheet for a bus company, and it creates a new sheet for each week, all input and calculations are on that one page, and as yet there is no summary (but I will use VBA for that if it comes up)
Cut and paste are bad! I had a boss who made spreadsheets and cut and pasted values, wondering why his formulas didnt work, (yes he had numbers as text!) he also didnt check his cut/paste so often his printouts had errors!
(Still I blame microsoft for that, paste should paste values/formulas as default, not everything, that should be under paste special!!!!!) I spend a lot of time banging heads, that you paste special values, not copy and paste it breaks less!!!!)
And I laugh at some people, I see them with a calculator and a spreadsheet calculating with a calculator then typing the result in the spreadsheet!
Still if i could have only one office application it would be a spreadsheet, I can write letters, have a database, do my accounts, print invoices etc, play pacman, post to twitter and watch AC/DC’s latest pop video What more does a business need! (oh yes and it also does sums!!!!!)
[...] program abused by millions (billions?) to do stuff that could be done with a 10 year old cellphone. What the bozos at [...]
Amazing how people use Excel to do things that a database was designed to do, even when its the absolute worst choice.