Lesson 8 - Excel Consolidation

Working with Multiple Sheets – 3D

Excel is great if you need to have a number of workbooks that are formatted the same - An example could be an organisation with multiple sites or branches or an organisation with multiple departments.

Our Class Example

South East TAFE library services has 5 branches in and around the south-east of Melbourne and the Mornington Peninsula. They are located in Dandenong, Pakenham, Cranbourne, Frankston, and Berwick. We need to create a budget for the next 12 months for each branch.


Open Excel and put the names of each branch on the sheet tabs - either double-click or right-click and choose Rename. You can add new sheets by clicking on the little + button at the bottom left of the screen, next to the last sheet.

We also need a sheet in front to summarise and total the Branches.

Click on the Add New Sheet button and type in All Campuses as the sheet name. You can drag this sheet to the front by clicking on the TAB and dragging it to the front.

This is what mine looks with all of the branches and total included.

You can now start to create your spreadsheet.

IMPORTANT: Before you start, you need to select each of the sheets to put Excel into Group Mode. You can do this by clicking the first sheet and then Shift-Click the last sheet. You could also click the first sheet and use Control-Click on each of the sheets you want to include. Up the top, next to the filename, you should see the word Group in brackets.

In Group Mode, complete the following:

• On the top Spreadsheet, put your cursor in A1 and type in the heading Running Expenses Budget for 2021. Increase the Font Size to 16.
• In A3, Expenses
• In A5, Salaries
• In A6, Stationery
• In A7, Telephone
• In A8, Electricity
• In A9, Book Repair and Maintenance
• In A11, TOTAL
• In B3, January then use Autofill to complete the months through to December
• In N3, TOTAL

Still in Group Mode, Now add the formulas!!

• In B11, Use Autosum to insert the formula to add the expenses for January.
• Copy this across to the months and TOTAL.
• In N5, use Autosum to total all the months Salaries expenses.
• Copy this down to the other expenses.

Still in Group Mode, Apply some basic formatting

• Increase the column widths to suit the content
• Make headings Bold so they stand out
• Apply borders or any other formatting to enhance your spreadsheet.

Your end spreadsheet may look similar to the following:

Now to add the data to each of the branch spreadsheets. Click on each of the Branch TABS to select each individual Branch to enter the data.

For simplicity, we will assume that the data will be the same for each month. Enter the following data into January for each branch and copy to the other months.

Dandenong
Salaries   4500
Stationery   680
Telephone  320
Electricity   240
Book Repair and Maintenance   620

Pakenham
Salaries   3200
Stationery   340
Telephone  280
Electricity   330
Book Repair and Maintenance   550

Cranbourne
Salaries   1800
Stationery   610
Telephone   190
Electricity   230
Book Repair and Maintenance   490

Frankston
Salaries   4200
Stationery   660
Telephone   580
Electricity   520
Book Repair and Maintenance   990

Berwick
Salaries   2500
Stationery   210
Telephone   140
Electricity  270
Book Repair and Maintenance   420


You should now have a worksheet for each branch complete with totals.

Complete the ALL Campuses sheet

