Ana səhifə

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

Yüklə 136 Kb.
ölçüsü136 Kb.

Microsoft Excel

Learning Manual

Vancouver Island Health Authority
Revised March 2004

Table of Contents

Section I: Introduction 2

Review 2

Section II: Absolute Referencing 3

What is Relative Referencing? 3

What is Absolute Referencing? 3

Naming a Cell 3

Section III: Creating Workbooks Summary Sheets 5

Six Steps to Building a Successful Spreadsheet 5

Its Just a Formula 5

Creating formulas in a Summary Sheet 6

Protecting Your Work 6

Section IV: Using Functions 9

Building a Function using the Paste Function Dialog Box 9

Working out the Future Value of an RRSP 10

Using the Payment Function to Calculate your Mortgage Payments 10

Using the Standard Deviation Function 11

Section V: Data Management in Excel 13

Structuring a Database 13

The Concept of Fields and Records 13

Using a Form for Data Entry 13

Custom Views 14

Conditional Formatting 15

Sorting 16

Creating a Custom Sort 17

Filtering 17

Using the AutoFilter 17

Creating a Custom AutoFilter 18

Excel Glossary 19

Section I: Introduction

To be successful in this class, you should have a working knowledge of Windows 2000 and be able to use a mouse. You should also have taken an Excel Introduction course or have a demonstrable solid understanding of Excel basics such as the Excel window, menu selections, formatting, building basic worksheets, and formula structure.

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.


Please make a copy of the Intermediate Excel Exercise Files workbook in the Excel Intermediate folder on the Desktop. Place the copy on the Desktop. This will be your practice file for the day.

Review Exercise 1A

Complete the Food for Annual Fly-In spreadsheet by calculating the Total Cost of Food. Save your work.

Review Exercise 1B

Complete the sheet named Total Annual Sales by calculating the missing Qty information, and then calculate the Total Annual Sales using formulas. Save your work.

Review Exercise 1C

Complete the Sales Totals by Region spreadsheet by creating formulas to calculate Total Sales using AutoSum and AutoFill. Save your work.

Section II: Absolute Referencing

What is Relative Referencing?

Relative referencing is the default way that Excel uses to copy formulas. A relative reference tells Excel which cell to use based on its location relative to the cell containing the formula. When you copy or move a formula that contains a relative reference, Excel changes cell references so they refer to cells located in the same position relative to the cell that contains the new copy of the formula. All references in formulas are relative references unless you specify otherwise. Usually you want to use relative referencing because you can then easily copy and move formulas to different worksheet cells.

What is Absolute Referencing?

Sometimes you need to create formulas that refer to a cell in a fixed location on the worksheet, or in the workbook. A reference that always points to the same cell is called an Absolute Reference. An absolute reference most often is a value that is used in a number of calculations. The value is typically one that changes from time to time. Having all of the formulas referring to one cell for the value means that when the value changes, it need only be changed in the one location to have an impact on all the formulas in the worksheet that contain the absolute reference.

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

In your practice copy of the Intermediate Excel Exercise Files workbook, complete the Selling Price Calculation spreadsheet by creating the formulas to calculate both the Markup and the Selling Price for the Swab the Decks Clothing Company. Use Absolute Referencing and AutoFill. Save your work.

Naming a Cell

The ultimate means of creating an absolute reference is to name the cell containing the constant. This method produces an absolute reference that works across the whole workbook. There can only be one cell in the workbook with any given name, which by definition is an absolute reference for the workbook.

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

Select the Naming tab and complete the spreadsheet by naming the appropriate cells to create absolute references for the workbook. Use the cell names in the formulas. Change the GST to 6.5% and observe the changes to your spreadsheet. Change the PST to 8% and observe the changes to your spreadsheet. Save your work.

We will be using these techniques in the following section.

Section III: Creating Workbooks Summary Sheets

A worksheet cell can only contain values, labels that describe the values, formulas that perform calculations and dates. When you build a worksheet, you usually enter the labels first. What you enter next depends on how you intend to use the worksheet. If you intend to use the worksheet as a template, enter the formulas and then enter values that test the formulas. If you are not creating a template, you generally enter values before you enter formulas.

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


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.


Gather the data and plan how the information must be presented to ensure the required output, and design any required reports.


Build the raw spreadsheet by placing row and column headings, and then build the formulas. Format spreadsheet as necessary.


Test the formulas using the value “1”. Most calculations can be estimated accurately when the value “1” is the only value being used.


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.


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

Formulas tell Excel what to calculate. To enter a formula into a cell first select the cell where you want the answer to show up and then type an equals sign (=) to start the formula. An equals sign tells Excel that the numbers and symbols that follow constitute a calculation, not data. Formulas can contain cell references (A1 or G14), operators (+, - , *, /), and numbers.

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:

