41. Videotutorial – Deal with Pivot Tables 1 – Basic model

The video series Deal with Pivot Tables starts with a tutorial Basic model. You will learn how to create a simple Pivot table in Excel. 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.

Creation of a basic Pivot table is not a big deal. Before we go into it together, see what kind of data we work with. Source file to download can be found below the video.

We see a table of business trips. This is a column table with all column headings. Such table is suitable for creating a summary table, which is called Pivot and this videotutorial series is all about this tool.

Click anywhere to the table, and select Insert tab, click PivotTable. In the activated dialog, check address that corresponds to the selected area of our table, or reselect it. We usually put Pivot on a new sheet, therefore nothing else is changed in this window and confirm by OK button.

On a new sheet, there is a left space displayed for our Pivot table and on the right we can see so called the Field list, and also Pivot table sections.

To see the summary data, eg. how much does total cost for each country, move country field (Země) to ROWS and total (Částka) to VALUES. Generally, the data that you want to group – individual values without duplicates, are placed in ROWS or COLUMNS, depending on the direction in which the values will be displayed. Calculations are always in VALUES section.  Filters (report filter) section we learn in more detail in a forthcoming parts.

To move the country field (Země) from ROWS to COLUMNS just drag and drop it. To remove pull it to the top Field list, to be displayed again drag back.

So, this is Pivot table that for the country summarizes the total cost of trips. Next time we will look at what basic summary functions in the VALUES Pivot table offers. Do not forget to watch!