Menu
By John Short, Marketer at Yesware
In this video tutorial on Pivot Table, learn how Pivot Tables can be used efficiently to sort the complex data into meaningful insights. Develop skills to master excel tools, formulae and function. Analyze data from different perspectives. Transcript For The Video – Pivot Table In MS Excel. It depends which version of Excel for Mac you have. The last version that was released is 2011 and I will use this as my basis. Let's say you have a 5-column pivot table, showing the population of European countries over the past 3 years.
As a marketer, I am always using Excel to analyze data, pull together reports and project future activity. Salespeople use it much the same way. You need to provide daily, monthly, and quarterly sales reports for yourself, your team—and often for your superiors.
An often overlooked tool in Excel is the Pivot Table. I know, it sounds boring, but as the end of the quarter inches ever closer, today is an excellent day to learn about this great assist.
A Pivot Table allows you to summarize data as averages, sums, or counts in Excel from data that is stored in another Spreadsheet, or table. It is great for quickly building reports because you can sort and visualize the data quickly.
For example, I have put together a spreadsheet, which you can copy, and paste into Excel, or use in Google Docs if you would prefer (just click File > Make a Copy). The spreadsheet contains data with a mock company’s customer purchase information. For this example, we want to sort the data to see how much revenue we’ve made from each company. Since companies purchase at different dates, a pivot table will help us to consolidate this data to allow us to see total buys per company, as well as to compare purchases across companies, for quick analysis.
Ready to work faster and smarter right from your Gmail or Outlook inbox?
An Example Is Worth a Million Cells
First, let’s get the basics out of the way. The Pivot table allows you to take a table with a lot of data in it, and rearrange the table so that you only look at what’s important to you.
a) Whether you’re using a Mac or a PC, you can select the whole dataset that you want to look at, and select: “Data” -> “Pivot Table”. When you hit that, a new tab should be opened with a table.
Pro tip: If you’re using Windows you can push Alt + D + P + F on your keyboard).
b) Once you have your table in front of you, you can drag and drop the Column Labels, Row Labels, and Report Filter
- Column Labels go across the top row of your table (for example: Date, Month, Company Name)
- Row Labels go across the left-hand side of your table [for example: Date, Month, Company Name (same as with column labels, it depends on how you would prefer to look at the data, vertically or horizontally)]
- The Values section is where you put the data you would like calculated (for example: Purchases, Revenue)
- Report Filter helps you refine your results. Add anything you would like to Filter by (for example: you want to look at Lead Referral Sources, but exclude Google and Direct)
Alright, now what? Let me show you another example. In this report I want to see the number of units sold (# Sold), and Revenue by company. I want to Filter out companies that came in directly to my site (as opposed to from a Referral, or from Google). To do this:
a) Add “Company” to Row Labels
![Pivot Pivot](/uploads/1/2/5/7/125744736/440603571.gif)
b) Add “# Sold” and “Revenue” to the Values box
c) Add “Referral” to the Report Filter, then from the drop-down menu, de-select “Google”
This will generate a report that looks like this:
Next I could add Date to the Column Labels box in order to look at what data companies were purchasing. If you do this in the example spreadsheet (linked to above), you can drag values down to Row Labels, below company to make it easier to view.
Pro Tip: If you want to look at who made the purchases in each company you can drag name and/or email to Row Labels below Company and that will show you the email address of the people who purchased from each company.
Pivot tables are a great way to manage the data from your reports. Play around with these examples to start. You can copy and paste the data into your own Excel file, or create a copy in Google Apps (File > Make a Copy).
Learning how to use pivot tables will speed up the rate at which you can product sales reports, and at which you can analyze and close more deals faster. Stay tuned as we will be providing more information on more complex operations you can perform in Excel—with pivot tables—as well as other functions and formulas.
Check out our ebook for data-backed tips to maximize your 1:1 meetings.
Do you have a use for Pivot Tables that you use for reporting? Share below. Also feel free to post templates of reports you pull. You can do this through Google Apps Spreadsheets (make sure you set the privacy setting to how you like them).
While there is no pivot table function in Numbers, you can simulate the most common uses for pivot tables using formulas, filters and hiding columns. In this example we'll look at a table of sales numbers and see how you can sum the number of sales for each product listed. Initially you end up with the first row containing each product showing the total for every sale for that product. But you can filter the list to only show those rows. Then you can hide columns that present excess data to end up with similar results as you would with a pivot table.Check out Simulating Pivot Tables In Numbers at YouTube for closed captioning and more options.
Related Posts: How Do I Save a Finished Working Table In Numbers for Other Users? ― Using the Numbers SUMIF Function