This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel (113 exercises)
Topic ==>	Multiple worksheets (1 exercise)
Level ==>	Average difficulty
Subject ==>	Excel training

Using 3D sums to calculate totals across sheets for 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.

Before you can do this exercise, you'll need to download and unzip this file .

Open the file contained within the folder name shown above.

This workbook has separate sheets showing an (imaginary) child'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 (do people still use iTunes?) 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 child 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.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

Excel training classes

This page has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Visual C# training

Excel training courses

Power Automate training

Power BI / DAX training

Python training courses

SQL training courses

VBA (macros) training

Site map

Terms & Conditions

EXERCISE TOPIC

EXCEL EXERCISES

EXCEL EXERCISES

Using 3D sums to calculate totals across sheets for teenage spending

Head office

London

Manchester