Discussion on:

10 ways to screw up your spreadsheet design

This should be required reading for EVERYBODY before even opening Excel for the first time.
#2: Most people at my former employer did this. Just as bad: Using Excel instead of Word
#3: I'd get spreadsheets with blank rows. I would sort and report on the data. Then the spreadsheet creator would question me why "ABC" isn't showing up. It's because they had blank rows throughout the spreadsheet and only the first section was sorted.
#6 No one at my last job had the smarts to know how to set options, much less change to manual recalculation.
#9 and #10: If the data should be on on tab it was on two and vice versa.

#2 Oh no, there's one thing waaaay worse than using Excel instead of Word. Using Word instead of Excel! Most officers know Word and use it for everything. Oh gee, thanks for that data, it's really useless to me. Eg. Can you sort those names and addresses you just spent a few hours typing into alpha order? One hour later... find them still copy/pasting! Spend half my time writing macros to extract data from information that somone decided to collect in Word. Grrr.

I like a combination of the ideas set forth in #4 and #9. I always start at A1, with headings. Any summarizing functions or information that management wants to see goes on a seperate sheet. For example: The very first sheet that it opens up to should be like a cover page with statistics, summaries and the kinds of things that your manager will actually read. The underlying data exists on the following sheets to be ignored by suits.

Even more: if you are using other tabs as source data, and you want them to be on the file but not used, simply add a Sheet(x).visible=xlveryhidden on the workbook open, and voil?? ! they can use the file as it's linked to an external source.Very, Very useful !!

Easy answer: because you're a professional, not a clown. If you need to draw attention to something, highlight the cell with a color, and leave it at that, even if it's the ten-digit growth/sales/etc figure. That's more than enough to grab the eye, and the color can convey whether it's a good or bad thing.
A spreadsheet is to present raw data visually, WordArt and other eye candy is not for meetings, it's for the brochures, and the graphic designers can do a better job then you can with better tools than you have.

I believe the advice here is good and Excel is a widely used spreadsheet. i think however it is a sign of narrow thinking that only Excel is mentioned. It may be odd but I believe in International Standards and Excel does not comply. Of course anything not invented here (ie in USA) is to be ignored. That is the one standard the USA has adhered to..

@misceng... sob. I'm a genuine old fossil from the SuperCalc days and I really miss my keyboard shortcuts... sob... the frustration every time I have to pause to grab the mouse and click away merrily to do something that four keystrokes could have done in a second... come back Borland all is forgiven!!

I have found Excel has some great shortcuts if you know what you're doing. I've been using it long enough that I feel pretty comfortable doing whatever's needed quickly and efficiently (though since learning T-SQL I don't prefer Excel for most things)

Just no.

You might as well hope for Peter to bring back VisiCalc! (LOL)
Willy

I KNOW!!

Wow. I cut my teeth on SuperCalc running on a C/PM machine made by Data General in around 1982 (or 1983?). It's probably the best way to appreciate the progress made with the more modern spreadsheets. Thanks for posting. I didn't think a SuperCalc user was still into spreadsheets.

This comment is all over the place... and poorly constructed.
Quote: "Of course anything not invented here (ie in USA) is to be ignored." I hope this is a personal observation!!

...it was just sarcasm.

Not quite. It was claimed that RCA invented television in 1942 despite the fact that the BBC in UK had public broadcasts of TV in 1936. The US adopted the system of air navigation where planes fly from beacon to beacon. A superior system was developed about the same time by Decca which allowed planes to fly by direct routes. For a while Eastern Airlines installed the Decca system in North East US to get round the congestion over beacons but American pressure forced the use of their system world wide. There have been several mid air crashes over beacons. Only now that GPS is available has the system changed.

