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