Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel enables you to build PivotTables from data found both inside and outside the workbook where you create the PivotTable. If you want to create a PivotTable from a data source you either can't or don't want to import into Excel directly, you can create a connection to that data source and work with it in place. So let's say that you want to create a PivotTable using data from another Excel workbook. To do that, you go to the Insert tab and click the PivotTable button. Then in the Create PivotTable dialog box you select the Use an external data source option and then click Choose Connection.
Then in the Existing Connections dialog box, if the connection or the data source that you want already appears, then you can click it and then click Open. In this case, I haven't created any connections yet so I will click the Browse for More button and this displays a version of the Open dialog box. In this case, it's named Select Data Source. The data that I want is in my Exercise Files folder and that is on my Desktop and it's in Exercise Files Chapter 01 and the file that I'm using for this exercise is called OutsideSource.
So, I can click that file and then click Open. Then the Select Table dialog box appears and the Select Table dialog box shows you the different data sources that you can use within that target data source to create your PivotTable. In this case, there's only one and that is named Sheet2 so it is selected. You can see it's highlighted here in blue. And then I happen to know from working with this data before that the first row of the data does in fact contain column headers, so I can leave that box selected and then, I can click OK, and Excel accepts that choice and sends me back to the Create PivotTable dialog box.
Now I want to create a PivotTable on an existing worksheet. So in this case, I have Sheet1, cell A1, which is selected here, and when I'm ready I can click OK. After I click OK, Excel creates the PivotTable on the Sheet1 and now I can create the PivotTable as I would normally. So let's say for example that I want to have my Company field in the Column Labels area and then the Year and Quarter in the Row Labels area and then the Revenue in the values area, and it's a PivotTable just like any other PivotTable. You can pivot it, filter it, add styles to it, and so on.
One important thing to note is that if you use an Excel workbook as your data source, you can't open that workbook while the workbook that contains the PivotTable is open. So in other words, if I display my Excise Files folder and I double-click Outside Source to try to open it, then Excel displays an Error dialog box indicating that the files in use and that it can't open it. In this case, the error is at the file format or file extension is not valid, but that's not the real error. In this case, the file is just in use by another file.
So we can click OK to close that dialog box and now if I close the external workbook and I'll do that by pressing Ctrl+W and I will not save my work and then,go back to my Excise Files folder, if I double-click Outside Source then the file opens normally. If possible you should always draw the data for a PivotTable from the original source so that any changes to that source are reflected in the PivotTable that use to analyze your data.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.