Create a pivot table based on data from an external source

To create a pivot table using data from an external source, follow these steps:

1.Open the Create Pivot Table dialog box in one of the following ways:

•On the Insert tab, click  Pivot table. In the menu that opens, select Use external data source.

•When working in macOS, select the Insert > Pivot table > Use external data source command from the command menu.

2.In the Create pivot table window, click Select source.

3.In the External data sources window, click Add source and select the data file for the pivot table in the file manager window that opens. If you want to use an external source already added earlier, proceed to step 5.

4.In the Select sheet window, select the sheet of the external document from the list. Clear the The first line of the data range contains titles checkbox if you do not want to use the first row as a title. After the sheet is selected, click OK.

5.Select the required document from the list of current sources and click OK.

6.In the Create pivot table window, specify where to insert the table:

•New sheet: Insert the table in a new sheet. The new sheet will be created automatically. By default, the new sheet will be named Pivot table <No.>, where No. is the number of the pivot table in the document you are working on. If needed, you can edit the sheet name.

•Existing sheet: Insert the table on the sheet that is currently displayed on the screen. In the Specify a destination cell box, enter the cell address manually or click to select an empty cell in the workspace where you want the upper-left cell of the pivot table to be located. There should be at least two empty rows above the initial cells for filters.

7.Click OK.