Menu:
Pivot Tables
Pivot Tables are one of the most powerful functions in excel.
If you are constantly manipulating, grouping, catergorising, rearranging data, then pivot tables would be of great help to you.
The following examples will demonstrate what pivot tables can do.
The Wedding List
Let's assume that we have the below list created for our wedding.Fortunately we don't have many relatives and friends at the moment, so its going to be a small wedding.
Unfortunately, mum and dad want to know how many are coming from the brides side, and how many are coming from the grooms side, and how much does each side cost. (Control freaks)
So, using the data in the above table, we need to first see who's attending the wedding, then work out if they are from the Bride or the Grooms side, then apply the cost of $50 to the number of Bride guests and to the number of Groom guests.
I can do the calculation manually by first deleting rows to leave only the Yes for attending wedding. Secondly, using the sort function on the column of Knows Bride or Groom, and finally placing a sum function in an extra column to subtotal the costs that are from the Brides side, ie $250, and the cost from the Grooms' side ie $150.
Now for the Pivot Table method.
1. Make sure that each column is labelled.
2. Highlight the data area including the labels for each column as per the diagram below:
3. Click on Data, then Pivot Table and PivotChart Report
4. On the below screen click on Next
5. Again click on Next
6. Here you have the option of choosing to place the pivot table on a new sheet, or an existing sheet. We will select New worksheet. And click on Layout.
7. On this screen, you now get to choose what data you want to analyse and how you want to arrange it.
8. Drag and drop the column labels on the right hand side to the positions as per the diagram below.
9 Double click on "Count of Dinner Cost" which was placed in the DATA box. We don't want to count the number of $50 there are but rather sum them to provide a total cost.
10. Click on Sum and then click on OK and you will get to the below diagram.
11. Click on OK.
12. Click on Next
13. And you now see the result!
14. Looks really poorly set out, so we'll drage the label Name and move it to the left hand side, per the diagram below:
15. Click on the drop down for Attending Wedding and untick Don't Know and No.
16. You now get to see the below diagram.
Click here to send us your request, and any attachments..