Copy formulas when an external data range expands - Help and How-to

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Printer-Friendly Version

Copy formulas when an external data range expands

Applies to: Microsoft Office Excel 2003

Enter a formula in a cell adjacent to the first row of data in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.).
Note The first row of data might be the first or second row in the external data range, depending on whether the first row contains headers.
Select the cell and double-click the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) to copy the formula to all rows in the external data range.
Click a cell in the external data range, and then click Data Range Properties on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.).
In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.
Note If the external data range expands when you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.), Microsoft Excel copies only those formulas that are immediately adjacent to or within the external data range.