Difference between PowerPivot and Excel use

Difference between PowerPivot and Excel use

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.

Related: Learn how to use PowerPivot

However, there is a difference between PowerPivot and Excel use which you will need to get used to. Below are a number of fundamental differences which will help you to easily move onto PowerPivot.

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 Window in Excel 2010 or Manage in Excel 2013. If you don’t have it loaded you will need to check if you have PowerPivot.

powerpivot-new-windowPowerPivot 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]

powerpivot-cell-referencing

You can’t change individual cells in PowerPivot

It is not possible to change individual cells, even if they are inputs. Once an 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 in PowerPivot

powerpivot-change-cell-data

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.

powerpivot-column-names

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.

powerpivot-change-table-name

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 others. What you do to one cell happens to all the cells in that column.

In the example below I could add 100 to the cell in Excel but when I try the same thing in PowerPivot the entire column adds 100.

powerpivot-change-individual-formulaA 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.

powerpivot-errors

Conclusion

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.

To find out more about this great tool look at our PowerPivot course outline