Excel triki #1 Filtriranje vrtilne tabele

Great! You have your population data cleaned after hours of working hard (if you don’t use Power Query) and it is almost ready and set.

Imported via Power Query

Basic pivot table

So, now you have to divide data on separate sheets for each country with its cities’ population. The task would not be that hard if you had a presence in, for instance, China only, but your cities are in many different countries. Imagine copy-paste for almost 40 times for each country. Waste of time! Let me explain.

Population by cities Pivot Table

Filter field

Once you’ve created your Pivot Table, let’s focus on the Filter field. In order for the three-click trick to work, the criteria by which we are separating the data set (in our case countries) must be in the Filter field. So, we simply drag the Country column into the filter field, a standard Pivot Table procedure. After that, let’s focus on the Analyze ribbon of our Pivot Table (if not visible, just click on the Pivot Table). Once you clicked on it, on the left-hand side there is an options button. Click on the black arrow -> Show report filter pages. Excel will ask you then by which criteria you would like to filter.

Pivot Table is asking us by which criteria we should filter. Click OK.

Of course, since we already did our homework and dragged the Country column to filters, it shows us exactly what we wanted. Just click on the OK button and let Excel do the magic.