PowerPivot - Inserting and working with Pivot tables

PowerPivot allows you to pivot columns of data from different tables and sources against each other. You may recall when creating pivot tables in standard Excel, one would often use Vlookups to pull all the data into one table and then create a pivot table.  This is no longer needed with PowerPivot because all the tables become fields that you can add.

Before you create a pivot table from PowerPivot you must ensure all your relationships have been set up. Once you are happy with your relationships you can begin to pivot and analyze your data.  Data in a pivot table set up with Powerpivot can be pivoted in the same way as a pivot table.

How to Insert a Pivot Table from Powerpivot

To set up a pivot table from PowerPivot, on the Home ribbon, select Pivot tables

When you select Pivot table, you are then directed back to Excel and given the option to choose a new worksheet or the existing worksheet to add your pivot table

You will then be given a blank pivot table on the worksheet canvas and a fields list on the right

The pivot table field list will contain all the tables that have been loaded into your power pivot data model.  By clicking on any of these tables you will then see the columns available for that table.

Any column can be used in the rows and columns of your pivot table.

For Example, we can drag the Country column from the Customer table to the Rows section of the pivot table.  We can drag the product name from the products table into the Column section and in the values, we can drag the Total Sales.

This will pivot the Total Sales values by the Country and product.

Watch this video now and to see all this in action. In this video, we will set up a pivot table from PowerPivot data.

How do I work with a pivot table from PowerPivot data?

Once you are familiar with working with Excels Standard pivot tables, then working with Pivot tables from Power Pivot will be a breeze.  Most of the options are very much the same.

When you add a column to the values field, if you are working with numerical data, the automatic aggregation is SUM and with TEXT is count.  This aggregation can be changed by selecting the drop-down on the Values and selecting value field setting.  Aggregations include SUM, Count, Average, Max, Min, StdDev, Var and Distinct Count.

We can also change the formatting and visual appearance of the pivot table.  These can be found on the Design ribbon. 

Layout options on the design ribbon will allow you to add or remove totals and subtotals.  It will allow you to change the report layout and it will allow you to add blank rows.

A pivot table can be named or renamed from the Analyze ribbon

You can add filters to a pivot table by dragging the field you wish to use as a filter to the report filters section.

Advantage of PowerPivot Generated Pivot tables in Excel

There are many advantages to generating Pivot tables from PowerPivot data. Having the ability to link tables with relationships changes the dynamics of working in Excel.

One massive advantage of using pivot tables from Power Pivot data is the ability to convert to formulas.  When you are working with Pivot tables, you have layouts and designs you can select from, however, if you are not happy with these and wish to layout the pivot table in a different format, you need to first convert to formulas. 

To convert a pivot table to formulas, select OLAP tools from the Analyse ribbon and select convert to formulas.  Now you can cut and copy parts of your pivot table and lay them out as you wish, knowing your data is still linked to Power Pivot and will update when you update your power pivot model

Watch this video now and learn how to work with pivot tables created with Power Pivot data

Learn and Earn Activity

Now that you know how to insert a pivot table from Powerpivot data and how you can work with that pivot table, what advantages do you think this will bring you in your daily use of Excel?

The ability to convert to formulas using OLAP is an amazing feature. When and how do you think this could be applied?

Answer the questions in the comments section below to be rewarded with steem tokens.

Do you have any questions or tips to share on using pivot tables from Power Pivot data? If so, please also drop them into the comments section below and you too could earn steem rewards.

Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox with Earn and Learn activities.

Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.

We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.

Find out more now and start earning while you are learning Excel and Power BI

---

Posted from my blog with SteemPress : http://theexcelclub.com/powerpivot-inserting-and-working-with-pivot-tables/

---