If you use Microsoft Excel in your job to create large reports and analyses, then you may be interested in learning some tips on keeping your files manageable and mistake free. Excel is an extremely powerful tool, but without some best practices it becomes easy to create a complete unworkable mess of your spreadsheets and reports. This article outlines a few best practices that I noticed in my career as a financial analyst for several years. Hopefully after reading these tips you will be more prepared to plan and execute your models and reports.
Begin With the End in Mind
One of the problems with Excel’s ability to add and insert new worksheets, columns, and rows to an existing file is that it starts to seem simple to add those “one more thing” type items to your reports. How many times have you developed a report and decided midway that you wanted to also include some data from so and so’s department. So, you just add a new worksheet that contains the data and adjust your formulas to add it to everything that was already there. That’s a problem though, because now you’re adding layers of complexity in managing any changes and updates you need to make to your file in the future.
Keep All of Your Raw Data in One Place
To avoid the Rube Goldberg effect of cobbling on new worksheets in the middle of your process, try to commit whenever possible to housing all raw data on a single worksheet, ideally in a single flat table. It’s OK if you decide later that you need to add data from another department, as long as that data is properly included in the sheet that’s already being used as the source for the other worksheets. By keeping the discipline of housing all raw data in a single sheet you eliminate a few frustrations down the road.
Only Enter Data Once
If you take my advice to include all data on a single sheet, you will eliminate another very common problem – redundant data entry. I’ve seen a lot of spreadsheets where the numbers had to be entered into multiple places of the workbook every time the report needed to be updated for a new quarter or month. Putting raw data in one place means only entering it in one place – this eliminates the inefficiency of redundant data entry as well as reducing the possibility of typos.
As you can see, it’s important to plan out your spreadsheets and reports before you begin to assemble them. Envisioning what your final report will look like and then crafting the layout of your raw data accordingly will save you time and effort, and most importantly eliminate errors.