This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel (113 exercises)
Topic ==>	Advanced pivot tables (3 exercises)
Level ==>	Average difficulty
Subject ==>	Excel training

Creating frequency pivot tables and using the show values as options for playground data

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Open the file contained with the folder name shown above.

Create a new pivot table on a new worksheet to show the following:

Show just the site type in the rows and a count of Days til next check as the values.

Right click on any of the pivot table numbers and use the Show Values As short-cut menu option to show the figures as % of Grand Total instead:

This should give these figures.

Now use the same menu option to show the percentage difference from the mixed playground type figures:

Everything is being compared to the base figures of the Mixed figures.

Rename the sheet tab to Using Show Values As.

Create another new pivot table of the playground data on another new sheet. We want to show a percentage frequency table:

We want to create a frequency table like this that shows how many of our playgrounds fall in each sub-category of number of days till the next check, so that we can plan our staffing levels accordingly.

This time begin with a very weird pivot table as shown below:

Add Days til next check in the rows and the values so you get 2 identical columns next to each other as shown here.

Change the Sum of Days til next check field to use the Count function instead.

Click on a single value in your row labels in column A and ask to group:

Change the values in the dialog box so that your Starting at value is below or equal to your minimum value, and the Ending at value is above or equal to your maximum value. Decide on the increment By value also.

Rename the sheet tab to Frequency Pivot and then choose Save As... to save the file in your own new Excel work folder.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here: