Close any open Excel
spreadsheets. Create a new spreadsheet following your
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"
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
copy this formula, the first cell will change, but the second cell
is fixed. Try it out to see!
Move onto the next task.