5 Cool Things You Can Do With Power Pivot for Excel

Work With Very Large Data Sets

The maximum number of rows in Excel is 1,048,576.

With Power Pivot for Excel, there is theoretically no limit on the number of rows of data. The actual limitation depends on the version of Microsoft Excel you are running and whether you are going to publish your spreadsheet to SharePoint.

If you're running the 64-bit version of Excel, Power Pivot can reportedly handle about 2 GB of data, but you also must have enough RAM to make this work smoothly. If you plan to publish your Power Pivot based Excel spreadsheet to SharePoint, be sure to check what the maximum file size is. 

Power Pivot for Excel can handle millions of records. If you hit the maximum, you'll receive a memory error.

If you want to play with Power Pivot for Excel using millions of records, download the Power Pivot for Excel Tutorial Sample Data (about 2.3 million records) which has the data you need for the Power Pivot Workbook Tutorial.

Combine Data From Different Sources

Excel has always been able to handle different data sources, such as SQL Server, XML, Microsoft Access and even web-based data. The problem comes when you need to created relationships between various data sources.

There are third-party products available to help with this, and you can use Excel functions like VLOOKUP to "join" data, but these methods are impractical for large ​datasets. Power Pivot for Excel is built to accomplish this task.

Within Power Pivot, you can import data from virtually any data source. One of the most useful data sources is a SharePoint List. You can use Power Pivot for Excel to combine data from SQL Server and a list from SharePoint.

When you connect Power Pivot to a SharePoint list, you are technically connecting to a Data Feed. To create a Data Feed from a SharePoint list, open it and click on the List ribbon. Then click on Export as Data Feed and save it.

The feed is available as a URL in Power Pivot for Excel. Check out the white paper Using SharePoint List Data in Power Pivot (it's an MS Word DOCX file) for more information on using SharePoint as a data source for Power Pivot.

Create Visually Appealing Analytical Models

Power Pivot for Excel lets you output a variety of visual data to your Excel worksheet. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts, and a Flattened PivotTable.

The power comes when you create a worksheet that includes multiple outputs, which provides a dashboard view of the data that makes analysis easy. Even your executives should be able to interact with your spreadsheet if you build it correctly. 

Slicers, available with Excel 2010 and later, add buttons that you can use to filter table or PivotTable data.

Use DAX to Create Calculated Fields for Slicing and Dicing Data

DAX (Data Analysis Expressions) is the formula language used in Power Pivot tables, primarily in creating calculated columns. Check out the TechNet DAX Reference for a complete reference.

You can use DAX date functions to make date fields more useful. In a regular Pivot Table in Excel that included a correctly formatted date field, you can use grouping to add the ability to filter or group by year, quarter, month and day.

In Power Pivot, you need to create these as calculated columns to accomplish the same thing. Add a column for each way you need to filter or group data in your Pivot Table. Many of the date functions in DAX are the same as Excel formulas, which makes this a snap.

For example, use =YEAR([date column]) in a new calculated column to add the year to your data set in Power Pivot. You can then use this new YEAR field as a slicer or group in your Pivot Table.