Preparing to Pivot: Why all Excel users should know about Pivot Tables

Many Excel users, even the intermediate to advances ones, are intimidated by Pivot Tables. They've heard of them, sure - but it's one of those things that sound too fancy, too scary, and too downright complicated to use. Data that pivots? No thanks! It's hard enough getting a formula to work, right?

What many people don't realise is that the Pivot Tables feature is one of the most powerful and flexible tools that Excel has. Once you've got the hang of it, you'll wonder why you didn't use it sooner. Get yourself trained up on them and you can use their many benefits. So - what are the benefits?

Perhaps it's easier to begin with the obvious - what is a Pivot Table anyway? Put simply, it's a way of summarising a large amount of data. Sometimes we all have to work with lots of data - whether it's sales records for your company, a census, a mailing list, or just keeping track of your own income and expenditure as part of a budget, for example. Naturally, you're going to put all the data in a worksheet, in the usual summary of rows and columns. What does it mean if you "Pivot" it? Well, you're literally, turning it around, looking at it from a different angle. You can make Excel compute it in different ways, by dragging, dropping and comparing different rows or columns.

Pivot tables are a flexible way of using your data. Let's say, for example, that you're an Ebay seller. Your data will probably be summaries of postage costs, customer information, where the items have been sent, and how many of them you're selling. These will be in the usual columns, under customer name, phone number, address, quantity sold, and so on. What are you doing with that data? Not much. It's a useful record - you can see the total that someone's bought, how many items you sold in a year, and perhaps look at how many times someone has bought from you. This data could be so much more useful to you, if you Pivot it.

A Pivot Table doesn't need you to copy cells, input any more data, or even create a formula - and they're meant to be complicated! With the drag of a mouse, you can analyze your data and make it work for you. Let's go back to the Ebay example. Now your data has been pivoted, you can get summaries of information that wasn't available in your rows and columns - meaning, the relationships between the data.

For example, by pivoting the quantity sold alongside the customer's address, you can tell how much you're selling regionally, and this will help you target your customers better. Or, you could look at a relationship between the cost of your items and the quantity sold - now you can tell if you've perhaps pitched your costs too high or low, and adjust your profit margin accordingly.

Pivot Tables can give you a vast amount of flexible, useful knowledge that is impossible with a static set of information - the usual way people use Excel. By looking for trends, you can use your data better. That's all that Pivot tables are about. You can see it makes perfect sense to include them in your arsenal of Excel tools - you'll realise that life was harder without being able to pivot it around, so get trained up on them, and get your data moving!