Microsoft Excel Intermediate a learning Manual Vancouver Island Health Authority Revised March 2004

Section I: Introduction

The goal of this course is to introduce you to some of the advanced concepts that will enable you to use Excel to your best advantage at VIHA.

Review

Review Exercise 1A

Review Exercise 1B

Review Exercise 1C

Section II: Absolute Referencing

What is Relative Referencing?

What is Absolute Referencing?

Formulas can contain references that are mixed, relative, or absolute. To indicate an absolute reference, the program needs a $ before the column reference or the row reference or both. When only either the column or the row reference has a $ to indicate an absolute, the cell reference is called a Mixed Reference. When there is a $ in front of both the column and the row references, the cell reference is called an Absolute Reference. If there is $ in front of neither the column or row reference, the cell reference is called a Relative Reference.

To create either a mixed or absolute reference, you can either type the $ signs in, or, on the formula edit bar, into the cell reference for the cell that will be the absolute reference, and type the F4 key on your keyboard. The first the F4 is typed the cell reference will be changed from a relative reference to an absolute reference. The second and third times you type F4, the cell reference will be turned into the two different kinds of mixed reference. The last time F4 is typed the cell reference will return the reference back to a relative reference.

Each worksheet can contain a different absolute reference in the same cell reference since the $ signs produce an absolute reference that is specific to the spreadsheet where it is located.

Absolute Referencing Exercise 2A

Naming a Cell

The easiest and most direct way to create a named cell is to name it in the Name Box at the far left of the Formula Edit Bar. Make sure you have the cell that contains the constant selected, and then click into the Name Box. This will select the contents: the cell reference of the active cell. Type the name you wish to give the cell and then press Enter.

There are a few rules to naming a cell. First, the name must begin with either a letter or an underscore. It cannot start with a number. Make sure that you use a name that doesn’t resemble a cell reference. The name must contain no spaces. Use an underscore if you wish to show spacing. The name can be no longer than 10 characters and can be alphanumeric. The name is not case sensitive.

You must remember to press Enter to engage the name. Clicking outside the box with your mouse doesn’t usually work. It is a good idea to keep all the named cells, along with any reference material for the workbook, on a separate worksheet since you can use the named cell reference across the whole workbook.

Some of the advantages of using a named cell for a workbook are the ease of changing the value of a constant across a whole workbook. You only have to change it in one place and the impact will take place wherever that cell name has been used in a formula.

Which brings us to the next major advantage. When a cell is named, you can type in that cell name in a formula instead of the cell reference. This means that you can use English terminology in formulas and it is then up to the program to match the ‘English’ to the value in the named cell.

There is also the fact that the cell is easily located when the value in the cell must be changed. It’s a simple matter of using the drop-down list arrow on the Name Box and selecting the appropriate cell name. The program will automatically take you to that cell and select it for you.

Absolute Referencing Exercise 2B

We will be using these techniques in the following section.

Section III: Creating Workbooks Summary Sheets

In addition to entering labels, formulas, and perhaps values, when you finish building a worksheet you should format it so that information displayed is clear and understandable.

It may also be a good idea to lock the cells that contain formulas so that they can’t be accidentally overwritten.

In the following exercises, we will be using a worksheet that has been created using the following steps to complete a workbook that details sales figures for a sales year. The worksheet we will be creating is a Summary Sheet.

Six Steps to Building a Successful Spreadsheet

1	Survey all key stakeholders to understand what is the required output of the spreadsheet, and how and from whom the input information is to be gathered.
2	Gather the data and plan how the information must be presented to ensure the required output, and design any required reports.
3	Build the raw spreadsheet by placing row and column headings, and then build the formulas. Format spreadsheet as necessary.
4	Test the formulas using the value “1”. Most calculations can be estimated accurately when the value “1” is the only value being used.
5	Test the formulas using real data that can be used to verify that the formulas are working correctly. Set up Protection on the cells containing the formulas to avoid overwriting.
6	Build any Custom Views to be used and verify that they meet the requirements of the end user(s). Build and test reports, printing them off to ensure that they print off correctly.

Its Just a Formula

Remember high-school algebra? Well, just in case you don’t, read what follows carefully. When Excel calculates the results of a formula that contains more than one operator, it follows the Standard Order of Operations known as BEDMAS:

After Excel has completed the BEDMAS calculations, it will also look for and calculate Comparisons.

Comparisons =
< >

> <

Creating formulas in a Summary Sheet

There is one important consideration to keep in mind, however, when you are creating a summary sheet. If you intend to use Autofil as a means of copying the formulas from cell to cell, you must make sure that each spreadsheet in the set is an exact copy of the others in terms of where values and formulas are stored. The best way to accomplish this is to create the first spreadsheet, and then copy the whole sheet structure, including summary formulas, onto the all sheets to be used.

Summary Sheets Exercise 3A

Protecting Your Work

To prevent these kinds of disasters, Excel enables you to protect either the workbook, the worksheet, ranges, formulas, or single cells.

