Think of Excel as a woodworker’s tool that you need to master to turn out fine timeless furniture. Anyone can use a hammer and a saw, but how many can create an ornate Philadelphia highboy (see image) that is still popular 200 years after their design. As an auditor and controller I have reviewed countless workbooks/ spreadsheets and have seen the good, the bad, and the ugly. Here are some Excel Tips that I think will help you do work you can be proud of.
1. First and foremost turn the Excel auto save feature on and test that it is working. I know, the auto save feature doesn’t seem specific about worksheets but it can be. Example: You’ve got a deadline and your is head down and put three hours into a spreadsheet and bam the power goes out or the network goes down. You discover you haven’t saved it since you started it. You think Oh My God. I know, because it has happened to me. With the limited time to get the task done, chances are that worksheet won’t be the polished work you wanted. That’s why auto save is a must.
2. Your spreadsheets “should be able to stand on their own two feet”, what this means is someone should be able to quickly understand where the data comes from, any calculations and how it flows. This only happens one way, to annotate thoroughly. It may be that you have to pick up that spreadsheet two years from now. Will you remember how it all worked? Likewise, don’t include any irrelevant information on a spreadsheet that may raise needless questions. If you want to see what happens when someone builds a application and fails to properly annotate it, keep it free of extranoeus data and have a backup may i reccomend my post
MS Access. ‘the perfect storm’ and good old Joe
Specific – These Excel Tips that can improve the quality of your work
1. Do not reinvent the wheel. Before you set off on that mission to create the perfect spreadsheet, ascertain if one already exists. The benefits are management may be used to the format, it will save time and you can focus on getting the content correct. Perhaps with the time saved, you can make your mark by improving the existing spreadsheet.
2. Stay away from overly colorful spreadsheets, they are the hallmark of a unsophisticated user. If you were selling your home you would want the walls in neutral colors because it appeals to the most potential buyers. Focus on content and keep it concise and muted.
3. Never hardwire a number where the user would expect a formula. If you need to force to a total, do so conspicuously. The user behind you may fail to note the hardwired number and generate faulty results (from then on it will be known as your bad spreadsheet)
4. Avoid overIy complex nested functions. If there are some complex calculations, consider an intermediate calculation. Simple is often better and always safer. The more transparent the spreadsheet, the less likely undetected errors will get past you.
5. If possible, incorporate self or integrity checks in your spreadsheet to be sure the spreadsheet ‘proves or ties out’, that it works mathematically and all dollars or widgets are accounted for.
6. If the spreadsheet contains huge amounts of data consider if it is the right tool. Would access or another database be more appropriate.
7. Carefully review your work and have someone else look at your work. A second set of eyes could save you from some embarrassment.
1. If the workbook is evolving, consider the use of versioning with a date component: i.e. Mortgage Analysis V10-01-19.xlsb, and have the version file name displayed on the sheet. In that way any paper copying display the version.
2. If there are users other than the creator using the spreadsheet, should certain cells/sections be security protected?
3. If the workbook is on a common lan, should it be password protected? If it gets backed up, what are the procedures and difficulty of getting it restored if it gets corrupted?
Please feel free to browse my site for other Excel tips and tricks for more Excel Tips that can improve quality of your work.