3 must haves when automating reports in excel

Overview

Let me let you in on a secret, you only ever need 3 sheets to automate all your reporting and analytics with Microsoft Excel. Excel is by far the most popular reporting application in the world, some 500 million to 1.1 billion people use it daily for all sorts of wonderful things. In this article, you will learn an innovative hack for streamlining report automation, one that the team at dbrownconsulting pioneered more than 17 years ago.

The three sheets you require are your Data Sheet, your Control Sheet, and your Report Sheet. Here is how they all work together.

The Data Sheet.

Your journey to automation starts with Data. Regardless of your data source (direct entry, a dump from a database, a direct connection to a source etc) the sheet containing your data must follow a strict set of rules.  We call them the seven golden rules of data. If you can judiciously follow these rules, you are well on your way to automating all your reports.

The 7 Golden Rules

Rule 1: Your data sheet must have only one row of headings

Rule 2: There must be no empty rows

Rule 3: You should not have empty columns

Rule 4: No totals or sub-totals.

Rule 5: All dates must be in a single column.

Rule 6: No obstruction around your data sheet

Rule 7: Every unique data category must have its own column

A clean data sheet that follows all seven golden rules Excel

Read this article for an in depth look at these rules.

Now that you have set up your Data Sheet correctly following these rules you can now directly connect your Data-sheet to whatever data source you have. An excel superpower to help you with this is Power Query (more on this in a future article). If you are entering the data manually, then it makes sense to control and validate your data entries to minimize errors. We’ll look at the Control and Report Sheet next. 

The Control Sheet.

Imagine a scenario where you have shared your simple report with up to 10 users in your department, each may want to see different aspects of the report, so you end up building 10 different reports to accurately satisfy this need, which can be very time consuming and frustrating. Your Control sheet solves this dilemma by building the structures for an easy user interface so the users can build out the report they need themselves while still guaranteeing accuracy and flexibility.

The sheet controls how you or users view your reports in the report sheet. It can also control how your users enter data in the data sheet where data entry is a manual process.

One essential control in every report is the ability to vary reporting dates. At the minimum, report users need to select the start and end date of your report (day, month and year) and even identify whether they need a Month to Date, Quarter to Date and Year to Date analysis per their reporting reference.

Dates can be entered directly into Cells in Excel; see How to validate your cells in Excel for Date and Format Styles ***hyperlink that blog*** but a more secure methodology for entering dates in Excel is the use of “Excel Controls,” more precisely the use of Combo Boxes. Let us look at how to build one.

A combo box in report sheet

How to create Combo Box for your control sheet in Excel.

As the name implies, the control sheet controls how people use your automated reporting tool. Yes, you need to see yourself as a software developer, creating a solution that can be used again and again without the need to ask you for help. You can refer to the control sheet as the backend and the report sheet your users see as the Frontend of your solution.

As we said earlier, combo boxes are more secure for report selection. Watch this short clip to understand how to create combo box controls for your reports

At the end of the tutorial, you should have a control sheet that looks like this.

A control sheet in Excel

The Report Sheet

The report sheet is what your users see and interact with daily. Your job as the report designer is to ensure the report shows an accurate and updated number every time it is opened. The practice of emailing daily reports is old school, your reports should be saved in shared folder and should always contain updated information every time it is accessed.

In Excel you really have 2 main options for creating reports:

1. Formulas & Function based Reports: This gives you cell by cell control of the look and feel of your reports. It, however, requires longer design and execution time and greater awareness of Excel Functions. Your reward, infinite report design flexibility. The Control sheet really comes in handy here to help you control how users use your report creation. For more advanced reporting users you may wish to leverage the Excel Data Model (Power Pivot) and use Excel’s CUBE formulas and DAX. I will talk more on this in another post.
2. Pivot Tables based Reports: Pivot Tables are an excellent report choice, they take seconds to create, and have some control features like slicers and timelines built into the tool, this reduces the requirements of the control sheet. Pivot tables do not give you cell by cell control though, they have a fixed structure for their tables and charts with a set of finite design options and you always need to refresh the reports whenever you have data updates in the data sheet. For advanced reporting you can also leverage the Data Model (Power Pivot) and connect to multiple data sources and use DAX based calculations to build your Pivot Table Reports.

You will use Tables and Graphs to create your reports. Your report sheet is connected to your Data sheet which is itself connected to a Data Source, so new data will automatically flow straight to your reports and your reports in turn are controlled by your business rules in your control sheet.

A Special Report Sheet

A Special Report Sheet

A special form of report is a Dashboard, I define a Dashboard as a set of visualizations set in a single page such that the viewer can take in critical information all in one view. Your report sheet can therefore be transformed to a single Page Dashboard. A great technique to achieve this is to create snapshots of your tables and charts and thoughtfully arrange them on a single page. A great Excel feature that allows you to do this is the Camera tool. Watch how to do it here.

In summary, to automate your reports in Excel you need three main sheets: The Data Sheet, The Control Sheet, and The Report Sheet. You will quickly become more efficient, productive, and professional when you implement these techniques.