SelectHub

Excel for BI: Using and Replacing It

For businesses, Microsoft Excel is the equivalent of, say, a really good website or a reliable accountant. It’s an essential tool, no matter what industry you work in or the size of your operation. While it isn’t the only program to offer formulas and pivot tables, Excel is the most popular tool for statistical analysis anywhere in the world.

Get our BI Software Comparison Matrix.

Much of its success is related to its familiarity. It feels like Excel never changes. The core functions remain the same, even as the volume of data handled by businesses continues to expand. Regardless of how vast or complex datasets may be, you can still use the same basic tools to identify trends and patterns.

Yet, the truth is that Microsoft Excel has been changing. Much of its recent evolution has occurred outside of the familiar spreadsheet software. Steadily, stealthily, it’s accrued the analysis and visualization tools needed to transform it into a complete business intelligence solution.

Bringing in the Big Guns

It’s true that Excel formulas and pivot tables are, arguably, the most popular programming language in the world. In other words, they’re very powerful. However, the new Power suite is looking to take analytics and data processing to another level. Power Query, Power Pivot, Power Map, and Power View allow businesses to source and filter millions of rows of information.

Perhaps the biggest change, however, is in the way these tools are being offered. Until recently, they were only available as add-ons for those with an Office ProPlus license or Office 365 E3 credentials. Once downloaded, these tools were stored in individual tables within Excel itself, despite being linked to standalone applications.

There is an obvious problem with this. It didn’t take long for businesses to get frustrated with launching three or four interfaces for a single dataset. While the add-ons themselves are valuable, it’s common to end up sourcing information in Power Query, reviewing it with Power Pivot, and presenting it with a Power Map. It’s messy and overly complex.

The Future of Business Analytics

In response to these problems, Microsoft introduced its Power BI service, and it’s already being touted as the future of analytics and business intelligence software. The cloud-based solution is powerful and, most importantly, easy to use. In addition, it comes with its own desktop companion app. Launching a suite of visualization tools has never been as simple.

Interestingly, Microsoft has chosen to keep the basic Excel program largely untouched. This helps to maintain a practical distance between the raw data and the insights it contains. Microsoft Excel is extremely useful for ad hoc analysis. It’s capable of processing almost any type of data, and it provides remarkably robust programming environments.

The general feeling seems to be that adding visualization tools directly to the mix would take away from this critical function. After all, every business benefits from the ability to reconfigure raw data at speed. Where they differ is in which parts of the data they find value, and how these insights are best presented and applied. So Power BI is really about choice.

Picking Up the Pace of Processing

The separation between Excel and Power BI is allowing developers to move at an unprecedented rate. New features are being added all the time, with plenty more to come if Microsoft is to be believed. Some of the earliest additions were to the Power Query, Power View, and PowerPivot functions. They allow worksheets and models to be imported into Excel.

This has extended the range of options for sourcing data. Businesses can pick from a wide selection of file formats, databases, and Azure services (with HDInsight Spark and Azure SQL Data Warehouse included). And that’s not to mention all of the methods for importing data directly into the Power BI suite. It’s just as compatible with cloud services such as on-premise data.

One particular perk is that on-premise data doesn’t have to be transitioned into the cloud before processing, even though Power BI is based on cloud architecture. There’s actually a free version of the service, but it’s only useful for trial runs if you’re a big company. It does, however, provide a handy way to get familiar with this business intelligence solution, albeit with a limited storage capacity.

Looking at Some Key Features

Microsoft Power BI can source information from a huge range of cloud services. This includes everything from ZenDesk to Salesforce, GitHub, Google Analytics, Twilio, QuickBooks, and much more. In recent years, links were added to both Sage and Adobe Analytics services, with additional content packs on the way.

It’s possible for businesses to construct their own content packs. These are shaped around existing on-premise solutions and cloud services. It’s worth taking time to check this feature out; it creates automated dashboards for displaying actionable insights. However, it uses the data model of the originating service, so the natural language Q&A feature remains accessible.

In simple terms, this would let you enter a question like ‘Which products generate the most revenue in X region, during Y timeframe?’ without having to enter any code. The system automatically constructs a map or chart with the data displayed.

And don’t forget that the Power BI Desktop application offers functions that go beyond these integrated visualizations.

The Role of Power BI Desktop

The developers at Microsoft are keen to point out that Power BI Desktop isn’t just a compact version of the larger business intelligence suite. Currently, it’s being used as a launch pad for custom visualizations. The desktop software and the visualization stack that generates it have both been released on GitHub as open-source tools.

This means the native visuals are freely accessible. In theory, businesses no longer have to rely on their BI vendor for visualization diversity. By making the software public, Microsoft is inviting data analytics developers to combine it with their services. The goal is to increase the degree of interoperability so that users have as much flexibility as possible.

According to Microsoft, the general objective is to continue making basic visualization tools and business intelligence functions accessible to everyone. It believes that, up until now, BI solutions have been too expensive and that experimenting with data models and displays should be largely cost-free.

What Comes Next for Microsoft Excel

So, the big question is whether Power BI will eventually replace Excel. It is one that keeps cropping up in discussions about the future potential of the Power suite. Yet, we’ve already covered some of the main reasons why this isn’t likely to happen. As Microsoft itself has pointed out, Excel continues to offer a lot of value.

It’s true that Power BI can be used as a rudimentary alternative to Excel. This is certainly the case if you want to harness information fixed in ERP systems and other data sources. On the other hand, if you were to receive a list of sales from a specific location and wanted to turn it into a report as quickly as possible, Excel BI would be the best option.

Ultimately, it depends on the strategy that you’re most interested in. Excel BI continues to be the superior choice for simple, accurate reports created at speed. Power BI is for investigating further and mining data for all it’s worth. Certainly, at this time, businesses are advised to hold on to both. While things may change in the future, right now, they form a cohesive BI package.

Get our BI Software Comparison Matrix.