Excel has an excellent range of power tools you should be using to analyze your business data. Whether you need to export for business intelligence, pivot your data to find trends, or just use your data more visually, Excel will have you covered.

Today we're going to explore the realms of Power Pivot, Power Query, Power View, Power Map, and if we have a little time left over, cloud-based Power BI, the Excel Business Intelligence tool. It seems like a lot, but we can cover all of these subjects and leave you yearning to learn more.

One of Excel's strongest selling points are these tools and their interlinked nature: once you use one tool for analysis, it is easy to see where the others could provide further support. With ample understanding of these Excel tools, you'll be master of your data.

Power Pivot

Power Pivot is one of Excel's power tool add-ins. On its 2010 release, Bill Jelen, founder of Mr. Excel, called Power Pivot "the best new feature to happen to Excel in twenty years," so you begin to understand the gravitas of the Excel extension.

Office 2016 saw Power Pivot included in the base installation for Excel, highlighting its importance as a data analysis and data modelling tool. Power Pivot can handle large volumes of data imported from a variety of external sources, extending the functionality of the standard Pivot Table function. Use Power Pivot too:

  • Import and manage millions of data rows for multiple sources.
  • Build analytical relationships between data from different sources, quickly creating and modelling data tables using standardized functions.
  • Feed data into Excel's other power tools: pivots, charts, grids, power view, power map, and more.
  • Use Data Analysis Expressions, or DAX, a new formula language extending Excel's data manipulation facilities. You can read an introduction to DAX here.

Power Pivot is in itself an important tool for Power BI. The enhanced data tables and models you can build using Power Pivot, along with the seamless integration with Power View and Power Map, give you chance to uncover hidden trends, build business insight, and most importantly turn that wonderful raw data into something parsable.

Loading Power Pivot

Power Pivot first appeared as an add-in, but is now included in the standard installation for Excel. You just have to enable it.

Office 2013/2016: open Excel and head to File > Options > Add-Ins. In the Manage box, at the bottom of the screen select COM Add-Ins, followed by Go. Make sure the Microsoft Office Power Pivot for Office 20xx is selected, and hit OK. It should now appear with your existing tabs.

Power Pivot Add-in

Office 2010: download Power Pivot for Microsoft Excel 2010 using this link [No Longer Available]. Run the installation package. Once installed the Power Pivot tab will appear in Excel and if you head to File > Options > Add-Ins you should spot PowerPivot for Excel as a COM Add-in.

Power Query

Power Query is another data analysis power tool that can be used for extracting data from a massive range of data sources, to clean and transform that data, and deliver that data into an accessible format. Power Query is important to both regular and professional users. Regular users can exert greater control over columns, formulas, filtering tools, modifying data types on the fly to transform raw information into valuable insight. With Power Query, you can:

  • Find and connect data from a massive range of sources, merging and molding that data to match your own requirements, or use integrated modeling tools, Power Pivot and Power View, to push your data further.
  • Use the inbuilt JSON parser to build massive data visualizations over your Big Data analysis.
  • Share and manage your queries across your sources, as well as exporting them to Power BI, or your business.
  • Search online for public data sources, including Wikipedia, Azure, and Data.gov.
  • Un-pivot your pivot tables.

The range of Power Query data sources is extensive:

  • Web page
  • Excel or CSV file
  • XML file
  • Text file
  • Folder
  • SQL Server database
  • Microsoft Azure SQL Database
  • Access database
  • Oracle database
  • IBM DB2 database
  • MySQL database
  • PostgreSQL Database
  • Sybase Database
  • Teradata Database
  • SharePoint List
  • OData feed
  • Microsoft Azure Marketplace
  • Hadoop File (HDFS)
  • Microsoft Azure HDInsight
  • Microsoft Azure Table Storage
  • Active Directory
  • Microsoft Exchange
  • Facebook

As with Power Pivot, Power Query also features as a central tool for Power BI, as well as providing visualization tables for Power Map and Power View. As mentioned above, Power Query can also be used to un-pivot tables, restoring them back to their original status for further analysis.

Loading Power Query

Power Query also appeared as an add-in, and as of Office 2016 is included as a default feature. Again, you just have to enable it.

Office 2016: open Excel and head to File > Options > Add-Ins. In the Manage box, at the bottom of the screen select COM Add-Ins, followed by Go. Make sure the Microsoft Office Power Query for Office 2016 is selected, and hit OK. Power Query tools are housed under the Data tab.

Power Query for Excel

Office 2010/2013: download Microsoft Power Query for Excel. Locate your download and install. Once installed, you should spot the Power Query tab. If not, head to File > Options > Add-Ins. In the Manage box, at the bottom of screen select COM Add-Ins, followed by Go. Make sure Microsoft Power Query for Excel is selected, and hit OK.

Power View

Next up: Power View! Power View is primarily an interactive visualization tool used to provide a drag-and-drop interface for rapid model building. Many Excel experts use Power View as their principal Power Pivot visualization tool, and there are a couple of important reasons why:

  • Power View groups data by corresponding values. For instance, if we are mapping figures relating to Tucson, AZ, but our data returns values for Dove Mountain, Rancho Vistoso, and Barrio Historico, Power View smart groups them offering one single value.
  • Power View can connect to a number of different data models in one workbook, meaning you can show clients a range of data tables and visualizations, rather than the single one available to base-Excel.
  • Modify the internal data models without leaving the Power View sheet: update your visualizations on-the-fly, create new relationships between current data, and introduce key performance indicators (KPIs) based upon those relationships.
  • Advanced pie charts, maps, and other data visualization tools, as well as data hierarchies allowing you to "drill" down through data, one layer at a time.

