by Karen Stern
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 provide peace of mind.
Consider these best practices:
1. 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]).
2. Keep these notification cells visible but out of accidental editing range.
3. Protect cells that should not change.
4. Add input and error messages to validate data entry (e.g., “this field should be a number” or “this is not a valid date”).
5. Password-protect spreadsheets so others have viewing privileges only.
6. Pinpoint related information by clicking Trace Precedents and Trace Dependents to identify the cells influencing and being influenced by the cell you are in.
7. Review spreadsheet macro results when the creator is no longer with the business or when changes have occurred with data inputs or output expectations.
8. 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 jmontes@bswllc.com or Karen Stern, partner in charge of the Brown Smith Wallace Entrepreneurial Services Group, at kstern@bswllc.com.
Karen Stern, CPA, (kstern@bswllc.com), partner in charge, Brown Smith Wallace Entrepreneurial Services Group, provides tax and accounting services for companies ranging from start-ups to $20 million in revenue.
Submitted 5 years 176 days ago