Using Pivot tables in Excel

It’s not hard to create a pivot table in Excel as long as you un­der­stand how pivot works. A pivot table is always based on a set of data that consists of several columns. The first row of the column must contain the name of the re­spec­tive category. The pivot table au­to­mat­i­cal­ly retrieves its in­for­ma­tion from this. It also makes sense to format the table as such (using the “format as table” function) and assign a table name. This makes the work easier, es­pe­cial­ly with several data sets.

There are two ways to create a pivot table in Excel: you can either navigate to the Piv­ot­Table function in the “Insert” tab, or you can use the table tools. However, this is only an option if you have formatted your data set as a table. Here, you can see the function “Summarize with Piv­ot­Table”. The same window is opened this way, but the cor­re­spond­ing section is already selected. In the template, users can decide where the new pivot table should appear: within an existing spread­sheet or in a new one.

Pivot tables provide countless functions that are extremely useful in the eval­u­a­tion of data. The ver­sa­til­i­ty of pivot in Excel is best described by means of an example. We’ve already created the pivot table itself; it contains a data set with a list of sales for one year. This includes the employees involved, the turnover, and the type of product sold. Note that the data set is a simple table with only the columns labeled and not the rows.

The new spread­sheet, in which data should now appear in the form of a pivot table, is com­plete­ly empty at the beginning. However, the window should now have a new sidebar: the Piv­ot­Table Fields. The cat­e­gories that you’ve defined for the columns of the data set should appear there. There are also four areas in the sidebar where you can drag the Piv­ot­Table Fields:

• Filters
• Columns
• Rows
• Values

By dis­trib­ut­ing the fields into the relevant areas, you are creating the actual pivot table. In general, it makes sense to define a field for the column names and one for the rows. Under “Filters”, you can specify the cat­e­gories that you would like to view in the history, e.g. those you would like to view in isolation. The area of most interest is “Values”. Fields entered here become the main focus of the table.

In our example, we enter the months in the rows and the employees in the columns. The pivot table au­to­mat­i­cal­ly analyzes the in­for­ma­tion it receives from the data set and de­ter­mines that twelve different months and four different employees were involved. The two product types are applied to the filters, allowing us to evaluate the table according to Package A or Package B.

Finally, our sales are reported under “Values”. Excel creates the table au­to­mat­i­cal­ly: a table with category ap­pro­pri­ate columns and rows is created from the original table with four columns related to the data set.

In our case, Excel au­to­mat­i­cal­ly de­ter­mines the amount of sales per month and per employee. Instead of the original monetary values, placing them under “Values” directly results in the “Count of sales”. This gives the user the option of placing the blank figures into context and then eval­u­at­ing them this way: Laura made two sales in April. The pivot table also displays the overall results au­to­mat­i­cal­ly: there were 3 sales in the month of June, and 24 sales for the whole year.

If you are more in­ter­est­ed in the monetary value of the sales, and less so in the number of sales trans­ac­tions, this can easily be altered. To do so, click on “Sales” in the “Values” field and navigate to the “Value field settings”. Here, under “Summarize values by”, change “Count” to “Sum“. As you can see, many other types of display are also available (the cal­cu­la­tion types cor­re­spond to the Excel functions).

For monetary values, it makes sense to have them displayed in the ap­pro­pri­ate cur­ren­cies. By clicking on the “Number format” button, you will be brought to a window that allows you to format cells (this is also available when working normally with Excel). From here, you can choose different formats as well as cur­ren­cies.