However, realize that although you can protect cells within a workbook, any vital information hidden or protected can be unprotected and disclosed by advanced Excel users. The only way to prevent this is to protect the whole workbook with a password so that the workbook can only be opened by an authorized person.

The process for protecting selected cells is as follows:

1. 
   Select the whole worksheet.
   
2. 
   Choose Format, Cells to open the Format Cells dialog box.
   
3. 
   Select the Protection tab and deselect the check for the Locked option. (This does not produce any visible effect.) Deselecting the Locked option allows you to then select the specific cells or ranges of cells that you want to protect and lock them.
   
4. 
   The Hidden option on this tab allows you to hide the cell contents or formulas for selected cells. Choose OK.
   
5. 
   Select the cells or ranges you wish to protect, and open up the Format Cells dialog box and the Protection tab again. Select the Locked option to protect the selected cells. Choose OK.
   
6. 
   To protect the sheet, choose Tools, Protection, Protect Sheet. In the Protect Sheet dialog box, you can make on of three choices:
   
   1. 
      You can protect the worksheet contents
      
   2. 
      You can protect the worksheet Objects
      
   3. 
      Or you can protect the worksheet scenarios
      

1. 
   If you would like to password protect the whole workbook to prevent unauthorized access to the workbook:
   
2. 
   In the File, Save As dialog box, display the Tools drop-down menu and choose General Options.
   
3. 
   In the Save Options dialog box, enter the password in the Password to Open text box. Without the password, no one will be able to open the workbook, so make sure you remember it.
   
4. 
   Entering the password in the Password to Modify box requires a separate password for editing. A second layer of protection.
   
5. 
   If you select the Read-only recommended option, the user will be asked if they want to open the file as a read-only. They can then save any changes as a new file, leaving the original as they found it.
   
6. 
   Click OK and then confirm the password. Click OK, give the file a name and a place to live and then click Save. Click Yes to replace the old version if you are saving over the original file.
   

Summary Sheets Exercise 3B

Section IV: Using Functions

However, not all functions are intended to complete complex calculations. For instance, the Sum function available from the Standard Toolbar (Greek letter Sigma: ) is not a complex calculation.

Nor are they all calculations as we would normally view it. The Now function creates a field code to put the current date and time into the cell that contains the function every time you open the file or send it to print creating a date and time stamp.

All functions have a common syntax:

If you just want the answer and don’t necessarily want to put the information into a cell, the most commonly used functions can be accessed by first selecting the range containing the values you wish to use in the argument, and then looking at the Status bar just to the right of centre. By default, the program will yield a sum of the selected cells, but if you right-click onto the summed amount, you will be given a pop-up list of further function choices to use.

If you know the name of the function, it is possible to type the function directly into the cell where you want the answer to display, but if you don’t get it spelled exactly right, it is exactly wrong, and it will not be recognized by the program. So for the most part, the easiest way to use a function is to use the Paste Function dialog box.

Building a Function using the Paste Function Dialog Box

1.

For some functions, when you use an argument box, another becomes available for a further argument. When all the arguments are in place, the Formula Palette will display the answer in the dialog box so that you can decide whether the function is working properly for you.

After you click the equals sign (=) to the left of the Formula Edit Area on the Formula Bar, the Name box to the left of the Formula Edit bar becomes available with a drop-down list of the most recently used functions. The Formula Palette can be launched from this set of choices as well.

As with any other formula, first you click the cell where you want the answer to appear. From there, you select the Function you wish to use and launch the Formula Palette to complete the formula.

Working out the Future Value of an RRSP

The syntax of the function is: =FV(rate,nper,pmt,pv,type) where,

For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

Using Functions Exercise 4A

What will your RRSP be worth in 10 years?

Change the payment from $250 to $200. What will your RRSP be worth?

Change the interest rate from 10% to 5%. What will your RRSP be worth?

Using the Payment Function to Calculate your Mortgage Payments

The syntax of the function is PMT(rate,nper,pv,fv,type) where,

The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.

Make sure that you are consistent about the units you use for specifying rate and Nper. For instance, if you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for Nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for Nper.

Tip To find the total amount paid over the duration of the loan, multiply the returned PMT value by Nper.

Using Functions Exercise 4B

Which mortgage would best meet your needs?

Why?

Will you have to negotiate for a better rate?

What could you change to make this mortgage better for you?

Using the Standard Deviation Function

The syntax of the function is =STDEV(number1,number2,…) where,

Number1,number2,… are 1 to 30 number arguments corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.

STDEV assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the standard deviation using STDEVP.

The standard deviation is calculated using the "nonbiased" or "n-1" method.

Using Functions Exercise 4C

What is the average value?

Section V: Data Management in Excel

Structuring a Database

It is very important to understand clearly why you are building the database, what reports are required and what information is required in those reports. Once you understand what you are going to want out, how you put it in is relatively easy.

