We recommend the following six best practices when writing your reports:
- Use cell references to enter data into formulas. Using cell references in formulas allows the formula to update when the data is changed at a later date, without having to manually edit each formula. This method makes modifying and maintaining your worksheet easier.
- Use account ranges in your reports to ensure new accounts being added to the general ledger are included in your reports.
- Use Conditional formatting – with proper visual design, you will be able to discern ‘good’ or ‘bad’ values in seconds.
- Avoid the extraneous – remove any ‘noise’. If it doesn’t serve a purpose in the spreadsheet, take it out. That includes prior old data, prior layout attempts etc. Many times even a comma disrupts formulas.
- Use a consistent naming strategy with versions and save the template using Report Manager often. For example: If you are working on updating the 4th version of your income statement spreadsheet (in other words, you have 4 different copies of the standard report) use some type of naming sequence to organize your reports. Name and save the workbook as Income Statement 5.0 before you start making your changes. If something goes terribly wrong, you can always revert to the old version.
- Set Freeze Panes in Microsoft Excel so you can easily scroll around the worksheet without losing view of report headings, etc.
Also keep the following in mind when writing your reports:
- Consistency – spreadsheets have a consistent structure and look, making sharing easier
- Clarity – spreadsheets are clear and structured, reading like a book, navigating like a website. This makes them easier to share and audit.
- Efficiency – spreadsheets use efficient formula structures. They will be easier to use and share, saving time at key points in critical processes.
- Flexibility – models are easily changed and extended without the need for a complete re-work
