Microsoft Excel - Data analysis

MS-Excel is one of the wonderful tool available today for data analysis. It is having number of lovely features to do data analysis. Here I would like to show you one simple but very time saving and important bucket analysis.

In data analysis field, it is very important to bucket the data and do the deep dive of each area seperately based on the bucket result.

I have one file with Population data as shown in Figure 1

Figure 1

Now the requirement is to identify each 20 years bucket right from 0, how many people are belongs to each.

We can easily analyse this by loading these table data into pivot and group accordingly which I have demonstrated below.

Step 1 : Create pivot table

Figure 2

Step 2 : pull the people field into aggregate window of pivot and age into row values as in figure 3

Figure 3

Step 3: right click on the row label (bucketing field) and select group and provide the details as in figure 4



Figure 4

Final Result

 we can take out the bucket for which high population and can do further analysis.

Figure 5

You can change the count into different view such as % by right click on the count of People and select  the required option.

Figure 6

Also we can view the details of the selected bucket by right click on the required bucket and selecting show details as shown below.

Figure 7