Hi Pete

If you have given the correct range, then it should retain the values.
However, it is better to create a Table first.
Click on a cell within your data table and choose>Insert>Table
This will create a Table of your data, which is dynamic, and will grow as
you add more rows. Excel will give it a default table name, which you can
change if you wish.
Then click on the Data>Design tab and click Summarise with Pivot Table in
the Tools section and Excel will default to using your Table name.
Click OK and you will be on a new sheet with the Pivot skeleton and a list
of field names to populate the different areas.