- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Linking an Excel Table to PowerPivot
When you convert your Excel data to a table and create a link between the table and PowerPivot, changes to the Excel data can be passed through to PowerPivot using the Update All icon on the PowerPivot tab of the Excel ribbon.
Follow these steps to convert your normal Excel data to a table:
- You should have a single row of headings above the data. If your headings use more than one row, you should convert them to a single row. One easy way to do this is to enter the first heading cell, press Alt+Enter, and type the second heading cell.
- Select one cell in your data and press Ctrl+T.
- Excel will show you the range of data. Provided you have no blank rows or columns, this will be correct.
- Excel will apply a default format to your data. If you don't like the formatting, open the Table Styles drop-down and choose a new style. Note that the very first style in the gallery contains no formatting.
- Look on the left side of the Table Tools Design tab. Excel chose an unimaginative name along the lines of Table1 for this table. Type a new name that describes the data.
- On the PowerPivot ribbon tab, choose Create Linked Table. Excel will open the PowerPivot window.
You will see your data appear in the PowerPivot window. Additional ribbon tabs and icons are available in the PowerPivot window.
Using Copy and Paste or Linked Tables will work when your data is small enough to fit in the 1,048,576 rows available in Excel. When your data is larger than that, you will need to import the data from another format.