How to Build a Good Excel Spreadsheet
A well thought out and well-built Excel spreadsheet can be worth far more than the cost of the labor
required to create it. On the flipside, a poorly designed Excel spreadsheet can create losses many times
over that cost.
Here are some principles and techniques to use in creating strong, functional spreadsheets that earn their keep and work to your best advantage. Please note that the comments in this article are intended to serve as basic guidelines and suggestions, not hard and fast rules. In approaching spreadsheet creation, there are several points to emphasize. First, no set of principles or standards can guarantee freedom from error. The design, maintenance and operation of spreadsheets are still carried out by people. The business environment in which spreadsheets are created, maintained and used can be just as important as the content of the spreadsheet. A spreadsheet may not only convey facts and figures, but also attitudes, opinions, biases and projections. Don’t forget that users may “read between the lines” in a spread sheet just as much as they may do in a letter. Make sure that your spreadsheet represents your best practices. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job. Determine what role the spreadsheet will play in your business, and plan your spreadsheet standards and processes accordingly. If your company does not already have one, adopt a standard for your organization and stick to it. Make sure the creator of the spreadsheet knows your company’s style, and understands the spreadsheet’s purpose. Is it for sales? For accounting? Inventory? Scheduling? Many spreadsheets are used by several departments. Make sure the spreadsheet can be read and utilized by all who will use it and that it is consistent with your company’s practices or your personal needs if you are setting a spreadsheet up for your home finances. Ensure that everyone involved in the creation or use of the spreadsheet has an appropriate level of knowledge and competence. Give the spreadsheet users an opportunity to provide input sooner, rather than later, to save on revisions. Don’t limit input to just upper management. All levels of users may have important contributions to make that will benefit the company as a whole. As the creator (author) of the spreadsheet you need to know both the program and the needs of the company to incorporate the information into the spreadsheet. Know who can help and where additional information may be obtained. Make sure the data is accurate and current. Consider if you need to incorporate past history. Perhaps the author will need to bring in raw data from different sources and formats into the new Excel spreadsheet. Check to make sure the spreadsheet will be compliant with regulatory laws if applicable. Consider if data from different program sources and formats will need to be converted into the Excel spreadsheet. Also look at security, privacy and formatting issues when converting the inserted material. Metadata may need to be removed. Tables, for example, may be shared by Word and Excel. Identify the audience. Think about who the readers and users of your spreadsheet are and will be. If a spreadsheet is intended to be understood and used by others, the design should facilitate this. The sophistication levels of internal and external audiences may be different. Be careful in using abbreviations, nicknames, acronyms, and terms of art. Sometimes plain language is best, or terms should be explained or spelled out the first time they are used, but can be shortened after that. Clarity saves time and eliminates misunderstandings. Organizing the spreadsheet in a logical manner aids in efficiency. Can the reader quickly find the information he or she needs? Do you want to organize the spreadsheet chronologically, alphabetically, by topic or by the steps in a process? If the spreadsheet covers new territory, perhaps it requires instructions. If the spreadsheet has many parts, a table of contents, index or dashboard will help users find the information they need faster. Make sure that the spreadsheet is readily searchable. Design for longevity. How far into the future will this data be used? Think of it as a life expectancy. A well-planned spreadsheet should allow for growth and expansion. Also think about possible links to other spreadsheets or data sources. Focus on the required outputs. Separate and clearly identify inputs, workings and outputs. A color coding scheme for the cells may be helpful here. It is important to visualize the final product and the user’s environment. Consider if the data is to be produced or represented in the form of charts, graphs or tables. Will the spreadsheet compare data from one year to the next? How will the spreadsheet be published (on paper, electronically, or be sent to another program) may also impact the creator’s decisions. Be structurally consistent. Check to see that the styles match up throughout the document and that it conforms to company practices. If there are many columns, consider republishing headers on each page so the reader doesn’t have to flip back to identify the column on subsequent pages. It may seem unimportant, but putting the document date, edition, and page numbers in easy to find places helps users know if they are in the most current version and helps them find an older one when needed. Simplicity is golden. Avoid using advanced features where simpler features could achieve the same result. Consider if Macros can help. Macros are instructions that tell the Excel how to do something. One of the best things about macros is they can run processes a business needs in a fraction of the time it may take someone to do by hand. Be consistent in the use of formulas. Keep formulas as short and simple as possible. Don’t embed anything that might change or need to be changed in a formula. Set formulas up in specific cells and reference them. Perform a calculation once and then refer back to that calculation. Try not to repeat calculations that can be referred back to a single cell. Have a system of backup and version control. This should be applied consistently within an organization. Rigorously test the workbook – have more than one person test it. Also test both the normal and abnormal limits of the spreadsheet to make sure it can withstand unforeseen circumstances. Double check your work. Make sure every link routes to the correct cells. For example, does each formula cover the correct range of cells and calculate the data accurately? Build in checks, controls and alerts from the outset and during the course of spreadsheet design. Be sure you have appropriate error handling so that if something does go wrong, the entire spreadsheet is not lost or corrupted. Take the user’s comfort into consideration. Ensure that the worksheets print out cleanly and are formatted properly for the paper size and printer. Can they be read without squinting? Are the page breaks in places where they keep information together that needs to be together, or do they split important pieces of information apart? Do portions of the spreadsheet that may have been copied in match up with the new material, or does your spreadsheet have that “cut and paste” look? Make sure all the workbook pages in your document are clearly marked. Protect parts of the workbook that are not supposed to be changed by users. Keep a back-up copy. Make sure someone else in the company can maintain and update the spreadsheet if the creator is not available. For more information on how to find the right MicroSoft Excel Developer contact Warren Schultz at Email : warren@tapsolutions.net or call him at 818-281-7628. Website : www.tapsolutions.net Click Here for interesting Articles about Ms Excel is used to become more effecient and productive which will save your company time and money. Click Here For Case Studies and Solutions where companies have become more productive and effecient using Excel. Prior Article How_To_Build_a_Good_Excel_Spreadsheet Next Article Microsoft Excel can be fun and helpful #TAPSolutions_published_article |