You sound like someone from the UK, but maybe you're just an Ameriphobe. Your inaccurate synopsis of both television and navigation system demonstrates that. Yes, the 1936 BBC event was the first public broadcast, but it was with a system that was technically and commercially invalid. The US (Farnsworth's 525 line) system that was adopted here in 1941 had been in limited use since 1936. That became the NTSC standard. The Russians developed the 625 line standard, which Europe standardized later as the CCIR system. But, the point is that there were actually numerous attempts at making a valid commercial TV system, and the US was the first to accomplish that. On your other point, the Decca Navigation System was designed by an American (William J O'brien) and sold to the British Decca record company in 1941. It was a nice low level navigation system more useful to ships than to aircraft. As aircraft high level capabilities increased it became pretty worthless. Your claim that mid-air crashes were because of the considerably better American system is just pure hogwash.

Excel is my expertise. I'm not going to discuss products I don't use with some frequency. Most everything I said is applicable to spreadsheets in general though. My choice of products isn't an attempt to ignore other products.

Whilst I would agree with the general principle, there are occasions when this is useful. I have worked in schools and staff often feel comfortable with Excel but wary of databases.
It was often useful to allow them to formulate their data in Excel and support them in converting the Excel file to Access. As well as their comfort this encourages staff to structure their data collection more efficiently - particularly when you demonstrate data mining that they had never anticipated! Excel also has native import/export - almost unlimited formats to interface with many tailored SQL systems.

@rjfandre You're totally right. Even professional engineers I've worked with have a phobia about Access. (The Bill of Materials being a prime candidate for Access.) However, using Link Tables you can use Excel as a front end for Access. Conversely with Excel Basic you can query an Access database. When you're working with the typical unadventurous and lazy user you will save yourself a lot of tears by letting them plod along in Excel!

I agree with your sentiment on the reasons Excel is not the best suited product for database use BUT I have found as it appears have others, that IT departments do NOT like staff using Access anywhere near a Network and definitely have phobias about them. I worked for one of Australia's largest employers and this was exactly the case. So although I used Access I didn't advertise it until I had well and truly got things up and running as otherwise I would be getting pressure to desist. Mind you I still found that most people know how to use Excel and very few can handle Access so for small databases I find it is just as easy to give them it in Excel.

You're absolutely right -- and frankly, Excel is easier to use than Access, so I understand why people lean on it heavily even when Access would be a better choice for their project.

I don't necessarily think that's true. This would depend upon exposure and need.

As as aside, what are your thoughts as to the revised interface in Excel (2003 menus vs. ribbon GUI)?

I came to access after being a Filemaker Pro user ... and I hated Access ... Still don't like it but was forced to use at my job. I had to fight the temptation to use Excel instead.

For a novice user ... Access is baffling ... Heck, it was baffling for me and I understood databases. And let's face it, most novice users don't have a great need for a relational database and Excel works just fine for them.

The problem I have seen time and time again is not the tool, but the user trying to use the tool to do something it wasn't designed for. I have seen many business users who don't know a database from a hole in the ground try to use Excel in that vein simply because they have no idea how to use a better but more complicated tool. Excel requires almost no training to start using, whereas even a basic database product like Access does. And instead of being willing to admit that they are trying to use a handsaw when a tablesaw exists, they hack through things (literally) instead of taking the time to learn how to set up and use the tablesaw.

When the only tool you have is a hammer, all problems present themselves as nails.

Here's how I tell which version of the worksheet I'm looking at. I have three standard items in the first row: "Created", "Run Date" and "Run Time". The cell to the right of Created is the date the worksheet was created. The cell to the right of Run Date uses the =today() function to enter the current date. The cell to the right of Run Time uses the =now()-today() functions to enter the current time. When the worksheet prints, each copy has a date and time stamp that let's me know which one I'm looking at. I put "Filename" in the second row so that I know the file that created the worksheet. The filename always starts with the the date YYMMDD so I can track revisions: 110627_SAMPLE_FILE_NAME would be the June 27, 2011 version of the file. Meaningful file names are important. With 255 characters available, I try to make the filename very detailed. This is especially helpful when sharing files and during file restoration.

Why not go that one step further and use colour to highlight whether the workbook has been changed since the last save. For a workbook with fairly complex functionality (mostly lookup, with some update), that can be a lifesaver for an unsophisticated user.
WtJ

I would suggest for tracking purposes that you use a Custom Header or Footer with Filename, Date and Time codes. That way regardless of which section of the spreadheet you print out or how many pages the printout extends over each one will be identified.

Thank you. I only ever truly learned Lotus. Microsoft just had to come up with their prototype known as Buddha..................... ( Buddha will assume the Lotus position.....). I can do Excel, and have designed spreadsheets that get used throughout the State. but Excel keeps getting changed. I have a neurosis........................ I also have a twisted sense of humor!

I can't tell you the number of Excel spreadsheets where someone has to change two cells because the same information is in two different spreadsheets. If there is one value, it is entered once.

And sometimes this rule is tricky. Consider this: you have a spreadsheet that had individual data. You take averages over a given timeframe. Some people will enter the average, once it has been calculated, on a separate page. Time goes on. Some of those individual data pieces get updated, but because the average was "re-entered", that information is frozen. This happens all the time with novice Excel users.

Great advices and many instructives comments !
Further to atomize, I split dates into separate columns of days, months, and years. Otherwise whenever changing country, dates are transformed into varied formats DDMM or MMDD or whatever. Import-export dates from text files further can be particularly troublesome. The trick to overcome Excel's automatic date formatting is arcane, I ended up atomizing the dates.

I think this is a classic that was not mentioned. You can easily add a column in the middle and forget to change the formula. It will still LOOK OK. I made a BIG mistake accepting a spreadsheet set up that way.

Susan - you put together a nice, organized list of 10 tips. I like it!

On the subject of using multiple worksheets, let me add that I recommend giving each workseet a "meaningful" name e.g. Budget.

I also recommend having a purpose for each worksheet - e.g. Data Collection, Data Analysis and Presentation of Information.

Danny Rocks
The Company Rocks

Spam blockers tend to focus on the word "screw" ... more's the pity, since the article was worth the while of retrieving it from the junk folder.

When someone sends me a spreadsheet that includes empty, headerless columns that they have shortened the width on for "formatting", I initiate the following sequence: 1) Print the document out 2) circle the headerless spots in red ink 3) Soak the document in desk-vodka 4) Tie it to a rock that is kept around for this specific purpose 5) set it ablaze 6) launch "tutorial" into offending cubicle 7) collect rock.

