Login to your new FMVA dashboard today.

Excel’s Power Pivot

How to use the Power Pivot tool in Excel

What is Excel’s Power Pivot?

Excel’s Power Pivot is an add-in for Microsoft’s spreadsheet application. While you can create data models and groups of tables that are related to one another, Power Pivot gives you more options to analyze and create data models and presentations that are more sophisticated. Power Pivot enables you to take massive volumes of data from different places, analyze the information quickly, and then process it and offer insights.

 

Excel’s Power Pivot

 

Power Pivot is available in Excel 2019, 2016, 2013, and 2010, as well as in Excel in Office 365. The add-in doesn’t require a significant amount of training to use. However, it simply needs to be enabled to take full advantage of its functionality. Once that’s done, Power Pivot can be used to do a variety of incredibly useful tasks.

 

Summary: 

  • Power Pivot is an Excel add-in that allows users to pull in large volumes of data from multiple sources and create relationships; the data generated can then be analyzed and worked with efficiently.
  • Adding data to Power Pivot involves a few simple steps that enable users to choose the data tables that need to be analyzed.
  • Power Pivot is a crucial tool for users, especially those in the business world; it enables users to input data, create relationships, make changes quickly, and provide company management with clear, up-to-the-moment analysis.

 

Importing Data and Creating Relationships in Excel’s Power Pivot

The first step to using any of Power Pivot’s amazing tools is pulling in the data that is to be analyzed. It’s important to make sure that the Excel files being pulled in contain names that are easy to find. The files are simply imported into the Power Pivot Data Model.

The next step is to create relationships between the tables of imported data. On the Home tab in Excel, there is a tab labeled “Diagram View.” Clicking the tab brings the tables of data up in separate boxes, each containing headings to explain the data contained within. The headings can be dragged and matched up to create relationships.

 

Creating a PivotTable

After adding the data and establishing the relationships, users can create a PivotTable, one of Power Pivot’s most useful features. The majority of the work is done by the program; it uses the relationships that have already been created to generate fields that will be used for the PivotTable.

Here are the steps to create a PivotTable:

  1. Open the Power Pivot window. Here, click on the Home tab and then click on PivotTable.

 

Excel's Power Pivot - PivotTable Window

 

2. A dialogue box titled Create PivotTable will pop up. Choose New Worksheet and then click OK.

 

Excel's Power Pivot - Create PivotTable

 

3. Find the PivotTable Fields pane. Choose the fields that need to be added to the PivotTable.

 

Add PivotTable Fields

 

4. The data in the table may need to be sorted. To sort the data, drag one of the fields into the Filters section. The example below shows that Class Name has been moved to the Filters section so that the list shows a student’s average grade in a class.

 

Sort Fields

 

Now the data can be analyzed, moving different fields to the filters pane to get the information that is needed.

 

Importing Data from Different Sources

Excel, on its own, is capable of managing data from a variety of sources, including XML, SQL Server, and Microsoft Access, and even data from the web. However, creating relationships between the data from such sources is tricky. Third-party products can help with the process, but using them is usually far more complicated and not a realistic option when working with large volumes of data.

This is where Power Pivot comes in handy. In fact, it is precisely what Power Pivot is designed for. Data from anywhere, essentially, can be imported and relationships can be created. A SharePoint List is arguably the most useful data source. With a few clicks, information from a SharePoint List and SQL server can be combined.

SharePoint lists are basically data feeds. In order to create a feed from a SharePoint list, open the Power Pivot window and select the List ribbon. From there, select Export as Data Feed. Then, simply save it. A data feed has now been created.

 

Benefits of Using Excel’s Power Pivot

Again, Excel is a great tool on its own. It has the power and capability to do great things. However, it is widely agreed that Power Pivot is one of the greatest and most useful Excel tools on the market. Why? Power Pivot has transformed the way users – especially companies – can input, view, analyze, and then use information.

In the past, analysis – with Excel – can sometimes take up to several days. With Power Pivot, data analysis can be done in about an hour. However, the real power of the add-in is the fact that data can be easily moved, changed, and adjusted. Companies frequently need to work with data that comes in quickly, and that can change even faster. Power Pivot enables users to manipulate data input, create relationships, and get a better understanding of where the company currently stands in relation to one metric or another.

Power Pivot also enables users to create visual data that can be outputted to Excel worksheets. It includes PivotTables and PivotCharts. Worksheets with multiple outputs create a dashboard of data that can easily be viewed and can be shared with higher-ups in a company. Executives can view and even interact with the data if the worksheets are built correctly.

 

More Resources

CFI offers the Financial Modeling & Valuation Analyst (FMVA)™ certification program for those looking to take their careers to the next level. To keep learning and developing your knowledge base, please explore the additional relevant resources below:

  • Advanced Excel Formulas Must Know
  • Financial Modeling Best Practices
  • How to Make a Graph in Excel
  • VLOOKUP

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!