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.

The answer to the exercise will be included and explained if you attend one of more of the courses listed below!

Software ==> Excel  (146 exercises)
Version ==> Excel 2016 and later
Topic ==> Multiple worksheets  (1 exercise)
Level ==> Average difficulty
Courses ==> Excel Advanced  /  Excel Business Modelling
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

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:

Multiple sheets

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:

Copying entire sheets

When you click and drag using CTRL it creates another copy of that sheet.

Type in new figures for June as shown below:

June figs

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:

3D sum

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 sport

No more sport, sadly!

 

 Use Save As... to save the file in your own new Excel work folder.

This page has 0 threads Add post