In the PivotTable Fields pane, drag the fields to the corresponding areas.ħ. In the Create PivotTable window, enter the range name you specified in step 2, choose where to place the PivotTable, and then click the OK button.Ħ. Then it returns to the Name Manager window, you can see the new created name range is displayed in the window, please close it.ĥ.
Please change them based on your own source data range.ģ. Note: In the formula, 'dynamic pivot with table' is the name of the worksheet that contains the source range $A $1 is the first cell of the range $A$A is the first column of the range $1$1 is the first row of the range. =OFFSET('dynamic pivot with table'!$A$1,0,0,COUNTA('dynamic pivot with table'!$A:$A),COUNTA('dynamic pivot with table'!$1:$1)) Copy the below formula into the Refers to box.Enter a name for the range in the Name box.In the Name Manager window, click the New button to open the Edit Name dialog. Select the source data range, click Formulas > Name Manager. In this section, I will show you how to create a dynamic PivotTable with the help of the OFFSET function.ġ. Then you can see the PivotTable is refreshed with the expanding data as below screenshot shown.Ĭreate a dynamic Pivot Table by using the OFFSET function Now if you add new data to the bottom or right of the source range, go to the PivotTable and right click it, and then click Refresh from the context menu. In the PivotTable Fields pane, drag the fields to the corresponding areas.ĥ.
In the Create PivotTable window, select where to place the PivotTable and click OK (In this case, I place the PivotTable in current worksheet).Ĥ. Keep selecting the table range, click Insert > PivotTable.ģ. Then the source data has been converted to a table range. In the opening Create Table dialog, click the OK button.Ģ. Select the data range and press the Ctrl + T keys at the same time. How to refresh a Pivot Table with expanding data in Excel? The methods in this article can do you a favor.Ĭreate a dynamic Pivot Table by converting the source range to a Table rangeĬreate a dynamic Pivot Table by using the OFFSET formulaĬonverting the source data to a table can help to refresh the Pivot Table with the expanding data in Excel. But if you add new data to the source range, such as adding new rows or columns data to the bottom or right of the source range, the expanding data can’t be added into the Pivot Table even manually refresh the Pivot Table. Normally, a Pivot Table can be refreshed with updated data in the source data range. How to create a dynamic Pivot Table to auto refresh expanding data in Excel?
0 kommentar(er)
