Update Pivot Table From Text File

In addition to creating pivot tables from Excel data, you can also use external data sources, such as a text file, like the billing data shown below.

Import the Data

In this example, the billing data was imported to Excel from the text file, onto a worksheet named BillingData. This created an external data range with a connection to the text file.

You can create a pivot table based on the imported data, to show a summary of the billing data.

Update the Text File

If new billing records are added to the text file, they appear in the external data range when it’s refreshed. However, you might not see the new data in the pivot table that is based on the imported data.

In the screen shot below, there are now 21 invoices in the imported data, but even after refreshing the pivot table, it still shows only 18 invoices. The latest data doesn’t appear in the pivot table.

Use the External Range Name

When you create a pivot table from an external data range, the default data source is a reference to a range of cells, such as BillingData!$A$1:$J$19, where the external data range is located.

If you use the external data range’s address as the pivot table source, it does not automatically expand, if new data is added to the external data range.

However, when you import external data to an Excel worksheet, a named External Data Range is created, and if you base the pivot table on this named range, it adjusts automatically if records are added or removed.

Change the Pivot Table Source

If a pivot table is based on a specific range of cells, you can change its data source, so it uses the external data range name.

1. To see the name of the external data range, right-click a cell in the external data range, and then click Data Range Properties. The range name is shown at the top of the External Data Range Properties dialog box. Click OK to close the dialog box.
   • 
2. To base the pivot table on this range, select a cell in the pivot table, and then click the Options tab on the Ribbon.
3. In the Data group, click Change Data Source.
4. With the Table/Range highlighted, press the F3 key, and click on the external data range name, e.g. Billing_1
   •  
5. The Table/Range box will show the sheet name and the external data range name.
   • 
6. Click OK, to close the Change PivotTable Data Source box.

After the pivot table data source is changed to use the external data source named range, it will update automatically, if data is added or removed.

• Refresh the pivot table to see the new data in the summary.
  • 

_______________________