Spreadsheets/Spreadsheet challenges

The following spreadsheet challenges are designed to flex your spreadsheet skills. The idea is simple: use your favourite spreadsheet program to re-create the document displayed in the picture for each challenge (using formulas where noted)!

The data for each challenge will need to be entered by you (unless some data is provided in the challenge) and provides a good opportunity to practise your keyboarding skills.

Note: You can right-click on any image and open it up in a separate window to see a larger version. If you then want to print the image you can then click on the "Download high resolution version" link on that page.

Challenge 1: Formatting and basic formulas

Right-click and open to view a larger version of the image

You're in the business of selling some soccer apparel on the weekends while your little brother plays his games! Your challenges is to create the spreadsheet displayed in the picture with the data provided below.

Notes:

• The data in columns A-D will need to be entered by you.
• Columns E-F require you to enter a formulas (and you should make use of the Autofill feature).
• The total profit and average price will also require a formula (Note: should be "Average selling price").
• You might like to review the intro tutorials on entering formulas at Roadmap to Excel 2003 Training

Challenge 2: Creating a chart

Right-click and open to view a larger version of the image (Note image is from open office, so might appear slightly different)

Building on your data from the previous challenge, use your spreadsheet software to re-create the chart shown here on a separate worksheet.

Notes:

• You might like to review the tutorials under the heading "A picture is worth a thousand words" at Roadmap to Excel 2003 Training

Challenge 3:

TBD

Challenge 4: Absolute vs Relative Cell referencing

Right-click and open to view a larger version of the image

Your challenge is to create a new worksheet (in your Challenges workbook) and add your 3-times tables as shown in the image. The key is to add a single formula that you can then autofil for the whole column.

Extra Challenges

• Insert more columns to create a table that shows not just the 3-times-tables, but right through to the 12-times-tables.
• Only for the super keen: See if you can enter one formula that can be autofilled either down for the whole column, or across for the whole row! Not for the feint hearted (and not required, just if you're keen!)

Notes:

• The tutorial Get to know Excel: Enter formulas from Microsoft provides an excellent explanations and practise activities

Challenge 5: If formulas - calculating a formula sometimes

Add a GST column (General Sales Tax) to the challenge to the spreadsheet from Challenge 1, in between the Item column and the Cost price column, with the following data:

GST Item?

No

Yes

Yes

Yes

No

This should show that the Drink bottle and Baseball cap are the only items that are GST free (and have a "No" value in the GST column.)

Your challenge is to add a "Selling Price (GST)" column (after the "Selling Price ($)" column) with a formula that adds 10% to the GST Items only.

Challenge 6: More formulas and references

Create a spreadsheet to calculate the pay for staff at Lawson Swimming Pool for the 6-week holiday season. There are 12 casual staff employed at the pool. Staff members under 18 are paid at the Junior rate of $10.50 per hour. Those 18 or over are paid at the Senior rate of $15.25 an hour. Make up names for the 12 employees. Put the pay rates in a table at the top of the spreadsheet.

You will need columns for:

• Staff name
• Date of Birth
• Week 1 Hrs
• Week 1 Pay
• Week 2 Hrs
• Week 2 Pay
• ...
• Week 6 Pay
• Total pay for season

Notes:

• You might like to review the tutorial "Plan payments and savings by using financial formulas" at Roadmap to Excel 2003 Training (this will help you learn lots of extra stuff too!)

Extra tasks:

• Your spreadsheet should also calculate total wages for all staff and the average wage paid to staff (for each week as well as for the whole season).
• Protect the whole worksheet except the cells to enter the hours worked.

Challenge 7: Your own timesheet

Right-click and open to view a larger version of the image