45. Videotutorial – Deal with Pivot Tables 5 – Grouping

Another episode of tutorials Deal with Pivot Tables continues with the topic Grouping of dates of intervals. You can simply view the summary according to time of year, for example total for each month or quarter. Also you can see the value in increments of thousands or in other units. 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.

From the previous parts you already know how to create and set up a basic Pivot table. The topic “Grouping of values”, which in this tutorial we will show, requires a properly selected data, and therefore we create entirely new Pivot table. We will use again spreadsheet of travels. Just to remind, you create new Pivot table so that we stand in the source table, select the Insert tab, click PivotTable, check the settings and select OK.

The first grouping options we try is about dates. Drag the Date (Datum platby) field to the Rows section, and the Cost (Částka) field to the Values ​​section. The resulting summary shows the total cost of travel by day. But it would be much better to add up the sum in months or years. And the fact is Pivot table possibility of a simple grouping. Just right mouse click on Dates and choose Group. Because Excel recognizes that this is the date vale, it offers possibilities for meaningful summary of periods. You can also select multiple options, we try Years and Months. Once you confirm your settings, Pivot table simply calculates the overall totals.

I return back Pivot table to the default blank format and try a second option. This time, drag the Amount field into Rows and ID field to Values. We understand that Pivot tables shows the frequency of the individual costs. Such information does not yet have a general explanatory value. But we can regroup Costs so that they are counted in the intervals by e.g. 20 000. Click the right mouse on Cost and choose Group. Excel will understand what we are going to do and offer formation intervals. You can set the initial and final value and incremental change. Let’s set the limits of 0 to 100 thousand incremental change is 20 000. After confirming we will see the result.

Other tricks for grouping I like to teach you on my training. The next time you can enjoy the demo to create custom calculations in Pivot table, without having to change the source data.