Brackets [ ( ) ]
Exponents ^
Division /
Multiplication *
Addition +
Subtraction -

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

Comparisons =
< >

> <

For example, the result of the formula 3+4*5 is 23 because Excel completes multiplication before addition. However, the results of (3+4)*5 is 35 because Excel calculates the operation in parentheses first it calculates the multiplication.

Creating formulas in a Summary Sheet

The process of creating a summary sheet is no different from creating linking formulas in any other spreadsheet: you start by selecting the cell where you want the result to display on the destination worksheet, start equation with an equals (=) sign, and then build the formula indicating which cells in the workbook contain the values to be included in the calculation. When the formula is complete, press either Enter or Tab, or click the Enter tick on the Formula Edit Bar. This kind of formula is frequently called a 3-D formula.

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

Using the 3-D formula structure and the information found on the four spreadsheets marked Quarter 1, Quarter 2, Quarter 3, and Quarter 4, to create and complete a Summary Sheet tab for the summary information for the annual sales figures. Place the new sheet immediately before the Quarter 1 sheet.

Protecting Your Work

One of the most heartbreaking things that can happen when working with a complex spreadsheet is to realize that you have accidentally overwritten a formula that you spent hours working out nanoseconds after executing the all-important Ctrl S command. Its particularly upsetting when it’s the only formula of its kind on the spreadsheet so you can’t just copy it over from a different location. All that work lost!

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

If you want to password protect the sheet, enter the password in the Password text box. With password protection, you can make changes to the protected cells only if the worksheet password is entered and the cells are then unprotected. A good failsafe, particularly if others will be accessing the spreadsheet for data entry. It will also prevent them from helping out by “fixing things” for you.

      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

On the Summary Sheet tab, protect the cells containing the 3-D formulas.

Section IV: Using Functions

Functions are built-in formulas that allow you to perform complex calculations by simply filling in information in a predefined format on your spreadsheet. Excel contains quite a few of them.

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:

=Function name(argument,argument,…)

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.Select the cell where you want the answer to show up.

2. Click the Paste Function button [fx] on the Standard Toolbar. This will launch the Paste Function dialog box.

3. Select a category of functions in the Function Category list.

4. Scroll if necessary to find the function you wish to use. Select the function. Read the description at the bottom of the dialog box ensure that this is the function you want to use.

5. Click OK to launch the Formula Palette. This is the dialog box used to identify the cells containing the argument information for the function.

NOTE: If the argument requires cell references, click the Collapse Dialog button (red arrow that points to the north-west to the right of the argument box) while you select the cells. Alternatively, you can drag the Formula Palette out of your way to select cells.
Click into the top argument box. A description of the argument displays at the bottom of the dialog box. There is also a Help button available in the bottom left corner of the dialog box. Enter the required information.

7. Repeat until all the arguments are completed. Choose OK.

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

We will be using the function called Future Value (FV). This function returns the future value of an investment based on periodic, constant payments and a constant interest rate.

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

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the PV argument.

PV is the present value, or the lump-sum amount that a series of future payments is worth right now. This is an optional argument. If PV is not filled in, it is assumed to be 0 (zero).

Type is the number 0 or 1 and indicates when payments are due. This is an optional argument. If Type is not filled in it is assumed to be 0 and the payments are made at the end of the period. The number one indicates that payment is made at the beginning of the period.

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

Using the information on the RRSP tab, complete the calculations for your RRSP.

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

In this section, we will be using the PMT function to compare the cost of a mortgage based on terms offered by several lending institutions in town. The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

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

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

PV is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 or 1 and indicates when payments are due. This is an optional argument. If Type is not filled in, it is assumed to be 0 and the payment is made at the end of the period. The number one indicates that payment is made at the beginning of the period.

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

Using the information on the Mortgage Comparison tab, calculate the best mortgage to meet your needs. Use relative referencing to find the payments for each interest rate.

Which mortgage would best meet your needs?


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

Estimates the standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Nonnumeric values are ignored.

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

Calculate the standard deviation of the samples on the tab STDEV.

What is the average value?

Section V: Data Management in Excel

Excel has a number of tools that make it an outstanding flat-file database program. Officially, these types of files are called ‘lists’. However, if your ‘list’ grows to more than 2,000 records, it would probably be a good idea to create a relational database in a program such as Access to store the data more efficiently. There is a scale of usability involved: the more information that is being stored, the more likely it will be that you will need to extract information in a format not native to Excel.

Structuring a Database

The structure of your database is extremely important. The structure ultimately dictates what information you will be able to get out of the database, and how effectively you can manage the data. Getting it right the first time is the most time efficient use of your time, so plan the database before you start. Time spent on the front end will save exponential amounts of time on the backend.

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

