PivotTables a probably one of the best features of Microsoft Excel, most people have not bothered too much with them as they think they might be very complicated or not that useful.
In this blog, I hope to show you just how easy they really are and hope that my example will give you some inspiration on what you can use PivotTables for.
I am going to use a scenario of a boat sales yard, it has 3 sales people and 3 types of boats that they sell.
Not very imaginative, I know but this will keep it simple and give you a good grasp of the power of PivotTables. The data set consists only of the columns containing the Month the sale was made, the sales persons Name and the Model of boat sold:
So we can see we have 31 sales in a 3 month period. The data has just been recorded but is not really much use in this format. But with a couple of clicks on the mouse, it will be a lot more presentable and understandable.
First we will open the worksheet that contains the data set of the sales.
Now open the Insert Tab at the top, and select the PivotTable icon
The Create PivotTable input box will appear. If the Table Range (highlighted below) is empty, just click in the text box and select the range you require, including the headers and select OK.
I have selected for the PivotTable to be created in a new Worksheet.
A new sheet will now appear with the following, do not be put off by what you see, it really is very simple, I will explain.
We will concentrate on the image to the right (PivotTable Fields), the image on the left is where the results will be displayed.
As you can see, Month, Name and Model are displayed in the PivotTable Fields area and below that are four sections called Filters, Columns, Rows and Values.
So, remember we had 31 rows of data that really did not make a lot of sense. This really is how useful and easy PivotTables are.
Just drag the Name (as highlighted in green above) into the Rows section as below, repeat for Month and Drag the Model into the Values section.
And this is what the PivotTable returns for you:
Here we can see the total sales figures broke down by the sales person and further by the month.
By simply moving the Month above Name in the Rows section, you will get it broke down by the Months first, then the sales person.
This happens immediately without having to do any further formatting or input.
Say we wanted the data displayed like this:
Then just set the order as putting the Month in Columns, Name in Rows and Model in Values:
Now by simply dragging the Model into the Rows the result will be broke down by the type of boat sold by each person for each month, giving a more detailed, useful and understandable data set.
Drag the Model above Name to group by the Model rather than the Name and the data set will be displayed as such:
So there you have it, I hope you will agree that PivotTables are really simple and will be very useful for displaying data in a more meaning and presentable manner.
PivotTables are the gem in Excels crown.
As always, this was a very basic tutorial, and the best way to learn and discover more is to play about with it, enjoy.
Note, I used Microsoft Excel 365 in this example, but the Pivot Table has existed in MS Excel since 2003 (at least)
In this blog, I hope to show you just how easy they really are and hope that my example will give you some inspiration on what you can use PivotTables for.
I am going to use a scenario of a boat sales yard, it has 3 sales people and 3 types of boats that they sell.
Not very imaginative, I know but this will keep it simple and give you a good grasp of the power of PivotTables. The data set consists only of the columns containing the Month the sale was made, the sales persons Name and the Model of boat sold:
So we can see we have 31 sales in a 3 month period. The data has just been recorded but is not really much use in this format. But with a couple of clicks on the mouse, it will be a lot more presentable and understandable.
First we will open the worksheet that contains the data set of the sales.
Now open the Insert Tab at the top, and select the PivotTable icon
The Create PivotTable input box will appear. If the Table Range (highlighted below) is empty, just click in the text box and select the range you require, including the headers and select OK.
I have selected for the PivotTable to be created in a new Worksheet.
A new sheet will now appear with the following, do not be put off by what you see, it really is very simple, I will explain.
We will concentrate on the image to the right (PivotTable Fields), the image on the left is where the results will be displayed.
As you can see, Month, Name and Model are displayed in the PivotTable Fields area and below that are four sections called Filters, Columns, Rows and Values.
So, remember we had 31 rows of data that really did not make a lot of sense. This really is how useful and easy PivotTables are.
Just drag the Name (as highlighted in green above) into the Rows section as below, repeat for Month and Drag the Model into the Values section.
And this is what the PivotTable returns for you:
Here we can see the total sales figures broke down by the sales person and further by the month.
By simply moving the Month above Name in the Rows section, you will get it broke down by the Months first, then the sales person.
This happens immediately without having to do any further formatting or input.
Say we wanted the data displayed like this:
Then just set the order as putting the Month in Columns, Name in Rows and Model in Values:
Now by simply dragging the Model into the Rows the result will be broke down by the type of boat sold by each person for each month, giving a more detailed, useful and understandable data set.
Drag the Model above Name to group by the Model rather than the Name and the data set will be displayed as such:
So there you have it, I hope you will agree that PivotTables are really simple and will be very useful for displaying data in a more meaning and presentable manner.
PivotTables are the gem in Excels crown.
As always, this was a very basic tutorial, and the best way to learn and discover more is to play about with it, enjoy.
Note, I used Microsoft Excel 365 in this example, but the Pivot Table has existed in MS Excel since 2003 (at least)
No comments:
Post a Comment