Microsoft Excel Test

fill handle

a small black square at the bottom right corner of the cell

Range name

a word or string of characters that represents one or more cells

Sum function

calculates the total of values contained in two or more cells

nonadjacent range

contains multiple ranges of cells

If function

evaluates a condition and returns one value if the condition is true and a different value if the condition is false

Median function

identifies the midpoint value in a set of values

fill color

the background color appearing behind data in a cell

row height

the vertical measurement of a row

Qualifications for a range

-1-255 characters -begin with a letter or underscore -can contain letters, digits, periods, or underscores -no blanks or other punctuation are allowed -range name must be unique (only used once)

How to create a formula?

1. Click cell where you want to make formula 2. Type an equal sign (=) to start the formula 3. Click the cell and select the cell range you want in the formula 4. Type the mathematical operators and finish the formula 5. Press enter to complete the formula

Steps to design a workbook?

1. State the purpose of the worksheet 2. Decide what input values are needed 3. Decide what outputs are needed to achieve the purpose of the workbook 4. Assign the worksheet inputs and results into columns and rows, and consider labeling. 5. Enter the labels, values, and formulas in Excel 6. Format the numerical values in the worksheet 7. Format the descriptive titles and labels attractively but so as not to distract your audience from the purpose of the worksheet. 8. Document the worksheet as thoroughly as possible 9. Save the completed worksheet

formula

a combination of cell references, operators, values, and/or functions used to perform a calculation

spreadsheet program

a computer application used to create and modify spreadsheets

workbook

a file containing related worksheets

nested function

a function that contains another function embedded inside one or more of its arguments

border

a line that surrounds a cells or a range

value

a number that represents a quantity or an amount

function

a predefined formula that performs a calculation

table array

a range containing a lookup table

output area

a range of cells containing results based on manipulating the variables

input area

a range of cells containing values for variables used in formulas

lookup table

a range that contains data for the basis of the lookup and data to be retrieved

range

a rectangular group of cells

lookup value

a reference to a cell containing a value to look up

syntax

a set of rules that govern the structure and components for properly entering a function

function Screentip

a small pop up description that displays the arguments for a function as you enter it

worksheet

a spreadsheet that contains formulas, functions, values, text, and visual aids

spreadsheet

an electronic file that contains a grid of columns and rows containing related data

logical test

an expression that evaluates to true or false

argument

an input, such as a cell reference or value, needed to complete a function

Average function

calculates the arithmetic mean, or average, of values in a range

PMT function

calculates the periodic payment for a loan with a fixed interest rate and fixed term

mixed cell reference

contains both an absolute and a relative cell reference in the formula; the absolute part does not change but the relative part does when you copy the formula

Order of Precedence/ Order of Operations

controls the sequence in which Excel performs arithmetic operations (PEMDAS: Parenthesis, Exponents, Multiplication, Division, Addition, Substraction

Formula Autocomplete

displays a list of functions and defined names as you enter a function

formula bar

displays the content (text, value, date, or formula) in the active cell

Today function

displays the current date

Now function

displays the current date and time

Min function

displays the lowest value in a range

sheet tab

displays the name of a worksheet within a workbook

wrap text

enables a label to appear on multiple lines within the current cell

Ctrl + F

enables a search box to find cell address

Autofill

enables you to copy the contents of a cell or cell range or to continue a sequence by dragging the fill handle over an adjacent cell or range of cells.

cell address

identifies a cell by a column letter and a row number

Name Box

identifies the address of the current cell

Max function

identifies the highest value in a range

text

includes letters, numbers, symbols, and spaces

absolute cell reference

indicated the cells specific location; the cell reference does not change when you copy the formula

relative cell reference

indicates the cells relative location from the cell containing the formula; the cell reference changes when the formula is copied

Hlookup function

looks up a value in a horizontal lookup table where the first row contains the values to compare with the lookup value

Vlookup function

looks up a value in a vertical lookup table and returns the related result from the lookup table

circular reference

occurs when a formula directly or indirectly refers to itself

Horizontal Alignment

positions data between the left and right cell margins

vertical alignment

positions data between the top and bottom cell margins

pv

present value of the loan

semi-selection (or pointing)

process of using the mouse pointer to select cells while building a formula

Ctrl + ` (accent)

shows all formulas in spreadsheet

Countblank function

tallies the number of blank cells in a range

Counta function

tallies the number of cells in a range that are not empty

Count function

tallies the number of cells in a range that contains values

column index number

the argument in a Vlookup function that identifies which lookup table column from which to return a value

active cell

the current cell, indicated by a dark border

Column Width

the horizontal measurement of a column

cell

the intersection of the column and rows

breakpoint

the lowest value for a specific category or series in a lookup table

nper

the number of total payment periods

rate

the periodic interest rate, such as monthly interest rate