A database table in any program consists of fields and records. If you think of a spreadsheet, the fields are the columns. Fields contain the same kind of information in each cell, vertically.

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

There is a form available for data entry and for isolating records when you are looking for specific information. The form is available by choosing DataForm…. The form is automatically ready to use.

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 following information, add three records to the database on the Databases tab using the form.

Last Name Stodart Farmer Richards

First Name Mary Eliza Tomas

Department Management Computing Computing

Rank Professor Instructor Instructor

Start Date 12-May-89 27-Aug-01 21-Oct-99

Salary $57,500 $28,000 $34,000

Extension 1643 2013 1742

Parking S142 S116 W251

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

Custom Views

Sometimes you find that although you are storing a quantity of information about each record, in truth, most of the time you only need to see part of it. Excel allows you to create and store custom views that you can invoke depending on the work you are doing with the database that day.

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

Then choose ViewCustom 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

Create a view that hides the Start Date and Salary columns. Create a Custom View called “My View”.

Conditional Formatting

Conditional formatting allows you to use the program to format in a defined way information in your database that meets criteria that you set. You can also use conditional formatting to create interactive 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

On the Databases tab, select the range of cells that include all the salaries. Choose FormatConditional Formatting.

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?


Excel gives you the ability to sort your database in a number of different ways as well as the ability to create custom sorts. To sort a list or database, select a cell in the column you wish to sort by and then click the appropriate sort icon (ascending or descending) of the Standard Toolbar, or for a multiple sort, select a cell in the range and choose DataSort to open the Sort dialog box and specify your sort preferences.

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:



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

Data Management Exercise 5D

On the Databases tab, sort the list by Department, then by Rank, and finally by Last Name.

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

To create a Custom Sort, choose ToolOptionsCustom Lists and create a custom list based on the criteria you wish to use for your sort. In the Sort dialog box, click the Options command button and select your Custom List from the First Key Sort Order drop-down list.


As your database grows, it will become increasingly more difficult to find the records, or a sub-set of records you wish to work with. Using the Filtering capacity of the program allows you to select and work with only the information you need to see. This does not change the list in any way as Sorting does, you simply ask the program to display only the records that meet criteria that you specify, and hide the rest. There are two kinds of filters available in the program: the AutoFilter and the Advanced Filter. Both are available from the Tools menu.

Using the AutoFilter

To use the AutoFilter, select a cell in the range (list) and then choose ToolsAutoFilter. The program will set the AutoFilter drop-down arrows on the first row in your list. This row will not be included in any filter, so it is important that this row is your field header row.

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

On the Databases tab, create an AutoFilter in the list and filter for Associate Professors in the Accounting Department. Which record(s) represents the return for this filter?

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

You can also create a Custom AutoFilter in Excel. From the drop-down filter lists, instead of selecting a criterion, select Custom…. In the Custom AutoFilter dialog box set up the criterion you wish to use and select OK. Be careful if you use more than one criterion in this dialog box. “And” means that both conditions must be met. “Or” means that either one or the other of the conditions must be met.

Data Management Exercise 5F

Create a Custom AutoFilter that selects the record(s) that do not equal Assistant Professor and which have a salary of either $33,000 or $29,500. Which record(s) meet these criteria?

Excel Glossary


Error message telling you that the formula is dividing by zero.


Error message telling you that there is text or a blank cell in a cell reference in a formula.

Absolute Reference

A reference to a specific cell in a worksheet that contains a value that you want formulas to always point to. An absolute reference is indicated by using $ signs in the cell name: $F$1.

Active Cell

The cell with the dark border that indicates where typed data will be entered.


The positioning of text of values in a cell.


The values a function uses to perform operations or calculations. The type of argument used is specific to that function.


Visual cues to the relationships between the cells that provide values to the formulas or the cells that depend on the formulas; If your formula displays an error value such as #VALUE! or #DIV/0!, the auditing commands can locate the cell that is causing the error.


A program feature on the status bar that gives you the results of basic calculations on selected cells.


A program feature that memorizes text entries in a column and automatically finishes new entries in that column that begin with the same letters when you use the Enter key.


A program feature that automatically corrects common typographical errors.


A program feature that allows you to copy a list into a range of cells. Some lists come with the program such as the days of the week and the months of the year, but custom lists can also be created. The AutoFill tool can also be used to create a number series.


A program feature that allows you to quickly impose a professional looking, preset format to spreadsheet or range of cells.


A function that automatically adds up cells above or to the left of the active cell. It is activated by clicking the  button on the Standard Toolbar.


The references against which values are plotted in a graph.

Axis Labels

