How would you provide a Dynamic range in “Data Source” of Pivot Tables?

To provide a dynamic range in “Data Source” of Pivot tables, first create a named range using offset function and base the pivot table using a named range created in the first step

Simplest way to provide a dynamic range as a data source of a pivot table is to format the source range as a Table. So, when the table is updated like a row is added or a column is added, the pivot table will automatically recognize the added rows and the column is also available if you wish to modify the structure of the pivot table.