Automatic change of data source in a pivot table

There is a problem with Pivot Table. When you add new row or column to source table, the pivot table won’t expand range. You must do it manually using menu “Change Data Source”. So this is the question, how can I do it automatically?

Example

The left table is a source and the right table is a pivot table. We want to add another rows or columns to the source and see them in the pivot table. Before that we have to format source table as Table 🙂

Step 1

Click somewhere on the source table. Then go to the menu “Format as Table” and choose one of the styles.

Confirm source table range.

Now, you can add data in rows or columns. Range of source table will expand automatically.

Step 2

Just refresh pivot table and you will see updated data.

Finish: