The Pivot Tables in Excel, work better when the source is a Table.
Please follow the following steps to convert the data into a table.
- Select all the cells that you have data in (From the image it looks like "A1:D11").
- In the Home tab in the ribbon, select Format as table and choose a style. Or Alternatively, you can use CTRL+T as a shortcut to create a table.
- In the create table window, make sure you choose "My table has headers" and click OK.
Following the above steps converts the data into a Table.
Now when you click on any of the cells in the table and Insert Pivot table from the Insert tab, the table will be selected as the source for the PivotTable. And whenever you add data at the row below the last row of the table and hit enter, the Table will automatically expand. After that, you can simply refresh the PivotTable.