ReviseICT.co.uk homepage
Microsoft Excel
Spreadsheet software

Tasks

 
Pocket Money

Close any open Excel spreadsheets.  Create a new spreadsheet following your teacher's instructions.

Directions

1. You should have a blank spreadsheet in front of you.  You are going to use information to create a more complex spreadsheet.

2. Look at the information below - this shows a child's pocket money over one month.  Some extra money has also been earned:

3. Design a spreadsheet to show this information.  One way to do this would be to have rows for each week and columns showing how money was gained.  Try to do this as quickly as you can. Remember to "format cells" as currency to show the money correctly.  You need five rows and five columns.

4. You now need to use the "Auto sum" function to work out the total money gained by each method.  Select a cell underneath your column for "Money from Dad", perhaps B8.  Click on the "auto-sum button" and highlight all the money earned from Dad:

5. You can use a clever feature in Excel to quickly copy your formula across.  Click on the cell showing the total money earned from Dad.  Hover over the bottom right hand corner until your mouse changes to a cross as shown below:

6. Hold the left button down, and drag across the four other columns.  You should find that your formula is copied across all five columns.  Check whether it works by changing some of the numbers - does the total money change?

7. In a cell to the right of your current work - perhaps cell H8 - create a formula that shows the total money earned during the month.  Is there a quick way to create this?

8. Finally, try to think how you could show the percentage of total earnings for each way you have gained money.  This will involve creating a new formula for each column using the total money calculation.  You can show your results as percentages by using the "format cells" command again.

Take care completing the final step - you can copy the formula across, but you may find errors occur.  This will be because moving a formula to the right means Excel tries to do the same calculation but with the numbers one to the right.  You need to tell Excel to always work out the percentage using the same cell.  One way to do this is to "fix" the cell reference by adding a "$" in front of the cell letter and number.  Instead of your first percentage formula reading "=B8 / I8", it will thus read "=B8 / $H$8".

When you copy this formula, the first cell will change, but the second cell is fixed. Try it out to see!

 

All done?
Move onto the next task.

Index page