Computer Data Analysis



Computer Data Analysis Instructor: Greg Shaw

CGS 2518

Microsoft Excel – Tutorial 7, Session 7.1

Working with Excel’s Editing and Web Tools

Skills Checklist and Notes

❑ Splitting a Worksheet into Panes

• In Session 5.1 we saw how the Freeze Panes command in the Window menu is used to “freeze” column and row titles so that they remain visible no matter how far we scroll the worksheet

• We can also divide or “split” the worksheet into two horizontal and/or two vertical panes, each with its own scroll bar, so that we can view two (or four) different areas of the sheet at the same time

• To create two horizontal panes

1. Position the mouse pointer over the split box at the top of the vertical scroll bar so that it changes into a two-headed arrow

2. Drag the pointer down to the row where you want to “split” the sheet (notice the thick gray bar across the sheet as you drag)

3. When you release the mouse button, a solid split bar will replace the gray bar and the window will be split into two scrollable panes at that point

• Creating two vertical panes is similar, but you begin by placing the pointer over the split box to the right of the horizontal scroll bar

• To remove a split, double-click the split bar (or choose Remove Split from the Window menu)

❑ Two Kinds of Spreadsheet Errors

1. If Excel cannot properly evaluate a formula or function (i.e. do the calculations), an error value will be displayed in the cell. Error values consist of the pound sign (#) followed by the error name. E.g., #DIV/0! indicates a division by zero. (See the table of error values on page EX 308)

2. The other kind of error is much more subtle in that an actual value (not an error value) does appear in the cell containing the formula, but that value is not correct!

There are many reasons why this might occur. For example, you reference the wrong cell or range in a formula or function, or you erroneously add where you should subtract in a formula.

❑ Formula Auditing – Precedents and Dependents

• Formula auditing can help us analyze and understand a complex worksheet, and find the source of errors that may occur

• Precedents are cells that are referenced by a formula.

E.g., in my grade book, each student’s “raw” course average (i.e. before curving) is computed as the average of two cells – the one containing the test average and the one containing the assignment average. So those two cells are precedents of the cell that computes the course average. The precedent cells may also have precedents themselves. E.g., the cell that computes the test average has 3 precedents – the 3 cells that contain the individual test scores.

• Dependents are the opposite of precedents. I.e. the dependent cell is the one that contains the formula

E.g., in the grade book, the cell that computes each student’s raw course average is dependent on the two cells that contain the test average and the assignment average

• Tracing the precedents of a cell

o Select the cell and then click the Trace Precedents button on the Formula Auditing toolbar

o Excel draws blue tracer arrows on the sheet indicating the precedents of the selected cell

o Tracer arrows will have a blue dot in each precedent cell and an arrowhead in the cell being traced

o If the precedent is a range, it will be enclosed in a blue rectangle (with the blue dot in one of the cells)

o Click the Trace Precedents button again to see the precedents of the precedent cells! These are known as the precedence levels

• Tracing the dependents of a cell

o Select the cell and click the Trace Dependents button on the Formula Auditing toolbar

o When tracing dependents, the tracer arrows will have a blue dot in the cell being traced and an arrowhead in the dependent cell(s)

• To remove tracer arrows, select the cell whose precedents/dependents are being traced, and click the Remove Precedent Arrows or Remove Dependent Arrows button

When multiple levels of precedents are displayed, clicking the Remove Precedent Arrows button will remove one level at a time

• The Remove All Arrows button will remove all levels of precedent arrows and all dependent arrows all at once

• If a cell has dependents or precedents in different worksheets or workbooks, Excel will draw a dashed arrow pointing to a spreadsheet icon. Double-click the arrow to open the Go To dialog box to display the worksheet

← For a summary of the buttons on the Formula Auditing toolbar, see page EX 304

( Error Values (See table on page EX 308)

• If Excel cannot properly evaluate a formula or function, an error value (e.g., #REF!) will be displayed in the cell and a green triangle will appear in the upper-left-hand corner

• When you select a cell containing an error value, an error indicator icon appears to its left

o Point to the icon and a Screen Tip explaining the error will appear

o Click the error indicator button for a list of options, including detailed Help on the error

( Tracing Errors

1. Select a cell containing an error value

2. Click the Trace Error button [!] on the Formula Auditing toolbar

3. Red tracer arrows will appear

4. Follow the tracer arrows through the different levels of precedent cells all the way back to the source of the error

5. Fix the error

( Locating Suspect Formulas (The Error Checking Button)

A “suspect” formula is one that does display a result (not an error value), but nevertheless Excel thinks may be incorrect. For example, a SUM or AVERAGE function omits adjacent cells that Excel thinks maybe should have been included.

1. Click the Error Checking button on the Formula Auditing toolbar

2. Excel locates the next cell containing an error value, and opens the Error Checking dialog box

3. Click a button to get help on the error, trace it, edit the formula that caused it, ignore it, or have Excel update the formula automatically

4. Click Next to move to the next suspect formula

( Using the Watch Window

• The Watch Window lets us monitor the new values stored in dependent cells whenever changes are made to any of their precedents

• The advantage is that we don’t have to navigate to a different part of the worksheet or to a different worksheet to see the changes

• To open the window, click the Watch Window button on the Formula Auditing toolbar

• The Watch Window displays the location, name, current value, and formula for every cell on the watch list

• To add cells to the watch list, click Add Watch... and select the cells

• To remove cells from the watch list, select them from the list and click Delete Watch

( Evaluating a Complex Formula

This feature allows us to see the different parts of a formula evaluated one at a time. This is useful for complex formulas such as “nested” IF functions

1. Select the cell containing the formula

2. Click the Evaluate Formula button on the Formula Auditing toolbar (the first reference in the formula will be underlined)

3. Click the Evaluate button to evaluate the underlined reference (see its value) and move to the next one

4. When done, click Restart or Close

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download