10 worst Microsoft Excel practices

tableI 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.

About these ads

44 comments so far

  1. Excel on

    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

    • michiel on

      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.

  2. Excel on

    #10 states there is only one way. NOT.

    • michiel on

      No it says there is only one really efficient way. I know, there are more ways, as you mentioned earlier.

  3. dbb on

    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

    • michiel on

      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.

      • Karin on

        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.

  4. Kip on

    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.

    • Sachin Acharya on

      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.

      • jonpeltier on

        These new formulas are nice, but not blessings. The redesigned charting interface on the other hand is a curse.

        • dcardno on

          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!

  5. Andy Holaday on

    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?

    • michiel on

      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).

      • Andy Holaday on

        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!

  6. Patrick O'Beirne on

    Also see:
    http://www.sysmod.com/praxis/prax0404.htm#Excel
    The Devil’s guide to spreadsheet creation

  7. jonpeltier on

    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.

    • michiel on

      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.

      • jonpeltier on

        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.

        • michiel on

          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.

  8. jonpeltier on

    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.

  9. John on

    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.

  10. Toby O'Brien on

    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.

    • michiel on

      - 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.

  11. Roy MacLean on

    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

  12. KIM WENNERBERG on

    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.

  13. Kassie Kasselman on

    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!

  14. aaaaa on

    toby o’brien and dbb: you guys are the ones he’s talking about, and i don’t think you don’t realize it.

  15. Darren on

    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!!!!!)

  16. [...] program abused by millions (billions?) to do stuff that could be done with a 10 year old cellphone. What the bozos at [...]

  17. Mike on

    Amazing how people use Excel to do things that a database was designed to do, even when its the absolute worst choice.

  18. richard on

    You are all wrong, this is the worst and most confusing , useless, time consuming , irrational, illogical, over complicated crap program ever created. I could save time by throwing my computer, and running my business with post it notes stuck all over my desk. It would be less frustrating!!!!

  19. Peter Bedson on

    Missed a whole bunch of things here I think – like not separating inputs and calculations which in my view is the number one problem. Like most prescriptions it is true in general but not true in the particular.

    Complex models need clear separation of data (which never changes), inputs (which the user changes) and calculations/outputs. Often the best way of differentiating is through cell colour – where I dont get inputs through dialog boxes I colour input cells a horrible yellow to clearly show what the user needs to fill in to run the model and I use different colours to distinguish between formulas and data too.

    I would also add a big caution around using INDIRECT too (though I admit I have sometimes used it myself to provide super flexible self-configuring models).

    TEXT formatting is another horror as is chjanging the allignment of cells (makes it hard to spot numbers as text) – I could go on and on and on…

  20. First_Last on

    Wow, How ignorant and self-absorbed you must be to think its ok to use Named ranges. they have no place in the business world. Whenever someone in my organization sends me a spreadsheet that needs “fixing” with named ranges I promptly send it back and ask them remove them all before I’m willing to look at it.
    It always seems to be the same kind of person who uses them too. It’s never the beginner or casual user, it’s always the self-proclaimed advanced or expert, that busts their models, but for all their expertise has no idea how to fix it.
    Of coarse you have no idea where the problem stems from, you have no cell references to tell you.
    If you ever want you spreadsheets/models to be of any value do not use Named ranges.
    They are not for labeling E1 as “Intrest_Rate”, sorry to break it to you and all the other self-proclaimed “experts”

    • michiel on

      I am not arrogant about this at all. If you have extremely large spreadsheets I can understand how it can be difficult to track down a spelling mistake in a name (I guess that’s what you mean).

      However, for smaller sheets the advantages of using named ranges weighs much more to me. Microsoft automatically creates named ranges for all tables (when you use Insert > Table). This makes working with data so much easier!

      If your sheets are getting into the thousands of formulas and data cells I don’t think Excel is the right tool anyway. I believe a financial, database or custom application should be used in those cases.

      I don’t believe it’s the fault of the user to want to use friendly names. We also don’t type IP addresses in our browser, even though for a sysadmin those are way better to track problems. Excel should make the casual user’s life easier, not the person who does the troubleshooting. Just like a car is supposed to be easy for a driver, not for the mechanic.

      I say if you have a problem with named ranges remove them yourself!

  21. IndirectCell on

    Self announced expert and teacher are we :)

    For defence of those you claim did a bad job, they did theyr job you state, and depending or not you stated it was suppose to be future prof is a entierly different matter, theres many efficent ways of doing it, but its not efficent to future proof a one time display job.
    And jan – dec sheets Works if you use them properly, also a form of future Proofing, easy to make everything in sheet chunks + i have never had any trubble indirecting months to a sum sheet, InFact it makes it more readable.
    And i can create table from anything in any direction of how the “end” user likes it, and you should be able to do it too. of corse its not the recomended way as you put it yourself, and this is whats called beeing flexible.. you can create all the sheets in the world how you like them, but thats not allways the way the end user wants it.. hence my first statement, you need to define what the user needs and wants.
    If you just creates what he needs then youv failed.

    • michiel on

      I believe in 99% of the cases the end user is the user him/herself, not someone else. When I make sheets for other people I make sure to have them idiot-proof of course, and present the data in a way that is required. But for efficient and effective work on my own data (or organization’s data) I work with Excel the way Microsoft intended: data in lists, presentations in charts, summaries and pivot tables.

      The _other_ end user should not be worried about the underlying data source, and be presented with a nice form e.g. to enter data.

      By the way when I create a one-time-display job I make it fast in any way it’s needed on paper, but those are very unlikely to have tons of data, and also unlikely to have separate sheets for each month in the year.

      You don’t tell your mechanic you want the motor on top of your car, you let him worry about what’s best as long as it doesn’t bother you and the car drives fine.

      If I am presented with a choice of having the user have a form that’s easy to enter for him, but will break after 12 uses, I go the extra mile to make sure it doesn’t break.

    • michiel on

      oh and by the way I am not self-proclaimed. As stated I was a full time Excel trainer, with credentials MCP, MCSD and MCT (Microsoft Certified Trainer). And a BS in Computer Science on top.

  22. DM/Diddy on

    Wow. Just Wow. First_Last is the most pompous a$$ I have ever seen on any forum. On top of that, he is 100% WRONG! If this idiot worked for me, I’d fire him on the spot. In fact, if I could figure out a way, I’d sue him for all of the salary wasted on him since he was hired. And maybe some more for all the damage he caused his company.

    I wonder what his credentials are – a couple of night classes at community college? Uncle owns the company?

    My credentials – 35 years professional software development. B.S. Computer Science. 18 years working intensively with Excel and VBA. Several patents filed. Blah, blah, blah…

    I know his type – sloppy work, unwilling to admit when he’s wrong, arrogant, defensive. And, as you can see from his post, unwilling to cooperate with his coworkers. If you want to get clinical about it, he might have narcissistic personality disorder. Fortunately, these people don’t usually rise to any level of professional responsibility, since most bosses and coworkers can’t stand to be around them.

  23. dean on

    you obviously know your excel, but dont assume that your views are entirely correct. cell refs are a good idea if you have time to name them all – and why become a simple sam by splitting your formula into multiple cells.

    • michiel on

      I think we probably agree, these are still just guidelines. I don’t always use named ranges, but I would certainly do if my formulas have more than 3 values. Because it’s way easier to understand the formula that way. Also I don’t always split formulas, but when I do, I make sure every step is necessary. In the example Rate*Hours is a useful value to have in the worksheet. When making sheets for other users splitting formulas can be a disadvantage, since the other user just needs 1 output, and you don’t want to confuse them with too many formulas.

  24. Jacob J. Walker on

    I like your article, and would share it with my students, except that when you say that you shouldn’t use tables, I think you are actually saying don’t use cross tabs, which I agree with. But if I shared your article with new students, they might think that you meant don’t use the tables feature, which in fact, is GREAT for lists.

    • michiel on

      Yes, that is exactly what I mean. Of course, what is called ‘tables’ in the Excel 2007 interface was simply called a list in previous versions, without having an actual implementation. I am using the word Table in the sense most people would call it. I fully agree that the TABLES feature is great.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: