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 | Property - Pivot Report Filter Pages, Slicers and Timelines
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 within the folder name shown above.
Create a new pivot table on a new worksheet to show the following:
- Postcodes in the row labels
- Garden size in the columns
- Asking price as the values
- A filter to show only the detached houses with 4 or 5 bedrooms
Change the sheet tab to Initial Pivot:
This is the initial pivot table.
Use the Pivot table Report Filter Pages feature to insert new pivot tables on separate sheets that filter the location for each location type - Remote, Town, Countryside and Village.
You should now have 4 new sheets as shown below:
Each sheet has a new pivot table containing all the same filters as the original, but has a new filter on the Location field which is different for each sheet.
On the Countryside sheet, add 2 slicers and use them to filter to show only:
- Detached and semi-detached properties; with
- 3, 4, or 5 bedrooms.
This should give this:
You can move and resize the slicers just like any object in Excel.
On the Initial Pivot sheet, insert a date on the market timeline and filter it to show:
- Properties with 1 or 2 bedrooms; which were
- Placed on the market between Nov 2017 and Feb 2018.
Your pivot table on the Initial Pivot sheet should now look like this:
A Timeline is like a Slicer but for dates only.
Re-name the sheet tab to Timeline Pivot and use Save As... to save the file in your own new Excel work folder.