MVP #52: Very Important Rules To Follow When Making Reports In Microsoft Excel

Excel is different from every other Microsoft Office program you use. Most of the reports and analysis you do with Excel will be used some day in the future for another report or analysis.

To become an expert in Excel, you have to always work with the end in mind. You have to create your Excel documents in such a way that you can easily use them for some bigger reports in the future. And there are some general rules I'll recommend you work with to achieve this.

1. Always use a compact table structure for entering you core data in Excel. 
This means using the minimum number of rows and minimum number of columns. Example of a compact table and non-compact table is shown below:



Same table but not compact, shown below.



In the non-compact table example, you can delete rows 37 and 38 without deleting any data in the table. The are meaningless spaces.


2. Use descriptive names for your column headers and row headers.
Be as descriptive as possible in naming the fields in your table, make it easy for anyone who will view your table to understand the information it convenes.

Below is a table with field names that are descriptive enough for anyone to understand the information the table convenes.


Below is a table that has headers that are not descriptive enough. It’s hard to figure out the specific information in the table. Is it a table of revenue or expense? Are the companies clients or suppliers? What year do the months represent — January 2014 or January 2015? Whomever you send this report to will call you back for a detailed explanation of what you intended to report.



3. Name your Excel sheets
Don’t just go ahead with the default names — Sheet1, Sheet2, … — rename the sheets to reflect the contents of the sheet. This makes your work better organized and future use more convenient.



To rename a sheet, right click on the sheet name (the default names are Sheet1, Sheet2, …) and select rename. And as you see below, you can change the color from the default too. 





4. Also use descriptive names when renaming the Excel sheets.



5. Avoid putting too many tables in one Excel sheet. 
Best to keep just related tables in the same sheet if you must put more than one table in the sheet. It makes naming the sheet easy and straightforward.

6. Use same naming convention and table structure across all similar Excel files, especially weekly, monthly and yearly reports of the same data. Don't name January Budget file as Budget_Jan_2015 and name February's as FebruaryBudget2015. It should be Budget_Feb_2015.

7.  Don’t use CAPS excessively. It makes your reports very unprofessional.