Like hiring processes and company publications, spreadsheets benefit from standardization and consistency among all staff. When employees follow company-wide conventions and formats, the documents they create are likely to be better understood, easier to read and more reliable. Additionally, others in the organization will be better able to edit worksheets designed according to a common set of rules. Learn how to develop guidelines for your workplace based on best practices for the use of Excel in your business.
Use
Excel offers enough flexibility and capabilities to make the program useful for a variety of purposes, such as presentations, storage of information, data analysis and more. However, Excel is not necessarily the best choice in all scenarios. Although the software is capable of storing large amounts of data, other programs such as databases are better suited for that purpose, particularly if you would like to perform complex searches or make comparisons of the data.
- Before starting a document, you should evaluate whether a spreadsheet is the most appropriate tool for the task.
- Design templates and workbooks for longevity; in other words, spreadsheets should be clearly understood and easily used by future co-workers.
- Train staff so they have the appropriate knowledge to use company workbooks effectively. Keep in mind that staff will have varying levels of knowledge and skill with Excel; use the simplest formulas and structure possible.
Formatting
Try to achieve a consistent look and feel. Specific formatting preferences you might implement include:
- Use of a standard font, such as 12-point Calibri, which was designed to be readable for both text and numbers
- Turning off the gridlines, which add clutter
- Separating and clearly identifying input fields, calculations and output cells, perhaps by using yellow-filled cells where data input is required and placing calculated results in blue-colored cells
Information
A workbook’s purpose might be clear to you when you create the document, but several months from now your memory might not be so clear. Similarly other users may not share the same set of assumptions or body of knowledge. To make the documents more useful, both now and later, consider the following:
- Every workbook should include a documentation page with the title, author, creation date and description of the document’s purpose.
- Larger workbooks, especially those with multiple tabs, probably would benefit from inclusion of a table of contents page with links to worksheets.
- Add details such as author, summary and keywords to the File Properties metadata.
- Gather all assumptions used throughout the workbook in one well-organized list in a clearly labeled worksheet.
- Do not embed assumptions or data that might need to be changed within a formula. Users cannot easily view your formulas, and if a value must be changed, the process will be a tedious one if someone must locate that assumption buried in a formula somewhere. Clearly identify the assumption or variable, then reference that cell in the formulas.
Security
- To prevent accidental or unwanted changes to either the structure or the data of your workbook, protect the parts that are not supposed to be changed by locking individual cells or tabs.
- Decide on a system of backup and version control as well as shared naming conventions for files.
- Use error-checking formulas to help prevent input errors and act as controls for data entry.
Contact ExcelHelp to learn more and schedule onsite Excel training for your managers, administrators and staff.