EXERCISE TOPIC▼
EXCEL EXERCISES▼
EXCEL EXERCISES▼
- Excel basics (1)
- Creating formulae (5)
- Formatting worksheets (9)
- Basic printing (8)
- Charts (10)
- Absolute references (9)
- Range names (13)
- Conditional formulae (17)
- Conditional formatting (7)
- Basic tables (6)
- Formatting numbers and dates (3)
- Working with dates (1)
- Data validation (6)
- Protection (1)
- Advanced IFs (6)
- Lookup functions (14)
- Advanced lookup functions (9)
- Text functions (1)
- Advanced charts (4)
- Multiple worksheets (1)
- Advanced tables (2)
- Pivot tables (3)
- Advanced pivot tables (3)
- Scenarios (2)
- Data tables (2)
- Array formulae (2)
- Building models (2)
- Masking (2)
- Cashflow calculations (1)
- Investment appraisal (1)
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.
You can learn how to do this exercise if you attend the course listed below!
| Software ==> | Excel (151 exercises) |
| Version ==> | Excel 2016 and later |
| Topic ==> | Advanced pivot tables (3 exercises) |
| Level ==> | Average difficulty |
| Classroom ==> | Excel Advanced |
| Online ==> | Excel Advanced |
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:
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:
|
|
| Choosing % of Grand Total... | ... has this result. |
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.
Rename the sheet tab to Frequency Pivot and use Save As... to save the file in your own new Excel work folder.