Contact us! 302-737-6230
 
In this tutorial I am going to show you how to create a PivotTable from data stored in another workbook. Before we get started, make sure your data is suitable for a PivotTable, e.g. each column should have its own unique heading. Now that you have your data ready, let's get started.

In the first method, I will show you how to do this from the workbook where the PivotTable will be located. Let's call this workbook, wbPivot. In the second method, I will show you how to do this from the workbook where the data is stored. Let's call this workbook, wbData.

First Method: Create PivotTable from the workbook, where the PivotTable will be located (wbPivot).
  1. Make sure you have both workbooks open.
  2. Select the cell in wbPivot where you want the PivotTable to be located.
  3. In the ribbon, click on the Insert tab and under the Tables group, select PivotTable
  4. Under Choose the data that you want to analyze, select Select a table or range.
  5. Alt+Tab to wbData; select the entire range of your data, including the column headers; and press Enter on your keyboard.
  6. Make sure the address in the Table/Range box is correct for your data.
  7. Under Choose where you want the PivotTable report to be placed, make sure Existing Worksheet is selected and the cell, where you want the PivotTable to be located, is correct in the Location box.
  8. Click OK.
wbPivot
Second Method: Create PivotTable from the workbook where the data is stored (wbData).
  1. Make sure you have both files open.
  2. In wbData, select any cell within your data.
  3. In the ribbon, click on the Insert tab and under the Tables group, select PivotTable
  4. Under Choose the data that you want to analyze, make sure Select a table or range is selected and the entire range of your data, including column headers, is correct in the Table/Range box.
  5. Under Choose where you want the PivotTable report to be placed, select Existing Worksheet.
  6. Alt+Tab to wbPivot; click the cell where you want the PivotTable to be located; and press Enter on your keyboard.
  7. Make sure the address in the Location box is correct for the PivotTable.
  8. Click OK.
wbData
Connect with Allan Chara on Google+
 


Comments

Katie
02/05/2014 10:41pm

Thanks for this, it works nicely. One thing to note is that if you create the workbooks in two separate Excel windows (ie you can see them in two separate windows, not having to Alt&Tab to get to them) this wont work. You need to create the first, then within that window create the second.

Reply
02/12/2014 4:42pm

Great quick tutorial, how about a method to refresh or change the source string to reference a closed book?

Reply
02/15/2014 10:57am

Derik, you can reference a closed workbook through the "Use an external data source" option in the Create PivotTable dialog box.

Reply
10/08/2014 1:55am

Nice to see your great article Chara. Education is the best to make us perfect in our daily tasks. Many students try to get their essays from the best essay services. It is a great way to express your ideas in your writing.

Reply
10/20/2014 6:24am

They are favored because students can work directly in their books, eliminating the need for looseleaf and copying questions from a textbook.

Reply



Leave a Reply