Pivot tables have always been an intriguing Excel feature ever since Microsoft introduced it to their users. Using this, data analysis has taken a positive turn toward the most creative and effortless direction.
While the advanced features from PowerPivot may confuse you, with the basic knowledge about Pivot Table, you can instantly upgrade your analytic game and impress your boss!
My article today caters to all levels of Excel users, from basic to intermediate, in the hopes of acquainting you with necessary Pivot Table information to get you started with and become the master in the fastest possible time.
What you’ll learn from this article is:
- What a Pivot Table is
- Type of data this table deals with
- Inserting a Pivot Table
- Pivot Table interface
- Creating charts from Pivot Table analysis
While it may seem like a lot, once you get the hang of one part, you’ll find the following ones to be more comprehensive. So without further ado, let’s jump into Pivot Tables in Excel and make your data analysis tasks more interesting and easygoing!
Contents
What Is A Pivot Table?
If you’ve never worked with Pivot Tables in Excel, you might wonder what this is. It’s basically a way of sorting, filtering and analyzing large sets of data into comprehensive portions.
Doing so renders the data easily understandable where thousands of columns and rows together might have left you in shambles, not knowing what to do with them.
It’s named as a ‘table’ because the way this Excel feature presents your analyzed data is in the form of a table with rows and columns but in a more organized way.
Furthermore, you can use that analyzed data and turn it into a visual demonstration with charts of your choice.
What Type Of Data Does Pivot Tables Deal With?
Pivot Tables usually deal with data sets that have different values across numerous rows and columns. These values can be both unique or similar and Pivot Table picks up on them to create its own categories for further understanding.
The sample here is a sales record of a grooming & hair care product company. The data is isolated based on the day of selling within a week’s range.
Rows in this data set represent unit sales of all the salespersons with the columns representing quantity, day, unit price, profit margin etc.
You can see that it’s really tough to accumulate how much each of them have sold or what product has been sold the least or the most.
This is where Pivot Table comes in to rescue you. It allows you to draw out data and analyze it the way you prefer it to. Sample analysis techniques are highlighted later in this article to equip you with how you can go about it.
How To Insert A Pivot Table?
Let’s get to the basics. Before going into the Pivot Table data analysis, you must first learn how you can insert a Pivot Table into your Excel spreadsheet.
There are two ways you can insert a Pivot Table, both of which you’ll be able to find under the Insert section on top, right next to the Home button
There, you’ll get to choose between PivotTable and Recommended PivotTables. The latter is suggested by Excel itself based on the type of your data, but I’ll suggest you stick to the former one for more control over your tasks.
Once you click PivotTable, you’ll find two options to choose from:
- From Table/Range
- From External Data Source
For the first option, Excel will allow you to select a table or data range within your spreadsheet. Excel does a good job at automatically identifying your data range, but I prefer selecting my range manually so that there’s no room for error.
I prefer having my PivotTable made into a New Worksheet because I wouldn’t want to be messing around with my large existing data set.
Once you hit Okay, you’ll find a new Sheet to be opened with a blank PivotTable template that’s ready to analyze your data however you want to!
Fields Breakdown: Pivot Table
To properly learn to use the Pivot Table after insertion, you must familiarize yourself with the PivotTable Fields section that you’ll find at the rightmost part of your screen.
There are two major portions you should look into first:
1. Fields: At the top part of your fields window, you’ll see the list of your column names with a tick-box at the left hand side of each title. These are called Fields in Excel, which you’ll drag down to the areas underneath for your analysis to take place.
2. Areas: The bottom part of the fields window holds four openings that are ready to consume the dragged field sections. These Areas are briefly explained below as they are integral parts of the PivotTable lesson.
Once you’ve done the dragging (which I’ll be showing you with examples in the next section), a PivotTable will be generated in a new sheet. You can play with that table by altering fields in the area portion and render outcomes that cater to your intent.
In the Areas section, the four openings perform unique tasks that directly reflect on the PivotTable analysis. The four parts are:
1. Filters: This is where you can filter all of your data through unique lenses from your data set. For example: if you drag the ‘Day’ field down to the ‘Filters’ area, you’ll find your analyzed data allowing you to select each day from your Day column.
This way, you can click on an individual or a group of days to see how much sales have been made during that time frame.
2. Columns: Columns area lets you create a column based on your preference. For instance: you can throw in the Products field into the Columns area to have it show info on your products.
In this example, the column areas show how much income each of the products earned, in total and in the hands of specific Salespersons.
3. Rows: Rows are the basis of information against which you’ll be highlighting other information tabs to analyze the data. In this case, I’m assessing how the Salespersons have performed in terms of their sales, so this field is thrown into the Rows area.
If you want, you can classify by days or products if that’s what you are going for. That way, you can assess how many products have been sold each day or how much profit was generated.
4. Value: This is the most important bit that you must understand while implementing PivotTable to your wide range of data.
Value can represent any number you want to count or sum up, either Excel will automatically add it up for you or you can select your desired formula from the list that I’ll show you later on in this article.
Say, you want to count how many times each of the Salespersons have sold a product but there’s no data column for that. You can easily throw in the Salesperson field into the Column and Value area together to make that count happen.
Note: For a Column to show specific data, you MUST add a field inside the Value area too, otherwise PivotTable will display blank columns.
Download the Excel Sheet that I’ve provided with my article and play around with the Pivot Table in Excel by yourself. Follow whatever I’ve done and see if you can extend it further and analyze the data in many different ways.
Remember, learning is never complete until you put your hands on it, so don’t be afraid to keep on practicing, screwing up and starting over again – only then will your lesson be completed.
Pivot Table Analysis Example With Sample Data
This section is more like an objective-based task, something that might be assigned to you at your workplace. I’ll show you how you can draw out specific analysis from a mountainous data set with 1,000 rows of unique information classified across 14 different data types in columns using Pivot Tables in Excel.
Suppose, from this gigantic data set, your organization is asking you to create an analysis of job position to annual salary comparison and determine highest to lowest earning positions. How to do that? Start by inserting a PivotTable with the entire set of data.
This is actually very simple. Once you generate the PivotTable sheet, you have to generate a clear conception of what you’re trying to achieve. From the instruction, our relevant information from the data are: Job Title & Annual Salary.
So, we’ll drag the Job Title field down to the Rows area and the Annual Salary field to the Values area. It’ll automatically sum-up using the Excel summation formula to add-up all the salaries of every employee holding that position.
But that won’t provide us with the analysis we are looking for, and an average of the salary will mitigate the employee number issue that’s adding up to the total.
So what we’ll do is, go to the Values area and select the drop down right next to the ‘Sum of Annual Salary’ tab. There you’ll find ‘Value Field Settings..’. Open it up.
There, you’ll see the regular Excel formulas that you are familiar with, but these are currently held specifically for this PivotTable. Under the ‘Summarize value field by’ section, select Average instead of Sum and press OK.
Immediately, you’ll find your analysis to be changed into the average of the salary for each position. All there is left to do now is to sort the salary down from highest to lowest with the positions following accordingly.
Look at the top of your Job Title column where you’ll find a Down arrow next to the Row Labels text. Click that, and from the drop down select ‘More Sort Options….’.
There, select Descending (A to Z) by: Average of Annual Salary instead of Job Title, since we’re looking to sort by highest paying position to lowest gradually. Hit OK to have the final result!
Here, you can see that the Vice President position earns the highest with Director at second and Sr. Manager at third. This is something I analyzed within 30 seconds! Imagine how fast you’ll be able to draw out specific information from gigantic data sets as such!
Visualizing Data With Pivot Table
Like the charts in Excel, PivotTable lets you utilize these charts from the PivotTable Analyze tab at the top which only appears once you have your PivotTable selected.
Using these charts, you can easily create visual presentations of your data analysis to make easier and direct understanding of numeric values without having to dig too deep into it and spending excessive amounts of time.
Let me show you this chart utilization with a couple of examples. For the first one, I’ll be using the Job Title against Salary Average data to visually present the earning discrepancies between positions in that organization.
Having the PivotTable highlighted, click PivotChart at the top-right portion of your Excel interface. This will let you choose between all the regular charts Excel has in store.
For such kind of analyzed data, I prefer to have the Bar Graph to present this information better than most other charts. So, I’ll simply select Bar from the left tab and pick Clustered Bar.
Once I hit OK, it’ll generate the Bar graph depicting the salary by position with the bars showing who earns the highest and by how much, and how large the discrepancy is.
This way, you’ll be able to understand your analyzed data through a visual medium only in a matter of seconds! Mind-blowing, isn’t it?
Advantages Of Pivot Tables
Although I’ve stressed on most of the advantages of Pivot Tables in Excel in my previous sections, there are a few more positives that I wanted to mention about this handy feature:
- You can produce different types of analysis with the same data set simultaneously using PivotTable without messing up the original data
- It can help with your databasing tasks as well, since it organizes data from a wide range of scattered information
- While working on PivotTable in MS Excel, you can export the table data via any SQL export
- You’ll be able to create Pivot Table analysis from external data sources in a matter of seconds
So you see, this tool is made for increasing efficiency and saving time. And with unique and creative approaches, you can present your data in such a way that no one ever had anticipated before!
Thoughts
I’ve now reached the very end of this article. From my explanations, you have learned how you can maneuver your Pivot Tables in Excel into delivering large data set analysis in the quickest possible way.
I tried my best to be thorough about each of the parts so that Excel beginners can pick up on this as well. With the visual aids and Excel Sheet I’ve provided in this article, there actually is little room for further clarification.
Also, the visual parts of Pivot Tables are great ways to demonstrate your data without messing up the minds of the recipients. Pivot Table lets you do all that almost effortlessly, you’ll only need to spend some time practicing it to perfection.
Similar Post: