Created by : Chetna Sharma, Delhi (India)

We will cover following Questions and Answers below.

Q1 - What is PowerPivot?

Microsoft introduced a new powerful feature in excel 2013 and 2016. PowerPivot is an add-in we can use for data analysis.

  1. Excel Data Model helps you to create your models.
  2. It allows you to use multiple data sources, you can import data from many sources like a database, text files, Excel itself etc.
  3. You could import, merge and perform analysis from multiple data sources together.
  4. DAX (Data analysis expressions) make it more powerful by adding new calculated columns in data Model.
  5. In PowerPivot we don’t have any restriction of data size.

Q2 - How we can add PowerPivot Add-Ins in our Excel?

By default the add-in is not enable, to enable this feature we have to follow below steps.

  1. Goto File menubar
  2. Excel Option
  3. Add-Ins
  4. COM Add-Ins then Go…

Please review below snapshot for more details.

Once you click on Go button you will find below options.

You will find multiple option over there example.

  1. Microsoft Power Map for Excel
  2. Microsoft Power Pivot for Excel
  3. Microsoft PowerView for Excel

We can select add-in as per requirement, for now you can select ‘Microsoft Power Pivot for Excel‘ and click OK.

 

Once you click Ok, you will find new ‘Menu‘ added to ribbon PowerPivot, review below image for more details.

 

There are multiple option available in Power Pivot Menu.

  1. Manage
  2. Measures
  3. KPIs
  4. Add to Data Model
  5. Update All
  6. Detect
  7. Settings

Lets begin with option Manage (Data Model).

Once you click on Manage(Data Model), a new window will pop-up.

Q3 - How to import data into PowerPivot through notepad?

There are multiple ways and multiple source to import data into data model.

  1. Notepad
  2. MS Excel
  3. MS Access
  4. SQL etc

Let begin with Note, we will learn how to import data from ‘Notepad’.

Click on Manage and follow below step.

  1. Manage
  2. Get External Data
  3. From Other Source

Review below snapshot for more details.

Once you click on ‘From Other Source’ you will find below options, go-t0 bottom and select ‘Text file’

 

Once you select ‘Text File’ you need to provide a ‘Path’ and ‘Column Separator’ and click the open.

It will import data to your ‘Data Model’.

The finishing step still left, Do we have data in ‘Notepad’. You will find a checkbox ‘Use first row as column headers’ below the ‘Column Seperator’, Check it if required.

Once you click ‘Finish’ you can see your all records imported to data model, it will show you how many records are imported.

Kindly compare you counts with ‘Notepad’ and click ‘Close’ and your data is now imported to ‘Data Model’.

You will find your data in below format, if required you can change formatting of your data.

Q4 - What do you understand by Data Model? How to add data to Data Model?

A data model is a collection of tables and their relationships for different data source. You can see data added data Model in PowerPivot window.

Please review See Question no 3. for add data to data model.

Q5 - How to create PivotTable from PowerPivot Data?

To add pivot table from ‘Data Model’ Once you click on ‘Pivot Table’.

Let begin with very familiar topic ‘Pivot Table’. PivotTable is as we have in ‘MS Excel’.

Once you click on PivotTable option you will find below option.

 

In New Sheet :- Once you select ‘New Sheet‘ a new worksheet will be added to Excel and you can create your PivotTable as per requirement.

In Existing Sheet :- Once you select ‘Existing Sheet‘ it will as you for location where you wants PivotTable.

Click Ok and you will find details as below.

Below point will describe above snapshot in more details.

  1. PowerPivot Table Image :- In above image a default area is created to create a PivotTable once you add data the image will show data as requested.
  2. PowerPivot Fields :- It is list of all columns headers we have in ‘Data Models’
  3. PowerPivot Rows :- It is a Primary field (depends on requirement) on which criteria we have to do analysis, like :- Country, product etc
  4. PowerPivot Columns :- It is a Secondary (depends on requirement) field on which criteria we have to do analysis, like :- Country, product etc
  5. PowerPivot Report Level Filter:- The is filter will operate your entire PivotTable
  6. PowerPivot Data Fields :- It is a Data area on which we have to do analysis. Like :- Sales, Budget, Actual etc.
  7. New Search option in PivotTable :- It is new option introduced in Excel 2016, in PowerPivot we have multiple Fields(columns) in multiple table. This feature is very useful for searching fields in table.

