50. Videotutorial – Deal with Pivot Tables 10 – More tweaks

The last part of tutorials Deal with Pivot Tables represents tweaks that do not fit into previous episodes and it is worth to know them. Learn eg. how to fill the empty cells with zero value, to generate the detailed in-depth analysis and how to update and expand the source table. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Please note that this tutorial is presented in czech language, with english subtitles.

Source file can be downloaded here.

We are in the last episode of this video series and I will show you useful tweaks that did not fit into previous episodes. We begin with a way how to update data in the completed Pivot table. In the source worksheet change some value and the change we need to move into the calculation of the PivotTable. The update does not occur automatically, just click inside with right mouse and select Refresh. Furthermore, it happens that in the source table are gradually growing rows and columns. To make these changes even caught a Pivot table, you need to resize source table through the Analyze tab > Change data source. Now we reselect current table. To facilitate it we can select directly all the columns if the table starts with headings in the first row of the sheet, and thus the selection is automatically included in future increasing lines and then I just need to update the data by selecting Refresh.

Let’s also see the possibility of setting up a Pivot table. Click the right mouse button to select PivotTable Options. Here we find eg. the option to automatically fill in any empty cells without calculations with the value of 0 or another selected, and this also applies to the value that contains the error.

If we turned the table layout into the table form, remind you way through the Design > Report Layout > Show in Tabular Form, can be useful in settings enable the Merge and center cells with labels. It should merge group heading information, but it is only a visual matter.

The last tweak I want to show you is called in-depth analysis. Just tap into any number of PivotTable and this will create a new sheet with lines from the original source table from which the number was calculated. Whenever we can trace the details of the source of our selected result. This sheet is no longer linked with the data, but can be used to check them or delete them later.

This brings us to the end of the series Deal with PivotTable. I hope I’ve convinced you that this tool can do a lot of useful things. Everything is described in more detail on my trainings, where I would like to see you, just contact me through the website www.itlektor.cz.