Pivot tables are the Excel gods’ greatest gifts. There is no better analysis tool within Excel.
As today’s example (download here), I’ve highlighted how you can analyze the amount of discounts and rebates that each customer receives within seconds. Insert pivot table, select the rows and columns that you want summarized and you are done! Just copy out the values for any analysis or chart. (We’ll be covering charting in a separate post)
3 things you must know about pivot tables –
1. The applications of pivots are huge. If you use Excel to track your accounts/expenses or your company’s expenses, you are just a step away from using pivots to view your data in many different ways.
2. The best way to become proficient in pivot tables is to do many of them. As you begin exploring them, you will come across many options to display your data differently to suit your needs. Just make sure you check out “Pivot Table Tools” and go through them.
3. If your job involves analyzing millions of rows or perhaps creating interesting pivot charts, you might want to consider a microsoft add in called “PowerPivot.” I haven’t used this myself but have heard good things.
Bottom line: If you’ve got a large set of data, don’t bother too with formulas. Just use pivot tables. They are very easy and you can get any kind of cut you want within seconds. You don’t have to be a superstar analyst to use them – they take 10 minutes to learn and will save you more time than you can imagine.
Series introduction, Part 1 (Set up), Part 2 (Shortcuts), Part 3(SUMIFS, COUNTIFS), Part 4 (INDEX+MATCH)