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?


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 🙂

Pivot table Data source

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.

add new data row

Step 2

Just refresh pivot table and you will see updated data.
refresh pivot table

New pivot table


Add a comment »2 comments to this article

  1. I thought that Refresh is enough. Why not?


  2. How can I include new row into my pivot table? I have a big data table which grows every day. I try to create formula for this but it failed. For me is very important to have this pivot table visible on the top of the sheet.


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress