Forum Discussion
Auto update and format pivot table from Excel import
Hello, I have no idea if this is possible, but I thought I would ask. There is a website that tracks people my company trains for a position. I am able to go to the site and export an excel file for...
You should think about using Get Data (Power Query) for this. It will allow you to pick the CSV file for import, then apply steps such as removing unnecessary columns. When you finish all the steps to get the data you want, then it will load into the workbook and you can set up a PivotTable on it. Next time, if you save over the CSV file (new file with same name as the previous), you can simply press Refresh All in Excel to bring in the new data. You will also need to press the refresh button on the PivotTable after bringing in the new data.
To get started, just go to the Data tab -> From Text/CSV, then pick your CSV file.
Then click the Transform Data button to be brought into the Power Query editor, where you'll find all the ways that you can transform your data. Browse the buttons on the ribbon tabs. You'll notice Remove Columns.
The great thing about setting up your data import this way is that the steps are recorded so they'll be automatically applied next time your CSV is updated and you press Refresh All.
To get started, just go to the Data tab -> From Text/CSV, then pick your CSV file.
Then click the Transform Data button to be brought into the Power Query editor, where you'll find all the ways that you can transform your data. Browse the buttons on the ribbon tabs. You'll notice Remove Columns.
The great thing about setting up your data import this way is that the steps are recorded so they'll be automatically applied next time your CSV is updated and you press Refresh All.