Essential Spreadsheet Excercises, Exercises of MS Microsoft Excel skills

Download Essential Spreadsheet Excercises and more Exercises MS Microsoft Excel skills in PDF only on Docsity!

Information Services

IT

Essential

Spreadsheets

Exercises

Essential Spreadsheets: Exercises

Sorting and Filtering.................................................................................................................

 - P1 Exercises: Spreadsheet basics - P1 Workspace ........................................................................................................................... - Playing with Auto-fill................................................................................................................... 

• TT11 EExxeerrcciisseess:: CCaallccuullaattiinngg wwiitthh sspprreeaaddsshheeeettss - Constructing formulae................................................................................................................ - Using functions ..........................................................................................................................
• TT22 EExxeerrcciisseess:: CCrreeaattiivvee ffuunnccttiioonnss - Names and Conditionals............................................................................................................ - Dates and Times ...................................................................................................................... - Further Conditionals ................................................................................................................ - Student Info..........................................................................................................................
• TT33 EExxeerrcciisseess:: WWoorrkkiinngg wwiitthh ddaattaa sseettss - Validation................................................................................................................................ - Sorting and Filtering................................................................................................................. - Collaborative Sort and Filter with Filter Views............................................................................. - Collaborative Sort and Filter with Data Functions ....................................................................... - Subtotals .................................................................................................................................
• TT44 EExxeerrcciisseess:: PPrroocceessssiinngg ddaattaa sseettss - Lookups .................................................................................................................................. - QUERY function ....................................................................................................................... - Query Editor............................................................................................................................ - Pivot Tables .............................................................................................................................

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)

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 CC1 18 8, create a formula to calculate the cost of the sandwich shown in BB1 18 8 (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 1 18 8. c. Repeat these steps to fill out all the costs and nutritional information for the other sandwiches in cells CC1 19 9::GG2 22 2. d. In row 2 24 4, 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 1 17 7, 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.

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 2 2 : 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 I 2 2::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 CC1 10 0::CC1 14 4 by inserting appropriate functions to calculate figures for the whole data set - note that there are two possible solutions for most of these.

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.

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 name purpose

Hols D3 days number of days to be charged

D4 partySize total number of people in party

HolsAdmin B1 dailyRate standard daily charge per person

B3 vatRate VAT rate to be applied to final bill

E1 concessionDiscount discount rate for concessions

H1 partyLimit number in party at which discount is applied

H2 partyDiscount 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 1 1 from your count to take into account the label at the top (‘Name’).

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 EE9 9. 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.

3. Another date has been entered in cell AA88.

In cells CC88::CC1 11 1 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 DD1 13 3::DD1 16 6 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,,CC1 14 4) adds/subtracts months

eomonth(AA88,,CC1 15 5) adds/subtracts months but returns the last day of that month

workday(AA88,,CC1 16 6) adds/subtracts days, but omits weekends

5. If you’re in Google Sheets, try the DDAATTEEDDIIFF function too. It’s great fun.

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::GG5 5 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 AA1 13 3 also contains an entered time. In CC1 14 4::CC1 16 6, extract the hour, minute and second components from the time, using the functions indicated:

hour(AA1 13 3) extract the hour (24hr clock)

minute(AA1 13 3) extract the minute

second(AA1 13 3) extract the seconds

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 ‘JJ4 4 ’ hours of work, charged at ‘JJ2 2 ’ 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 2 24 4 - 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, 6 6 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.

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.

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 v

Open the file TT33 -- EExxppeennsseess vv2 2

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.

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::BB1 12 2 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::CC1 12 2 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).