The power of Pivot Table (Part II)

Not convinced by what I have shared yesterday? Read on. I will prove to you with examples. The only way you can be convinced is to let you experience the power of Excel Pivot Table. Here's what I will do. Over the next few sessions, I will share with you what you could do with Excel Pivot Table and you judge for yourself whether what I said is true.

1) Download the data file I have created for you.
2) Open up the file.
3) Go to menu, data and select "pivottable and pivotChart report"
4) In step 1 of 3 of the pivot table wizard, select the option "Microsoft Excel List or database" and for the kind of report you want to create, select "pivot table"
5) Click Next
6) In Step 2, highlight the entire data table
7) Click Next,
8) Click on the Layout button when you are at step 3 of the pivot table wizard.
9) Drag the label called "customer" on to the section of the table called row.
10) Do the same for "Slsman No" and "Amount". The layout should look like this.
11) Click ok to go back to step 3 of the wizard.
12) Click Finish. Remove all the pop up boxes.
13) You should see the layout as shown in this diagram.

The power of Pivot Table (Part I)

For the benefit of those who don't know what is pivot table and how it works, I will start from basic and progress gradually from simple to difficult.

What is pivot table and what it can do?
Pivot table is a tool that is provided in Microsoft Excel Application. It's purpose is to help you analyze the data you have. Pivot table in Microsoft Excel seems like a cheapo tool to many because it is just one of the features in Excel. What most people don't know is that it would have serve the needs of most data analysers. In fact, with the right experience, Microsoft Excel pivot table would have diminished the value of the crystal reports based on the price paid. Not only crystal report, but it can be a strong competitor for business objects, inpromptu and other reporting tools. I have used these other tools listed and I still fall in love with Microsoft Excel Pivot Table. It is easy to use and the amount time you need to learn how to use pivot table is much less than those reporting tools listed. What more, Microsoft Excel is a tool that is familiar to you and most probably, you have it on your computer.

PS: I am conducting an Excel Workshop on the 12 and 19 Dec. If you are in Singapore during this 2 dates, yoiu might want to consider attending the course. Click here for more details

The power of Pivot Table

I have spoken to many and am amazed that many Excel users are not aware of the existence of pivot table. For some, they have heard of it but have not used it before. To these 2 groups of people, I must say that they have not truly experience the power of Excel. In fact, my performance in data analysis improved by leaps and bounds after I learnt about Excel's pivot table.

For those who have used Excel Worksheet as a data source, you are one step ahead of these two groups. But you are only using 30% of Excel's pivot table capability. Those who are already using MS Query would have harness the true power of Excel pivot table. In fact, it would take about a day for me to cover every aspects of Excel's pivot table. In the next few days, I would share with you some of these functions so that you could appreciate what Excel pivot tables can do for you and how it could improve your data analysis capability.

Getting the list of unique records (Method 2)

I have recently discover another method to retrieve the unqiue list of items. And here it is:

Assuming that you have the same list as yesterday.

What is a blog

Internet Marketing

Excel Formula

Adword Equaliser

Internet Marketing

Adword Equaliser

Excel Formula

Excel Formula

1) Sort the list so that all the same items are group together.
2) Input a header on top of the list. This is required for the sub-total function we are going to use in the next step.
3) Activate the subtotal by going to menu, data, sub-total
4) When the dialog box appears, set the following options
a) At each change in: list (Header)
b) Use function: Sum
c) Add Subtotal to: list
d) Select the option: Replce current total
e) Select the option: Summary below data (see diagram for details)
5) Click OK.
6) Click on the button 2 located at the top left hand corner. The list would be compressed as shown in this diagram.
7) use the Goto Special function to extract the unique list to another excel worksheet.
8) Use the Replace function to remove the " total"
9) The list is extracted.

I might not be able to share more excel application with you on a daily basis from tomorrow till 12 Dec. This is because I might be tied up with the preparation of my Excel course which I am conducting on 12 Dec and 19 Dec (1.5 days in total). This is an inhouse course I am conducting for my client. As there are still some places left, my client has agreed to allow me to offer this course to you if you are interested. Of course, you must located in Singapore during this time. Drop me a comment if you are interested to find out more and I will furnish you with more details when it becomes available.

Getting the list of unique records (Method 1)

Previously, we touched on the various methods to count the number of unique records listed in an excel worksheet. However, nothing was mentioned about producing the list of unique records using Excel. And I thought you might be interested to find out how. And here is one method you could use:

Assuming that the following product list is in column A starting in row 2.

What is a blog
Internet Marketing
Excel Formula
Adword Equaliser
Internet Marketing
Adword Equaliser
Excel Formula
Excel Formula

1) Sort the list so that all the same items are group together.
2) In cell A2, input the formula "=if (A2=A1,1,0)".
3) This formula will check to see if the cell A2 is the same as A1. If they are the same, the number 1 will appear. If they are different, the number 0 will appear. Since we have grouped the same items together, the 1 will appear if item appears the second or third time in the list. For example:

Adword Equaliser 0

Adword Equaliser 1

Excel Formula 0

Excel Formula 1

Excel Formula 1

Internet Marketing 0

Internet Marketing 1

What is a blog 0

4) Activate the autofilter function and filter using the criteria "0"

5) Once the duplicates are hidden, use the Goto Special function to extract the unique list to another excel worksheet.

Mission completed.

About Goto Special function

Press the F5 function key and then click on the Special button. Select the option "Visible Cells only" (see diagram). Click "OK". Only the visible cells are selected for now. Activate the copy function, go to another Excel worksheet, select a cell say A1 and activate the paste function.