The Business Intelligence Software You Didn't Know You Had

Users of MS Excel 2013 or above have BI capabilities built in with PowerPivot; a powerful, low-cost solution for collecting and analyzing your data. Here's a look how its implementation and productivity increasing abilities can be gained without investing too much time or money.

The growth of the overall Business Intelligence and Analytics market is forecasted by Gartner, to continue at 7.9% through 2020. There are many offerings in the market with various strengths, weakness and fit based on the customer's need and capabilities. The 2017 Gartner Magic Quadrant for BI and Analytics below highlights their analysis on the major vendors that made the cut to be included.

MS BI Gartner Chart.jpg

Microsoft continues to stand out as a Leader with both, a completeness of vision and a high ability to execute. The product reviewed above in Gartner's most recent Magic Quadrant for BI and Analytics Platforms was Microsoft's Power BI, which is the cloud based application. The solution that predates Power BI is PowerPivot for Excel. If you are using Excel2013 or Excel2016, PowerPivot is built into them.

RELATED: Building A Winning Organization Through Data-Driven Decision Making >>>

What is PowerPivot?

PowerPivot is a data modeler that is a built in extension for Excel2013 and above. It allows you to import millions of records from multiple data sources, model the data with logic and calculations and present them using Excel. All of this is done at the desktop. In addition it can be shared via Sharepoint or Power BI. See graphic below.



Where Does the Power in PowerPivot Come From?

The major benefits and the power added to the standard Pivot Table and VLookup functionality in standard Excel are as follows:

  • Instant linking and splicing of data sets (entire tables in seconds)
  • Smart, reusable, advanced formulas
  • Virtually limitless data
  • Front end is Excel - familiar with many users already
  • Dashboards, visualizations can be created via Excel

Effective, Agile and Low Cost BI Solution

Microsoft's BI solutions can work for a wide range of customers, so if you're a small to mid-size company who's just getting started with BI, it may be a good starting point. But it must be noted, BI isn't just about software capabilities, it also requires insight capability; knowing the right business question to ask and then finding the data that helps answer the question. The software by itself is never the complete solution and developing people with these capabilities takes time.   

A first step in any BI initiative is to gather the data you need and presenting it so it can be more productive in the analysis portion. This is where PowerPivot excels as a low cost and agile solution:

  • Since it's embedded into Excel there is not any extra software cost from the user side.
  • Initial data model such as one to do analysis on G/L can be implemented in a week or two.
  • Incremental approach rather than a big bang works well with PowerPivot starting with a data model at a time.

Other BI solutions have cost ranges into the thousands of dollars per user, with implementation time measured in months or even years. However, if your company has been using Excel2013 or above, PowerPivot is already in your application suite.  Additionally, many of your users will be familiar with the UI for PowerPivot as they have already been using Excel. 

Case Study

A client with less than $ 150 million of revenue made the leap from a smaller ERP system to the Oracle JD Edwards EnterpriseOne platform. JDE had the transaction data the company needed for analysis and decision making. The data they required was in many different tables in JDE or viewed through many applications through JDE. They were importing this data manually into Excel and then consolidating to get information into the format needed for their analysis. In one particular case, the client was averaging 60 to 75 minutes per day in batching and consolidation efforts for a single data set. In another, the effort it took to get the data was simply too burdensome, so the analysis was abandoned altogether.


With two primary issues identified; the inability to access required data in a productive manner and the time investment sometimes being too burdensome to even bother, we set out on a path to solution. Since they were using Excel2013 already, PowerPivot was readily available. The cost to setup the initial installation, connection to the Oracle database and model for Planning was about $6,000, with an annual savings of $19,500 and no additional software or licensing costs outside of what was already a part of their IT environment. Another model, on production data to monitor efficiency of the production lines, was created and allowed the client to manage and improve their throughput.  Data for both of these solutions automatically refreshes before the analyst starts working so now, instead of wasting an hour or more to get the needed information, they can now kick off the analysis at the beginning of their day.  

Incremental Solution

These successes have allowed the client to build further data models to manage G/L, to manage inventory, etc. As they learn the right questions they need to ask and determine the right data to help answer the questions, the PowerPivot Models and Excel front end can be adjusted accordingly with minimal time or effort.


If you're operating with Excel2013 you currently have BI capabilities inside PowerPivot. This combination may provide a smarter solution for your intelligence needs without a lot of additional time or overhead. After gaining some momentum with PowerPivot, you can easily move up to the next step with Power BI; a low cost solution considered to be one of the leaders in the market for BI, analytics and visualization- all inside the cloud. It's a cost effective opportunity to use what you already have to increase productivity and gain valuable insight into your business.