Excel Tips to Produce Quality Worksheets

Excel Spreadsheet Complexity, Speed, Overhead and Data
I love my work as a Microsoft Office VBA Freelance Developer

 Excel tips for quality worksheets

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

Build Better Excel Worksheets
(Excel Tips to improve the quality of your work) 

Here’s an amazing fact, there are at least 750 million Microsoft users in the world. I can attest to its global foot print because each day my website gets hits (views) from all over the globe.  Its uses range from creating a simple personal budget to an Investment Bank modeling exotic derivatives.  Why is it so popular? It is flexible, fast, unlimitedly customizable, powerful and it is written in kind of universal language.  Let’s face it Excel and Access have been and are the world’s new standard. 

Excel Tips that can improve the quality of your work

Specific Excel Tips For Quality Worksheets

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. 

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

Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets.

Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” 
If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me

Leave a Reply

Close Menu

Excel and VBA Craftsman offers experienced VBA Development: Complete GUI Driven Applications, Automated Reconciliations, VBA ODBC Database Data Mining, VBA Excel, Access, Outlook and Word Customization. VBA Custom Functions, VBA Concept Assessment, VBA integration of MS office applications into powerful single seamless solutions, Excel Help, Automate Excel, Excel Automation