Hello again!  Today we are going to tackle one of the most useful and one of the most powerful features of Excel, the pivot table.  In my experience, employers and companies most often use this tool to determine the importance of a large set of data.  In my summer internship, my supervisor actually requested that I learn this tool to analyze a set of sales data, and I have had the opportunity to use it a few times at my site this semester.  However, through this learning goal, I have had the opportunity to learn the ins and outs of the tool rather than just going with a trial-and-error approach.

In this post, I aim to walk readers through the steps of creating a pivot table, and then I will explain how this tool can be utilized most effectively.

Before

Before starting, keep the following things in mind:

• Your data must be in the proper format, organized by tabs or headers and without any blank rows or columns.  ***PRO TIP: Use an Excel table template if you need it or just to make things easier.***
• Make sure that all the data in the columns are uniform.  Don’t try to mix dates and text and times in the same column or the analysis will definitely fall through.
• Remember that PivotTables actually just work on a snapshot of your data in a different worksheet so do not worry about your actual data – it will not get altered in any way.

Create

1. Select a cell range or select a group of cells in the table that you would like to analyze by dragging your cursor over the area.
2. Go to Insert>PivotTable.
3. Excel will show you a dialog box entitled “PivotTable Builder” with your selected data (See Figure 1).
4. This part was overwhelming to me at first, but I promise that it is straightforward.  Simply drag the fields between the areas to generate different reports.

***PRO TIP: Excel actually has a new feature called “Recommended PivotTable” that actually analyzes your data ad gives you several options.  All you have to do is select the PivotTable that looks best and press OK.  Excel creates a PivotTable on a new sheet and shows the PibotTable Fields list as well.  Use this if you can.***

Practice

Again, the best way to learn how to analyze large amounts of data is to practice, practice, practice.  There are plenty of free resources online to learn this skill.  In the attached file below, I took random sales data and organized it in Region and Data filters with Name and Object rows alongside the Sum of Item Costs.  This not only took about 60 seconds to create, but it also is the most effective and adaptable way to view this information.  The most amazing thing is that you can actually filter and sort all of this information even after creating the Pivot Table.

In the figure below (see Figure 2), I actually sorted the table by who (in all regions and on all dates) made the most money selling a specific item (pencils).  Although this data is artificial, the ramifications are endless.  For example, every company website has access to Google Analytics which gives you a mass of information concerning who visits your site, when they visit it, what they click on, and much more.  With all of this data, it would be helpful to create monthly reports with PivotTables to ensure that your website design sends users to the correct place without losing their interest.

Here is the Excel file that I was practicing with, so feel free to download the attachment below to start working with PivotTables.  Trust me, the time will pay off later on down the road.

PivotTable Practice