Pivot Table Tutorial for Excel - Productivity Portfolio
One of the most popular pivot table tutorials on the web can be found over at Anne Hennegar’s Productivity Portfolio blog. Anne has a nicely laid out web site that has tips on a variety of ways to increase your productivity. This pivot table tutorial has also been featured on lifehacker.com. Her excel pivot table table tutorial provides a nice clear description with a sample file that will allow anyone to get a pivot table up and running quickly. Check it out.
| Learn to use Pivot Tables in Excel 2007 to Organize Data |  |
| Monday, 18 February 2008 | |
Pivot tables are an Excel feature that you should learn how to use. Instead of analyzing rows upon rows of records, a pivot table can aggregate your data and show a new perspective with few clicks. You can also move columns to rows or vice versa. The problem is people believe creating a pivot table is too difficult to learn. Grab a seat and we’ll walk you through a short tutorial using Excel 2007. (Includes tutorial in PDF format with larger images)
What is a Pivot TableYou might think of a pivot table as a user created summary table of your original spreadsheet. You create the table by defining which fields to view and how the data should be displayed. Based on your field selections, Excel aggregates and organizes the data so you see a different view of your data. |
Excel Pivot Table Tutorial
Over at Jason Morgan’s Babeled blog there is an excellent pivot table tutorial that shows how to use the pivot table wizard. In my experience, using the pivot table wizard is the fastest and easiest way to get a pivot table set up. Check it out.
Excel Pivot Table Tutorial: Wizard Usage and Basic Manipulation
Pivot Tables in Excel can be very quick and powerful data analysis tools. Pivot Tables allow the user to arrange a contiguous range of data in a variety of columns and perform multiple calculations on an aggregate level. Click here to download the Excel file used as an example in this tutorial.
Microsoft Excel Team Blog Pivot Table Posts
One of the great things about the web is that it has allowed developers to have a conversation with end users. Over at the Microsoft Excel team blog there are a number of posts with the pivot table tag. Some of these posts discuss OLAP analysis and SQL lookups that I’m not that familar with. However, there are posts discussing some of the other less used pivot tables features such as calculated fields. You can check it out here.
Excel 2003 Pivot Table Tutorial
If you have ever been confused by the terminology that is used to describe pivot tables, then this video will help. It does a good job of explaining what the various parts of the pivot table and pivot table data are called. It covers items such as source data, data items, and fields.
Excel 2003 Pivot Table 101 - Free videos are just a click away
Microsoft Excel Pivot Tables
A Business Software Tip by J. Carlton Collins, CPA
Accountants have long known the value of PivotTables. But they’re a great data analysis tool for management, too. PivotTables allow you to create multidimensional data views by dragging and dropping column headings to move data around. J. Carlton Collins, CPA, president of ASA Research, LLC, is an accounting systems and spreadsheet expert. In this article, he’ll walk you through both the basic and complex issues involved.
PivotTable Primer
To rearrange the worksheet, simply drag and drop column headings to a new location on the worksheet, and Microsoft Excel crunches the data accordingly. The diagram below depicts a typical PivotTable and highlights its moveable elements.
Excel Pivot Table Tip - Show Pages
Ever want to create multiple pivot tables on separate tabs from one original pivot table? David Carter’s Show Pages tip shows you how to do it.
Tip #3 - Use the Show Pages command to generate pivot tables for Page fields automatically
If you use Page fields in a pivot table, you can use the Show Pages command to create a new worksheet for each page. So if the Page field contains Department codes, for example, Show Pages will automatically create a pivot table for each department.
In earlier versions of Excel, the Show Pages command is easy to find. But in Excel 2003 it has been removed from the Pivot Table menu and hidden away in the menu bar.
If you are using Excel 2003: Right click the mouse on the pivot table to call up the pivot table menu. Select Show Pivot Table toolbar. In the toolbar, click on the down arrow next to ‘Pivot Table’ at the left. Show Pages is at the bottom of the menu.
Basic Excel Pivot Table Set Up
Michael Kan’s Tip of the Week blog has a good tutorial on setting up a basic Excel pivot table. Included in the tutorial is a nice clean description of how to use the pivot table wizard and the layout dialog box.
As demonstrated in Michael’s excel tutorial, I’ve found that using the pivot table wizard and the layout dialog is the fastest the easiest way to get a pivot table set up in Excel. Just remember that your data needs to be in a continuous block and that the top row must contain a header description.
A great tip included in Michael’s pivot table post is to double click data items in the layout dialog box. This seldom covered tip will allow you to correct the formatting and subtotal options during set up. In my experience, the default pivot tables formats leave a lot to be desired.
Basic Pivot Tables
Why It Matters To You
Pivot tables are one of the easiest analytical tools available to quickly summarize a large amount of data. They have their limitations, but as long as you understand where they are powerful and where they are limited, you should be good to go. I’ve written up a quick step by step of how to including a sample data set on which you can try out the Pivot table functionality.
Pivot tables are useful anytime you want to quickly summarize data (cross tabs). They a great for quickly creating a table of summary results and better yet, let you double-click to see all the detail underlying any specific value. The drop downs at the X, Y, and page level let you quickly filter data for very specific looks at your data.
Google Spreadsheet Analytics and Pivot Tables Gadgets
If you are using or trying out pivot tables with Google Spreadsheets you may want to check out this article.
Panorama and Alchemex spearhead ‘analytics as a service’ push
The recent addition of a pivot table gadget for Google Spreadsheets signalled the beginnings of an online attack on Microsoft Excel’s hold over business analysis. John Stokdyk reports
Google’s Analytics Gadget can automatically extract and summarise data into a pivot table and is a great way to construct online performance charts. The gadget comes from Panorama Software, a long-time specialist in online analytical processing that created and then sold to Microsoft the OLAP tools that now power SQL Server’s Analysis Services module.
“Now we are building the next generation of OLAP solutions for the new era of cloud computing,” said Panorama CEO Eynav Azarya.
A Critical Pivot Table Skill - Removing Subtotals
If there is one thing that you need to learn about creating pivot tables, this is it. Removing subtotals is a simple but critical step in getting a better view of your data. Take it from someone that uses pivot tables every day, you need to learn this skill. Once removed, subtotals can always be added back later if necessary.
David Carter’s article linked to here shows you how it is done.
