Excel knowledge needed to properly use PowerPivot

power pivot training courses

Excel knowledge needed to properly use PowerPivot

Although PowerPivot is new, it does rely on some knowledge of certain Excel features. Some are nice to know features (conditional formatting in a Pivot Table) and some are critical. Below we cover the use of Tables in Excel which is critical for PowerPivot use.

Tables in Excel

Watch the video or read the extract below

Tables are a new feature in the more recent versions of Excel. Below is a quick introduction (you can use the sample file you downloaded called ‘Difference-between-excel-and-PowerPivot-use-20xx’ to try it out)

If you have a block of data, you can switch the table on by highlighting the entire block (not necessary but best practice) , clicking on INSERT and then the TABLE button. It gives you the option to change the area and asks whether the data has headers or not.

excel-table-feature-for-powerpivot-1

You will notice that once you click OK the formatting becomes much cleaner but it does more than that.

excel-table-feature-for-powerpivot-1

These cells are now associated and Excel is aware that they need to exist as a block of data. So for example if you add a new row at the bottom by typing some information, Excel will automatically include it in the table. Furthermore, if you add a new column adjacent to the table, and enter a formula in a single cell, Excel will automatically copy that formula to all the cells in the column and it will be incorporated into the table.

This means that you don’t need to redefine any ranges for

  • pivot table ranges or
  • graphs or
  • VLOOKUP areas or
  • anything that looks at a range.

Excel will automatically adjust all tools or formula that look at the table to incorporate the new row/ column.

When you click on a Table you will see you get a new Ribbon option. This has many features and options.

For our purposes, the key one is to give the table a meaningful, descriptive name which is critical for use within PowerPivot (you will see why Tables are so important for PowerPivot when you cover the section on Importing data into Pivot Tables). Type the name (no blanks allowed so use _) and click enter.

excel-table-feature-for-powerpivotNow that you have the basics, let’s really learn how to use PowerPivot. In the next sections you will learn all about how to:

  • Import data into PowerPivot (from many various sources)
  • Manipulate the data in PowerPivot
  • Create relationships between the data
  • Develop Pivot Table dashboards
  • Build DAX Measures to enhance your dashboards and pivot tables and provide you with the information you need to do you job (quickly!).
Lesson tags: powerpivot
Back to: PowerPivot Training Online > Starting to use PowerPivot