Excel 2010 provides many features that aid users in the analysis of large datasets. The increased number of rows to over 1,000,000 is a huge improvement over the 65,000 row limitation in Excel 2003. Additionally, Excel 2010 makes it easier than ever for users to create a dynamic link between an Excel workbook and data in SQL Server, Analysis Services, XML data, Access data and many other data types. PivotTables have long been a reliable tool for data analysis in Excel.
PivotTables create a summary for large datasets by grouping records by common values in selected fields. The grouping can occur by row as well as by column and the grouping can easily be changed by re-assigning different fields to the row and column grouping. The data assumes a sum summary, however, count, average and many other summary calculations may be used.
PowerPivot is a 2010 Excel add-in that allows PivotTable summarization for data that exceeds 1,000,000 rows. The add-in adds a new tab to the Excel ribbon for managing the data. The feature does not allow data editing, only analysis. Not only does PowerPivot allow users to analyze large datasets, but it also allows linking of data using a one-to-one or one-to-many relationship. The result is a rich dataset that can then be analyzed in a PivotTable. The add-in may be downloaded at: Microsoft PowerPivot
PowerPivot stores only the raw data so the data is viewed in Excel PowerPivot, but the PivotTable generated by the data is viewed in the same Excel Workbook. Since the same tools are used to create a PivotTable from PowerPivot data as other data sources, the analyst will utilize prior knowledge of PivotTables and PivotCharts to examine this new rich datasource. A function set called Data Analysis Expression Language (DAX) may be used perform dynamic aggregation for date and time functions, filtering functions and more. The Slicer makes filtering PivotTable data easier to visualize.
PowerPivot add-in is available for both 32-bit and 64-bit Excel 2010. Only the 64-bit Excel will allow analysis of more than 1,000,000 records. PowerPivot data may also be used in SharePoint 2010 and Reporting Services. One-day Excel PowerPivot classes are available at Levi, Ray and Shoup Education Services. Students will import data into PowerPivot, link data, create PivotTables and PivotCharts and use DAX functions.