Since you will only be able to see part of the list on the screen, it is probably appropriate to have the most commonly referred to information to the left on the sheet, with the less used categories sorted to the right in order of importance.

One of the most important issues to be sorted out before the database is put into use is that of consistent nomenclature. It’s impossible to get accurate data out if consistent data does not go in. For instance, it is becoming common practice to use ISO codes for countries, provinces, states, etc because they are used consistently across the larger population. They also take up less storage space: they’re shorter. Consistent spelling and text case are similarly important.

The Concept of Fields and Records

Records are the rows. Each record contains all the information (in the field cells) for each individual or item on the table.

Using a Form for Data Entry

To start a new record, click the New command button.

To find a particular record, click the Criteria command button and type the information that defines the record(s) you are looking for in the appropriate field(s). From there, you can use the Find Next and Find Previous to find other records that meet the criteria.

To go back to the form, click the Form button.

To delete a record, display the record and then click the Delete command button.

If you accidentally delete the wrong record, click the Restore button to get it back. It functions as the “thank goodness its there, Undo”. However, you must click it right away.

To close the form, click the Close command button.

Data Management Exercise 5A

Using the form, find the record for Trevor. What is his parking slot?

Custom Views

To create a custom view, first set up the View as you would like to see it.

Then choose View  Custom Views to open the Custom Views dialog box.

Click the Add… command button to open the Add View dialog box.

Type a name for the new custom view and then click OK.

To delete a view, from the Custom View dialog box, select the view you wish to delete and click the Delete command button.

Data Management Exercise 5B

Conditional Formatting

Interactive conditional formatting allows you to use the program to highlight the cells in the list that meet a condition entered into a source cell or cells.

To create a conditional format:

Select the range you want to apply the conditional formatting to.

Choose Format Conditional Formatting.

In the Conditional Formatting dialog box, create the condition you wish to be met.

Click the Format command button and set the format you wish to use to highlight the cells that meet the condition.

Click OK.

Data Management Exercise 5C

In the Conditional Formatting dialog box, make sure that Condition 1 reads, “Cell Value is”.

Make sure that the first criterion is that the value be “between” and then click the first roll-up. Click into cell F5. Click the roll-down.

Click the second roll-up and click into cell G5. Click the roll-down.

Click the Format… command button. Select the colour you wish to use to highlight those values that meet the condition(s).

Click the OK button and then click OK again.

In cell F5, type 32000. Tab to cell G5 and type 42000.

What happens?

Change the values in one or both of the F5 and G5.

What is the result?

Sorting

At the top of each column in a database, it is a good idea to put a label that names the fields. To allow the program to distinguish these labels from the rest of the data, format the labels by bolding, underlining, or even better, both.

Formatting the labels differently allows the program to identify and use the labels in the Sort dialog box as column indicators in the Sort By drop-down lists.

Another consideration when creating a list that you know you will be sorting is to make sure that the range that is your list is set up correctly. Excel identifies a range by the empty space around it. It is therefore important not to leave rows between the field labels and the data. It is important not to leave any empty rows or columns in the middle of the database list. Make sure there are no hidden rows or columns that are empty, etc. The program will simply not read across and empty rows or columns to include data on the other side and your sort will not be accurate.

Finally, it is important to understand how Excel sorts your data. The program sorts from left to right of course, just as we read, character by character, beginning with numbers first, then spaces, symbols, and finally letters. If your list contains some data that include spaces and some that don’t for instance, you won’t get the sort you expect. For example:

List Sort
McArdle Mac Kinnon
Mc Ardle Mackinnon
McCandle Mc Ardle
Mc Lean Mc Lean
Mackinnon McArdle
Mac Kinnon McCandle

Because Mc Lean includes a space, it falls before any names beginning with Mc that don’t include a space. To make it more interesting, Excel ignores apostrophes (O’Sullivan) but sorts data with hyphens (Knowles-Smith).

Sorts that include alphanumeric data are also sorted in a way that may confuse you. If your part numbers run from B1 through B102, you will get the following sort:

B1
B10

The program sorts literally from left to right with out regard for normal numeric sequencing.

Data Management Exercise 5D

Would it make more sense in terms of how you read the information to move the columns into the same order as your sort? This must be considered when you are planning your database.

Creating a Custom Sort

Filtering

Using the AutoFilter

To use the AutoFilter, click the drop-down arrow on the field containing the information you wish to filter for and select the filter criteria. The program will display all records that meet that criterion, exactly. Another reason to make sure that data in input in a consistent format.

For instance, you wish to filter for all the records working in the Accounting Department and you have not been consistent in how you input the information for Department. If you select Accounting for your criteria, the resulting return will not include those records where you used the short form Acct. or Acc. or Acctg. The program will do exactly what you tell it to do.

Data Management Exercise 5E

When you set the filter, what is the visual clue that tells you which filters are being applied to the list?

Creating a Custom AutoFilter

Data Management Exercise 5F

Excel Glossary