Pinterest Stumbleupon Whatsapp
Ads by Google

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 Excel vs. Google Sheets: Which One Is Better for You? Excel vs. Google Sheets: Which One Is Better for You? Do you really need Excel? Both desktop and online solutions have their advantages. If you have trouble choosing between Excel and Google Sheets for managing your spreadsheets, let us help you decide. Read More .

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 Power Up Excel with 10 Add-Ins to Process, Analyze & Visualize Data Like a Pro Power Up Excel with 10 Add-Ins to Process, Analyze & Visualize Data Like a Pro Vanilla Excel is amazing, but you can make it even more powerful with add-ins. Whatever data you need to process, chances are someone created an Excel app for it. Here's a selection. Read More . 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 Upgrade to Office 2016 for Free Today with Your Office 365 Subscription Upgrade to Office 2016 for Free Today with Your Office 365 Subscription Microsoft Office 2016 for Windows has landed and it brings many smart new features. If you have an Office 365 subscription, you can get it now for free and we show you how below. Read More in the base installation for Excel, highlighting its importance as a data analysis How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis. Follow our step-by-step demonstration to learn all about it. Read More 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:

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.

Ads by Google

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. 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 Need Help with Excel Formulas? 7 Resources to Consult Need Help with Excel Formulas? 7 Resources to Consult Excel is the spreadsheet gold standard. If you are required to use Excel and yet have to familiarize yourself with it, these resources will quickly introduce you to the basics and more. Read More , 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 What Is JSON? A Layman's Overview What Is JSON? A Layman's Overview Whether you plan to be a web developer or not, it's a good idea to at least know what is JSON, why it's important, and why it's used all around the web. Read More to build massive data visualizations over your Big Data analysis How to Become a Data Scientist How to Become a Data Scientist Data science has gone from a newly coined term in 2007 to being one of the most sought-after disciplines today. But what does a data scientist do? And how can you break into the field? Read More .
  • 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 5 Easy to Use & Free Alternatives to Microsoft Access 5 Easy to Use & Free Alternatives to Microsoft Access Microsoft Access is a complex database tool with a steep learning curve. If you're searching for more intuitive and free alternatives with comparable features, we've got you covered! Read More
  • 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 4 Data Visualization Tools For Captivating Data Journalism 4 Data Visualization Tools For Captivating Data Journalism Words and numbers are not enough. In the end, visuals will capture and convince your audience. Here are the tools that will help you turn your data into something great. Read More , 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 8 Types of Excel Charts & When You Should Use Them 8 Types of Excel Charts & When You Should Use Them Graphics are easier to grasp than text and numbers. Charts are a great way to visualize numbers. We show you how to create charts in Microsoft Excel and when to best use what kind. Read More , 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 Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More , 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.

  1. Dr Jeffrey Lybarger
    October 28, 2015 at 1:21 pm

    Excellent article, thank you for sharing. But the link in this sentence "Office 2010: download Power Pivot for Microsoft Excel 2010 using this link" results in an error page being displayed.

    Once again, great article.

Leave a Reply

Your email address will not be published. Required fields are marked *