If you have followed through my recent blogs on The power of Pivot Table Part I to Part VI, you would have formatted the pivot table in a presentable format with the customers ranked by the total amount they have purchased from the company in descending order. The pivot table would have displayed which salesmen have sold products to the customers.

You may find that the above format is not sufficient for you to make decision on how you should classify the customers. You wonder whether the top customers are purchasing more of promotion items or normal items from you. Is pivot table able to do that? The answer is yes and here is how:

1) Move the mouse pointer over the pivot table.
2) Right click the mouse button.
3) A pop up menu will appear. Look for wizard and select it.
4) You are brought back to the step 3 of the pivot table wizard. Click on the layout button.
5) Now drag the field “promotion” from the list to the column area right below the customer name.
6) Click Ok and finished.
7) You should see the field “promotion” appearing on the left side of the “customer name”
8) Move the mouse button over the the right border of the row label as shown in this diagram.
9) Left click on the mouse button.
10) All the sub-total would be selected.
11) Highlight the sub-total with a color. For me, I would choose yellow.

Now you are able to make more informed business decisions with the additional dimension added.

Note: You can add as many dimensions to the column or row area. But remember that having more may not be better. Try not to micro analyse.

Take care and God Bless.

Leave a Reply