• Place your cursor in B5 of your TOTAL sheet.
• Type in =SUM(    OR use the autosum button
• Click on B5 in the Dandenong TAB followed by a comma
• Then B5 in the Pakenham sheet followed by a comma - do this for each of the branches and at the last one, close the bracket (if you need to) rather than a comma. Press ENTER.

Your formula should look similar to the following:

=SUM(Dandenong!B5,Pakenham!B5,Cranbourne!B5,Frankston!B5,Berwick!B5)

• Now all you have to do is copy the formula down and across to the other months.

Your completed Spreadsheet should look similar to the following:

N.B. as well as the SUM using each sheet with commas as above, you can also use the shortcut

=SUM(‘Dandenong:Berwick’!B5)

OR

=SUM(‘*’!B1)

If you like, you can even colour code the TABS for each branch.

Activity 1

You are the manager of an organisation that sells educational resources to Government and independent Primary Schools throughout Victoria. You have 4 sales reps who are on the road, each dealing with a different region of Victoria – North, South, East and West.

You need to do a forecast for the rest of the year and based on previous experience, you have decided to use the actual figures from January and apply a percentage increase across the rest.

The Monthly Percentage increases across the products are as follows:

	North	South	East	West
Books	3.00%	4.00%	2.00%	0.50%
Magazines	0.00%	0.00%	1.00%	0.00%
CDs	1.00%	0.50%	1.00%	1.00%
DVDs	1.50%	1.00%	0.50%	0.00%
Software	2.00%	3.00%	1.50%	1.00%

January data for each of the regions is:

North

Books	3209
Magazines	238
CDs,	120
DVDs	450
Software	2460

South

Books	5430
Magazines	534
CDs,	200
DVDs	250
Software	1200

East

Books	8730
Magazines	449
CDs,	20
DVDs	220
Software	1090

West

Books	2100
Magazines	130
CDs,	54
DVDs	400
Software	1630

Your Task: COMPLETE EACH REGION SPREADSHEET AND THE TOTAL SHEET USING THE ABOVE CONSOLIDATION TECHNIQUE

NB: If putting the percentage growths in is too complicated, just assume that it is the same for each month as in the previous exercise.

Consolidation using Paste-Special

I am using the same spreadsheet from the previous exercise. I have removed the figures and formulas from the total workbook.

Start by clicking on the Dandenong Workbook. Select the cells with the data – B5:N11.

Choose Copy from the Home Ribbon or use the shortcut keys (Ctrl-C)

Change back to the ALL Campuses workbook, Highlight the range B5:N11 and choose Paste Special from the drop down box on the Paste button or right-click and choose Paste Special from the option box.

Choose Add from the Operation choice.

Repeat for all of the branches. This method will work whether the data is in the same workbook or totally separate workbooks.

The disadvantage is that it is not dynamic and any changes to data on any of the related sheets will not automatically be reflected in the TOTAL sheet.

Consolidation using Data Tab

Another method is to use the Consolidation Button on the Data TAB. There are two options:

By Position – use this if the worksheets are laid out exactly the same (as they are in our case)

By Category – Excel will use Row and Column labels to match data in the source worksheets.

Taking our previous example, I have again erased the information on the TOTAL worksheet.


From the Data Ribbon, select Consolidate.

There are different types of consolidations, the most common being Sum.

When you click on Consolidate, the option box will appear. If the data is in different workbooks, then you can click on browse to open the workbook and then select the range. If the data is in the same workbook, you can just click on the range select button and select the range from the sheet. You need to Add each range to the consolidation options.

Tick the option to create links to data.

Click OK to finish. The consolidation data will now appear in your spreadsheet.

When you create a link to the data, your consolidation becomes an outline. The plus buttons allow you to collapse or expand the consolidation to see the individual items.

Activity 2

With the spreadsheet created in Activity 1, complete the consolidation using the Data TAB consolidate

Pivot Tables versus Subtotals

A Pivot Table can be used instead of formulas in some circumstances. Have a look at the following spreadsheet.

This is the same file that was used for the statistical IFs but without the totals. You can download this one if you like or just use the one from last week. The file is called
PivotTableDataOnly.xlsx


Our spreadsheet included Branches - how can we total or average the information easily by branch?

Subtotals

Start by Sorting the table by Branch.

From the Data ribbon, select Subtotal from the Outline section.

Select Branch in the first box - At each change of and select all of the quarters and click OK

You can collapse or expand using the + - or the numbers 1 2 3 in the top left hand corner.

You can use the same method to Average or even count the amounts.

You can even use more than one summary type!!

Using the numbers and + - on the left hand side, you can change the detail that is displayed.

Pivot Tables

So how can we do the same thing using a Pivot Table?

• Load the original pivot chart spreadsheet and remove the totals

Click on Insert - you will see the Pivot Tables button on the far left.

The Recommended Pivot Tables button will analyse your data and create a pivot table based on its assumptions - it usually does a pretty good job.

With the Insert Pivot Table button, you need to "build" your report.

You drag the information you want from the top section to the pivot table sections.

The default is that Excel will Sum your values, but you can change this by right-clicking on any value and changing to a different summary type or click on the item in the PivotTable dialog on the right hand side of the screen.

And if that wasn't enough fun.....

We have Pivot Charts.

Context sensitive Design and Format Ribbons can be used to improve your pivot table and chart. The best way to learn about Pivot Tables and Charts is to play.

If you change your data, you will need to update the pivot table - Right-Click and Choose Update.

The sky's the limit with Pivot Tables - you can easily summarise pretty much any size spreadsheet into any levels.

************ IMPORTANT **************

Don't forget your Protection

Why Protection? The best reason to use protection (apart from the obvious) is so that all the great work you have done in preparing, formatting, and perfecting your spreadsheet is not ruined by someone putting information in the wrong spot.

By default, all of the cells in Excel are "protected", so if you want people to be able to edit cells, you need to unprotect them or unlock them. Any protection that is applied to individual cells is not enforced until the actual spreadsheet is protected.

• To unprotect the cells, use Format Cells.

Once you have unprotected the cells that YOU want people to be ABLE to access - protect the sheet.

There are a few different levels of protection that you can apply. You will find these under Review.

OR from the Home Ribbon

 

You can be very specific about what you will allow others (or yourself) to change when using the spreadsheet and you can set a password which must be entered before the spreadsheet can be unprotected. Stay safe people!!