As a regular Excel user you have a distinct advantage when it comes to learning PowerPivot as it has been built with you in mind. The idea was to provide a tool which will allow Excel users to leverage off their existing knowledge and training.
However, there are some differences which you need to understand in order to get started.
If you want you can download the appropriate file below and try out the steps to get your first feel for PowerPivot.
Note that Excel 2010 and Excel 2013 versions of PowerPivot are NOT interchangeable. You need to download the version that matches your version of Excel (not strictly true as you can open up the 2010 version on 2013 but the only way to use it is to convert it and then it can’t be opened in Excel 2010 again!)
Open the file and try each of these steps
PowerPivot has its own window
In order to see PowerPivot you need to first open it. You can do this by going to the PowerPivot ribbon and then clicking on PowerPivot button in Excel 2010 or the Manage button in Excel 2013 (it may take a few seconds to load the data model).
Open the file you downloaded, click on the PowerPivot ribbon and then the Manage (Excel 2013) or PowerPivot (Excel 2010) button.
PowerPivot works on table and column NAMES, not cell references
A significant difference is that you need to use table and column names for formula. You CANNOT use traditional cell referencing.
So in Excel you might say
= G2 + H2
but in PowerPivot you would need to say
=Leave Register[Total Days incl weekend and public holiday]-Leave Register[Total number of working days Sat Sun are weekend]
Have a look at the formula in column I of the Excel file versus the column called ‘Annual Leave Only’ in the PowerPivot window. Same formula but different referencing.
You can’t change individual cells in PowerPivot
It is not possible to directly change individual cells in PowerPivot even if they are ‘inputs’. Once a item is in PowerPivot the only way to change it is at the original source.
In the example below I can change the 7 into 100 in Excel, but it is not possible directly in PowerPivot (later in the course you will learn how to set-up the files so that changes you make in Excel will immediately flow through to PowerPivot).
Naming of tables and columns is CRITICAL in PowerPivot
Everything in PowerPivot is achieved by referring to the table and column name. As a result it is CRITICAL that you create meaningful names (and you must do it at the start-see next point).
In the example below will you really remember what Calculated 2 and Calculated 3 mean especially if they are in Table 1 and Table 2.
Calculations and formula DO NOT adjust for name changes after the fact
Unlike Excel, PowerPivot DOES NOT correct your formula for changes in table and column names. If you change a name after the fact, you may need to correct all the associated calculations.
In the example below we changed the column names after creating the formula. The end result is ERROR messages and lots of work.
In your sample file, in the PowerPivot window change the name for the column ‘Leave Type’ to ‘Leave Types‘ (double click on the name and type a new name). Look what happened to the column called ‘Annual Leave Only’. In a real life situation you would need to find all the formula that use the ‘Leave Type’ column and change them to ‘Leave Types‘. Click Undo or change the name back.
No inconsistent formula are allowed. Each column has to have a single consistent formula in PowerPivot
Consistency in formula is enforced in PowerPivot. You cannot fix one cell’s formula without affecting all the other cells in the column. What you do to one cell happens to all the cells in that column.
In the example below I could add 100 to the one cell in Excel but when I try the same thing in PowerPivot the entire column adds 100.
In your sample file, in the PowerPivot window go to the column called ‘Annual Leave Plus 100’. Click in any one of the cells and add 100 to the formula and click enter. You will see that the entire column assumes that formula. If you delete the plus 100 and click enter all the formula change again.
This means that you will need to be extra careful in creating formula. You can’t fix it by overwriting individual cells.
A single error in data will affect the entire column in PowerPivot
An error in a single cell will affect the entire column in PowerPivot. In the example below the numbers written as words make the entire PowerPivot row an error whereas in Excel it would only affect those calculations.
In your sample file have a look at the formula in the column called ‘Weekend Leave’. Scroll down to the bottom. You will notice that someone has written the words instead of the numbers. In Excel only the 2 affected cells would show an error message (look at the Excel file cell J68 and J69). In PowerPivot the whole column has an error message. Ideally you get clean data, otherwise you will need to address this type of issue in another way (IFERROR works in PowerPivot in the same way as in Excel).
Although PowerPivot is very similar to Excel and should be easy to transition to, there are a few fundamental differences which you must be careful of. These difference sometimes result in experienced Excel users giving up on PowerPivot. Don’t make that mistake. This is a tool you must learn more about.