Today, let us learn a bit about
PivotTables and the convenient assistance they offer us in many ways. This tip
is based on a special request from one of my friends P Sreenath, who wanted me
to write a few things on Pivots.

Let us take an example to
understand the summary functions better. Consider the following table of
student and subject-wise academic performance (data of marks scored):

Now, the school management wants
to do a summary analysis by subject and find out the maximum marks scored, the
minimum marks scored and the average marks scored, so that they can evaluate
the respective subject teachers.

Since the data is available by student, if we
need to get the summary level data by subjects, we must sort the data by
subjects and then use the MAX, MIN and AVERAGE worksheet functions on each data
range to get the values of maximum, minimum and average scores respectively - see the restructured sample below.

However, there is an easier
option available and would be a boon especially when you have a huge number of
records to work on. You can forget the manual methods of sorting and applying
formula to each selected range of subjects and three different forumulas for
each one – in this current example, 4 subjects x 3 functions each = altogether
12 formulas to be setup manually.

Take recourse to the PivotTables, and let us find
an easy solution. Just follow these steps:

Step 1: Select the data range,
and click on the PivotTable button on the Insert menu of the Ribbon.

Step 2: Choose your option of New
worksheet or existing place in the current worksheet, to get the PivotTable skeleton
structure.

Step 3: Drag the Subject field to
the Row Labels zone, and drag the Marks field thrice to the Values zone – as shown
below:

Step 4: Now click on the first
Marks field (“Sum of Marks” in the values zone) and select Value Field
settings. In the dialog box, just select the function MAX (instead of the
default sum) and click ok.

Step 5: Repeat the process on “Sum
of Marks2” – making it MIN and “Sum of Marks3” – making it AVERAGE. Your work
is done in a jiffy now. See the summary results below.

__Note:__
When you do the average,
you will note that there are too many decimals that appear. If you want your
worksheet to look clean and without decimals, you can use the value field
settings again on that field, select the Number Format button which will bring
up the Cell properties dialog box. You can set the field to Number type with zero
decimal places there and click OK. Note that the number formatting would be
retained on the PivotTable only when you set it through the Value Settings
dialog box. If you use the comma button or $ button on the Ribbon to format the
cells, the formatting would be lost the next time the Pivot is refreshed.

The example file is available on Skydrive for you to take a quick online view - http://sdrv.ms/SsLOOa

Hope you will find this tip
useful and handy. I will try to share few more interesting tips on PivotTables
in the coming days as well. Would be glad to have your comments on this, and
help your friends also to learn a trick or two – share the blog details with
them!

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.