How to Transition From Excel Reports to Business Intelligence Tools

If you are one of those people manually creating reports using Excel, you know it can be overwhelming to meet the organizational expectations for quality, insights, and velocity. The business demands of twenty-first-century data analysis using twentieth-century tools are the root of too much pain and frustration. 

If you decided to do a little research on the latest and greatest alternatives, you quickly see how many tools exist to solve those challenges. Reviewing the options you think, “Hurray! This is going to be a snap. These tools make it seem so easy!” Next, you decide to take the plunge with a trial of a preferred tool like Tableau, Microsoft Power BI, Looker, Amazon QuickSight, or Google Data Studio. Don't have a tool picked out to trial yet? You can check out business intelligence software options on G2. 

However, you are quickly confronted with the realization that transitioning your company reporting from Excel is more complicated than anticipated. You ask yourself “Wait, how exactly do I get from all these mystery meat Excel reports to the data analytics promise land? Where do I start?”. This is where the self-serve nature of most tools leaves you wanting. 

You need a methodology and a process. We have a few tips that will help you find success with your new tool as you transition from Excel to a new analytics model.

The Good News

First, let's recognize that your Excel reports are a good thing. Yes, I said they are a good thing. Rather than view Excel reports in a negative context, look at them as the starting point to build upon. Why? The fact you have some reporting, even Excel-based, is a starting point. Excel reports have KPIs defined, calculations embedded, have a wide distribution within the company and rudimentary workflows for creating them. These are all positives. 

Your current Excel reports provide a reference implementation for whatever you will create in Tableau, Power BI, or whatever the preferred tool may be. An Excel starting point provides fertile ground for working with internal stakeholders as you refine workflow, discover data sources, document calculations, and explore the desired visualization end-state using your new tool. Think of these Excel reports as an alpha or beta version.

How to Make the Transition From Excel

There are a few different steps that will assist in your transition from Excel. We suggest a “component” based approach. What is that? It is an approach that borrows techniques employed by the user interface and experience design profession. 

The basic premise of this approach is deconstructing your monolithic Excel reports into smaller blocks. These blocks become more manageable and represent future reporting elements. As you will see later in the post, identifying a component should be fairly straightforward once you know what to look for.

Step 1: Inventory Your Current State Excel Reports

As best you can, create an inventory of your reports. The goal is to build a library of reports that you are aware of. You can also ask around to other teams or even external partners. The purpose of creating this library of working reports is to start to identify patterns and scope the work ahead. 

We would suggest picking a small subset of Excel reports that have high real AND perceived value. Maybe a report that is commonly used by executives or key leaders within the company? Get them excited about the opportunity this work can deliver. 

Limiting the initial scope of this phase of your work will increase the probability of you being successful. 

Step 2: Deconstruct Your Current Excel Reports Into Components

In this step, we are going to review your selected report(s) and start to break them down into smaller components. It might sound intimidating, but you will likely find there are logical groupings for each.

For example, let's say you have a “Sales Performance” (SP) report. Within this SP report you have broken it down into 24 components:

Each component would be assigned a unique ID (i.e., SP1, SP2, SP3….SP24). Systematically deconstructing Excel files into formal report and data definitions is a critical step in a transition. You will start to see how each one of those blocks reflects a specific visual treatment, data source(s), and collection of metrics/dimensions.

For example, let’s assume that component SP1 was showing a visual for “average sales.” This is the starting point to define how SP1 works. Know that SP1 reflects average sales, you start to ask questions like, “How are average sales calculated? Does it include gross or just net sales?” and “What period of time is used to calculate the average?” As you dig into SP1, you will be led down a path that defines where sales data is sourced from, how average sales are calculated and what dimension of time should be used for this component. 

Questions at this point are good because you want to understand how these are created. However, just asking is not enough, you should make sure you are documenting all of this. 

Step 3: Document Your Components 

As we mentioned previously, you will likely have a lot of questions relating to each component. Up until this point, any knowledge of how an Excel report worked, or how something was calculated, would be embedded in macros, calculations, elbow grease, and voodoo. Nobody really would know for sure. That will not be the case anymore.

This is why we suggest you create a document (Google Sheet, Excel Sheet, Word Doc…) that details the specific metrics (e.g. retail sales, average price) that you are reporting on and the dimensions (e.g. channel, category) for each component. Not only does this provide a baseline set of sources, metrics, and dimensions, it will also inform the use of filters and any groupings across the various report views. It will also provide critical insight into where you will need to source data from in the future. 

Here is an example of a worksheet which inventories and documents each component:

You can then overlay each component with metrics and dimensions aligned with their corresponding fields in the source data (along with any calculations/filters required):

Step 4: Build Your Prototypes

Once you feel Step 3 is complete, you have a strong baseline defined. You can use these definitions to build initial reports and visualizations in Tableau, Microsoft Power BI, or any other similar tool. You can start to socialize these outputs with “friendlies,” or people you know are willing to provide feedback and support. These are also the same people who can become your advocates as you look to socialize more broadly across the organization, including those in IT.

Step 5: Refine Data Visualizations

Lastly, we suggest saving formatting of visuals (type, color, layout…) as a final step in your process. You will ALWAYS need some refinement based on subjective preferences. There is a risk of having the visualization elements dominate the upfront work effort which can be detrimental to data quality, functionality, and operations. Keeping a focus on functionality versus aesthetics will help you iterate more quickly when exploring visualization treatments.

The Benefits of This Approach

Benefits of this component-based approach allow you to accomplish a broad cross-section of activities, all of which contribute to the success of your effort:

• A component approach facilitates prioritization. Conversations can occur on which elements of a report may be more valuable than others.
• Use of components provides a consistent method of defining requirements, tasks and ownership.
• Allows teams to test the accuracy of calculations since “logic” can be localized to a given component.
• Simplifies workflow which allows teams to “sign-off”, or approve, at a component level.
• A library of components allows you to rapidly “assemble” combinations of components into new reports.
• Allow you to quickly iterate/copy a component with new aesthetic treatments.
• Identify underlying data sources and calculations that power a given component.

Congratulations on making a leap to a new data visualization, analytics, and reporting tool! We hope this post provided some ideas on how you can set a path to realizing success transitioning from Excel to a modern data analysis platform. Good luck!