Loading Power View

Unfortunately, Power View is only available for Excel 2013 and 2016, though it is backwards compatible with SharePoint 2010, meaning you can base a Power View file in SharePoint 2010 using the SQL Server 2010 Reporting Services add-in. However, features such as hierarchies and KPIs may cease to work.

Office 2013/2016: open Excel and head to File > Options > Add-Ins. In the Manage box, at the bottom of the screen select COM Add-Ins, followed by Go. Make sure Power View is selected, and hit OK.

Power Map

Power Map enables you to plot and visualize data in three-dimensions. It is particularly suited to visualizing geographic or temporal data, and certainly provides some visual relief to clients or businesses seeking to understand their data further, gathering insights previously unseen in the traditional, two-dimensional workbook.

Power Map can:

  • Plot millions of rows of data across a three-dimensional map, using data taken directly from a table, or using a data model.
  • Capture screenshots and build cinematic tours through your data, updating the data throughout to see time-stamped information changing.
  • Extensively filter portions of data to compare how different factors affect your data.
  • Build custom regions to illustrate localized data models.

Loading Power Map

If you have an Office 365 ProPlus subscription or Office 2016 Power Map will already be installed in Excel as part of the self-service business management tools. Activate it by heading to File > Options > Add-Ins. In the Manage box, at the bottom of the screen select COM Add-Ins, followed by Go. Make sure Microsoft Power Map for Excel is selected, and hit OK.

Power Map for Excel

Office 2013: download the Excel Power Map Preview, and follow the installation guide. Once installed, follow the above procedure to activate.

To use Power Map open Excel and head to Insert > Map.

Power Map is not available for Excel 2010.

Power BI

The aforementioned tools amalgamate as providers into the cloud-based Power Business Intelligence tool. It comes in two flavors: Excel-Based Power BI, or Power BI for Desktop. There are advantages to both, but many business use both in hybrid.

It makes sense: the Excel-Based Power BI is extremely well integrated into Excel and has a massive following, while Power BI for Desktop offers a massive range of visualizations for publishing interactive dashboards, as well as being optimized for both mobile and PC users. Both clients use the same engine, the same language, the same cloud hosting, access to SharePoint, so there is no messy transition between clients or users.

Office 2016 brought forth a number of Power BI updates, including:

  • Around 30 new DAX functions and expressions.
  • Query Editor to ease data shaping, adding more analytical depth to Power Query.
  • Power BI will suggest relationships between data models and Pivot Table Fields, as well as the addition of Time Grouping for Pivot Tables.
  • Extensive range of forecasting tools for data modelling.
  • Field summarizations with additional operators.
  • HDInsight Spark and Azure SQL Data Warehouse connectors.

The Power BI tagline is "let us help you bring your data to life," putting the emphasis on letting your data do the work for you, exploring your data using "free-form drag-and-drop canvas" systems, and bringing your "stories" to life. I personally hate the use of the word "stories" in this context, but everything is shiny, practical, and relatively open to newcomers.

Ultimately, you can make use of each individual component of the Power BI stack to power up your data into a competitive business advantage. Power BI becomes the central spool, drawing thread from each service to expand the capabilities of your analytics toolset through dashboards, easily published reports, and visually attractive data modelling.

Power BI data sources

Loading Power BI

To download the standalone Power BI for Desktop, follow this link, then follow the installation instructions, or head to the dedicated Power BI site here.

The dedicated site offers you chance to sign-up as a pro user for $10 per user per month. For this, your data capacity is lifted from 1GB per user per month to 10GB, your data refresh rate is raised to an hourly schedule, and you can process over 1m rows per hour. Furthermore, you gain access to Office 365 Groups for Power BI, making it easier to share between teams. Take a look at the site to understand more about what Power BI could do for you.

Office 2016: I'm afraid you will have to hold on a little longer. Power BI can only be used with ProPlus versions of Office and Microsoft recently confirmed this might not be released until early 2016. There are details for administrators right here.

Office 2013: Only Office 2013 ProPlus, or those with an Office 365 ProPlus subscription can make full use of Power BI, using the existing tools we set out in the previous section.

Office 2010: Sorry, friend, you don't have the requisite version of Excel to utilize all of the latest Power BI features. However, Power BI works with any XLSX Excel workbook, so you can always migrate some of your data into the standalone desktop version.

Power Tool Roundup

We've covered the Excel power core, and introduced you to the business intelligence tool Power BI. Hopefully, you'll feel comfortable activating them on your own system and having a good rummage around with their many features.

It is worth remembering that each of the standalone tools feed into Power BI, and with the latest updates to Power BI for Desktop there is even more emphasis on Power Query and the extensive range of Power View tools. Meaning you can explore and expand your own understanding of each tool with the sound knowledge it can all be deployed in one powerful business intelligence punch.

What is your favorite Excel power tool? Do you use desktop or integrated Power BI? Let us know below.