Excel Workbook Structure
Input Separate from Output
Keep the raw data on a separate Excel sheet from the report. The datasheet will often be hidden from the user to prevent accidental corruption of the data’s structure and/or connective services.
By separating the report from the data, different levels of protection and access can be granted. This ensures that users can interact with the data as needed without granting access to sensitive areas of the workbook.
Define a Purpose for Each Sheet
Each Excel sheet should have a defined and probably singular purpose, like the datasheet versus the report sheet. Separating each component of the workbook into separate sheets provides greater control over access and delivery.
Just as we have a sheet to store the raw data, calculations that aggregate the raw data in preparation for service of the report sheet should be kept on a separate sheet as well. It is likely that the calculation sheet will also be hidden from the user to prevent accidental corruption.
Have Control (Sheet)
Finalizing an Excel Report is often temporary. Requirements change; business requirements change. Workbooks oftentimes need updating to change with the needs of the day.
Having a control sheet to document the changes will help you remember what has been updated and when the update took place.
Documentation is never fun, and there always seems to be more important things to do. Good documentation will bring rewards later once time has passed, questions come up, and memories have faded.
Add Instructions
Using an Excel workbook created by someone else can often lead to questions about the workbook’s purpose and operational process. In what order is the data input? What is the order of the calculations? What are the dependencies between the calculations? Where does the data come from and how is it refreshed or updated? These are all questions that a user will ask sooner or later.
No matter how involved you are in the development of a workbook, if enough time passes, you will likely not remember the answers to all of these questions.
Adding an instruction sheet that outlines the purpose and use of each sheet will help users find the answers to these and other questions when the Excel workbook developer is not available.
Keep File Backups
It is often the case that it’s not a matter of if the Excel file is lost or corrupt, but when the file is lost or corrupted.
Excel has a robust file recovery system to aid in cases where files become corrupted. However, (accidentally) deleting a file with no backup copy will often require a complete rebuild of the file from the ground up.
If your file is essential to day-to-day operations, or you are planning on implementing a change to the file that could prove disastrous if not performed properly, make certain you have a proper backup of the file. Creating additional backups, both local backups and offsite backups, are a great way to have peace of mind when things don’t go as planned.
Visual Design of Worksheets
Prepare for Print
If the user is likely to print any of the Excel worksheets, preview each sheet in Print Preview mode to ensure proper page orientation, page numbers, scaling, etc.
Adding the file’s name and location to the footer will save time when updates are needed to the file.
Page numbers are a must for Excel reports; not just the page number, but also the total number of pages. This ensures we aren’t missing any “last” pages.
Consistent Color Coding
Don’t go crazy with colors.
This is a perfect opportunity to think “less is more”. Stick with 2 to 3 color choices that denote specific messaging. Try to keep the colors more muted than bold. Ensure colors have consistent meaning: color code by department, profit/losses, region, etc.
Consider shading cells for data input differently from cells that perform calculations. You can shade cells to indicate input cells…
…or, shade cells to indicate calculation cells.
Format for Appreciation
Make sure your hard work behind the scenes doesn’t go unappreciated because the public-facing side of your spreadsheet is disorganized or fragmented in its messaging.
Take time to present your results in a clear, easy to read and easy to understand format.
If anyone asks, “what does this mean?”, it means your report needs some adjustments.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
