Microsoft Excel PivotTable - easy steps how to do it

Often listening listeners to Microsoft Excel training on how they would like to know to create pivot tables. I had the impression that this is something difficult for them, but the reality is not difficult at all. In the following lines I describe as quickly and easily create pivot table in Microsoft Excel. Let's do it!


First you say the definition of a PivotTable. According to the Wikipedia is as follows:

"In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet."

My representation of Microsoft Excel pivot table is following:

"Pivot Table quickly summarizes, analyzes and filters the data."

So I'll show you how I mean this:

We have example table in such contracts by salesman and date of sale. For each contract we assign turnover in dollars ($).
So you could imagine how such a table input data looks like, see (and click) below, or simply download it by clicking here

Microsoft Excel PivotTable - example table data

And now we need to know the total turnover by individual salesman for the entire period.

On average, my course participants would like use the filter and then sum. I say: "Is not so nice way but ok. But is it possible to calculate the total sales for each salesman per quarter?" 

What do you think, is it possible? :-). If yes, please write you way in comments below :-).

In my way we use Pivot Tables. Firstly we calculate total turnover by individual salesman for the entire period. Then we show how to calculate total turnover by individual salesman per quarter. 

We find PivotTable button in Microsoft Excel in tab Insert as following picture:

Microsoft Excel PivotTable - tab INSERT and PivotTable

The first step after inserting the PivotTable should select the range of cells that will be taken into calculation. Then we can choose whether we want PivotTable entered into a new or existing worksheet. Let choose a new worksheet:

Microsoft Excel Pivot Table - Create PivotTable

If you agree with the field data that Excel will automatically identified, then just OK.

The result is a newly created sheet which contains a sort of blank table on the left and the right column (PivotTable fields) with all the column names that you created in the source table:

Microsoft Excel PivotTable - PivotTable Fields

So let's go back to our entry: We need to calculate total turnover by individual salesman for the entire period. And now it comes ease pivot tables. Just tick the right option Salesman and Turnover. That's it! As you already noticed the Excel already filled in the required calculated data on the left side even the summaries:

Microsoft Excel PivotTable - PivotTable Fields SUM calculation

So it so easy, isn't? :-)

Now we want to calculate total turnover by individual salesman per quarter. So tick Sale Date on the right side:

Microsoft Excel PivotTable - Tick right PivotTable Fields - the date is additionally

The left table now includes the appropriate sale dates of each salesman. But we need quartes no individual dates. So click the right mouse button to any date (not to name or sum) and choose Group ...:

Microsoft Excel PivotTable - Group Dates

And in the opened window (Grouping) deselect Months and select Quarters (or any other period if you need). 

Microsoft Excel PivotTable - Group Dates - Grouping Quarters

Notice: You can also select more than one option as I did. For example if you plan to calculate total turnover per quarters then select Quarters.

The result:

Microsoft Excel PivotTable - Calculating Quarters

How to improve it? I recommend you to group dates by the years too. Click the right mouse button to any quarters (e.g. Qtr2) and choose also Years (now Quarters and Years are selected):

Microsoft Excel PivotTable - Group Dates - Grouping Quarters and Years

Now the result looks like:

Microsoft Excel PivotTable - Calculating Quarters and Years

For every salesman we see appropriate year(s) and quarters. Do you like to see salesmen divided by year(s)? Simple! Just flipping rows Years and Salesman (drag and drop):

Microsoft Excel PivotTable - Flipping rows

The result:

Microsoft Excel PivotTable - Sum calculation by flipping rows

Only one thing what should be better in the end of this tutorial is renaming the column of salesmen. Click to Row Labels and type Salesmen for example:

Microsoft Excel PivotTable - Renamed row

Notice: Also we can format the numbers in Sum of Turnover into dollar currency.

If you have any questions or suggestions, please write comment bellow this article. If you like it please share this tutorial. Thanks!