Text that indicates what the data graphed to an axis represents.


The rectangular space at the intersection of a column and a row. It is the container for storing data in a worksheet.

Cell Reference

The combination of the column letter and the row number that is the name of a cell: F7. It is also called the Cell Address.


A graphical representation of worksheet data.

Chart Area

The whole chart or graph including all of its elements.

Chart Wizard

A program feature that assists the user in creating graphs of the values in the selected cell ranges.

Circular Reference

A formula that contains a cell reference that refers back on itself.


A vertical range of cells.

Column Header

The letter at the top of each column that is the name of that column. The row containing the column header is called the Column Header Row.


A program feature that allows you to attach a comment or additional information to a cell. A red triangle in the upper right corner of the cell indicates that a comment is attached.

Comparison Operators

Symbols that are used to compare the relationship between two values: < less than, > greater than, = equals to

Conditional Formatting

A program feature that allows you to program a cell to display its information in a specified format under specific conditions.


The information stored in the cells of a worksheet. Data can be a value, a formula, or text.

Data Labels

Labels attached to the value points on a graph to specify what the value is or represents.

Data Map

Data graphed onto a map.

Data Series

The range of values used to create a graph or chart.


Cells containing values or formulas that refer to another cell or range of cells.

Destination Worksheet

The worksheet containing the cell(s) into which formulas or values copied from another worksheet are pasted.

Dynamic Link

A link between worksheets, cells, and/or charts that causes a dependent to reflect a change in the origin worksheet or cell(s).

Error Value

The display of an error message in a cell that indicates a problem with the formula.


A series of values, cell references, and operators that is used to perform a mathematical function. Formulas must always begin with an = to tell the program that it is to perform a mathematical calculation.

Formula AutoCorrect

A program feature that offers suggested corrections to detected formula mistakes

Formula Palette

A program feature used to select and used built-in functions.

Freezing Panes

A program feature that allows you to keep specified columns and/or rows (usually containing labels) fixed while you scroll through the worksheet.


Built-in formulas used as an aid in helping to help build complex or simple formulas and calculations.

Function Name

A group of letters that tells the program which function (usually mathematical) you wish it to perform on the named values.


An element of a chart that uses colours to indicate what an element of graphed values represents.

Logical Function

A function used to evaluate a condition and return one of two possible values.

Mathematical Operator

The mathematical symbols used in a formula to indicate how the values are to be used to calculate the result. Using the keyboard the symbols are + (add), - (subtract). * (multiply), and / (divide).

Mixed Reference

A cell or range reference containing both relative and absolute references

Nested Functions

The functions created when one function uses other functions as arguments.


A subdivision of a file window that allows you to look at more than one area of a worksheet at the same time.

Paste Function

A program feature that aids you in using the programs built-in functions.

Plot Area

The part of the chart where the values are graphed.


The values in the cells referred to in a formula.

Print Titles

Rows or columns that are set to print on each page of a worksheet.


An area of a worksheet consisting of two or more cells and identified by the top left cell name and the bottom right separated by a colon: A1:C4 This range identification is referred to as the Range Reference or Range Name.

Range Finder

A program feature used to locate data in formulas and charts by colour-coding the data in the formula or chart and applying similar colour-coding to the data in the worksheet.

Relative Reference

A cell reference that changes when copied to reflect its new position relative to the cells around it and its originating position.

Roll-up/Collapse Dialog button

A button used to 'roll-up' the dialog box to little more than the title bar in order that you can select cells and cell ranges behind it. The text box attached to a roll-up will display the name(s) of the cell ranges selected.


A horizontal range of cells.

Row Header

The number in to the left of each row in a spreadsheet that is the name of that row. The column containing the row heading in called the Row Header Column.

Serial Format

The number that represents the number of days since the 1st of January 1900. This format allows Excel to perform calculations with dates.

Sheet Tabs

The tabs at the bottom of the screen that let you move from one worksheet to another in a workbook.

Source Worksheet

The worksheet containing the value(s) that are pasted into the destination worksheet.

Split Boxes

Thick areas located just above the vertical scroll bar and just to the right of the horizontal scroll bar that allow you to separate the file window in up to four panes.


The rules that must be followed when entering the arguments of a formula so that the program understands what calculations you want it to perform on the values.

Text Data

Alphanumeric data that cannot be used in calculations.

Unattached Text

Chart text such as the name of the chart that isn't attached to values and other chart elements.


Data that can be used in a calculation.


The name for an Excel file intended to emulate a paper ledger.


A sheet in a workbook.


The virtual area created when you save a combination of files using the Save as Workspace command in the File menu that allows you to open up a group of workbooks together with one command.

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur © 2016
rəhbərliyinə müraciət