First, let us recap what we have covered so far. 1) We have learnt how to create a pivot table. 2) Format the results 3) Present the company sales results by customers, by promotion and by salesman.I mentioned in point 3 that the sales results were company's results. What if you want to find out who are the key customers in a particular category or division? Here is how:a) Move your mouse over the pivot table.b) Click on the right mouse button and select wizard.c) When you are brought back to Step 3 of 3 of the wizard, click on the layout button.d) Notice on the top left hand corner that say page? Drag and drop the field call "division" into the page area.e) Click Ok to get out of the layout dialog box and click finish to complete the wizard.f) On the top left hand corner of the pivot table, you will see the field "division". With this, you can select the desired division and the data are filtered to show only the top selling customers by promotion by salesmen for the that division.I hope I have amazed you with what Excel can do. Please come back if you want to discover more. Do come back if you are still not convinced about the power of Excel.Have a blessed weekend.
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.
Now that you have formated the results (Sum of Amount) for the pivot table, you might want to know that you could also rearrange the customers within the pivot table by ascending or decending alphabetical orders. That is not all. You can also arrange the customers list based on their purchases (ascending or decending order). What's more, you could even do a filter such that only the top x or bottom x number of customers are displayed in the pivot table. Interested to find out how? Read on.1) Double click on the grey box/button on the top left corner of the pivot table with the label "Customer Name"2) A dialog box called Pivot Table Field wil appear. Click on the Advanced button.3) Another Dialog box called PivotTable Field Advanced Options will appear.4) Under the AutoSort Option, you can select Manual (Arranging the customer based on your own preference), ascending or descending order.5) If you click on ascending or descending order, you would be able to choose the sorting preference, By customer name or by Sum of Amount (Purchases)6) You can activate the Top 10 Auto Show by selecting the "on" option. Click on the drop down box and choose bottom if you decided to show the bottom x results in the pivot table. X is determined by the box next to the top/bottom selection.7) If you have more than one result in the pivot table, you could select the field which the top or bottom x results should be based on. 8) Once you have made the relevant selection for the pivot table, click "ok" twice to return to the pivot table.Task Completed.God bless you with a wonderful day ahead.If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. It will benefit those who are interested in business intelligence. Click here for more details.
I was reading an article in a Sinapore newspaper which talks about using business intelligence and using the right tool to yield predictive data. It was a worldwide bank which has successfully cull their databases for information as they assess the likely response for their new credit card.As I read it, I was thinking that small and medium business owners must be envious of the cash that big corporations have to purchase tools to extract this data (customers' profile) out of their systems. What these small and medium business owners don't know (and I hope that they are reading this), is that their have the tool right in their desktops. Which tool is more suitable and at NO incremental cost to them than using Excel. I expect some of them to be skeptical about what I said but I would challenge them to find out more. One such evidence I have provided is the current subject which I have been sharing with you on this blog, that is, the Excel Pivot table. It is not only capable of pulling out data from an Excel Spreadsheet. In fact, I have used Excel to pull out data from huge databases and present reports without the need to learn another software tool. Excel is all I need. If you are a small and medium business owner, come back often to this blog and you will know that I am not lying.It was mentioned that business intelligence requires technologist and business analyst. True and not true. As I was working for some of the mobile companies and even a Fortune 500 company, I was the one (a business analyst) that are pulling out the data from databases and systems. And throughout my job, the IT department did not really intervene. If you are a business analyst, you must also be equipped with the skill of to cull databases or the concept of doing it. This is because data quality is important and half of these technologists do not know which set of data to extract. You have to because it affects your analysis. As the saying goes, garbage in garbage out. There is so much to share and I can write and write but I wouldn't want to overload you with too much. I am planning to commit some resources to start a page on this. Look out for it. I am still planning the site.If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. It will benefit those who are interested in business intelligence. Click here for more details.
After creating the pivot table, it is important to format the results from the pivot into an easily viewable format. This is important for your readers as well as for your own data analysis. By having a well formatted pivot table allows you to see the data patterns more clearly.For example, the "Amount" field can be formatted the same way as a cell. The more important consideration is to ensure that the format remains even if you re-arrange the rows(in this case "customer names") and columns(Slsman No). This is how it should be done. Double click on the label called (Sum of Amount - from last week example). A dialog box appears. Click on the number button and the familiar dialog box for cell formating appears. Select the desired format and click Ok twice to complete the formating. The formating will be preserved even if the pivot table fields are re-arranged. If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. Click here for more details.
