Spreadsheet Exercises
There are Solutions available from the Topic list in the Control Panel at the foot of the page.
Exercise - Windows and Titles
a) Create a model consisting of 12 columns, one for each month and an
average column.
There should be 5 rows of Sales of different types of apple.
Use the window command to show the average sales alongside sales
for March.
Practice moving from window to window.
b) Clear all windows and SAVE this exercise
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Exercise - Range Names
Setting up
Retrieve the exercise on windows and titles
Add a total to each column using a sum() function
Range name Exercise
(a) Highlight the table, excluding the Totals and use the
Insert Name Create command to give each apple its name.
The formulas should be unaffected.
(b) Highlight the Column totals and use Insert Name Apply and again observe the effect on the formulas.
They should now have replaced the cell references with the new cell names.
(c) Use the F5 key or the Name Box to jump from named range to named range.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Consolidation Exercise 1 - The Boiler
Complete the spreadsheet using functions for the average and totals
Set the width of all columns to 11 character.
Set the number of decimal places to 3.
Freeze the row and column headings.
Protect the spreadsheet except for the area inside the blue box.
Name each of operators range of results and apply the name to the Totals column.
(Hint: The formula in E3 should read =SUM(LUCY) )
Centre all the text.
Email your solution to your tutor for checking.
Exercise - ARITHMETIC FUNCTIONS
Determine the results of the following functions by entering them into Excel.
=ABS(-7.567)
=INT(7.567)
=ROUND(7.567,2)
Round off the following number to 1 decimal place -34.5632.
=SQRT(16)
=SQRT(ABS(-64))
What is the square root of 234
=RAND()
Use the RAND function to generate a number between 0 and 10
Use the RAND function to generate a whole number between 1 and 10
Use a combination of functions to get a random number between
3 and 10
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Exercise - STATISTICAL FUNCTIONS
Determine the result of the following
- AVERAGE(4,6,10,12)
- SUM(5,6,7)
- COUNT(2,2,2,2)
With cell A1 = 36 and B3 =45, try the following
- AVERAGE(4,6,A1)
- SUM(A1,B3)
- COUNT(A1,B3,C3,4)
For the following exercises create a list of 20 numbers in a column.
- Get the average, maximum value and minimum value of the list.
- Total the list
- Use the Count function to count the number of entries in list
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Exercise - IF and Lookups
a) Write an IF function that places 100 in cell C5 if cell A1 is equal or less than 100, and 300,in cell C5 if A1 is greater than 100.
b) Write an IF function that places 100, 200 or 300 in cell C5 if cell A1 is less than 100, equal to 100 or greater than 100 respectively.
c) Write an IF function that places 100, 200, 300 or 400 in cell C5 if cell A1 is less than 100, equal to 100 or greater than 100 or greater than 1000 respectively.
d) Write the IF function that places 100 in cell C4 if cells A4 and B4 are greater than zero.
e) Given the cheque date cheque number, payee, dissection code and amount, prepare a Cash Payments book that automatically dissects cheques into the appropriate column.
Your cashbook should hold at least 15 cheques and provide totals for all columns containing amounts.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
f) Repeat Exercise c using a LOOKUP table.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
g) Design a spreadsheet that produces a random number between 1 and 5 and asks the user to guess it.
Consolidation Exercise 2 - Patented Weather Forecaster
Step 1
Write an If Function to Display the results in cell E3 of any Temperature entered into cell A1.
| If the Temperature in A1 is | 0 or less | 1- 10 | 11- 20 | 21- 30 | 30+ |
| Then E3 should display | Cold | Cool | Tepid | Warm | Hot |
Step 2
Create a RAND function that produces a temperature in the range -10 to 50 in cell A1.
Step 3
In cell C3 type in "Its going to be
In cell F3 type in "tomorrow.
Step 4
Press F9 to see tomorrow's weather forcast.
Step 5 Email your solution to your tutor for checking.
Exercise - Winelist Database Exercises
Retrieve the file Winelist.xls before commencing these exercises
a) Sort the list by producer
b) Query for age = 1976
c) Query for age = 1980
d) Query for Producer = Cullens
e) Query for Dry Reds
f) Query for 1984 and Dry whites
g) Query for cost greater than $10.00 and older than 1978
h) Fred Derf is anxious to impress his friend and wants any wine that is priced at greater than $30 or
is a champagne. Can you help ?
i) A customer wants either 12 bottles of Dry White or 12 bottles of dry red,
can you provide it ?
j) Create a subset of 1981 wines elsewhere on your worksheet.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Exercise - Macro and VBA Exercises
a) Write a macro to write your name in a cell
b) Write a macro to change the width of a column to 15 characters.
c) Create a macro that takes the text in any cell and formats it as 20 point and Bold.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
d) Create a button to execute this macro
e) Join the macro that writes your name in a cell to the macro you created in exercise c.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
f) Create Macros and Buttons that allows a user to jump between Sheets 1 and Sheet 2
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
g) Use a List box to display the states of Australia.
Selection of a State in the list box should display the name of the State capital in a cell.
(Hint: try using a lookup table once you have got the list value)
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Exercise - Dialog Boxes
12.1) Enter the following code into module 1 to create a message box as per example
sub box()
response=Msgbox("I'm sorry I can't do that Dave",0,"Hal")
End Sub
12.2) Produce the following dialog box.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
12.3) Create a dialog box that contains
the Buttons Yes and No.
the Title "Exercises" and
the prompt "Are you ready for the next exercise"
12.4) Make the dialog box in question 12.2, display the Information Icon.
12.5) Extend exercise 3 by causing excel to display in cell A1 the text
'You pressed the Yes button'
'You pressed the No button'
depending on which button was pressed.
To see a solution use the Control panel at the foot of the screen and select the Solutions page.
Exercise - Auto_Open, Auto_Close
You should assign buttons to all the macros you write in these exercises
13.1) Create a macro that turns off the following:
gridlines, row and column headings, status bar and change the colour.
(Hint:Use the Tools Options View tab
- do it manually first if uncertain)
13.2) Write another macro to reverse the above.
13.3) Save the macro in exercise 13.1 under the name auto_open.
Save the macro in exercise 13.2 under the name auto_close.
Save the worksheet and close the book.
Now reopen the book.