Creating Excel Reports Part 2 – Get Your Workbook Ready for Data Entry

In the previous part of this tutorial, I went through how to design the layout a professional summary report page, being careful to making it easy to read while packing lots of information into just a single page. At this point it is entirely possible to use this as a report template; you could enter all of the data into each field one at a time, calculate profits by hand, and then save a new excel file for each day. Unless your tracking a lemonade stand, you are going to need a much more efficient method of report generation. One of my cardinal rules for creating summary reports is that the main report page shall only require the current date to be entered into it; all of the rest of the report should generate itself. All information should be pulled from raw data stored in the other sheets of the same workbook.

Keeping your raw data separate from the main report offers two main advantages. Your raw data stays raw, that is it never gets directly modified. If a formula error or other mistake is made, your old data will always be there unchanged and is still safe for re-analysis. Secondly, this way of storing and referencing data allows you to regenerate a report from a past date instantly, all from the same report file. So if you ever need a copy of a report from a previous week, comments and all, you only need to change the date at the top of the page and then everything relevant to it will be displayed for printing. Now lets see how we can get your workbook ready for data entry using the following steps.

Step 1 – Setting-Up New Sheets For Data

The first step is fairly easy. We need to create a new sheet for raw data. You can use as many different sheets as you see fit. I personally like to make one for each major type of data, and so in the case of our ongoing example of Joe Transport, I am going to use two. First I want a sheet that holds the comments and other miscellaneous information such as the weather and workforce, and then I want another sheet where raw shift data can be entered, one line for each employee’s completed work for that day.

Starting with the miscellaneous data sheet, I choose to design it in a vertical sense. What I mean by this is that it will be a table that gets updated daily, with a new column holding the information for each day:

As you can see from above, the first column should be the labels for each entry field. Go ahead and set up a column for each day, I usually prepare a month or so at a time. Notice that the rows that hold text have been stretched out, this is to make it easier to display while you’re filling it in. You can set this up in any way you wish, with whatever information you deem important for your report.

Bonus Tip: To make the first (label) column stay put when scrolling with the rest of the (days) columns, click on the view tab, and select Freeze Panes à Freeze First Column. Now when you approach the later days of the month, you will still be able to see the label column on your screen when you scroll over.

Step 2 - Setting-Up Shift Report Data Entry

The next data tab we need to set up is the most important, as it will be used to calculate and track our financial information. It will compute the total revenue based on vehicle operating hours (using the generous simplification that a given vehicle generates a certain revenue per hour), as well as the total expenses based on total labor hours that must be paid out to employees on payday.

This data table is to work differently then the miscellaneous one. I find it easiest to track this type of information vertically, with a new row for each employee’s production for the day. Make sure you use the first column is reserved to enter the current date, but besides that it’s up to you to determine what information you need to collect. You can see my choices below, with a couple lines of example data included:

As you can see I really like to keep my daily raw data page layout as simple as possible. You may be wondering what all of the arrows next to my data labels are. These arrows are for a very useful feature called “Auto-Filter”. Once you have this sheet ready for data entry, highlight all of your column labels (row #1), then look up to your ribbon and go-to the Data tab,  then select Filter. You will now see the arrows. You can click these arrows anytime to sort the data below alphabetically, numerically, or select it to display only the rows that contain certain values in a chosen column. You can read more about this elsewhere if you really need too, but once you try experimenting with it you will very quickly figure out how it works; it’s quite simple.

This brings us to the end of this part of the tutorial. In the third part we will start to set up the report to organize and reference data from these entry sheets we have created using formulas as well as basic calculations.

5 comments

  1. Dazo says:

    I’ve always wondered what that Filter button was for, but to be honest I never got to actually try it out, thanks to your review I can actually ease my work a bit now. Anyway, great tutorial, I’d surely use a trick or two from it when creating my montly payout reports.

  2. kesly13 says:

    Thanks for this! Now I know how to design my excel sheet in a professional layout. It’s looks like now its easy for me to set-up my shift report data entry. Now I understand the functions of each labels in a data sheet. This is very helpful. This should be shared to everyone! Keep up the good work!

  3. I think the best of reports is that what you cant change original data. This excel reports are very easy for using, and this is very good explained.Nice job!

  4. cristi1595 says:

    This helped me a lot to improve my skills to working with excel, i will watch all your tutorials.

  5. Excel Reports says:

    It is very important to build a spreadsheet with end report use in mind. Keeping the layout as simple as possible will greatly reduce your headache down the road.
    -Conner