Spreadsheet Exercises
Solutions are available from the Topics Menu at the foot of the screen
Exercise 1 Entering Labels, Numbers, Formula and Saving Files
1 Build a spreadsheet with,
The months JAN-JUNE as column headings.
Enter 4 rows of figures under each heading.
Row labels sales 1, sales 2, sales 3, sales 4.
Totals of each column and each row and a grand total.
2 Position your cursor to cell A1 and Save your spreadsheet under the filename EXER1.
3 Quit Excel.
4 Restart Excel and retrieve your spreadsheet.
Exercise 2 Inserting Rows and using the Copy commands Global Formatting in currency.
Using the spreadsheet created in exercise 1.
1 Insert one blank row under the month labels
Insert another to separate the column totals from the figures above.
2 Format the month labels with a single underline
3 Format the column totals with a double underline.
4 Use the format command so that all numbers are expressed in currency format with no decimal places.
5 Save spreadsheet under the name Exer2.
Exercise 3 Review, Widening columns, deleting cells, adding labels.
1 Retrieve your spreadsheet created in exercise 2.
2 Insert a blank row above the month labels.
3 Enter the heading Rusty's Risky Restaurants in cell C1
4 Change the currency format to 2 decimal places
5 Set the Width of the first column to 15.
Set the width of the other columns as narrow as possible so that all the values are visible. (note you may need to experiment to achieve this)
6 Remove the double underline in the last row of your spreadsheet.
7 Enter the row labels and values for Food costs, Wages and Overheads as listed below.
Add formulas for overheads, total expenses and Net Profit to complete the spreadsheet
| Food costs | 25 per month |
| Wages | 20 per month |
| Overheads | 1/3 of each month's sales |
| Total expenses | total of the expenses |
| Net Profit | Total Sales-Total Expenses |
8 Using the SUM function total the expenses.
9 Build a formula to calculate net profit based on Total Sales -Total Expenses.
10 Use the copy command to complete the other month's expense and total cells.
11 Under each months net profit build a formula that calculates and shows net profit as a percentage of total sales.
12 Save your spreadsheet as exer3.
Exercise 4 Cell addressing ,Simple formula
You are required to build a multiplication table using a formula placed in cell B2 and copied throughtout the range B2:F6.
Exercise 5 Windows and Titles
a) Create a spreadsheet similiar to the one below.
Use the window command to show the Average column alongside the March column.
Practice moving from window to window.
b) Clear all windows and SAVE this exercise
Exercise 6 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.
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 7 ARITHMETIC FUNCTIONS
Determine the results of the following functions by entering them into Excel.
7.1 =ABS(-7.567)
7.2 =INT(7.567)
7.3 =ROUND(7.567,2)
7.4 Round off the following number to 1 decimal place -34.5632
7.5 =SQRT(16)
7.6 =SQRT(ABS(-64))
7.7 What is the square root of 234
7.8 =RAND()
7.9 Use the RAND function to generate a number between 0 and 10
7.10 Use the RAND function to generate a whole number between 1 and 10
7.11 Use a combination of functions to get a random number between 3 and 10
Exercise 8 STATISTICAL FUNCTIONS
8.1 Determine the result of the following
- AVERAGE(4,6,10,12)
- SUM(5,6,7)
- COUNT(2,2,2,2)
8.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.
8.3 Get the average, maximum value and minimum value of the list.
8.4 Total the list
8.5 Use the Count function to count the number of entries in list
Exercise 9 IF and Nested IFs
9.1 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.
9.2 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.
9.3 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.
9.4 Write the IF function that places 100 in cell C4 if cells A4 and B4 are greater than zero.
9.5 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.
Exercise 9 Lookups
9.6 Repeat exercise 9.3 using a LOOKUP table.
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.