Excel | Advanced pivot tables exercise | Playground - frequency pivots and show values as

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.

Software ==> Excel  (151 exercises)
Version ==> Excel 2016 and later
Topic ==> Advanced pivot tables  (3 exercises)
Level ==> Average difficulty
Subject ==> Excel training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Open the file contained with the folder name shown above.

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

The initial pivot table

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 menu to show the figures as a % of Grand Total instead:

Percent of grand total Result
Choosing % of Grand Total... ... has this result.

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

Using the Show Values as option

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:

% frequency pivot 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:

Initial Pivot Table

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:

Grouping for a frequency pivot table

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.

Rename the sheet tab to Frequency Pivot and use 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.
This page has 0 threads Add post