How to Update the Data in a Pivot Table

Author Zaheer    Category Guides     Tags ,

A Pivot Table is very useful in deriving meaning from data. This is also very helpful in analyzing and getting an overview on the relationship of the data between the columns and the rows. Manipulation of data, viewing of data in different perspective, and other data related action can be done in the Pivot Table.

After you have created the Pivot Table and inputting data from your existing data on the workbook you can update the data in the Pivot Table. Also, while updating your data you can also change the design of the Pivot Table with your own preference without altering the current data and information n your table. Aside from giving you the freedom to update the data in the Pivot Table, you can also organize the data in a very organized manner. But, before anything else you must input first the data in Microsoft Excel.

Also, while inputting your data do not forget to set or select the “preserve formatting on update” selection or dialogue box. In this manner, any update on the data on the Pivot Table will automatically take place. Also, this command will immediately save changes on the data and even after refreshing it. Likewise, remember that the update on the data of the Pivot Table will only take place if the original data is updated or changed.

After updating the source of the data you can now update the data on the Pivot Table. The process would first start on placing the cursor in the worksheet where the original data is placed or on the database. After doing so, place or point the cursor on the cell of the data you will be updating. Now, change the numerical data. After, you can now return or o back to the Pivot Table and check the update. The steps done were the updating of the data in the database of the original table in Excel.

Next step is to update the data on the Pivot Table. First, go back to the worksheet where the Pivot Table is. After, find the image which has an exclamatory point mark which has a red color. Then click on the image to automatically generate the data which was changed in the original worksheet or database.  The update or changes made on the must automatically take place after clicking the image because if you won’t click on the image no updates will take place.

In another manner, if the Pivot Table you have created is linked to another Pivot Table but still in the similar workbook, in another workbook, or unto another source or data base, you can always perform the operation to refresh to update the data on you Pivot Table. Every time you update your data base, make sure to refresh your Pivot Table to acquire the recent updates on the data you have inputted on your data base. Also, you can command that the Pivot table be automatically refreshed every time you add, input, or update data.

To manually refresh or update data on your Pivot Table just click on the Pivot Table workspace. The PivotTable Tools box will now be visible on the workspace with the adding Options and the Design tab. Select on the Options tab, click on the Data group and press Alt+F5 or hit the Refresh button. Also, you can click the right click of your mouse and click on Refresh. To be able to refresh and update all data in the Pivot Table workbook, click on the Refresh button and select on Refresh All. To maintain the same format of the Pivot Table, select first the Autofit Column Width on Update and then select Preserve cell formatting on update.

To review the status of your data or to cancel the refresh command you can do it by hitting Options tab, select on the Data group, and click on the Refresh button row. After doing so, click the Refresh Status for you to be able to see the current status or hit Cancel Refresh to stop the update.

You can also automatically refresh the inputted data when you open your Pivot Table workspace or file. Here is how, click on the report anywhere, this will eventually display the PivotTable Tools wherein you will see the adding Options with the Design tab. Now, on the PivotTable group select on Options. And, in the Pivot Table Options dialog box you will see the Data tab, on the Data tab select the Refresh data when opening the file.

Post comment