Pivot tables are magic. For those of us who know how to use pivot tables, we cant imagine using Excel without them. If pivot tables and their potential are a mystery to you, this article will help. First, explaining pivot tables without using pictures in this article is a bit like explaining the Internet to cowboys in the 1800s. Its hard. But Ill give you two explanations that will help: one technical and one metaphor. A technical explanation is that pivot tables sort and count. If you have 30 salespeople that work for your company throughout the country, you might want to sort all of your monthly sales by salesperson and then SUM all the sales for each salesperson. If you dont know pivot table technique but you do know how to sort in Excel, the salesman summary problem is doable but tedious. You sort the salesman and their sales, and then you do some basic but repetitive formulas to sum sales for each salesperson. It is doable. But then the sales manager hears of your good work and asks you to sum not only by salesperson but by sales region. Your sorting and summary techniques that worked for salespeople is very difficult to sort and sum by salesperson and region. You need to learn pivot tables. But pivot tables are much more than a technical answer to a request from your sales manager. Pivot tables are a revealing tool of analysis for your business or organization. Heres an information metaphor of why pivot tables are a great research tool for numbers and information. In my backyard, theres a big pine tree right adjacent to the deck. Its autumn now as I sit by my tree. Visually, from the side, the tree is green, with no movement. But I hear many birds chattering in the tree as they settle in before sunset. If I walk underneath the tree slowly, I can see birds flying about within the tree branches. Im sure my tree would look different from the top. Whats my point? If my pine tree were a spreadsheet, I could look at it from the top, bottom, side or even fold it inside out to examine it for data. And for birds. Pivot tables are a bit of Excel magic. It all begins with a common looking spreadsheet with columns of data and hundreds or thousands of rows. It might be a sales spreadsheet or expenses spreadsheet. The sooner you learn how to use pivot tables, the sooner you will reap the benefits of turning common business data into useful information. The pine tree in your backyard will never look the same to you again. |