These 7 steps guarantee that spreadsheet integrity will be maintained in your organization. Excel tables are not for display, so don't put in headerless columns that you have shortened the width on in an attempt to "format" your document. Excel is a calculator, and non-contiguous data is generally not sorted together. Sometimes, Excel will sort all data columns together, but usually it counts the empty column as a break in the data. Then, when you go to sort a column, all of your rows become incorrect.

And these are the same people whose Word documents are replete with [tab][tab][tab][tab][tab]--a tab is not a big space and ^p^p for vertical spacing. A paragraph mark is neither a carriage return (if you remember typewriters) nor a vertical space.

I'm still pondering whether the Molotov cocktail is a bit too extreme...Nah!

11. Use direct references to unprotected cells on a spreadsheet intended to be used as a form for general use. If you think a protected cell on a protected Excel Worksheet cannot be changed, you will learn otherwise when someone enters a value in the wrong unprotected cell and chooses to cut and paste it to the correct cell. At best, the form will suddenly show all kinds of errors and be obviously unusable (unless the person knows Excel well enough to press Ctrl+Z before a save or use of a form control). At worst, the form will produce incorrect results with no indication that something might be wrong (this can be very bad indeed).

Protected or not, Excel automatically updates references when referenced cells are moved; and cutting and pasting one referenced cell over another always replaces references to the destination cell with "#REF!".

As long as (spreadsheet) size doesn't matter, the best way to do this is probably to duplicate an area of unprotected cells in a (later to be) hidden, protected adjacent area, and to make all references to entered data using references to that protected, hidden area. The cells in the protected, hidden area copy the unprotected entries using indirect references to the unprotected data.

Unless a form needs very few "protected" references to unprotected cells, using indirect references to the unprotected data everywhere can make the form extremely difficult to change (e.g., update) or debug. You can use a computed OFFSET reference to copy the unprotected data to the hidden, protected area. It's less messy than computed INDIRECT references; but you should design the computation so it will allow moving the two areas relative to each other.

Even though using the above technique will prevent users from damaging the function of the form, be aware that it will not prevent them from making changes to the form that you don't want. Formatting is disabled on a protected Worksheet, but the formats of unprotected cells on that sheet are not protected. If the user copies a protected cell to an unprotected cell, the formatting of that protected cell will be copied, too; so if you put a lot of effort into the appearance of input cells, your effort may be in vain.

Can anyone think of any reasonable use for a protected form with unprotected cells that would make this behavior appropriate? Unfortunately, as bad as it is, Excel is better in this respect. Office Libre behaves similarly, but is less likely to clearly show that the form has been damaged. For example, if the original protected equation is =A1+A2+A3, and the user cuts and pastes A2 to A1, Excel will change the (not) "protected" equation to =#REF!+A2+A3 (an obvious error), whereas Office Libre will change it to =A1+A1+A3 (the wrong answer).

I haven't used this in a while but think it is sometimes a good idea but not for the reason you give. If you save the workbook with the current cell as cell A100 then when the workbook is opened it will be at cell A100 and the users won't have to scroll anywhere if that is the row where the totals are. If you saved it with A100 as current and the totals are at row 1 then guess what they will have to scroll, unless of course they know how to do ctl+home but how many do?

The reason I think it can be good is if you need to add rows then you don't have to insert them you can just put in the extra data, of course you do need to make sure your formulas pick up the new data.

maj

Great list. Getting past the basics, there are plenty more gotchas in Excel that end up wasting time and causing issues. Would love to see a best practices list for preventing excel file corruption, such as avoiding corruptions (shared spreadsheets on the network, leftover data connection corruptions when importing data), There are also time consuming traps even the experienced fall into such as dealing with merged cells, and vlookups that don't return values if the text string is too long or contains certain characters that the function does not match on. I would love to be pointed to an article that has more best practices on the subject. Good job.

We offered clients the opportunity to send us data as Excel spreadsheets for importing into a database. We specified column names and column order so we could automate the process. One client's data kept blowing the import routine, even though the data looked OK visually. It was only after several fruitless days that I realised that the colum letters had gaps - e.g. Column N was followed by column T etc - they had extra columns in their source data and rather than delete these columns, they just hid them, assuming that if they couldn't see them, our computers couldn't either!