Excel pivot tables

If you want to extract certain data from a table into a smaller table, you could do it with the help of a pivot.
If you were to do this the hard way, you would create overly large formulas that could easily confuse you.

Using pivots

The first step when working with pivots is to have a well-defined table, that means you don’t have empty columns, empty rows, or spaces, so it’s important that your table is well formatted.

1. You click anywhere inside the table.
2. Go to Insert tab.
3. On the right side, you will see Pivot Table.
4. Click on the Pivot Table and get a menu.
   • The first thing Excel asks you is if this is the range we want.
     You can see this by what is highlighted, usually the entire table.
   • Excel asks you below whether you want to create a new worksheet and create a Pivot there or on an existing worksheet.
5. Let’s choose, for example, a new worksheet and call it Pivot 1.
   • Here we now have a Pivot Table and a Pivot Field on the side.
   • If you look to the right in the fields, you will see that the list is made up of your columns.
   • Below this are filters, columns, rows, and values.
   • Drag and drop a column into one of these fields and the table will start to align.
     Depending on the information you need and a little practice, you will understand how a Pivot Table works.

Value field settings in a pivot table

Same process for creating a Pivot Table as above.

Excel will automatically do the summation and insert it as the result of the value field.

If you want to change the formula that Excel uses, you do it by right-clicking on the cell value and choosing Value Field Settings.

There you have types of functions that you can use, if you choose COUNT for example, it will count how many values ​​there are, that is, how many times a certain thing from table is repeated.

You can also use design in the rows to make the data more beautiful.

1. Click on Design in Pivot Table Tools.
2. Then on Report Layout.
3. Show in Tabular form is an example of an option or display that you can use.

Calculated pivot table field

To use a pivot table calculated field, for example, if you want to calculate a tax percentage or something similar on the total values ​​in the pivot table.

1. In Pivot Table Tools, you would select Analyze and then Calculated Field.
2. You would get a menu where you can set the name of your field, and below that the formula you want to use for this field.
3. If you calculate a tax of say 15% on the total value, your formula would be something like this:
   = Total*15%
4. A new field will appear on the side, and you can use it in a specific table field (column or row).

Excel pivot table grouping

If you want to group an Excel pivot table, you would do it like this:

1. Go to Insert, while any mouse cell in the table is highlighted.
2. Go to Pivot Table.
3. What you add to the Rows field is sometimes grouped.
   Things we don’t want can simply be dragged and dropped from this box.
4. After you have selected the required data.
   • Click on Design in Pivot Table Tools.
   • Then on Report Layout.
   • Show in Tabular form is an example of an option or display that we can use.
5. In case you don’t get a split view, right-click on the cell, and select Group.
   Here you choose what you want to group things by.