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.

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 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:

Initial pivot table

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:

Pivot Table Reports

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:

Slicers

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:

Timelines

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.

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