Pivot tables are a valuable tool for transforming data into a more comprehensible format by sorting, reorganizing, grouping, and aggregating the information contained within a dataset or database. This process is achieved by repositioning data fields between rows and columns, creating a compact dashboard that can be manipulated with ease through the drag-and-drop feature on the right side of an Excel spreadsheet. Pivot tables provide an efficient means of summarizing and analyzing large amounts of data.
In this guide, I will demonstrate the process of creating a dynamic pivot table and illustrate how the pivot table adjusts its defined range automatically when new entries are added to the dataset.
How to create a Dynamic Range for a Pivot Table in Excel
We have a dataset consisting of stock price information, including the Stock Name, Closing Price, Price Change, 52 Weeks Low, and 52 Weeks High for 50 stocks within the NIFTY 50 portfolio. On the right-hand side of the dataset, column G features a pivot table with the heading "Stock Name." This pivot table has been pre-filtered to only display stocks that contain "Bank" in their name.
Upon creating a new pivot table in Excel, the user is prompted to specify the range of the dataset to be analyzed in the pivot table. Typically, users manually define the range by selecting it from the dataset.
To ensure that the pivot table automatically adjusts its data source range and captures new entries, such as "ABC Bank," a name must be assigned to the dataset. This can be achieved by going to the Formula Tab and selecting "Define Name."
Formula Tab > Define Name
In the "Enter a name for the data range" field, the user should define a name for the dataset, such as "StockDataset." In the "Select the range of cells" field, the user defines the range of the dataset.
However, to make the range dynamic, the OFFSET Function should be used.
=OFFSET(reference, rows, cols, height, width)
reference = the starting point of the dataset i.e., the reference cell from where the dataset starts. [ in our example, reference is "A2" ]
rows = it refers to the number of rows that users want to excel to ignore. Suppose, if the dataset starts from the A2 but we want excel to ignore the first 4 rows from the reference cell then rows will be 4. [ In our example, rows is "0" which means the starting point is "A2" only and not "A6"]
cols = it refers to the number of columns that users want to excel to ignore. Suppose, if the dataset starts from the A2 but we want excel to ignore the first 2 columns from the reference cell then cols will be 2. [ In our example, cols is "0" which means the starting point is "A2" only and not "C2"]
height = it refers to the number of rows that users want to excel to consider in the range. Suppose, if the dataset starts from row 2 and ends at row 52, then the height will be 51. [ In our example, we want excel to identify such numbers i.e., 51, and not hardcoded so as to make the dynamic range height. We use COUNTA Function inside the height field of the OFFSET Function- =COUNTA(A:A) ]
width = it refers to the number of columns that users want to excel to consider in the range. Suppose, if the dataset starts from column A and ends at column E, then the width will be 5. [ In our example, width is "5" ]
In our example, the OFFSET Function will look like this-
=OFFSET(DynamicPivotTable!$A$2,0,0,COUNTA(DynamicPivotTable!$A:$A),5) and then press OK.
The next step is to specify the data source range of the pivot table. This can be done by going to-
PivotTable Analyze > Change Data Source
In this location field, the name of the already defined dataset (in our example, "StockDataset") must be entered, and press the OK button.
It’s done!
With the dynamic data source range in place, if a new entry (such as "ABC Bank") is added to the dataset, it will automatically be incorporated into the pivot table upon refresh.
Creating a dynamic range for a pivot table in Excel is a useful technique to simplify data analysis and save time. With the use of the OFFSET function, you can make sure your pivot table always captures the latest data, even when new entries are added to your dataset. This feature is particularly helpful when working with large data sets and enables you to perform efficient and accurate data analysis with ease.
Comments