Session 7 - Consolidations and Multiple Sheets

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. 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 2015. Increase the Font Size to 16.
• In A3, Expenses
• In A5, Salaries
• In A6, Stationery
• In A7, Telephone
• In A8, Telephone
• 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

Now to 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.

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.

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(
• 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 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

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 Cam[uses 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 Formulas

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

You may download this basic spreadsheet from Moodle. The file is called 2015PivotTableData.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!!

Pivot Tables

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

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.

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.

Session 6 - Advanced Formulas, Functions and Conditional Formatting

Advanced Formulas

All of the sample files from today's lesson are available from Moodle.

Logical Functions

Nesting Ifs

 Have a look at the following spreadsheet.

A reminder of what the IF function looks like and the components required.

The top one is a simple If function with a calculation. In this case, our logical test will be whether the savings are less that $4000. If the amount is less than $4000, the condition will be true and the rate would be 2%. If the amount is not less than $4000, then condition is false (the amount must be $4000 or higher), which will display the rate of 5%.

=IF(A8<4000,2%,5%)



N.B. You can do the same function by placing the interest rates in cells on your spreadsheet and using a cell reference for the interest rates.

We can also NEST an IF Function. Nesting means that for the true or false activity, there is another IF function. The second example uses a nested IF function.

EXERCISE 1

Complete EXAMPLE 1 and EXAMPLE 2 in the 2015LogicalFunctionsIf spreadsheet.

AND Function

The AND function is used to compare more than one condition. It returns TRUE only if all of the conditions are met, and takes the format:

=AND(condition1, condition2,…)

For example, you could use the following formula:

=AND(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested

C2 < 300 is the second condition being tested

This will only return the result TRUE if the value in cell B2 is greater than 400 and the value in cell C2 is less than 300. In all other situations, the result will be FALSE.

EXERCISE 2

Complete PART A and PART B in the 2015LogicalFunctionsAnd spreadsheet.

The OR Function

The OR function is also used to compare more than one condition. It returns TRUE if any of the conditions are met, and takes the format:

=OR(condition1, condition2,…)

For example, you could use the following formula:

=OR(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested

C2 < 300 is the second condition being tested

This will return the result TRUE if either the value in cell B2 is greater than 400 or the value in cell C2 is less than 300. The result will be FALSE only if neither of the conditions are met.

EXERCISE 3

Complete PART C in the 2015LogicalFunctionsAnd spreadsheet.

LOOKUP FUNCTIONS

Choose

The lookup function we will use is the CHOOSE function. The CHOOSE function is designed to allow you to make a choice from a list of items. The format of this function is:

CHOOSE(lookup-value, list of items)



Create the following spreadsheet

To use the CHOOSE function, each of our values need to be given a name, not the whole data table.
Click on the first rate – cell B24 in the example and in the name box, type rate1. Call B25 rate2, B26 rate3, B27 rate4.

The CHOOSE Function will be:

=CHOOSE(C4,rate1, rate2, rate3, rate4)

Have fun nesting one or more of these functions together.

COUNTIF, SUMIF, AVERAGEIF

The functions Sum, Average and Count can be used to provide statistical information about a range of data.

These functions also have a counterpart that allows you to incorporate conditions.

Count how many have a branch of Cranbourne: =COUNTIF(B4:B29,"Cranbourne")

This is what the SUMIF looks like

EXERCISE 4

Using 2015StatisticalIf spreadsheet perform the following:

• Sum the Q1 values that are over $5000
• Sum the values in Q1 that are not Frankston.
• Count how many are Dandenong
• Count how many reps earned more than $5000 in Qtr 1
• Total the sales for Cranbourne

Multiple Conditions

Use the SUMIFS, COUNTIFS, AVERAGEIFS to have multiple conditions that are to be tested.

Count how many Reps in Cranbourne that earned over $5000 in Qtr 1

EXERCISE 5

Count of Reps in Dandenong who earned a total of more than $20000
Count of Reps who earned more than $5000 in ALL Quarters
How many of the above were Dandenong?

Conditional Formatting

Data Bars

In later versions of Excel (2007 - 2013), under conditional formatting, you can create an inline chart. I personally think these are cool!! For those visual people, you can see the data as a number as well as represented as a chart bar.

Use the same spreadsheet - 2015StatisticalIf

Highlight the values in Qtr 1. Under conditional Formatting and Data Bars. Hover over one of the buttons and see what it does.

I know, right!! AWESOME isn't it. Just a little thing, but it can mean a lot more than just the dollar figures when it comes to comparing - how easy is it now to see where the larger amounts are???

Color Scales

Another way to be more visual with your data is to use Color Scales. Rather than creating a bar, your cells are coloured based on the values, making it easier to find the lower amount and/or the high amounts.

For the default color scale, Excel calculates the median or middle value.  That cell is coloured yellow. The cell that holds the minimum value is coloured red and the cell that holds the maximum value is coloured green. All other cells are coloured proportionally around these.


If you want to set your own scale and/or rules for formatting, you can.

Icon Sets

These work in a similar way to the color scales except that icons are used rather than the cells being coloured.

If you just want to see the icons and not the values, you can check the box Show Icons Only.

With a 3 icon set, Excel, by default will take the maximum and minimum values and create 3 percentiles for the amounts in between. Again, you can change the rules to suit yourself.

Session 5 - Goodbye Access; Hello Excel

Sadness for many, but for some, I think perhaps a sigh of relief.

Final Bits

So, today I will impart some final bits on Access and of course give you all the details for your ASSESSMENT TASK.

Calculated Fields

The last of the special Access field types is Calculated Fields!! You can probably guess from the name what this means...

Rules:

    The calculation cannot include fields from other tables or queries.
    The results of the calculation are read-only.

You can also use any of the in-built Access functions

Here is an example. In our Video Vidiots database, we want to be able to keep track of how long each rental is.

To do this, we need to take the Returned date and subtract the date borrowed - this will give us the loan period in days.

Step 1: Add the calculated field to the table.

Step 2: When the expression builder window appears, put in the calculation for the field.

Click OK and make sure that you save the new table design.

Step 3: Add the field to your form. I added mine to the Rentals and the Returns Subform

Now view the form and see that the calculation works!!

Basic Coding - Advanced Search on Form

This is optional as it does require a little bit of coding - just a taste of what you can do!! It doesn't really matter whether you understand it all, so long as you can duplicate it to suit your circumstances.

What this will enable you to do is to find the first record that matches the criteria and then click through the records that match until you find the one that you are after. This uses the principle of Find First and Find next.

Events

The use of macros and coding in Access requires you to understand what an event is - an event is anything that you can do as a user i.e. click on something, type something, load something etc. The list of available events for an item on a form are within the properties for that item.

To start with, I am going to make sure that the Headers and Footers for the form are displayed -  I want to add a search box within the form footer.

To display headers and footers, right-click on the Detail bar.

 

I minimised the Header, but increased the size of the Footer so that I could add some search boxes and button etc.

In the Footer, add 2 text boxes - they should both be unbound.

In the Properties Sheet, make sure you give them meaningful names (and remember what they are).

The Membership one is called SrchMembership
The Surname one is called SrchSurname

You will also need to add a Command Button - this will be where we will run the macro from. If this button is clicked (The EVENT), then the macro will run.

Once you have your text boxes and your command button, you are ready to type in your first bit of code. We will assume that we want to include the wildcard in the search so that it is not relying on an exact match.

Select the button and make sure that the properties sheet is displayed. Click on the drop down arrow next to On Click and select [Event Procedure]. Click on the ... button.

You will then be taken to the Visual Basic editor. Don't worry too much about it at this stage, just type in the same as you I have using the names that you have called your text boxes and fields etc.

 

Save and test your form - does it find the first record that matches your criteria? You can leave it at this, or you can add some more "Tweaks".

In my example, I have added a Find Next command Button, with the view that you can scroll through a list of "matches" until you find the one that you are looking for. The code is identical except for the FindNext instead of FindFirst. Because we have included the like and * wildcard, the match does not have to be exact - one letter is enough. The easiest way is to cut and paste the code and change the FindFirst to FindNext.

Bookmark will return the user to the current record.

Slightly More Advanced

You can add multiple buttons to search using different fields. I allowed a search on either membership number or by last name. I used a test to determine which of the fields had a value entered into it and created the search accordingly. Here is my code.

SEE SEPARATE DOCUMENT WITH ASSESSMENT TASK DETAILS.

Options - turning off stuff

If you were creating this for someone to use who does not know Access, you would customise it so that the user would only be able to do the things that are in your menu.

You will find all of the things that you can turn on or off in the Options for the Current Database. Turn off all of the things that you don't need as well as setting the Man Menu to be the form that displays when the database is opened.

Of course, turning off stuff also means that YOU are unable to access some of the tools that you may need to make changes to your database. To open the database so that you have all the essentials available, hold down the Shift key while double clicking the file to open it.

EXCEL Basics

It is expected that you can perform the following things in Excel before we move onto some more advanced Excel stuff.

• Create a New Spreadsheet
• Add Text, numbers and Dates into Cells
• Use the AutoFill tool
• Use formulas to perform calculations
• Understand when to use absolute and relative addresses in formulas
• Format the spreadsheet to look pretty
• Create a basic chart
• Use some basic Functions

If you are confident with the above, you do not need to stay for the quick refresher task