Excel Module 3 for Intro to Computers4
Replacing a formula with a constant
1)click the cell with the formula; (2)press the f2 key or click in the formula bar; (3)press the f9 key to display the value in the formula bar; and (4)press the enter key.
Valid format symbols
$ , %
How should you format various elements of the worksheet
(1)format the numbers; (2)format the worksheet title, column titles, row titles, and total rows; and (3)format the assumptions table.
General form of the IF function
=IF(logical_test, value_if_true, value_if_false)
Relative cell reference
A cell reference that Excel adjusts as it is copied to the destination area
Absolute cell referencing
A cell reference that remains constant when copying a formula or function (that is, the cell references do not change relative to where you are copying the formula)
Mixed cell reference
A cell reference where one factor remains constant and the other one varies
Office clipboard
A temporary storage area in the computer's memory that allows you to collect text and graphics from any Office document and then paste them into almost any other type of document
Options one the Auto full options menu
Copy cells Fill series Fill formatting only Fill w/o formatting Fill months
#REF!
Excel message used to indicate a cell reference error
NOW function
Excel function that returns a number that corresponds to the system date and time beginning with December 31, 1899
Nested IF function
Function in which the action to be taken for the true or false case includes yet another IF function
IF function
Function that assigns a value to a cell based on a logical test
Goal seeking
Goal seeking assumes you can change the value of only one cell referenced directly or indirectly to reach a specific goal for a value in another cell.
Logical operators in IF functions
IF functions can use logical operators, such as AND, OR, and NOT. For example, the three IF functions =IF(AND(A1>C1, B1<C2), "OK", "Not OK") and =IF(OR(K5>J5, C3<K6), "OK", "Not OK") and =IF(NOT(B10<C10), "OK", "Not OK") use logical operators. In the first example, both logical tests must be true for the value_if_true OK to be assigned to the cell. In the second example, one or the other logical tests must be true for the value_if_ true OK to be assigned to the cell. In the third example, the logical test B10<C10 must be false for the value_if_true OK to be assigned to the cell
Fill handle
If you drag the fill handle up or to the left, Excel will decrement the series rather than increment the series. To copy a word, such as January or Monday, which Excel might interpret as the start of a series, hold down the CTRL key while you drag the fill handle to a destination area.If you drag the fill handle back into the middle of a cell, Excel erases the contents of the cell
Freezing titles
If you want to freeze only column headings, select the appropriate cell in column A before you click the Freeze Panes button (View tab | Window group). If you want to freeze only row titles, select the appropriate cell in row 1 before you click the Freeze Panes button. To freeze both column headings and row titles, select the cell that is the intersection of the column and row titles before you click the Freeze Panes button
Inserting multiple rows
If you want to insert multiple rows, you have two choices. You can insert a single row by using the Insert command on the shortcut menu and then repeatedly press the F4 key to continue inserting rows. Alternatively, you can select a number of existing rows equal to the number of rows that you want to insert. For instance, if you want to insert five rows, select five existing rows in the worksheet, right-click the selected rows, and then click Insert on the shortcut menu
Chart templates
Once you create and format a chart to your liking, consider saving the chart as a template so that you can use it to format additional charts. Save your chart as a chart template by accessing the chart shortcut menu and then selecting 'Save as Template' from that shortcut menu. The chart template will appear in the Templates folder for Charts. When you want to use the template, click the Templates folder in the All Charts sheet (Insert Chart dialog box) and then select your template
Selecting nonadjacent ranges
One of the more difficult tasks to learn is selecting nonadjacent ranges. To complete this task, do not hold down the CTRL key when you select the first range because Excel will consider the current active cell to be the first selection, and you may not want the current active cell in the selection. Once the first range is selected, hold down the CTRL key and drag through the nonadjacent ranges. If a desired range is not visible in the window, use the scroll arrows to view the range. You need not hold down the CTRL key while you scroll
Paste gallery commands
Paste Formulas Formulas & number formatting Keep source formatting No borders Keep source column widths Transpose Values Values & Number formatting Values & source formatting Formatting Paste link Picture Linked pic
Alternative paste
Pressing the enter button
Sparkline charts
Provides a simple way to show trends and variations in a range of data within a single cell. Excel includes three types of sparkline charts: line, column, and win/loss. Because sparkline charts appear in a single cell, you can use them to convey succinct, eye-catching summaries of the data they represent.
Category names
Ranges that identify the major groups for a chart.
What-if analysis
Scrutinizing the impact of changing values in cells that are referenced by formulas in other cells
Using IFERROR
Similar to the IF function, the IFERROR function checks a formula for correctness. For example, =IFERROR(formula, "Error Message") examines the formula argument. If an error appears (such as #N/A), Excel displays the Error Message text in the cell instead of the Excel #N/A error.
Format symbols
Symbol that assigns a format to numbers as they are entered
Insights
The Insights feature in Excel uses the Bing search engine and other Internet resources to help you locate more information about the content in your workbooks
Ranges and undo
The incorrect use of copying, deleting, inserting, and moving ranges of cells have the potential to render a worksheet useless. Carefully review the results of these actions before continuing on to the next task. If you are not sure the result of the action is correct, click the Undo button on the Quick Access Toolbar
Error messages
The more commonly occurring error messages are as follows: #DIV/0! (tries to divide by zero); #NAME? (uses a name Excel does not recognize); #N/A (refers to a value not available); #NULL! (specifies an invalid intersection of two areas); #NUM! (uses a number incorrectly); #REF (refers to a cell that is not valid); #VALUE! (uses an incorrect argument or operand); and ##### (refers to cells not wide enough to display entire entry).
Accessibility
The practice of removing barriers that may prevent individuals with disabilities from interacting with data or an app.
Data series
The range A9:G13 contains the data that determines the individual columns in each month cluster, along with the names that identify each column
Sketch of a worksheet
Titles, column and row headings, location of data values, calculations, and a rough idea of the desired formatting
What should you take into account when planning a worksheet layout
Using Excel, you can change text and number formatting in many ways, which affects the visual impact of the worksheet. Rotated text often provides a strong visual appeal. Rotated text also allows you to fit more text into a smaller column width. When laying out a worksheet, keep in mind the content you want to emphasize and the length of the cell titles relative to the numbers
Drag and drop
Using a mouse to move or copy cells
When would you not want to use the system date
Using the system date results in the date value being updated whenever the worksheet is opened. Think carefully about whether or not this is the result you want. If you want the date to reflect the current date, using the system date is appropriate. If you want to record when the worksheet was created, using a hard-coded date makes more sense. If both pieces of information may be important, consider two date entries in the worksheet: a fixed entry identifying the date the worksheet was created and the volatile system date
Assumptions
Values in cells that you can change to determine new values for formulas
Charts
When you change a value on which a chart is dependent, Excel immediately redraws the chart based on the new value. With bar charts, you can drag the bar in the chart in one direction or another to change the corresponding value in the worksheet
Cutting
When you cut a cell or range of cells using the Cut command on a shortcut menu or Cut button (Home tab | Clipboard group), Excel copies the cells to the Office Clipboard; it does not remove the cells from the source area until you paste the cells in the destination area by either clicking the Paste button (Home tab | Clipboard group) or pressing the ENTER key. When you complete the paste, Excel clears the cell's or range of cell's entries and their formats from the source area
Custom fill sequences
You can create your own custom fill sequences for use with the fill handle. For example, if you often type the same list of products or names in Excel, you can create a custom fill sequence. You then can type the first product or name and then use the fill handle automatically to fill in the remaining products or names. To create a custom fill sequence, display the Excel Options dialog box by clicking Options in the Backstage view. Click the Advanced tab (Excel Options dialog box) and then click the 'Edit Custom Lists' button in the General section (Excel Options dialog box).
Customizing sparkline charts
You can customize sparkline charts in a number of ways on the Sparkline Tools Design tab. In the Show group (Sparkline Tools Design tab), you can specify values to show as markers on the chart, such as the highest value, lowest value, any negative numbers, the first point, and the last point. You can change the color of the sparkline and markers in the Style group (Sparkline Tools Design tab).
Dragging ranges
You can move and insert a selected cell or range between existing cells by holding down the shift key while you drag the selection to the gridline where you want to insert the selected cell or range. You also can copy and insert by holding down the ctrl+shift keys while you drag the selection to the desired gridline
To use the check accessibility command
click File on the ribbon to open the Backstage view, click the Info tab, click the 'Check for Issues' button, and then click Check Accessibility.
Smart lookup
uses Bing and other Internet resources to find useful information about text in your spreadsheet and then displays that information in the Insights task pane