Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy		PowerPivot from Identical Structure Excel Files Introduction Create a Connection in the Workbook Combine the Data in PowerPivot Change the SQL Statement Create the Pivot Table Watch the PowerPivot Video Download the PowerPivot from Identical Workbooks sample files Introduction PowerPivot for Excel 2010 is a powerful data analysis add-in that will let you work with millions of records within the familiar Excel environment. To download the free add-in, go to the PowerPivot page on the Microsoft website. You can use PowerPivot to create a pivot table from multiple Excel workbooks or worksheets, by using the Primary and Foreign keys to join the tables. For example, there could be a 'ProductID' field in an Orders table and a PriceList table. Occasionally you might want to create a pivot table from files where you can't use keys to join the tables. Maybe you have workbooks for different years' sales data, or budget data from several departments. In this example, we'll combine data from two Excel files. The files have different data, but an identical structure -- sales data for the East and West regions. In this case, we can't use a key to connect the tables; instead, we want to create one combined table from all the data. The following technique allows you to import more than a million records from Excel, despite the fact that one worksheet can only contain up to 1,048,576 rows. Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip with us. You can see more of Kirill's work in the Contextures blog posts on Combining Data from Two Excel Files in a Pivot Table. Create a Connection in the Workbook The key to combining data from identical files is to start by creating a workbook connection, before you launch PowerPivot. On the Excel Ribbon's Data tab, click Connections. In the Workbook Connections window, click Add At the bottom of the Existing Connections window, click Browse for More. Navigate to the folder where your files are located. In this example the files are in the C:\_TEST folder Select one of the files that you want to import -- EastSales.xlsx in this example -- and click Open. Select a table to import, and click OK. The new connection appears in the Workbook Connections window. Combine the Data in PowerPivot Close the Workbook Connections window, and on the Ribbon, click the PowerPivot tab. Click PowerPivot Window, to launch the PowerPivot add-in. Note: I'm using Windows XP, so the PowerPivot window has a menu bar in the screen shots below. If you're using Vista or Windows 7, you'll see a Ribbon instead. On the Table menu, click Existing Connections, or, on the Ribbon, click Design, then Existing Connections. At the bottom of the Existing Connections window, under Workbook Connections, click on the connection that you added, and click Open. In the Table Import Wizard, click Next, then select the table, and click Finish After the data is successfully imported, click Close. Change the SQL Statement Now that the first table has been imported, you can change its properties, to combine it with data from the second table. On the Table menu, click Table Properties, or on the Ribbon, click the Design tab, then click Table Properties. From the Switch To drop down list, select Query Editor. Edit the SQL statement, to create a union query, combining the two tables. In this example, the SQL statement is: SELECT [EastSales$].* FROM [EastSales$] UNION ALL SELECT * FROM `C:\_TEST\WestSales.xlsx`.[WestSales$] After you change the SQL statement, click the Validate button, to verify that the statement is correct, then click Save. Note: The SQL query string can also be edited in the Excel workbook connection window, but there's no Validate feature there: On the Excel Ribbon, click the Data tab, and click Connections Select the connection you want to edit, and click Properties. On the Definition tab, edit the SQL string in the Command text box. Create the Pivot Table Now that the data from the two files are combined, you can create a pivot table from the combined data. On the Toolbar, click the Create a PivotTable button, or on the Ribbon, click the Home tab, then click PivotTable. Select a location for the pivot table, and click OK. In the PowerPivot Field List, add  fields to the pivot table layout, to see a summary of the combined data. Here's the pivot table that was created from the combined data, with columns for the East and West regions. The Report Layout is Tabular, and Number format is used, with thousands separator and zero decimals. Download the PowerPivot from Identical Workbooks sample files Watch the PowerPivot Video To see the steps for combining data from multiple tables in PowerPivot, please watch this PowerPivot from Identical Excel Files video tutorial. Download the PowerPivot from Identical Workbooks sample files

Pivot Table Tutorials Pivot Table Introduction  Clear Old Items in Pivot Table Create a Pivot Table in Excel 2007  Custom Calculations  Data Field Layout Dynamic Data Source FAQs - Pivot Tables Field Settings Filter Source Data   Filters, Top 10  Filters, Report Filters GetPivotData Function Grand Totals Grouping Data Layout, Excel 2007 Multiple Consolidation Ranges Pivot Cache    PivotTable Style Printing a Pivot Table    Protection   Running Totals   Show and Hide Items  Sorting Subtotals  Summary Functions Unique Item Count

Pivot Table Books Beginning Pivot Tables (Excel 2007)  Pivot Tables Recipe Book (Excel 2003)  Pivot Tables Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Power  Pivot Play PLUS  Pivot Table Videos Clear Old Items Copy a Custom PivotTable Style Create Pivot Table in Excel 2007 Create Pivot Table from Multiple Sheets Data Field Layout Date Filters, Add GetPivotData Group Data Layout, Excel 2007 Report Filters, Add Running Totals Select Sections Subtotals, Create Multiple Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish

Last updated: February 27, 2013 10:36 AM