Information Services
IT
www.york.ac.uk/it-services/training
Essential
Spreadsheets
Exercises
1
Essential Spreadsheets: Exercises
P1 Exercises: Spreadsheet basics ................................................................................................................................................................................................ 22
P1 Workspace ...........................................................................................................................2
Playing with Auto-fill...................................................................................................................3
TT11 EExxeerrcciisseess:: CCaallccuullaattiinngg wwiitthh sspprreeaaddsshheeeettss ..................................................................................................................................................................44
Constructing formulae................................................................................................................4
Using functions ..........................................................................................................................6
TT22 EExxeerrcciisseess:: CCrreeaattiivvee ffuunnccttiioonnss....................................................................................................................................................................................................88
Names and Conditionals............................................................................................................8
Dates and Times......................................................................................................................11
Further Conditionals ................................................................................................................16
Student Info..........................................................................................................................16
TT33 EExxeerrcciisseess:: WWoorrkkiinngg wwiitthh ddaattaa sseettss .................................................................................................................................................................................. 1188
Validation................................................................................................................................18
Sorting and Filtering.................................................................................................................20
Collaborative Sort and Filter with Filter Views.............................................................................21
Collaborative Sort and Filter with Data Functions.......................................................................22
Subtotals .................................................................................................................................23
TT44 EExxeerrcciisseess:: PPrroocceessssiinngg ddaattaa sseettss.......................................................................................................................................................................................... 2244
Lookups ..................................................................................................................................24
QUERY function.......................................................................................................................26
Query Editor............................................................................................................................29
Pivot Tables .............................................................................................................................30
EExxeerrcciissee ffiilleess can be found on our EEsssseennttiiaall SSpprreeaaddsshheeeettss practical guide at:
ssuubbjjeeccttgguuiiddeess..yyoorrkk..aacc..uukk//sspprreeaaddsshheeeettss
…and on University computers at:
TTeeaacchhiinngg((TT::))\\IITT TTrraaiinniinngg\\EEsssseennttiiaall SSpprreeaaddsshheeeettss
Last updated: Summer 2020.
2
P1 Exercises: Spreadsheet basics
P1 Workspace
Open the file PP11 WWoorrkkssppaaccee.
Help for these exercises is at ssuubbjjeeccttgguuiiddeess..yyoorrkk..aacc..uukk//sspprreeaaddsshheeeettss//bbaassiiccss
Make sure you are viewing SShheeeett11, which shows the beginnings of an attempt to keep a record of
project spending.
You may find our guidance on MMaannaaggiinngg yyoouurr wwoorrkkssppaaccee helpful.
1. Rename Sheet1 ‘YYeeaarr 1144--1155’ and set a red colour on the tab.
2. Increase the width of column BB so that the text in rows 33--1122 fits OK.
3. Reduce the height of row 77 to match the other rows.
4. Adjust the width of columns DD--II so they are all equal and the dates in row 1122 are clearly
visible.
5. Select the data in cells DD2200::II2266 and move it to the range DD33::II99 in such a way that the
calculations in column J don’t break.
6. Column CC is empty, so remove it completely.
You may find our guidance on EEnntteerriinngg aanndd eeddiittiinngg ddaattaa helpful.
7. Insert a row between SSuubbssiisstteennccee and PPrriinnttiinngg, and add the label ‘Hospitality’ in column AA of
your new row.
8. Use auto-fill to complete the months in row 22 from October (column CC) to March (column HH).
9. Enter these Hospitality values for October-March: 50, 0, 25, 35.5, 0, 65
10. Using auto-fill, complete the Item Refs in column AA, rows 33--99, with items numbered 1, 2, 3, 4
etc down to 8.
11. In row 22 turn on text-wrap (and adjust the row height if necessary), so ‘six-month subtotal’
wraps to two lines in column II.
12. There is a redundant copy of the expenditure values on SShheeeett22 - delete this sheet completely.
13. SShheeeett33 is data from a previous year - rename Sheet3 as ‘YYeeaarr 1133--1144’.
3
Playing with Auto-fill
Open the file PP11 AAuuttoo--ffiillll.
The eexxaammpplleess tab contains several different ‘starters’ for lists.
Select the shaded (green) cell(s) in a column, and then drag down with the ffiillll hhaannddllee (in the bottom-
right-hand-corner of the selected area) to see how auto-fill completes the list.
NNootteess::
1. If you provide the first two or more items in a regular series, auto-fill can continue the pattern,
either forwards or backwards:
a. days (cols B, C)
b. months (cols E, F)
c. years (cols G, H)
d. dates (cols I, J)
e. numbers (cols K, L, M)
2. Other single cells or patterns are repeated (see cols A, D & N)
4
T1 Exercises: Calculating with spreadsheets
Constructing formulae
Open the file TT11 -- FFoorrmmuullaaee
Help for these exercises is at ssuubbjjeeccttgguuiiddeess..yyoorrkk..aacc..uukk//sspprreeaaddsshheeeettss//ccaallccuullaattiinngg
You may find our guidance on BBaassiicc aarriitthhmmeettiicc and RReepplliiccaattiinngg ffoorrmmuullaaee helpful.
1. Switch to the tab called OOrrddeerrss.
a. Adjust the width of column A so the item descriptions fit OK.
b. In cell D4, use a formula to calculate the cost, based on the price in B4 and the quantity
in C4.
c. Auto-fill this formula down to row 10.
d. In column F, calculate the total price by subtracting the discount from the subtotal.
NNoottee:: The discount is pre-calculated using conditional functions.
You may find our guidance on CCoommmmoonn ssttaattiissttiiccaall ffuunnccttiioonnss helpful.
2. Switch to the SSaannddwwiicchheess tab.
a. In cell CC1188, create a formula to calculate the cost of the sandwich shown in BB1188 (cheese
on white bread) by adding together the costs of its specified ingredients.
b. Use auto-fill to replicate this formula across the row to calculate the calories, protein,
carbs and fat content of the sandwich in row 1188.
c. Repeat these steps to fill out all the costs and nutritional information for the other
sandwiches in cells CC1199::GG2222.
d. In row 2244, use the SSUUMM function to calculate the total cost and nutritional information of
all the sandwiches for the week.
e. Switch to the AAccccoouunnttss tab.
f. In cell DD44, create a simple formula that will deduct the debits from the credits to give a
total cash flow for January.
g. Use auto-fill to replicate this formula for all 12 months. You should be able to see
relatively clearly those months where the debits exceeded the credits.
h. In row 1177, use the SSUUMM function to calculate overall totals for credits, debits, and cash
flow.
i. In column E, see if you can work out a way to show a running total for the cash flow you
created in column D.
5
You may find our guidance on AAbbssoolluuttee rreeffeerreenncceess helpful.
3. Switch to the EEnneerrggyyCCoossttss tab.
This sheet calculates the amounts spent on Electricity and Gas each month over the course of a
year. Meter readings at the start of each month are being used to calculate the number of
units used during that month (rows 8 and 17). These are charged at the rates given in the box
at O2:P4 There’s also a standing daily charge on top of this (given in the box at O6:P8). We’ll
be using all of this to calculate the cost of the units being used each month, the standard cost
for the month, and then the total of these two figures.
a. In cell BB1100, calculate the cost of electricity in January. This will be the number of units
used (BB88) multiplied by the electricity unit cost (PP33).
HHiinntt: In order to be able to copy this formula for other months, you will need to use an
absolute reference for P3.
b. In cell BB1111 calculate the standard monthly charge for January, based on the standard
daily charge in PP77 and the number of days in January (helpfully provided in BB22).
HHiinntt: In order to be able to copy this formula for other months, you will need to use an
absolute reference for one of these.
c. In cell BB1122 calculate the total cost of electricity for January by adding the unit charge (the
one you calculated at step 4a) to the standard monthly charge (the one you just
calculated at 4b).
d. Check these three formulae, particularly as regards the use of absolute references, and
copy them to calculate costs for all the remaining months.
e. Likewise, in cells BB1199, BB2200 & BB2211, calculate the cost of gas for January, using the unit
cost in cell PP44 and the standard daily charge in cell PP88. Make sure you use absolute
references where appropriate.
f. In cells PP1122 and PP1133 use SSUUMM functions to calculate the respective total costs of
electricity and gas for the whole year.
g. Add these two values together in PP1144 to give the combined total energy cost for the year.
h. In cell BB2233 calculate the combined total cost of electricity and gas for January.
i. In cell BB2244 calculate the average cost per day for January by dividing the value in BB2233
by the value in BB22.
j. Copy the formulae in cells BB2233 and BB2244 across for all months.
k. In PP1166, calculate the total number of days in the year using the values in row 22.
l. Use this value to calculate in PP1177 the mean (average) daily cost for all the energy used
this year.
m. There is a lot of inconsistency in the display of values. Format all costs in columns BB--MM to
2 decimal places. Display the total costs (PP1122::PP1144) as currency and the mean daily cost
(PP1177) as currency to 3 decimal places.
6
You may find our guidance on RReeffeerreenncciinngg bbeettwweeeenn wwoorrkksshheeeettss helpful.
4. Switch to the MMiilleeaaggee tab.
This is used, in conjunction with the MMiilleeaaggeeDDaattaa tab, to record car trips for work purposes
so that a mileage allowance can be calculated. MMiilleeaaggeeDDaattaa includes the cost per mile and
also needs to include totals for the number of miles and the cost.
a. In cell EE22 of the MMiilleeaaggee sheet, enter a formula to calculate the claim cost of the
distance shown in DD22 - this must use the Cost per mile figure in BB22 on the MMiilleeaaggeeDDaattaa
sheet but should convert that figure from pence to pounds (£).
HHiinntt 11: you will need to use an absolute reference to ensure the formula will replicate
correctly down the column.
HHiinntt 22: there’s no magic spreadsheet-y way of converting from pence to pounds; you’re going to have
to employ some maths.
b. Replicate your formula down the column.
c. Switch to the MMiilleeaaggeeDDaattaa tab and in cell BB44 use a suitable function to total the number
of miles recorded in column DD of the MMiilleeaaggee sheet. You’ll need to ensure this total will
still be correct when more values are added to the MMiilleeaaggee sheet.
d. In cell BB55 of the MMiilleeaaggeeDDaattaa sheet use a suitable function to total the costs in column
EE of the MMiilleeaaggee sheet. Again, you need to ensure this will still give a correct answer
when more values are added to the MMiilleeaaggee sheet.
e. Both sheets have poor number formatting. Ensure that values are displayed to an
appropriate number of decimal places, allowing for the fact that mileage is recorded to
the nearest 0.1 miles.
Using functions
Open the file TT11 -- FFuunnccttiioonnss
You may find our guidance on CCoommmmoonn ssttaattiissttiiccaall ffuunnccttiioonnss helpful.
5. Switch to the NNuummbbeerrss tab.
This is the attendance for a series of training sessions run over the course of a week, with
space to calculate some summary data using common functions, including CCOOUUNNTT,, SSUUMM,,
AAVVEERRAAGGEE,, MMAAXX and MMIINN. Each session was delivered once each day, and the values in
B2:H8 give the attendance for every session.
a. In cells I22::LL22 use functions to calculate the total, average highest and lowest
attendance during the week for the Access (all areas!) classes.
b. Replicate these formulae down the columns to obtain results for the other groups.
c. Complete cells CC1100::CC1144 by inserting appropriate functions to calculate figures for the
whole data set - note that there are two possible solutions for most of these.
7
6. Switch to the tab called SSttuuddeennttss.
This sheet shows the Numeracy, Literacy and Science assessment marks for a group of primary
school pupils. The tests were out of 110, 125 and 95 as shown in row 22, but the marks need to
be shown as percentages.
a. In cell GG55, enter a formula to convert the Numeracy mark in DD55 to a percentage -
divide the numeracy mark by the value in DD22 and format as a percentage. Use
absolute references as appropriate to ensure that your formula will replicate down the
column.
b. Likewise, convert the marks in EE55 and FF55 to percentages in HH55 and II55 respectively.
c. Copy these formulae down the columns to generate values for the remaining students.
d. In column JJ use the AAVVEERRAAGGEE function to calculate the average %mark for each student
(average of columns GG, HH & II). Likewise, use suitable functions in columns KK and LL to
show the highest and lowest %marks for each pupil.
7. Switch to the tab SSttuuddeenntt SSuummmmaarryy.
This sheet calculates some summary figures for the data on the SSttuuddeennttss sheet.
a. In cell BB66 enter a function that will ccoouunntt the number of pupils listed in column AA of the
SSttuuddeennttss sheet.
b. The collection of cells BB22::DD44 is intended to calculate the average, highest and lowest
percentages in the three assessments. In cell BB22 enter a function to calculate the
average of the Numeracy results on the SSttuuddeennttss sheet, column GG.
c. Add the appropriate functions to find the highest and lowest Numeracy results, and
similarly find the average, highest and lowest for Literacy and Science.
8. Switch to the tab PPoollll..
You’re organising an event, and you need to know which days people can attend. There are
three options: Tuesday, Wednesday, and Thursday.
In FF44, FF55 and FF66, use the CCOOUUNNTTAA function to find the number of respondents for each day. Account
for the possibility of an unlimited number of further respondents adding their name.
8
T2 Exercises: Creative functions
There’s quite a lot of exercises in this section, but if you don’t need to work much with dates and times
you could skip some of these and return to them another time.
At a bare minimum, take a look at EExxppeennsseess, TTiimmeesshheeeett, and SSttuuddeenntt IInnffoo.
We’ve also got solutions files for this section, which you can look at if you get stuck.
Names and Conditionals
Expenses
Open the file TT22 -- EExxppeennsseess
Help for these exercises is at ssuubbjjeeccttgguuiiddeess..yyoorrkk..aacc..uukk//sspprreeaaddsshheeeettss//ffuunnccttiioonnss
You may find our guidance on NNaammeedd rraannggeess and CCoonnddiittiioonnaall ffuunnccttiioonnss helpful.
Details of expense claims are entered on the EExxppeennsseess tab. Summary information is calculated on the
SSuummmmaarryy tab, which also includes the value at which an expense must be checked with the manager.
1. To make later formulae simpler, on the SSuummmmaarryy tab name cell BB22 checkLimit.
2. Switch to the EExxppeennsseess sheet:
a. In cell EE22 construct an IF function that will display “CChheecckk wwiitthh mmaannaaggeerr” if the amount
in DD22 is greater than the check limit set in the cell you just named (i.e.
DD22>>cchheecckkLLiimmiitt); otherwise it should display “ookk”.
b. Copy the formula down the column and check it has replicated correctly.
c. Also try changing the value for the check limit (on SSuummmmaarryy) - messages should
change appropriately.
3. Switch to the SSuummmmaarryy tab:
a. In cell BB44 enter a function to count the total number of date entries in EExxppeennsseess column
AA.
b. In cell BB55 enter a function to total the amounts in EExxppeennsseess column DD.
9
Holidays
Open the file TT22 -- HHoollss
Switch to the HHoollss tab. This sheet is used in conjunction with HHoollssAAddmmiinn, to calculate the cost of a
group of people staying for several nights at a campsite.
The arrival and departure dates for a holiday have been entered in cells DD11 and DD22 of the HHoollss tab,
and details of the holidaying party are entered from row 99 downwards. This information, together
with values for discounts for concessions and larger groups, will be used to calculate the cost of the
holiday.
In order to make formulae easier to work with, cells containing key values will be configured as
nnaammeedd rraannggeess: these names can then be used in the formulae without having to worry about using
dollar notation for absolute references.
1. Create nnaammeedd rraannggeess for the key data cells, as shown below:
sheet tab
cell
purpose
Hols
D3
number of days to be charged
D4
total number of people in party
HolsAdmin
B1
standard daily charge per person
B3
VAT rate to be applied to final bill
E1
discount rate for concessions
H1
number in party at which discount is
applied
H2
discount rate for larger parties
NNoottee:: the names are also shown as notes on the appropriate cells.
2. A quick calculation: Back on the HHoollss tab, in cell DD33, work out the length of the holiday in
days by subtracting the arrival date from the departure date. Ensure this is formatted as a
number. See our guidance on calculating with dates if you’re not sure.
3. In cell DD44, count how many people are in the party (use the list of names in column BB) - you’ll
need to use COUNTA (not COUNT) as you are counting text rather than numbers. To allow
for the possibility of people being added to or removed from the list, count the wwhhoollee ccoolluummnn
but subtract 11 from your count to take into account the label at the top (‘Name’).
10
4. The cells in column EE, starting with EE99, need to calculate the daily rate for each person. Those
being charged the standard daily rate are marked with an ‘s’ in column CC; those qualifying
for the concession discount are shown by a ‘c’ in column CC. Use an IIFF function with these
values to calculate the appropriate charge in EE99. Here’s a breakdown of the arguments we
might use:
a. To test if the person qualifies for a concession we can use:
CC99== ""cc""
b. If a person qualifies for a concession (i.e. if C9 does equal “c”), the calculation we
need (using the named ranges we created) is:
ddaaiillyyRRaattee--((ddaaiillyyRRaattee**ccoonncceessssiioonnDDiissccoouunntt))
If a person does not qualify for a concession then the value is simply the ddaaiillyyRRaattee..
5. Copy the formula down the column and check it is correctly identifying concessions.
NNoottee:: You may notice that this formula still generates a daily cost for rows where there is no person
entered - this could be rectified by using a more complex formula, but for now simply copy it only as
far as the last name.
6. Now two easier bits:
a. In cell HH11 calculate the total daily charge for the whole party by adding together the
values in column EE (for maximum flexibility total the whole column).
b. In cell HH22, use the figure you just created to calculate the overall charge incurred by
the party across the number of days in the holiday - you worked out the number of
days in step 2 and you named its cell in step 1.
7. In cell HH33 a large party discount needs to be calculated IIFF the number of people in the group
is greater than or equal to the large party limit on the HHoollssAAddmmiinn tab. Use named cells again
wherever you can.
There’s a subtle hint above as to which function you may need!
8. Complete the calculations on the HHoollss tab in cells HH44 to HH66:
a. HH44 - subtract the party discount from the charge for visit.
b. HH55 - calculate VAT for the amount in HH44 (remember vatRate is a named cell).
c. HH66 - add the VAT to the cost of the visit to find the total amount payable.
9. Make some changes to the composition of the party to check values are changing as they
should, in particular to ensure the larger party discount is only applied when it should be.
10. Make some changes to key admin values such as the size of a ‘larger’ party and the
concessions discount rate.
11
Dates and Times
Always bear in mind the basics:
● dates are stored as a single whole number, and the number increases by one each day
● times are stored as a single decimal number (less than 1)
● when working with times it is important to distinguish between ‘time of day’ and ‘duration’
● the display on the sheet is entirely the result of formatting these numbers
● as dates and times are stored as numbers, you can perform calculations with them
You may find our guidance on NNuummbbeerr ffoorrmmaattss:: ddaatteess && ttiimmeess and CCaallccuullaattiioonnss aanndd ffuunnccttiioonnss ffoorr
ddaatteess aanndd ttiimmeess helpful.
Dates
Open the file TT22 -- DDaatteess && ttiimmeess
Switch to the ppllaayy ddaatteess tab. Cells BB22 and BB33 contain entered dates, and columns CC::GG in these two
rows simply replicate these two dates. Formatting can be used to display these replicated differently.
Below row 33 are some opportunities to try some date-related functions.
1. Configure the dates in rows 22 & 33 in the format indicated in the header row:
format
example
medium date
30 Jun 15
long date
30 June 2015
day
Tuesday
custom
Tuesday, 30 June 2015
date value
42,185
You may need to use custom formatting, using combinations of:
d, dd, ddd, dddd (days)
m, mm, mmm (months)
yy, yyyy (years)
2. In cell BB44 find the number of days between the two entered dates using simple subtraction -
you may need to reformat the result as a number.
Try the same calculation in cell GG44 - you should hopefully get the same result!
12
3. Another date has been entered in cell AA88.
In cells CC88::CC1111 try the functions indicated, with AA88 as the argument, which should return individual
components of the supplied date:
day(AA88)
day of the month
weekday(AA88)
day of the week, where Sunday =
1
month(AA88)
month number
year(AA88)
4-digit year
4. In cells DD1133::DD1166 try some further functions that calculate another date from the one supplied.
All except the first require 2 arguments: the date (AA88) and a number of days or months to
add/subtract (given in column CC):
simple addition/subtraction
adds/subtracts days
edate(AA88,,CC1144)
adds/subtracts months
eomonth(AA88,,CC1155)
adds/subtracts months but returns
the last day of that month
workday(AA88,,CC1166)
adds/subtracts days, but omits
weekends
5. If you’re in Google Sheets, try the DDAATTEEDDIIFF function too. It’s great fun.
13
Times
Switch to the ppllaayy ttiimmee tab. Cells BB22::FF33 have some start and end times entered for some days of the
week.
1. In cell BB55 use a simple subtraction to find the difference between the start and end times for
Monday, and replicate this for the other days. Check the result is formatted correctly.
2. In cell GG55 insert a SSUUMM function to total the times. This is where the distinction between time of
day and time duration becomes apparent: if GG55 is formatted as time rather than duration, it
will not show the correct answer (you may wish to try the two possibilities). All the cells BB55::GG55
should be formatted as duration too, so make sure you do this.
3. A duration displays time appropriately, but cannot easily be used in calculations, for example
to multiply the number of hours by an hourly cost. In cell HH55 enter a formula to multiply the
duration in GG55 by 24 to obtain the duration as decimal hours. Have a think about why this
works!
4. Time of day can be displayed using 24hr or 12hr notation, and can be set not to display
leading zeros on hours. Cell AA99 contains an entered time value and BB99::EE99 replicated this.
Apply formatting as indicated - you may need to use custom formatting (examples shown
here):
HH:mm:ss
hours, minutes and seconds (24)
HH:mm
hours & minutes (24)
H:mm
hours & minutes (no leading zeros, 24)
H:mm AM/PM
hours & minutes (no leading zeros, 12)
5. Cell AA1133 also contains an entered time. In CC1144::CC1166, extract the hour, minute and second
components from the time, using the functions indicated:
hour(AA1133)
extract the hour (24hr clock)
minute(AA1133)
extract the minute
second(AA1133)
extract the seconds
14
Date-times
As dates are stored as whole numbers, and times as a decimal less than 1, it follows that you can
potentially store both a date and a time using just one value, using formatting to generate the
appropriate display. It even allows you to perform calculations involving both dates and times.
Switch to the ppllaayy ddaattee--ttiimmee tab.
1. In cell DD22 enter a simple formula to add together the date value (BB22) and the time value (CC22).
Do the same in DD33 for the date and time on this row.
2. Make sure DD22 and DD33 are formatted to display both date and time, and you should see the
combined values in these cells. For interest, the underlying date and time values are shown in
cells EE22::GG33.
3. In DD44 enter a simple subtraction to find the difference between the two date-times in DD22 and
DD33, but make sure the result is formatted as a time duration. This gives the time between the
two date-times, even when it spans several days.
4. In DD55 convert this duration into a decimal time in hours (just multiply by 24 - maths is magic
isn’t it?).
5. In cell AA99 enter the function,
==NNOOWW(()). This displays the current date and time, based on the
computer’s internal calendar and clock. As it is a function, it doesn’t ‘tick’ automatically, but
whenever the sheet is recalculated (which happens every time you make a change) it will
update.
Cell DD99 is set equal to AA99, but has been formatted differently. As the value is a date-time, you
can apply formatting to display any part of it you wish, from just the seconds to the entire date
and time. Experiment with this using custom formatting.
Timesheet
Open the file TT22 –– TTiimmeesshheeeett
Switch to the tab TTiimmeeSShheeeett. This is intended to keep a record of the time spent on a job, for which an
hourly rate is paid (JJ22); a 30min lunch break must be allowed for (cell FF11) if 5 or more hours are
worked (FF22), and for very small jobs there is a minimum charge of £50 (JJ11). The date and start/end
times for periods worked are entered in columns AA, CC and DD.
1. First create any named ranges you think you will need, particularly for single cells that would
otherwise require absolute references (dollar notation). Add more as you go along if needed.
2. Edit the data area of the sheet (i.e. from row 44) as follows:
a. Column BB: Set as equal to the date in column AA, but format to display just the day of
the week rather than the full date.
b. Columns CC & DD: Format to display 24hr time without seconds.
c. Column EE: Insert a simple subtraction to calculate the amount of time worked, and
format this appropriately.
15
d. Column FF: Use an IIFF function to apply the break length (FF11) if the number of hours
worked exceeds the maximum continuous working time permitted (FF22) - otherwise, this
should be zero.
NNoottee:: you may need to change the way the value in F2 has been entered, or you could use a function
in your formula to convert it to an appropriate format.
e. Column GG: Insert a simple subtraction so the break time is subtracted from the hours
worked.
3. Additional calculations:
a. In JJ44 find the total chargeable time for the timesheet (total of column GG).
b. The calculated value in cell JJ55 should be the amount being paid for ‘JJ44’ hours of work,
charged at ‘JJ22’ pounds per hour. It should be a simple multiplication, but this will give
a time duration rather than a figure that can be displayed as currency - try it and you’ll
see the problem.
To get round this:
c. In cell LL44 convert the total hours (JJ44) to a decimal value (bbiigg hhiinntt - you can just multiply
it by 2244 - can you figure out why this works?).
d. Calculate the total charge/hr in cell JJ55 using this decimal value.
e. Finally, in cell JJ66 pop in an IIFF function for the amount payable, so that if the total
charge/hr is less than the minimum charge (JJ11), it will give the value of the minimum
charge rather than the total charge/hr.
4. If this was ‘for real’, you would now want to test your timesheet to ensure the conditionals
work correctly:
a. Try clearing the contents of the data entry cells (those bordered in blue) in rows 55, 66
and 77, leaving the formulae intact.
b. Change the End time in cell DD44 to 9:00 - this should give a total below the minimum
charge, checking the condition in JJ66.
NNoottee:: This is a very simple attempt at a timesheet, mainly designed to help you understand how to
work with times. In practice you would almost certainly want to put the ‘processing’ and key data
(hourly rate etc) on another tab. The approach used here also has the disadvantage that each ‘job’
would need a separate file, or one file with an awful lot of similar sheets.
An alternative solution would be to record all jobs on one sheet, using a unique identifier for each
job, and interrogate this data to extract the values for each separate job. If using Google sheets, the
data could even be entered using a Form, removing the need to access the spreadsheet to record
daily figures.
16
Further Conditionals
You may find our guidance on CCoonnddiittiioonnaall ffuunnccttiioonnss helpful.
Student Info
Open the file TT22 -- SSttuuddeenntt IInnffoo
The SSttuuddeennttss tab is a list of the students taking the Culinary Appreciation course, each of whom also
makes a donation towards food for a homeless charity (if only so we can practice using the SSUUMMIIFF
function!).
You are going to process this data to find out ‘useful’ information.
1. We need to know if a student has passed, so SSttuuddeennttss column HH is prepared for this. The pass
mark is set on the AAddmmiinn tab, cell BB22. To make it easier to use this value, make this cell a
named range called passMark.
2. Back on the SSttuuddeennttss sheet:
a. In cell HH22 enter an IIFF function that will display “Pass” if the mark in column FF is greater
than or equal to the value in passMark, or “Fail” if it isn’t.
b. Copy this function down the column - it should replicate correctly.
c. Try changing the value in ppaassssMMaarrkk (on AAddmmiinn) to see the change in SSttuuddeennttss col HH.
3. To make it easier to work with the columns of data on the SSttuuddeennttss tab, make them into
named ranges as follows:
All of column DD – module
All of column EE – year
All of column FF – mark
All of column GG – donation
All of column HH – result
Now we’ll use these to process the SSttuuddeennttss data - you’ll need to switch to the AAddmmiinn sheet for this.
4. In cell BB66:
a. Use a COUNTIF function to find how many students are taking the Pies module. Use
the named range you defined for the module column and although you could ‘hard
code’ the word Pies into the function for the criterion, it’s better to reference it from
AAddmmiinn cell AA66.
b. Copy this function down for the other 3 modules - it should replicate OK.
17
5. Complete the cells for Donations total, Average marks and Number of passes using
appropriate conditional functions. There are some subtle hints in row 1111, but note that
Number of passes needs two criteria - you’re looking for the correct module (pies etc) and also
for the word Pass in the Result column - that’s why it needs the plural version countifss.
6. In AAddmmiinn!!FF66::FF99 calculate the Pass rates - the proportion of passes against the number of
students taking the module (a simple division, formatted as a percentage).
7. Try changing the Pass mark again (AAddmmiinn!!BB22) to see the effect this has on these calculated
results.
8. Now the hardest bit: The group of cells AAddmmiinn!!BB1177::EE2200 are very similar to the first set, except
this time the year group shown in BB1144 needs to be used as an additional criterion, so only
results for this year are shown (changing the year should change the results). This means that
all the functions need to use their ‘plural’ versions.
9. Include the pass rates in column FF as before.
10. Have a well-earned cuppa!
18
T3 Exercises: Working with data sets
Validation
In order to reduce errors you can set validation tests on entered data. This cannot show if something
is correct, but it can show if something is ‘wrong’ or ‘needs attention’ by virtue of not meeting
specified criteria.
Expenses v2
Open the file TT33 -- EExxppeennsseess vv22
Help for these exercises is at ssuubbjjeeccttgguuiiddeess..yyoorrkk..aacc..uukk//sspprreeaaddsshheeeettss//ddaattaasseettss
You may find our guidance on DDaattaa vvaalliiddaattiioonn and NNaammeedd rraannggeess helpful.
This is another version of the expenses data from T2, which we’ll use to illustrate a few different types
of validation. The ccoonnffiigg tab contains some lists of data that we can use in the validation.
We’ll be applying validation to the EExxppeennsseess tab. In each case where we apply our validation, we’ll
apply it to a wwhhoollee ccoolluummnn, to allow for extra data being added, but you may wish to subsequently
remove any validation from the ttoopp rrooww of headers.
1. DDaattee column: since this is an expenses sheet, the entered date should not be later than today.
Set the validation to allow only dates, and specify they must be earlier than or equal to today
using
==NNOOWW(()) for ‘today’. RReejjeecctt the input if the date is invalid, and write a suitable error
message.
Try entering a date later than today to check if it works.
2. SSttaaffff column: to make sure the name is always spelt the same you could provide a drop-down
list to pick from.
a. The list of staff is on the ccoonnffiigg sheet, at the top of column BB. Make the whole column
a named range
1
. We’ll do the whole column to account for the possibility of new staff
being added.
1
When using Google Sheets or Excel for Microsoft 365, you could auto-generate the list from
EExxppeennsseess!!BB::BB using the UNIQUE function. This means another name can be added on the live sheet
and will automatically also appear in the dropdown. To do this, in ccoonnffiigg!!BB11 you could enter
==UUNNIIQQUUEE((EExxppeennsseess!!BB22::BB)) in Google Sheets, or ==UUNNIIQQUUEE((EExxppeennsseess!!BB::BB)) in Excel.
19
b. Return to the EExxppeennsseess tab and add a drop-down list validation to the SSttaaffff column,
using the named range you just created as the source of the list. Configure it so values
not in the list may also be entered.
3. CCaatteeggoorryy column: this could be another drop-down list, but as you wouldn’t want staff
inventing extra categories, it should be configured so only values in the list are allowed. Start
by naming the list of valid categories in ccoonnffiigg!!DD::DD.
4. CCoosstt column: there is already an IIFF function in column FF to check the value entered in column
EE, but the amount could also be validated in column EE itself. The value above which entries
need checking is defined in ccoonnffiigg!!FF11. Use this value in a validation rule which will alert the
user if the amount entered into the CCoosstt column is above the check limit, but will still allow the
entry to be entered.
You may find our guidance on CCoonnddiittiioonnaall ffuunnccttiioonnss helpful.
5. Switch to the SSuummmmaarryy tab.
a. Cells BB99::BB1122 need to count the number of claims made by each person. To do this
you’ll need to use the CCOOUUNNTTIIFF function in each of the cells. Ensure your formula
accounts for the possibility of more items being added to the list.
HHiinntt:: You’ll be counting in EExxppeennsseess column BB, and you should be able to reference the
person’s name on the SSuummmmaarryy sheet for the criterion.
b. Cells CC99::CC1122 need to show the total cost of expenses for each person. You will need
to use the SSUUMMIIFF function for this. Ensure it will remain correct if more items are added
to the list.
HHiinntt:: the values for totalling are in EExxppeennsseess column DD, but the values for testing are in column
BB. As with COUNTIF you should also be able to reference the person’s name on the SSuummmmaarryy
sheet.
6. If all has gone well, trying making some changes:
a. Change the check limit on the SSuummmmaarryy sheet to a lower value (eg £10) and check the
effect this has on the EExxppeennsseess sheet.
b. On the EExxppeennsseess tab, add some more expenses (using the same staff names in col BB).
20
Sorting and Filtering
Open the file TT33 -- SSoorrtt && ffiilltteerr
(NNoottee:: the bracketed figures at the end of each task indicate which records should be at the top and
bottom of the list if the sort is correct, and for Q4 onwards may also indicate the number of records
that should be obtained)
You may find our guidance on SSoorrttiinngg and FFiilltteerriinngg helpful.
1. The SSttuuddeennttLLiisstt sheet lists marks for module exams. To help with this exercise, students also
donate a sum of money to a homeless charity. The list of students is currently sorted in SSttuuddeenntt
NNuummbbeerr order. Try the following sorts:
a. Sort by FFiinnaall MMaarrkk, highest mark first. (10014,10002)
b. Re-sort by amount donated, lowest amount first. (10029,10034)
c. Re-sort the list alphabetically by SSuurrnnaammee and FFoorreennaammee (so that those with the same
surname are sorted by forename). (10009,10029)
d. Return the list to its original sort order, by SSttuuddeenntt NNuummbbeerr. (10001,10036)
2. Filter to display the following – clear the filter after each one:
a. Students who are studying CChhooccoollaattee as their module subject (8)
b. 3rd year students who have scored a mark of 6600 or more (5)
c. 2nd and 3rd year students who have donated less than ££1100 (13)
d. People with surnames starting with Sm (eg Smith, Smythe, Smithers etc) (3)
3. Use a combination of sort and filter to generate a list of Year 1 students with final marks in
descending order. (12, 10003, 10002)
4. Produce a list of all students taking the Chocolate module, sorted alphabetically by surname
and forename. (8, 10014,10029)
5. Use sorting and filtering to produce a list of students in second and third years who donated at
least £5, sorted with the highest donation at the top (20, 10034, 10035).
6. Generate a list of year 2 and 3 students (in full alphabetical order) where the Final Mark is nnoott
in the range 40-70 inclusive. (8, 10014, 10029)
NNoottee:: You will see that cell KK11 counts the number of records in the list. You can’t do this with a simple
COUNT function as this does not take into account filtering. To allow for filtering, we’ve used the
SSUUBBTTOOTTAALL function instead.
21
Collaborative Sort and Filter with Filter Views
The methods in the exercises above would change the view of data for all concurrent users
in a Google Sheet. Google Sheets therefore includes extra features and functions so that
more than one person can work on the same data set at the same time.
This exercise can only be done using Google Sheets as the feature is not available in Excel.
You may find our guidance on FFiilltteerr vviieewwss helpful.
To get the most of the first part of this exercise, find a friend, share your copy of TT33 -- SSoorrtt && ffiilltteerr, and
work together in this file. If you don’t have any friends to hand, open the file in another browser
window so you have it open twice. You can then pretend you have a friend!
1. Work on the SSttuuddeennttLLiisstt tab. First, one of you should apply a simple sort or filter, as above, so
you can see how this affects the view for both of you. Not very helpful - remove any filters.
2. Now both of you should create new filter views:
a. Person 1 - Show all the first years taking the CCaakkee module, with marks sorted in order,
highest first. Save the filter view as 1st years - Cake.
b. Person 2 - Show all students taking the PPiieess module, in alphabetical order. Save the
filter view as All years - Pies.
3. Both of you now switch out of the filter view and check the new views have been saved in DDaattaa
>> FFiilltteerr vviieewwss…
4. Check it’s working correctly:
a. Person 1 - View person 2’s filter view.
b. Person 2 - Check the source data list is unaffected.
c. Person 1 - exit filter view.
d. Now swap roles and repeat.
22
Collaborative Sort and Filter with Data Functions
This exercise can only be done using Google Sheets or Excel for Microsoft 365 (the functions required
are not available in Excel 2019 on University managed machines).
You may find our guidance on SSOORRTT and FFIILLTTEERR helpful.
This approach uses data functions to do the sort and filter on another sheet, leaving the source data
intact (you don’t need a ‘friend’ for this part).
Still in your copy of TT33 -- SSoorrtt && ffiilltteerr:
1. Switch to the SSoorrtt tab:
In cell AA22 (leaving space for a header row), enter the SSOORRTT function so as to list the data
sorted by exam mark, highest first. You will find that if the data range argument includes the
header row, this will be shown in the sorted list, so the range will have to start at row 22.
2. Try some further sorts using the sort function:
a. Sorted by surname and forename, in alphabetical order.
b. Sorted by module, then by years.
3. Switch to the FFiilltteerr tab:
In cell AA22, enter the FFIILLTTEERR function so only students in year 3 are shown. The header row
should not appear as it will never contain the correct criteria, however you will find that
columns containing both text and numbers behave oddly when numerical criteria are used.
4. Try some further filters using the filter function:
a. Show only students in year 1 who have passed.
b. Show only students taking the Stews module who have a mark less than 40.
23
Subtotals
This exercise can only be done using Excel. The subtotal feature is not included in Google
Sheets.
If a list of data is sorted appropriately, the SSuubbttoottaall feature can be used to insert additional rows
containing totals, averages etc for grouped values.
Open TT33 -- SSuubbttoottaallss..xxllssxx in EExxcceell
You may find our guidance on SSuubbttoottaallss helpful.
1. We’d like to find the total donated by each year group:
a. Sort the list by YYeeaarr.
b. Use the subtotal feature to find the total donated by each year group.
c. We’d also like to know the average mark for each year group - add this without
removing the donation total.
2. We’ve also been asked to provide averages for each module.
a. Remove the existing subtotals and re-sort the list by Module, with the students in each
module in alphabetical order (if you can’t figure that out, at least sort the list by Module).
b. Add subtotals to show the average mark for each module.
24
T4 Exercises: Processing data sets
Lookups
Open the file TT44 -- LLooookkuupp ffuunnccttiioonnss
Help for these exercises is at ssuubbjjeeccttgguuiiddeess..yyoorrkk..aacc..uukk//sspprreeaaddsshheeeettss//pprroocceessssiinngg
You may find our guidance on LLooookkuupp ffuunnccttiioonnss helpful.
This spreadsheet contains 2 tabs: MMoodduulleess contains a list of course modules, with two columns
containing coded data; the DDaattaa tab includes tables to interpret the codes used for department and
college. We’ll use Lookup functions on MMoodduulleess to decode the dept and college IDs.
1. On the DDaattaa tab, create a named range ddeeppaarrttmmeennttss for the department list, using columns
AA::BB (naming entire columns makes it easier to add new departments).
2. Likewise, create a named range for the list of ccoolllleeggeess in columns DD::EE of the DDaattaa tab.
3. Switch to the MMoodduulleess tab, and insert two extra columns for the department and colleges
names - it would make sense to insert these next to the relevant coded columns, so that the
department name is column DD and the college is column FF. Enter suitable headers for these in
row 11.
4. In row 22 of the new department name column, enter a VVLLOOOOKKUUPP function that will look up the
department name (in the ddeeppaarrttmmeennttss named range) using the ID in column CC.
5. Replicate this down the column.
6. Likewise, in row 22 of the new college name column, use VVLLOOOOKKUUPP to decode the college ID to
a college name, and copy this down the column.
7. Alcuin college has decided to change its name to Albert college. On the DDaattaa tab, cell EE22,
change AAllccuuiinn to AAllbbeerrtt and note how this affects all instances on the MMoodduulleess sheet. You can
change it back if it bothers you!
25
Open the file TT44 -- SSttuuddeennttss 550000
The SSttuuddeennttss tab lists results and other data for 500 made-up students (that was a fun afternoon for
somebody, I’m sure!).
8. Switch to the LLooookkuupp tab. The idea here is that details for an individual student can be found
by simply entering their ID in BB22
2
.
Switch back to SSttuuddeennttss and make the whole student data set into a named range: ssttuuddeennttLLiisstt.
To make replication of the lookups more straightforward also name cell BB22 on the LLooookkuupp
sheet.
9. Using these named ranges, enter an appropriate lookup formula into LLooookkuupp!!DD22 to display
the correct forname.
10. If you’ve used named ranges you should now be able to copy this lookup into the other cells
(DD33::DD66 and BB55), needing only to change the column reference in the function to make it
display the appropriate value.
11. On the SSttuuddeennttss tab, column MM will display the degree awarded based on the Result in col LL.
A table on the ccoonnffiigg tab defines the boundaries for the degree awarded. Start by defining this
table as a named range.
12. In cell SSttuuddeennttss!!MM22, enter a range lookup that will show the degree awarded.
13. Edit the file as necessary so that cell LLooookkuupp!!BB66 will also display the correct degree awarded.
2
Using this method is quicker than looking down a long list, and the alternative of using the ‘Find’
feature will require looking across several columns, increasing the possibility of reading data from the
wrong row. It also allows you to extract just the data you need, and the sheet could be designed for
printing if necessary. It also has the advantage of showing how a lookup works…
26
QUERY function
The QUERY function is available in Google Sheets, but not in Excel. It is part of a collection
of features and functions that make Google Sheets better-suited to collaborative working.
This exercise can only be done using Google Sheets; the function is not available in Excel.
You may find our guidance on the QQUUEERRYY ffuunnccttiioonn helpful.
The QUERY function allows us to:
● Extract selected columns, rather than the whole set
● Define criteria to filter for values and ranges of values
● Define a sort order independent of the original data
● View a subset of the data without affecting the original data source
Use the Google Sheets version of the file TT44 -- SSttuuddeennttss 550000.
In this exercise we’ll construct QUERY functions to extract data from the list on the SSttuuddeennttss sheet.
This exercise needs a named range for the student list, so if you did not create one in the LLooookkuuppss
exercise above, do this now, making the whole student data set (on SSttuuddeennttss) into a named range
called ssttuuddeennttLLiisstt.
1. Switch to the QQuueerryy sheet, and in cell AA44 enter this query function:
=QUERY(studentList,"select B,C,G,L",1)
This should generate a full list of students, but showing only the four defined columns.
2. The result of the query has a live link to the underlying data. Try these:
a. On the QQuueerryy tab, attempt to edit someone’s forename; this should generate an error
in AA44 as you are overwriting cells populated by the query.
b. On the SSttuuddeennttss tab, edit someone’s forename; when you view the QQuueerryy tab again it
should have changed to match the edit.
3. Edit the query to include the degree class as a fifth column (if you’ve not got any values for the
degree class, copy them in from the solutions file):
=QUERY(studentList,"select B,C,G,L,M",1)
4. Edit the query function again to add a criterion, showing only those students in year 3:
=QUERY(studentList,"select B,C,G,L,M where I = 3",1)
27
Note that a column can be used for criteria, even though it is not displayed.
5. Edit the function again to use a text-based criterion - this needs single quotes around criteria
values (in this case the word ‘Fail’):
=QUERY(studentList,"select B,C,G,L,M where M = 'Fail' ",1)
6. Construct a query that will show only 3rd years, but sorted by surname:
=QUERY(studentList,"select B,C,G,L,M where I=3 Order By C Asc",1)
7. … and also sorted by forename:
=QUERY(studentList,"select B,C,G,L,M where I=3 Order By C Asc, B Asc",1)
8. Construct a query to combine the two criteria and the two-column sorting:
=QUERY(studentList,"select B,C,G,L,M where I=3 And M='Fail' Order By C Asc",1)
Extension: Criteria external to the function
Criteria do not have to be ‘hard-coded’ into the function; instead, values in other cells can be
referenced. The syntax is different for numbers and text, as text must be enclosed in single quotes, but
numbers must not.
1. Numbers: In QQuueerryy!!CC11 enter the label Year and in cell DD11 enter 1 as the year.
2. In AA44 enter a query that shows the same five columns as before, filtered for year 1 students (as
per step 4 above), but referencing DD11:
=QUERY(studentList,"select B,C,G,L,M where I=" & D1 ,1)
Note that as the criteria value does not need enclosing in quotes we close the query text and
concatenate the cell reference using ampersand (&&).
3. Try changing the year value in DD11 - the result of the query function should also change.
28
4. Text:n In QQuueerryy!!CC22 enter the label Degree and in cell DD22 enter Fail as the degree class.
5. Enter a query that shows the same five columns for all students with Fail as the degree class:
=QUERY(studentList,"select B,C,G,L,M where M=' " & D2 & " ' " ,1)
As the criterion needs to be enclosed in ssiinnggllee qquuootteess, which must be inside ddoouubbllee qquuootteess, the query
text is constructed by concatenating three parts together, the middle one being the referenced cell.
=QUERY(studentList,"select B,C,G,L,M where M=' " & D2 & " ' " ,1)
________first part_________mid pt_end
6. Try changing the degree in DD22 from Fail to U-2nd.
7. This query function will combine the year and degree class queries (it has five sections, glued
together with &&s):
=QUERY(studentList,"select B,C,G,L,M where I="& D1 &" And M=' "& D2 &" ' " ,1)
29
Query Editor
This exercise can only be done using Excel. The Query Editor is not included in Google
Sheets.
Open TT44 -- SSttuuddeennttss 550000 in EExxcceell.
You may find our guidance on QQuueerryy EEddiittoorr helpful.
In this exercise we’ll use the Query Editor to extract data from the student list on the SSttuuddeennttss sheet.
This exercise needs a named range for the student list, so if you did not create one in the LLooookkuuppss
exercise above, do this now, making the whole student data set (on SSttuuddeennttss) into a named range
called ssttuuddeennttLLiisstt.
1. Launch the QQuueerryy EEddiittoorr to query the data in ssttuuddeennttLLiisstt.
a. Create a table that only shows the columns ffoorreennaammee, ssuurrnnaammee, ppoossttccooddee, and rreessuulltt.
b. CClloossee && LLooaadd to generate a full list of students, but showing only the four defined
columns.
2. The result of the query has a ‘live’ link to the underlying data. Try these:
a. On your query’s tab, attempt to edit someone’s forename; it should accept the edit.
b. Hit the refresh button at QQuueerryy >> LLooaadd >> RReeffrreesshh and your edit should get reverted.
c. On the SSttuuddeennttss tab, edit someone’s forename. View your query table again and
RReeffrreesshh. The value should change to match the edit.
3. Edit the query (QQuueerryy >> EEddiitt >> EEddiitt) to include the ddeeggrreeee ccllaassss as a fifth column (if you’ve not
got any values for the degree class, you could copy them in from the solutions file). You will
probably need to delete the RReemmoovveedd CCoolluummnnss step in your AApppplliieedd SStteeppss list to get the
column back.
4. Edit again to add a criterion, showing only those students in yyeeaarr 33. You don’t need to delete
your RReemmoovveedd CCoolluummnnss but you will need to step back to an earlier step before applying your
filter. A new step should then be inserted into the AApppplliieedd SStteeppss history.
5. Edit again to only show those students in yyeeaarr 33 with a Fail in the DDeeggrreeee column.
6. Have the editor sort by ssuurrnnaammee.
7. ...and by ffoorreennaammee (as a ‘secondary’ sort).
30
Pivot Tables
Open the file TT44 -- NNeeww ppiivvoott
You may find our guidance on PPiivvoott ttaabblleess helpful.
The ddaattaa tab is a list of events undertaken by students to raise money for charity, with most students
undertaking 3 activities. Note that each activity is recorded on a separate row, duplicating some data
- this is to be expected in ‘pivotable’ data sets.
Create pivot tables to:
1. Display the total sum raised by each AAccttiivviittyy (as row labels), grouped by YYeeaarr (as column
headers).
a. Add a filter for the whole pivot table so the totals can be shown for specific CCoolllleeggeess.
b. Configure to display totals for years but not activities.
2. Display the total sum raised by each AAccttiivviittyy, grouped by CCoolllleeggeess, with the Activities and
Colleges in alphabetical order.
a. Add a filter for the whole pivot table so the totals can be shown for specific yyeeaarr
groups.
3. Display a count of how many students took part in each AAccttiivviittyy (row labels), grouped by
ccoolllleeggeess, and with a filter so figures for yyeeaarr groups can be viewed.
4. Show a small table of the total sum raised by each aaccttiivviittyy, which can be filtered by yyeeaarr
group.
5. Display the sum raised by each student (row labels), including SSttuuddeenntt IIDD, FFoorreennaammee and
SSuurrnnaammee, grouped by AAccttiivviittyy (column labels), with the option of filtering to display data for
the different ccoolllleeggeess.
Ensure that subtotals are not shown for ID and Forename.
6. Show the average sum raised by each ccoolllleeggee, grouped by yyeeaarrss.
Ensure the averages are displayed to a sensible number of decimal places.