Excel Best Practices for Small Businesses
If you use Excel to organize your finances, track inventory or make important business decisions, then data accuracy is vital for your business. This month in the Small Business Monthly “Financial Fitness” column, Karen Stern, Partner in Charge, Entrepreneurial Services Group, shares some tips to help you avoid spreadsheet errors.
Excel is an amazing tool. It’s affordable, many people have experience with it, and business owners can directly control data.
Data accuracy is critical to maintain, though. If a spreadsheet is broken, it can give bad results. Most people don’t have time to double-check all formulas every time updates are made. However, best practices for maintaining data accuracy can save time and your peace of mind.
Consider these best practices:
- Create conditionally formatted cells that reflect the results key formulas are supposed to have and produce specific messages indicating potential data or calculation errors as they arise, (e.g., Asset Subtotal does not match SUM(F5:F60)).
- Keep these notification cells visible, but out of accidental editing range.
- Protect cells that should not change.
- Add input and error messages to validate data entry (e.g., “this field should be a number” or “this is not a valid date”).
- Password protect spreadsheets so others have viewing privileges only.
- Pinpoint related information by clicking Trace Precedents and Trace Dependents to identify the cells influencing and being influenced by the cell you are in.
- Review spreadsheet macro results when the creator is no longer with the business or when changes have occurred with data inputs or output expectations.
- Keep key reporting functions in your accounting system. The time spent to build frequently used reports in your accounting system is worth minimizing human error.
You may also have that one spreadsheet that takes 10 minutes to open and has 100 tabs. If your business is outgrowing Excel or needs more advanced spreadsheet strategies, contact Joe Montes, Data Analysis Manager at Brown Smith Wallace, at email@example.com or Karen Stern, Partner in Charge of the Brown Smith Wallace Entrepreneurial Services Group, at firstname.lastname@example.org.