This page has 0 threads | Add post

Exercises

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

Software ==>	Excel (113 exercises)
Topic ==>	Spill functions (1 exercise)
Level ==>	Average difficulty
Subject ==>	Excel training

Use combinations of the UNIQUE, SORT and FILTER functions to manipulate a table of 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 workbook in the above folder (it contains a list of property sales for the - fictional - WrongMove company):

The start of the list of 50-odd properties.

This is a long exercise: see how far you can get with it!

Create ranges for each of the columns in the table, and another range called Data to refer to all of the data (excluding the column headings):

Shrink your worksheet down to less than 40% or so and you'll see the range names you've created!

On a new worksheet, create formulae to show alphabetical lists of the types of property and the locations used:

Combine the SORT and UNIQUE functions to produce these lists.

On another new worksheet, use data validation to allow a user of your workbook to choose the type and location of properties from a drop list:

Remember to put a # after the cell name for the data validation list source range to refer to the whole of the spill array, not just the top cell.

Copy in the title rows, and create a formulae to list out all of the properties for the type of property you've chosen:

If you choose Flat, you should see two properties only.

If you get this working, try adding (in this order) as many of the following refinements as you have time for:

Refinement	Notes
Reference the location choice too	Extend your filter formula so that it shows properties for both the property type chosen and the location type chosen.
Deal with no data	Show a suitable message if there are no properties meeting the criteria you chosen.
Ignore empty criteria	If someone deletes the value for either criteria cell, ignore this criteria (you can do this by testing if its value is an empty string).

If you get all of this working, give yourself a pat on the back!

Save your workbook as Special spills, then close it down.

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: