- Creating formulae (5)
- Formatting worksheets (7)
- Basic printing (8)
- Charts (10)
- Absolute references (9)
- Range names (13)
- Conditional formulae (17)
- Conditional formatting (7)
- Basic tables (6)
- Formatting numbers and dates (3)
- Working with dates (1)
- Data validation (6)
- Protection (1)
- Advanced IFs (6)
- Lookup functions (14)
- Advanced lookup functions (9)
- Text functions (1)
- Advanced charts (4)
- Multiple worksheets (1)
- Advanced tables (2)
- Pivot tables (3)
- Advanced pivot tables (3)
- Scenarios (2)
- Data tables (2)
- Array formulae (2)
- Building models (2)
- Masking (2)
- Cashflow calculations (1)
- Investment appraisal (1)
Excel | Multiple worksheets exercise | 3D Sums - Teenage Spending
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
You can learn how to do this exercise if you attend one of more of the courses listed below!
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Open the file contained within the folder name shown above.
This workbook has separate sheets showing a teenager daughter's expenditure from January to May:
Click on the sheet tabs for each month to see the expenditure for that month.
Use your CTRL key to copy the May sheet to create another copy and re-name it as June:
When you click and drag using CTRL it creates another copy of that sheet.
Type in new figures for June as shown below:
June is an expensive month! Change the month name in cell B1 to June.
Use your CTRL key to copy the June sheet to create another copy and re-name it as TOTALS.
Delete all the costs in cells B4:B10.
On the TOTALS sheet, in cell B4, use a 3D sum to add together all iTunes costs for all 6 months.
Copy this down to calculate all the other costs.
Change the iTunes cost in June to £45 (your daughter now drives and bought the TomTom app) and check that the total updates:
Your formula in column B is a 3D-sum:
Select all the sheet tabs to go into group mode so that any changes you now make will be made to all the sheets, then do the following:
- Format all the numbers to Currency with 2 decimal places;
- Add light shading behind all the number cells;
- Change the text in cell A7 from Socialising to Tickets for Clubs
- Remove row 10 - your daughter says sport is too expensive!
Remember to now make sure that group mode is off (click on the sheet tabs) at the end!
The new TOTALS sheet should look very similar to this:
No more sport, sadly!
Use Save As... to save the file in your own new Excel work folder.