Let Start Creating Basic Pivot in PowerPivot.

Suppose we need a analysis for Budget and Actual on each Country level. In this scenario we will drag ‘Country’ field in ‘Row field’ and Actual and Budget field in ‘Data field’.

Once you drag it the PivotTable will looks as below.

For more about this PivotTable and Tips on PivotTable.

Q6 - How Many Type of Pivot Table available in PowerPivot?

You will find multiple options as below.

  1. PivotTable
  2. PivotChart
  3. Chart and Table (Horizontal)
  4. Chart and Table (Vertical)
  5. Two Charts (Horizontal)
  6. Two Charts (Vertical)
  7. Four Charts
  8. Flattened PivotTable

Q7 - What do you understand by KPI in PowerPivot?

KPI stands for Key Performance Indicator, Its is a measure used to achieve business objectives, they use KPI at a multiple levels to achieve their targets.

Applying aggregation logic to data fields, for which we have to create KPI.

Point in above image :-

  1. Select any range that you want to create KPI.
  2. Create ‘∑’ sign to aggregate function or type your own function.

Adding KPI to PowerPivot.

Now let define the percentage for Actual vs Budget.

We are analyzing Actual expense vs Budget allocated to us in below steps.

  1. We are selecting Actual in KPI based field.
  2. Comparing it with Budget Amount we have.
  3. Criteria Fields where variance 1% is ‘Green, variance 10% ‘Yellow’ and if variance grater then 10% it will be ‘Red’

Please review below snapshot for more details.

I am adding default icons, you can customize as per your requirement.

 

Once you follow above 3 steps new Fields added to ‘PivotTable Fields’.

Know we will follow the same steps as we follow to create PivotTable.

Once create pivot in Excel, drag fields as per analysis make sure for Icons you have to select ‘Status’ from newly added field, after completing you step you will find below format.

In Below snapshot you will find status field in Pivot fields at bottom.

Basic KPI chart

Note :- the above pivot is similar to Pivot table we have in our Excel.

Q8 - How to add Slicer in PowerPivot?

Toggle content goes here, click edit button to change this text.

Q9 - How to Add TimeLine in PowerPivot?

Toggle content goes here, click edit button to change this text.

Q10 - How to create KPI Dashboard with PowerPivot?

After adding Slicer and TimeLine the KPI will looks like below.

  1. Slicer
  2. TimeLine
  3. PivotTable(iconic presentation)

Q11 - KPI Dashboard, With Slicer and TimeLine?

We can add multiple Slicer and TimeLine as per our requirement refer below snapshot.

  1. In below snapshot we have 4 slicer.
    • Product
    • RiskType
    • Division
    • Catagory
  2. Having 1 Timeline for Data of sales

Note :- For creating Slicer and Timeline based Dashboard please refer google and find excellent designs.

Q12 - What is difference PivotTable and PowerPivot?

Below are few difference between PivotTable and PowerPivot.

  1. The biggest difference in PivotTable and PowerPivot is Performance, PowerPivot performance is much better the PivotTable.
  2. PivotTable is a Excel Functionality where as PowerPivot is Add-ins introduced in Excel version 2010 and above.
  3. PivotTable is created from Excel Data so it is limited to 1.048576 rows only where in PowerPivot we can utilized data upto 2 GB.
  4. In PowerPivot we can use multiple source of data, where in PivotTable it is limited to single source (Except multi consolidation ranges)
  5. In PowerPivot (KPI) we can create Pivot with Iconic presentation where PivotTable gives data summary only.

Q13 - How to query data from 2 or more then 2 data source?

Toggle content goes here, click edit button to change this text.

Q14 - How to build relation in 2 or more the 2 tables?

Kindly Study below topics to know more about Pivot Table and uses.

To create above Chart in 2D format lets study ‘PowerView’.

To create above maps in 3D formate lets study ‘PowerMap’.

 

ExcelVBATips is a group of professionals, Managed by Chetna Sharma. Please whatApp us 8285052214 for more information or email us on ExcelVBATips@gmail.com