Please purchase the course before starting the lesson.


Create a Pivot Table based on the sheet ‘7.23 PhoneBill’ and then create the following reports. After each report refer to the solution sheet corresponding to the question number and see if your report looks the same. If the description is a bit vague you can look at the solution first and then create the pivot that is expected.

Note in this exercise we require information in the time format. Depending on what is required this may be clock time (which resets after 24 hours) or stopwatch time which keeps going. Format the duration using the Time Format of ‘[h]:mm:ss ‘ (look in your custom formats). If you don’t use the above format, Excel considers anything over 24 hours the start of a new day. So instead of seeing 26 hours you will be told it is 02h00.

1-    A report showing the total duration (sum) for calls in January, February and March.

2-    Change the data so that instead of seeing the sum of duration you see the average length of a call in each month

3-    Change the data so that instead of seeing the sum of the duration you see how many calls were made (you will need to change the formatting back to a number)

4-    Using the previous report as a base, create a report with the following data fields next to each other

a.     The number of calls in each month

b.     The total duration for calls in the month

c.     The average duration per call each month

d.     The percent each month makes up of the column (based on the duration)

e.     A running total (cumulative duration) of calls for the months

f.      The change in NUMBER OF CALLS per month from the previous month.

To see the full course contents click here.

Back to: Beginner Excel Course > Pivot Tables- A very special tool