In recent years, some of the most significant improvements to Microsoft Excel haven't been part of the familiar spreadsheet software directly. Instead, the new analysis and visualization tools that turn Excel into a hugely business intelligence (BI) platform arrived as add-ons –available only if you have a separately installed Office ProPlus license (or an Office 365 E3 tenant) -- that live on their tabs in the Excel ribbon, but launch separate apps.
Excel formulas and pivot tables might arguably be the most popular programming language in the world and these new tools – Power Query, Power Pivot, Power Map and Power View – are even more powerful. You can connect to, clean, deduplicate and transform almost any data; explore and filter millions of rows of data; build KPIs; and create visualizations.
Even though you use Excel to build the data model that you’re going to analyze, the tools you do that analysis with aren’t part of the Excel interface. Even worse, they all have their own interfaces – which look almost, but not quite like Excel – in separate windows. You can end up connecting to data and cleaning it in Power Query, analyzing it in Power Pivot and then visualizing it in Power Map and Power View as three or four disconnected steps.
Powerful also means complex -- very complex compared to self-service analytics solutions like Tableau. Some relatively simple things (like drawing lines on a map) can get complicated with Excel power tools. And when you’ve finished using up to four different tools on top of Excel, what you get needs a complex mix of SharePoint, Silverlight, Excel and HTML5 to share with other users.
Compare that to Microsoft’s Power BI service, which is both powerful and easy to use – with new features being added every week – and those complex Excel tools make even less sense. That’s why Power BI, the cloud service, has a desktop companion app, Power BI Desktop (recently renamed from Power BI Designer), that’s taking over from the complex Excel extras.
Visualizing the future of analysis
“Business analysts have wanted the self-service BI capability that allows them to not have to stand in line to get their work done, to not have to go get someone else to do measures for them in Cognos or Analysis Services,” says James Phillips, the general manager of data experiences at Microsoft. “I want something that allows me to get my work done without being a BI professional.”
Phillips has overseen the transition of Power BI from a SharePoint feature to a service that he expects to have a billion users. The service only came out of preview in July 2015, and it was already being used by 500,000 users at 45,000 companies in 185 countries (even though it’s only officially available in 145 of those).
Sign up for CIO Asia eNewsletters.