Excel Module 3 for Intro to Computers4

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

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


Ensembles d'études connexes

Consumer Behavior Chapter 3 Practice

View Set

Module 1 Professional identity & Ethics

View Set

Coursera Basics: How Does It Work?

View Set