I often encounter the term "pivot table," yet until recently I had only a vague notion as to what a pivot table is and what it does. Matthew MacDonald, author of "Excel 2010: The Missing Manual," got me up to speed.
Q: What is a pivot table and why would I use one?Matthew MacDonald: A pivot table is a way to group and summarize huge amounts of information, like sales records pulled from a database.
The nice thing about pivot tables is that they are very "explorable." For example, you can start with a subgrouped view that shows you all your sales grouped by customer country and then quickly "pivot" your way to a different view that shows you all the sales grouped by, say, customer city.
Either of these examples would be an extremely simple pivot table. It's more likely you would use a pivot table on a report to group and subgroup multiple ways at once [Note: See screenshot, below. It shows country, city, product category, and product quantity]. This lets you dig for hidden relationships and trends. For example, you might discover that customers in New York love the products in the "cheese" category of your catalog.
MacDonald developed a screencast and tutorial on pivot tables in conjunction with his book, "Excel 2007: The Missing Manual." According to MacDonald, pivot table functionality is basically the same in Excel 2010:
- Microsoft Excel's top 5 tricks
- When to use Excel, when to use R?
- Full collection of Excel questions and answers
Learn more about this topic from Excel 2010: The Missing Manual.
Fast-paced and easy to use, this guide shows you how to get the most out of Excel 2010. You'll learn how to develop a spreadsheet from scratch, create formulas, add data, and analyze and graph data so you can make informed business decisions. With clear jargon-free explanations, step-by-step instructions, tons of illustrations, and lots of undocumented tips and shortcuts, you'll get hands-on guided tours and explanations of Excel's new features.