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 cross tabs
Cross tabs (contingency tables, like e.g. years on the left column and 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
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. Update: too little worksheets is bad as well, if you think you need to put everything including charts and pivot tables on one sheet, think again.
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 (calculations need to start with ‘=’). So the value is automatically converted to a date value. If you would type ‘January 1st, 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 Universe Divided 
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.
I’m rather writing some code lines than use a pivot table… pivot tables make the workbook heavy and slow, they are awfule.. and in most of the cases people use pivot tables to save them writing a few count- or sumifs..
#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.
Good tip!
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!!!!!)
Amazing how people use Excel to do things that a database was designed to do, even when its the absolute worst choice.
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!!!!
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…
The ‘horrible yellow color’ trick is used too often. There is a better way and it’s called sheet protection. You can actually prevent the user from ever making a mistake, and protect your precious formulas as well. http://office.microsoft.com/en-001/excel-help/lock-or-unlock-specific-areas-of-a-protected-worksheet-HA010096837.aspx
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”
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!
Sorry, but named ranges are useful, as long as you understand them. They’re especially useful in VBA for many reasons.
Yours truly,
“self-proclaimed ‘expert'”
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.
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.
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.
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.
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.
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.
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.
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.
You’re right, I agree. Oops. I think I should clarify in the article. I didn’t know the term cross tabs.
Item #2 should not be in your list. Using cell references isn’t bad in itself. “Horrible to work with”? I agree that you can simply the readability of some formulas by using named ranges, but that should be a top in a list of “best practices”. In fact, you’re kind of doing that with item #7 “too complex formulas”. Having a formula that “looks” complex isn’t necessarily bad practice IMO. I suppose it depends on your audience.
Well I am exaggerating a bit on purpose here. I use a lot of simple cell references in formulas, but as soon as I work a little longer on a sheet, I start improving it will cell names, just to understand what I am doing. Also the example in #7 is fairly easy to understand, so I actually mean convoluted stuff with 10 or more references.
Just wow. Named references confuse people to no end. The average user generally hates. Even simi complex models well have 2, 3, 4 version of the same thing. You’ll have Rate_1 Rate_2 Rate_3 Hours_1 Hours_2 Hours_3
By the end it’s a mess and difficult to use.
If the user is just to READ it and perhaps changes some values, the named ranges can look professional and neat. If you expect people to review the formulas (common) and want people to be able to maintain them, you’ve just infinitely increased the learning curve.
Named ranges have a place (much easier to index and match against Monthly_Data than to go select the data each time you need it.
Also table references generally confuse most people. Making excel look like a database for people who don’t use databases is a terrible idea. Now if I were giving a model to a developer with a CS degree to automate….I’d likely make excel look more like a database, because they are a different type user.
Pivot tables have a place…but personally I avoid them like the plague. Unless you’re doing data models and using DAX in power pivot.
I think we should clearly differentiate between people who use Excel for their own reports, and people who make sheets for others to use. If you make sheets for others who are less knowledgeable I agree with all points. There no reason to confuse people. But these powerful tools are something to educate them about so they can properly make their sheets work.
It’s like saying cars are bad, because I don’t have a license. You really don’t need a computer science degree to use Excel properly. Also, the table references can be used as[price] and [amount] instead of the crazy stuff Excels makes of it.
A lot of these are preferences really. #1 = Maybe you don’t care about using the data elsewhere, and want to set it up upside-down and sideways. Doesn’t make it a mistake. #2 = Personally, I cannot stand naming ranges/cells, and my brain prefers to see the cell reference names. #3 = If you need the data separated by tabs, do it, it really is not that difficult to reference sheet names in formulas. #4 = Good point, but a better solution than saying this is wrong, would be to suggest a key/legend, if you really want to color code. #5 = Links are notorious for breaking due to inexperienced users and/or the spreadsheets being used over time; unless it is within same workbook. #6 = You could most certainly use a formula to change that text date into useable numbers; but I do agree it adds complexity, but that is no reason not to do what needs to be done for the boss, or whatnot. #8 = Should read that autosum ranges will need to be adjusted, it makes it sound daunting. #9 = 100% agree. #10 = I have yet to find a need for a pivot table. I have practiced with them to learn them, but they are limited compared to what you can create (but I do love VBA). These are all just opinions, like the article. But there are many ways to do things, and if you do a few on this list…who cares. It doesn’t make them less of an Excel user, if they don’t subscribe to your methods. If the spreadsheet is accurate and not slowed down by improper methods, then it is correctly done. Sorry, author, but you are a bit snobby in this article.
You may be right, I am snobby in this article. It’s on purpose. I teach Excel in a school and I see people just use what they know. That’s fine. People shouldn’t try to use what they don’t know. But I think it’s really a shame many don’t know there are tools to do what they want 10 times faster, more obvious and more clear. E.g. about #8 I say the format messes up autosum. Guess what, that is only their problem, not mine. I never have to use autosum again, because I have filters and pivot tables. Really, if you would learn about them you will start using them daily (provided you are dealing with data tables).
If you like sheets without names and multiple tabs, then you just don’t have complex sheets with a lot of data. If you have complex sheets with a lot of data, it’s best to learn to organize.
Michiel, thank you for your take on this. If I may, as a user, I’d suggest that you consider and state more clearly different business needs as a teacher/ trainer. The moment your trainee comes to an actual team of real people (not necessarily idiots, if we dare to disagree with some comments above) and, based on the feedback on your article and the tone you set, starts dispensing perceptions of what is a “stupid” practice and what is not, he/she is in trouble. A bit of perspective wouldn’t hurt. Corporate world still does have some place for Excel for large data sets, and small businesses grow fast too – it’s hard to find an environment where files are not shared by multiple users over extended periods of time. People move around, data owners change, human errors happen more often than not, and extra steps in data integrity controls do cost time/ money. The most transparent way sometimes is not the most elegant – and it is the end business user to decide what works best for them. For you and some authors of the comments here to suggest that this or that set of tools to use is the absolute best is somewhat like for a Formula-1 pilot to sell a car to Joe, long haul truck driver: for you it’s the speed and design that matters, for him how safe his trips would be (just respectfully hint that a red dashboard might not be good for his eyesight).
Yeah I totally agree with you. Since writing this I’ve come to a different conclusion on writing for the web as well. Sincere and thorough research, taking into account multiple viewpoints beats the “I know better” route.
It’s not for everyone though, sometimes you have to dumb things down to get a message across.
Still most errors are really made because people have no idea there is a better, safer, quicker way.
Whoa….such a handy site.|
Overall some great tips for professional Excel users, but I wholeheartedly disagree with n°2. I really hate working with named ranges because they make auditing formulae that much difficult, especially in complex financial models. Also, n°4 “Using color to indicate meta data” is not a bad practice at all, it is actually a BEST practice used in almost all financial institutions : black means formula, blue means hard-coded values, green means direct references to other cells. It actually makes Business Plans much easier to grasp at a glance and to tweak them when needed.
Also, you might be interested in an Excel plug-in that builds on these best practices : automatic color code, tool to trace back your formulae step-by-step, and some other interesting features. Here’s the link: http://www.oso.world/#try-oso
I work extensively with Excel and with relational databases, and see them as complimentary tools. I am largely in agreement with all of the original points. I couldn’t do my work without pivot tables, although I am heartily sick of having to manually reset every single default option that Excel picks whenever I set up a new one.
I use named ranges extensively but most of the time I use them to reference single cells holding values I want to change so I can see their impact on the whole picture in real time – date range, a coefficient or something similar. I also use named ranges as lookup tables a lot- absolutely critical when I can’t get the original query in mySQL to pick up every data element I need because related values are in different databases,
The cost of named ranges is that they need to be deleted when no longer being used for anything. No one should ever have to deal with one of those frightening messages about external input values when they open up a spreadsheet.
The empty row and column problem can be a huge problem and I am always trying to break people of that habit. I’ve been badly bitten by it myself in past years. Often it is enough to make the column wide and then indent the text so it does not shove up against the left edge of the cell. If I need to put in an empty column to organize really wide spreadsheets, I put a header on it like “start of xxx section” or if really uninspired, just a period will do the trick. I see no purpose in empty rows at all.
i have dealt with a lot of excel probelms from the users.
so i love all of your points, those will keep your excel in good shape at long run.
# 11 Merged Cells….the work of the devil
(OK they have their place, but it’s a very small place IMO)
I have been pulling all the data off a massive workbook onto a single damn list for three days. It will be 9 columns and ~3000 rows. Face-palming all day that someone could make a simple list so complicated! I googled something like “why are people so bad at spreadsheets” and this post came up. EVERY SINGLE ONE of this items omg! I’m going to lose it. I can’t wait to be done tomorrow and do a bunch of pivot tables and everyone will crap their pants. GREAT POST. HAHA.