Excel Module 2 for Intro to Computers

Entering functions using one of five methods

(1)keyboard, touch gesture, or pointer; (2)the Insert Function button in the formula bar; (3)the Sum menu; (4)the Sum button (Formulas tab | Function Library group); and (5)the Name box area in the formula bar.

Automatic Recalculation

Every time you enter a value into a cell in the worksheet, Excel automatically recalculates all formulas. You can change to manual recalculation by clicking the Calculation Options button (Formulas tab | Calculation group) and then clicking Manual. In manual calculation mode, pressing the F9 key instructs Excel to recalculate all formulas

When you enter a two-digit yr value that's less than 30

Excel changes that value to 20xx

Spelling checker

Excel command that checks for spelling errors by comparing words on the worksheet against words contained in its standard dictionary.

Best fit

Excel feature that automatically increases or decreases the width of the column so that the widest entry will fit in the column

Range finder

Excel feature that checks which cells are referenced in the formula assigned to the active cell

Flash fill

Excel feature that looks for patterns in the data and automatically fills or formats data in remaining cells

MAX function

Excel function that displays the highest value in a range

AVERAGE function

Excel function that sums the numbers in a specified range and then divides the sum by the number of cells with numeric values in the range to determine the average.

MIN function

Excel function used to determine the lowest (minimum) number in a range

Point mode

Excel mode that allows you to select cells for use in a formula by using the pointer

Statistical functions

Excel usually considers a blank cell to be equal to 0. The statistical functions, however, ignore blank cells. Excel thus calculates the average of three cells with values of 10, blank, and 8 to be 9 [(10 + 8) / 2] and not 6 [(10 + 0 + 8) / 3]

Indirect circular reference

Occurs when a formula in a cell refers to another cell or cells that include a formula that refers back to the original cell

Direct circular reference

Occurs when a formula refers to the same cell in which it is entered.

What Excel displays

Option buttons in a worksheet to indicate that you can complete an operation using automatic features such as AutoCorrect, Auto Fill, error checking, and others

Why is the paste options button important

Paste an exact copy of what you copied, including the cell contents and formatting. Copy only formulas. Copy only formatting. Copy only values. Copy a combination of these options. Copy a picture of what you copied.

Margins

Portions of a printed page outside the main body of the printed document and always are blank when printed.

The purpose of a worksheet

Provide a user with direct ways to accomplish tasks

Circular reference

Reference that occurs when one of the defining values in a cell is itself

If Flash fill makes mistakes

Simply click the Undo button, enter a few more examples, and try again. In addition to formatting data, Flash Fill can perform tasks such as concatenating data from multiple cells and separating data from one cell into multiple cells.

Selecting a range

You can select a range using the keyboard. Press the F8 key and then use the arrow keys to select the desired range. After you are finished, make sure to press the F8 key to turn off the selection process or you will continue to select ranges

Default row height

15pts

default height of table row is

48

Theme

A collection of fonts, font styles, colors, and effects to give a worksheet a consistent appearance

Pixel

A dot on the screen that contains a color; short for picture element

Character

A letter, number, symbol, or punctuation mark

Hidden Rows and Columns

An alternative is to use the keyboard: select the columns to the right and left of the hidden columns and then press ctrl+shift+) (right parenthesis). To use the keyboard to hide a range of columns, press ctrl+0 (zero). You also can use the keyboard to unhide a range of rows by selecting the rows immediately above and below the hidden rows and then pressing ctrl+shift+ ( (left parenthesis). To use the keyboard to hide a range of rows, press ctrl+9.

Additional conditional formatting info

Automatically changes how a cell appears based on the value in the cell.

Consider the following when designing your worksheet

Avoid the temptation to use flashy or confusing visual elements within the worksheet. Understand the requirements document. Choose the proper functions and formulas. Build the worksheet.

Formula

Cell references, numbers, and arithmetic operators that instruct Excel to perform a calculation

When you enter a value that is 30 or greater

Excel changes the value to 19zz.

Error checking

Always take the time to check the formulas of a worksheet before submitting it to your supervisor. You can check formulas by clicking the Error Checking button (Formulas tab | Formula Auditing group). You also should test the formulas by employing data that tests the limits of formulas. Experienced spreadsheet specialists spend as much time testing a workbook as they do creating it, and they do so before placing the workbook into production.

Entering Numbers in a Range

An efficient way to enter data into a range of cells is to select a range and then enter the first number in the upper-left cell of the range. Excel responds by accepting the value and moving the active cell selection down one cell. When you enter the last value in the first column, Excel moves the active cell selection to the top of the next column

Troubling formulas

If Excel does not accept a formula, remove the equal sign from the left side and complete the entry as text. Later, after you have entered additional data in the cells reliant on the formula or determined the error, reinsert the equal sign to change the text back to a formula and edit the formula as needed

How to use Flash fill

Simply start entering formatted phone numbers in cells next to the unformatted numbers. After entering a few formatted phone numbers, Flash Fill will suggest similarly formatted phone numbers for the remaining cells in the column. If you do not want to wait for Excel to offer suggestions, type one or two examples and then click the Flash Fill button (Data tab | Data Tools group). Flash fill will autocomplete the remaining cells.

Destination range

Term used to refer to a range being used as a destination.

Source range

Term used to refer to a range being used as a source

Footer

Text and graphics that print at the bottom of each page.

Header

Text and graphics that print at the top of each page.

Order of operations

The collection of rules that define which mathematical operations take precedence over the others in expressions with multiple operations 1st Negation - % ^ * / + -

Debugging

The process of finding and correcting errors in a worksheet

Addional spelling checker considerations

To check the spelling of the text in a single cell, double-click the cell to make the formula bar active and then click the Spelling button (Review tab | Proofing group). If you select a single cell so that the formula bar is not active and then start the spelling checker, Excel checks the remainder of the worksheet, including notes and embedded charts. If you select a cell other than cell A1 before you start the spelling checker, Excel will display a dialog box when the spelling checker reaches the end of the worksheet, asking if you want to continue checking at the beginning. If you select a range of cells before starting the spelling checker, Excel checks the spelling of the words only in the selected range. To check the spelling of all the sheets in a workbook, right-click any sheet tab, click 'Select All Sheets' on the sheet tab shortcut menu, and then start the spelling checker. To add words to the dictionary, such as your last name, click the 'Add to Dictionary' button in the Spelling dialog box (shown in Figure 2-58) when Excel flags the word as not being in the dictionary. Click the AutoCorrect button (shown in Figure 2-58) to add the misspelled word and the correct version of the word to the AutoCorrect list. For example, suppose that you misspell the word, do, as the word, dox. When the spelling checker displays the Spelling dialog box with the correct word, do, in the Suggestions list, click the AutoCorrect button. Then, anytime in the future that you type the word, dox, Excel will change it to the word, do.

How to wrap text

To wrap text, right-click in the cell, click Format Cells on a shortcut menu, click the Alignment tab, and then click Wrap text.

Trace Error

When an error occurs in a formula, this occurs. This identifies the cell with the error by placing a green triangle in the upper left of the cell

Formatting Worksheets

With early worksheet programs, users often skipped rows to improve the appearance of the worksheet. With Excel it is not necessary to skip rows because you can increase row heights to add white space between information

Conditional formatting

You can assign any format to a cell, a range of cells, a worksheet, or an entire workbook conditionally. If the value of the cell changes and no longer meets the specified condition, Excel suppresses the conditional formatting