Being that you said you're deleting the "raw data" and then open it again, I'm guessing you're not using Power Query to import the data. If you're unfamiliar with PQ, Mr. E has plenty of excellent videos on the subject as does the
ExcelIsFun channel on YouTube. (I mention ExcelIsFun because those videos all have sample files attached with both starting and completed worksheets which is really useful!).
I'm using Office 365. If you're using an earlier versions you should be able to find your way around. If you're using Excel 2010 or earlier, you'd need to download and install the Power Query add-in. Both YouTube channels should have details.
Assuming you're using a CSV file for your Pivot Table, you would go to the
DATA tab, and in the
Get & Transform Data use
From Text/CSV. After locating the file use the
Edit/Transform Data button to verify the data and then from the
Home tab select the
Close & Load DROP DOWN, and select
Close & Load To... then select what you want to do with it.
You can load it directly to a Pivot Table with no intermediate Table needed. If you have a large amount of data, checking the
Add this data to the Data Model will speed things up and reduce the file size significantly.
Now, when you get new data, you only need to
Refresh the Query and Pivot Table (it may take two Refreshes). Your Pivot Table will maintain the options you've chosen. If you haven't already, you may want to uncheck
Autofit column widths on update in the Pivot Table's properties.
I know this sounds like a lot of work. For simple situations Power Query is very easy to use, and it should take maybe a few hours to go through some videos and get going, but believe me it will be well worth the time! I used to have to export an "Excel" spreadsheet from a database with over 350,000 records and at least 20 columns and spend at least 10-20 minutes cleaning it up to make it useable daily. I only learned how to use Power Query two months before I was laid off. I could have saved literally hundreds of hours had I learned it years earlier.
Good luck!
