Say I have a pivot table in sheet2 and the raw data is from sheet1. Is there any way that let me can add more rows to sheet1 periodically and the pivot table still can auto update?
Make your data range on Sheet1 a List. Then make sure your pivot table's data range is the list, not just a cell range. This is automatic if you make your pivot table from an existing list.
When you add data to a list, the list automatically grows. All you need to do is hit Refresh on your pivot table and it will reflect any changes/additional data in the list.