Power Tools in Excel: Power Query, Pivot, BI,...

Published on
4,274 Points
Last Modified:
►Yolanda Cuesta Excel Contabilidad TIC
Economista y Formador Linkedin #Learning Microsoft #Excel, #contabilidad, fiscal, gestión empresas.
Learn about Power Excel add-ons and how to use them.

Power Pivot. Get and transform (Power Query).Power View.3D Map (Power Map).Extra Power BI.Power summary video (Spanish)

Power Pivot

This Excel add-in is useful for processing and analyzing data, achieving a display of significant information by creating models with them.

Previously you will have to load the Power Pivot add-in in Excel. If you don´t have it loaded, here's how;

  • Go to File > Options > Add-Ins.
  • In the Manage box, click COM Add-ins> Go.
  • Check the Microsoft Office Power Pivot box, and then click OK.

If you have other versions of the Power Pivot add-in installed, those versions are also listed in the COM Add-ins list.

Be sure to select the Power Pivot add-in for Excel.

More information can be found on the Microsoft website.

Power Pivot allows, among other functions:

- To perform calculations with DAX formulas whose potential is very large.
- To create KPI or Performance Indicators to measure results.
- Maximize dynamic tables, graphs and their utilities in Excel.

Power Pivot Editor


I find really interesting to use Power Pivot in order to analyze a large amount of data from sales in the company I work for and make KPI in order to make measures, control deviations and so on.

Get and transform (Power Query)

With Get and Transform, you can obtain data from different sources, internal or external.

Once imported from the query editor, you can work to adapt them to the needs you have.

Once this work is done, also known as ETL tasks that are the acronyms of Extract, Transform and Load; You can have them in your Excel sheets to use all the tools of the spreadsheet program.

Power Query Editor

Power View

Power View provides the ability to create some really intuitive visualizations.

This helps in the analysis of the data in your spreadsheet.

Reports that are generated with this resource are really easy to interpret, as well as highly effective. Power View is available from Excel 2013 and upwards, although first it must be activated the same as you did with the Power Pivot Add-in. You must also have previously installed the Microsoft Silverlight component.

Here is the link: Get Silverlight - It does not need to be installed into Excel itself, just Windows.

Beware that you may have some adjustment to make if it happens to you: Error in Excel Power View fixed.

Here is an image of a report generated with Power View to give you an idea of the potential that it has.

Power View Report

3D Map (Power Map)

3D Map (Power Map) is an Excel tool that allows you to make maps with visual data in 3D.

From the data of an Excel spreadsheet, it allows you to represent them geographically and also over time.

You can create a video with data and regions, countries, ... to create a really different and impressive professional report.

I have a first report with 3D Map that you can see: Power Maps in Excel Introduction.

Power Maps Scene

This is a spectacular tool because it allows you to make a video and show all your company’s figures. You can explain the different results (sales, profit,…) not only around your own country but also around all the World.

EXTRA Power BI Desktop.

In this case, it is not a Power Excel 2016 Add-on but a free Microsoft program that allows you to automate the reporting processes.

This free tool integrates many of the elements that exist in Power View, Maps, Power Query and Power Pivot and its own characteristics.

Power BI football team results from Desktop Panel

Video summary Power Excel 2016 add-ons

To reinforce everything that I have explained so far I have created a small video that summarizes all these components and shows you the work screens of them.

Please check my Experts Exchange profile page for a link to my blog about Excel. Though it is in Spanish, you can use translator tools for all the languages in the world.

Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free