Creating Excel Reports Part 1 – Designing a Professional Looking Report

When it comes to generating summary-type reports on a continual basis, such as a Total Production Summary, a weekly summary of revenue/expense activity, or similar, the best option is almost always Microsoft Excel. With a well organized and programmed Excel workbook, raw data can be entered directly into the sheet at regular intervals, and reports can be automatically generated by simply entering a date. Over the course of several tutorials, I will demonstrate how to design your report to look professional, be well organized, and to be fully automated to pull and display the data that you want it to.

For this and future tutorials I will be using an example of a fictitious transport company I have named “Joe’s Transport”. This is going to be a medium-sized company, which runs transport trucks, taxis, and busses in its business model. I will create a daily report that will allow Joe to track his day-to-day fleet usage, employee’s hours, total acquired revenue and total expenses broken down into each service offered by his company. This will allow him to monitor every part of his business, all in one compact, neat and professional report.   

Step 1 - Determine Exactly What You Need to Report

The very first step to designing an effective and efficient summary report in Excel is to figure out exactly what your report needs to display, before you even open up the program. I find it best to physically list out the required sections and subsections on paper. For my example of Joe’s Transport, I figure I will need to see the total number of employees working and total labor hours, the vehicle fleet being used, and a detailed breakdown of revenue gained from and total cost of operation for each service. I also require some additional information about each day that might effect the workday, such as weather conditions and room to record any additional comments or relevant incidents.

Step 2 - Design The Layout

Once we have established what we want our report to display, we need to decide how we want to display it. Having a good layout is absolutely critical. You information must be organized enough to read and interpret, while also being clean and professional looking. For a situation like our example, I would try to limit a Daily Summary to one or two pages, max.

It is now time to start the Excel work. First of all, I am assuming you know how to do some basic Excel tasks, such as merging cells, changing the font, and setting up boarders. If you run into any trouble with things like this just check out Excel’s help files or search the web. I find it easiest to first set-up the header, or top of your report. In the top part you are going to need at least the reports title and the current date underneath. Try and use lots of columns, that way you are sure to have room for adding as much information detail as you need later, then can always be merged if there are any extras. It is very important to make the label “Date:” a separate cell from the cell that you will actually enter the date in, as this will be later used for formulas. I chose to also include the weather conditions since this would be an important factor of business in Joe’s company. A small company logo in one of the top corners will help make the report look more professional. Here’s how I ended up formatting my header:

For my layout I plan on printing in “landscape” orientation rather then portrait, it’s up to you to decide how you would like to print your reports, but make this decision right away, or you will regret it later. Also please don’t laugh at my MS Paint logo; it’s only for the example!

 Next we must decide how we want each report element to be displayed. I like to first split the report area in half from top to bottom, and then split each half up into blocks.  Now I assign a section to each of the blocks. Try to visualize your report like this:

I can now choose roughly where I would like to place each of my sections. You can decide how to fill these up however you wish, I like to start from the top making small headers for the first two sections, then fill them out and move on to the next two, and so on. Make use of merging cells to make a layout that is clean and works for you, separate each section with some attractive boarders. I try to use a little color, something that matches the colors in to business logo. Try and keep the number of different colors to a minimum, too many makes the report look tacky and unprofessional.

I filled in my different sections and finally ended up with this:

Yours may look absolutely nothing like mine and that’s great, as long as it is well organized and looks professional. Don’t be afraid to experiment and play around a bit. In the next tutorial I will cover setting up a new sheet in the same work book to start entering data into that will be referenced into and be shown in the report sheet that we just created.