Excel Pivot Table is a great way categorizing information in your spreadsheet. It’s especially useful if you are working on large spreadsheets and you require summation on different categories. It’s also useful if you are using a lot of SUMIF like functions. Pivot Charts are useful if you want to get summarized information in graphical views.
Let’s see how a Pivot Table and a Pivot Chart is created. Look at the sample data below :
This is a monthly sales data of a company in Electronics. As you can see they sell in all four regions, the salesmen name are given and so is the item sold.
This spreadsheet is a good candidate for Pivot Table processing. Let’s assume that you want to summarize Regionwise sales summaries. Here’s what to do :
Go to Insert Menu and click on Pivot Table as shown below :
Now you will see the following Popup
For the Table Range select the entire range from A1 to F22. an for the second option choose the default location where the Pivot Table needs to be printed: (In this case I10 on the current sheet)
Now you will get something like this :
On the left side you see a placeholder where the Pivot Output would be displayed, On the right are the Pivot Table Paramters. Select Region, Salesman and Amount. You will get a pivot table like this:
Now change the Pivot Paramters. Select Sum of Quantity and Amount by Regionwise and Itemwise:
Now you see the power of Pivoting. You can mix and match the parameters on the right to get a new pivot every time you change it,
Now let’s look at Pivot charts. Go to insert Menu and click the Dropdown on PivotTable. You will get an option of Pivot chart.
Select parameters just like you did for Pivot Table. It will create the chart as shown below :
You have selected Region / Item wise summary of amount as shown above. You can have various different types of charts. Click on Chart Types, and select the type of Chart that you want.
Pivot Tables and Pivot Charts are one of the coolest ways for Data Analysis.