Business Intelligence Features in Excel
Leveraging Power BI to get the most from Microsoft Excel
By Aaron Kaiser
Microsoft’s new BI tool “Power BI” can seamlessly integrate with a plethora of Business Intelligence features from Microsoft Excel, including:
- Power Query
- Power View
- Power Map
All these features can be a little confusing as there are so many options to choose from it can be difficult to choose which one to start with. When talking about Microsoft Excel we are talking about a tried and true business application which has stood the test of time.
Let’s take a deeper look into how Excel provides you a set of powerful features to arm your business with complex Business Intelligence.
This is a data analysis feature for Excel that enables you to connect to various data sources, transform your data as well as share your queries.
Some of the more popular data sources that are attached to Power Query are:
- BI Universe
- Hadoop Distributed File System (HDFS)
When leveraging these sources, you are armed with the ability to build PowerPivot data models and visualizations rapidly.
Power Query provides data transformations that before required formula manipulations or an ETL developer. These new features come with the built-in capability to pivot, un-pivot, split by delimiter, remove extra white space and much more.
This is more of an all-encompassing dashboard that provides you with the ability to create interactive visualizations in Excel. You need to create a PowerPivot connection with your data and then you can begin dragging and dropping fields into interactive reports & analytical views.
With this feature you create “tiles” of charts, tables and maps. Each element in the title is related so you can select a section of a pie chart and then drill down into the most relevant insights within that pie chart.
You can then use these dashboards and export them into formats like PowerPoint or you can just use Power View for your presentations.
Just like PowerPivot, Power View was created as an add-on for Excel 2013 so there are no additional downloads necessary. Let’s say that you haven’t procured Excel 2013. You can still create Power View files provided you have one of the following Microsoft applications:
- SharePoint 2010 or later versions with SQL Server 2012 Business Intelligence or later versions
- Power BI in the cloud.
This is a feature that provides the ability to import a variety of data sources into an Excel spreadsheet. You can also create relationships between these data sources and do your own ad-hoc analysis utilizing a PivotTable.
These data sources can be:
- An Excel spreadsheet
- Text files
- A database platform
After you have successfully created a connection with your data, you can just hit the “Refresh” button in Excel to learn of the latest updates.
You no longer have to re-export information into Excel or a text file. To share these spread sheets, you just need to upload them into Power BI, SharePoint or by saving the files in a central network location.
With Microsoft Excel 2013, PowerPivot is now a built-in add-on. If you are using Excel 2010, you can download a PowerPivot add-on for free from Microsoft.
This feature is an Excel add-on for generating geographical visualizations. You can then represent your data on a two-dimensional map.
With Power Map, you can take your data one step further by showing geographical information on a three-dimensional map or a custom coordinate map. To show data over time, you need to add a time component and Power Map will animate requested adjustments over time. You can also create a variety of information views and save them as storyboards.
If you have an on-premise version of Microsoft Office Excel, you may download Power Map at no cost from Microsoft. However, if you have an Office 365 version of Excel 2013, Power Map is already built-in, and you will automatically receive the latest updates from Microsoft.
After this article hopefully, you are more aware of all the powerful Business Intelligence features in Excel that can dramatically improve the way you work and give shape to your organizations Business Intelligence data. When connecting multiple data sources into a single data model, PowerPivot is the tool for you.
If your goal is to present powerful, visually stunning dashboard look to Power View or Power Map. If your aim is to transform data from external sources, Power Query is your best bet.
If you’re looking to engage with an expert and searching for assistance on these Business Intelligence features, please contact us at EPC Group and we would be happy to help.