Creating a Pivot table: a powerful guideline
By Glenn On September 15, 2014
HOW TO CREATE A PIVOT TABLE FOR BUSINESS, PRIVATE AND ANY OTHER PURPOSE: A POWERFUL GUIDELINE PAPER
Each day the team of MyHelpster is confronted with Microsoft Office questions of any kind, in particular Excel. Despite the area of Outlook, in specific Hotmail account settings, MS Excel is the most demanded area of support. People are simply overwhelmed by the variety of functions, tools and possible settings within Excel, with the natural consequence of frustration in the use of this application. In this context one main question the team of MyHelpster Ltd. is regularly confronted with is “how to create a simple and plain pivot table in Microsoft Excel”. This frequency is due to two reasons; pivot tables are very useful (especially in order to organize large amount of data) but also very tricky. Realizing this, the team of MyHelpster has decided to dedicate this nerve racking but important area of spreadsheets a specific blog article. In the following it will be described how one can easily create a simple pivot table and what great use.
When creating a ‘Pivot Table’ in Microsoft Excel, the most important first step is to determine what information you want to organize. In this article, we will use a table that gives us a unique Employee ID number, a geographic location, employment type, salary (in U.S. Dollars) and that salary as a percentage of the whole.
This data continues for 75 total lines (employees) across four locations (USA, EU, UK, Asia). So, as I stated above, the first thing to determine is the information you want to display. In this specific case we want to answer the question; “What’s the sum of all paid salaries for each location?” Raising this question we automatically understand what is so great about pivot tables: simply the ordering of huge available data and connected content, in this specific case paid salaries according to different areas.
Select your data by marking all relevant columns and lines:
Once your data is selected, it’s time to create a Pivot Table. Click your mouse onto the “Insert menu” and click “Pivot Table”
After you’ve created your Pivot Table, you will get a menu that looks like the image bellow (likely on the right hand side of your screen):
Understanding this, let’s focus on the original question we wanted to answer: “How much salary is paid in each location?”:
First, click and drag “Location” to the “Row Labels Box”:
Next, in order to determine the sum of the salaries , just click and drag “salary” to the “values box” (Excel will automatically determine the possible categories to analyze):
At this point, Excel will automatically sum the values and hence will tell you the information you need to know! Let’s expand on the data you just created and further elaborate on how to find out the average salary in each location in addition to the sum of the salary field. For this purpose you can create another column in the pivot table (based on the same data) and calculate it using a different method.
The most simple and useful calculations available in pivot tables (though there are others) are “=Sum”, “=Average”, “=Min” and “=Max”. Small annotation, to understand how to work with specific formulas in Excel the team of MyHelpster will write an additional blog article.
Anyways in order to change the calculation method in your Pivot Table, click the small black arrow on the right of the field you want to change and click “Value Field Settings” (see image bellow).
This will open the following window, which gives you all mentioned formulas on a clance:
As the image above shows, we currently using the Sum function, but as stated above, we also want to find out the average salary for each location. For this; simply click “Average” and “OK”.
The image above shows how easy it is to analyze other parameters.
To put it in a not shale: Pivot tables are tricky! This article focused on simplicity in its core, means even though the variety how to extend a pivot table seems endless; the article above tried to minimize its complexity to a brief recipe how to create it. Nevertheless, you will understand that the area of pivot tables seems – no matter which office suite you are looking at – endless and can get REALLY complex. This article covered the topic of pivot only very rudimentary, which shell not mean that MyHelpster is not aware and able to answer even more complex questions in the area of spreadsheets. In this context additional articles will and can follow in future and meanwhile, our professional Helpsters are anytime available to answer additional and question in more depth.