Please go through the previous seven pages I have written about spreadsheets before you attempt this. If you click on my name - RJ Evans at the top of this page, you will see them all. Please add this page to your favourites if you wish to go through these exercises as you may have to refer back to them.
Spreadsheets have basic database functionality. In order for this to work, however, it must be switched on. Then, you can do some things that you would normally associate with databases.
Open your spreadsheet Household Goods 2
Delete Rows 4 and Rows 10 (the empty rows between your headings and the items, and the items and totals. The contents of the spreadsheet need to be right next to each other in order for this to work).
Click in to the Cell G3 (Total Cost).
Then click on the tools Data menu and choose the Filter option and then AutoFilter (see the example below).
Your spreadsheet should the look like this, with filtering option along row 3, for each of your headings. All of the columns that contain data have now gained a dropdown box.
Click on to the Total Cost dropdown box. You will see a variety of choices available to you.
Choose sort ascending, and the data will start with the smallest total.
Choose sort descending and the data will start with the largest total.
You can also customise your choice, so if you want to see any particular data you can.
We will see data less than £20 first of all.
Click on to the custom option
Change the dialogue box so it corresponds with this on the right.
You should then get just the items that have a total of less than £20
Repeat this for greater than £50
You should then get a list of the items over £50 in price.
To remove the autofilter function - at any time - click on the tools Data menu and choose the Filter option and then AutoFilter (it should have a tick next to it to say it is “on”)
Your spreadsheet should then appear normal.
Save the spreadsheet and Exit
NEXT: More practice with Min, Max, If and the filter function
