Excel Chapters 5-7

Cycle

A category of SmartArt graphics that illustrates a continual process.

Picture

A category of SmartArt graphics that is used to display pictures in a diagram.

Relationship

A category of SmartArt graphics that is used to illustrate connections.

Process

A category of SmartArt graphics that is used to show steps in a process or timeline.

Pyramid

A category of SmartArt graphics that uses a series of pictures to show relationships.

Hierarchy

A category of SmartArt graphics used to create an organization chart or show a decision tree.

Matrix

A category of SmartArt graphics used to show how parts relate to a whole.

List

A category of SmartArt graphics used to show nonsequential information.

Wildcard

A character, for example the asterisk or question mark, used to search a field when you are uncertain of the exact value or when you want to widen the search to include more records.

Legend

A chart element that identifies the patterns or colors that are assigned to the categories in the chart.

Column chart

A chart in which the data is arranged in vertical columns and that is useful for showing data changes over a period of time or for illustrating comparisons among items.

Embedded chart

A chart that is inserted into the same worksheet that contains the data used to create the chart.

Line chart

A chart type that is useful to display trends over time; time displays along the bottom axis and the data point values are connected with a line.

Data marker

A column, bar, area, dot, pie slice, or other symbol in a chart that represents a single data point; related data points form a data series.

Error Checking command

A command that checks for common errors that occur in formulas.

Trace Dependents command

A command that displays arrows that indicate what cells are affected by the value of the currently selected cell.

Trace Precedents command

A command that displays arrows to indicate what cells affect the value of the cell that is selected.

Freeze Panes

A command that enables you to select one or more rows or columns and freeze (lock) them into place so that they remain on the screen while you scroll; the locked rows and columns become separate panes.

Find

A command that locates and selects specific text or formatting.

Go To

A command that moves to a specific cell or range of cells that you specify.

Go To Special

A command that moves to cells that have special characteristics, for example, to cells that are blank or to cells that contain constants, as opposed to formulas.

One-variable Datatable

A data table that changes the value in only one cell.

Two-variable Datatable

A data table that changes the values in two cells.

Table array

A defined range of cells, arranged in a column or a row, used in a VLOOKUP or HLOOKUP function.

Cell style

A defined set of formatting characteristics, such as font, font size, font color, cell borders, and cell shading.

Sort dialog box

A dialog box in which you can sort data based on several criteria at once, and that enables a sort by more than one column or row.

Auto Filter menu

A drop-down menu from which you can filter a column by a list of values, by a format, or by criteria.

Protection

A feature that prevents anyone from altering the formulas or changing other template components.

Orientation

A feature with which you can rotate text diagonally or vertically in a cell and is useful when you need to label a narrow column.

PDF(Portable Document Format)

A file format developed by Adobe Systems that creates a representation of electronic paper that displays your data on the screen a sit would look when printed, but that cannot be easily changed.

Tab delimited text file

A file type in which cells are separated by tabs; this type of file can be readily exchanged with various database programs.

CSV(comma separated values)file

A file type in which the cells in each row are separated by commas and an end-of-paragraph mark at the end of each row; also referred to as a comma delimited file.

Comma delimited file

A file type that saves the contents of the cells by placing commas between them and an end-of-paragraph mark at the end of each row; also referred to as a CSV (comma separated values) file.

Advanced filter

A filter that can specify three or more criteria for a particular column, apply complex criteria to two or more columns, or specify computed criteria.

Compound filter

A filter that uses more than one condition—and one that uses comparison operators.

Custom filter

A filter with which you can apply complex criteria to a single column.

Trendline

A graphic representation of trends in a data series, such as a line sloping upward to represent increased sales over a period of months.

Lookup functions

A group of Excel functions that look up a value in a defined range of cells located in another part of the workbook to find a corresponding value.

HTML(Hypertext Markup Language)

A language web browsers can interpret.

Axis

A line that serves as a frame of reference for measurement and that borders the chart plot area.

Validation list

A list of values that are acceptable for a group of cells; only values on the list are valid and any valuenoton the list is considered invalid.

Major unit value

A number that determines the spacing between tick marks and between the gridlines in the plot area.

Vertical Value axis (y-axis)

A numerical scale on the left side of a chart that shows the range of numbers for the data points; also referred to as the y-axis.

Pane

A portion of a worksheet window bounded by and separated from other portions by vertical and horizontal bars.

Filtering

A process in which only the rows that meet the criteria display; rows that do not meet the criteria are hidden.

Query

A process of restricting records through the use of criteria conditions that will display records that will answer a question about the data.

Datatable

A range of cells that shows how changing certain values in your formulas affects the results of those formulas and that makes it easy to calculate multiple versions in one operation.

List

A series of rows that contains related data that you can group by adding subtotals.

Custom list

A sort order that you can define.

Template

A special workbook that may include formatting, formulas, and other elements and is used as a pattern for creating other workbooks.

Field

A specific type of data such as name, employee number, or social security number that is stored in columns.

Data validation

A technique by which you can control the type of data or the values that are entered into a cell by limiting the acceptable values to a defined list.

Major sort

A term sometimes used to refer to the first sort level in the Sort dialog box.

Trace Error command

A tool that helps locate and resolve an error by tracing the selected error value.

Organization chart

A type of graphic that is useful to depict reporting relationships within an organization.

Data point

A value that originates in a worksheet cell and that is represented in a chart by a data marker.

Smart Art graphic

A visual representation of information and ideas.

Watch Window

A window that displays the results of specified cells.

Text Pane

A window that displays to the left of a SmartArt graphic, is populated with placeholder text, and is used to build a graphic by entering and editing text.

Name

A word or string of characters in Excel that represents a cell, a range of cells, a formula, or a constant value; also referred to as a defined name.

Defined name

A word or string of characters in Excel that represents a cell, a range of cells, a formula, or a constant value; also referred to as simply a name.

Chart sheet

A workbook sheet that contains only a chart.

Standardization

All forms created within the organization will have a uniform appearance; the data will always be organized in the same manner.

Record

All the categories of data pertaining to one person, place, thing, event, or idea.

Recommended Charts

An Excel feature that helps you choose a chart type by previewing suggested charts based on patterns in your data.

PMT function

An Excel function that calculates the payment for a loan based onconstant payments and a constant interest rate.

INDEX function

An Excel function that finds a value within a table or range based on a relative row and column; commonly combined with the INDEX function to find the row number and/or column number, and then use INDEX to display the value you are looking for.

MATCH function

An Excel function that finds the position of an item in a range of cells instead of the item itself; by providing search parameters, the MATCH function indicates where in the list you can find what you are looking for.

HLOOKUP

An Excel function that looks up values that are displayed horizontally in a row.

VLOOKUP

An Excel function that looks up values that are displayed vertically in a column.

Criteria range

An area on your worksheet where you define the criteria for the filter and that indicates how the displayed records are filtered.

Formula

An equation that performs mathematical calculations on values in a worksheet.

Tracer arrow

An indicator that shows the relationship between the active cell and its related cell.

Type argument

An optional argument in the PMT function that assumes that the payment will be made at the end of each time period.

Password

An optional element of a template added to prevent someone from disabling a worksheet's protection.

Database

An organized collection of facts related to a specific topic.

Unlocked [cells]

Cells in a protected worksheet that may be filled in.

Precedent cells

Cells that are referred to by a formula in another cell.

Dependent cells

Cells that contain formulas that refer to other cells.

Labels

Column and row headings that describe the values and help the reader understand the chart.

Criteria

Conditions that you specify in a logical function or filter.

Tickmark labels

Identifying information for a tick mark generated from the cells on the worksheet used to create the chart.

Locked[cells]

In a protected worksheet, data cannot be inserted, modified, deleted, or formatted in these cells.

Rate

In the Excel PMT function, the term used to indicate the interest rate for a loan.

Data labels

Labels that display the value, percentage, and/or category of each particular data point and can contain one or more of the choices listed—Series name, Category name, Value, or Percentage.

Gridlines

Lines in the plot area that aid the eye in determining the plotted values.

Chart elements

Objects that make up a chart.

GoalSeek

One of Excel's What-If Analysis tools that provides a method to find a specific value for a cell by adjusting the value of oneother cell—find the right input when you know the result you want.

Financial functions

Prebuilt formulas that perform common business calculations such as calculating a loan payment on a vehicle or calculating how much to save each month to buy something; financial functions commonly involve a period of time such as months or years.

Data series

Related data points represented by data markers; each data series has a unique color or pattern represented in the chart legend.

Comparison operators

Symbols that evaluate each value to determine if it is the same (=) greater than (>) less than (<) or in between a range of values as specified by the criteria.

Hyperlink

Text or graphics that, when clicked, take you to another location in the worksheet, to another file, or to a webpage on the internet or on your organization's intranet.

Nper

The abbreviation for number of time periods in various Excel functions.

Interest

The amount charged for the use of borrowed money.

Horizontal Category axis (x-axis)

The area along the bottom of a chart that identifies the categories of data; also referred to as the x-axis.

Plot area

The area bounded by the axes of a chart, including all the data series.

Active area

The area of the worksheet that contains data or has contained data.

Walls and floor

The areas surrounding a 3-D chart that give dimension and boundaries to the chart.

Split

The command that enables you to view separate parts of the same worksheet on your screen; it splits the window into multiple resizable panes to allow you to view distant parts of the worksheet at one time.

Arrange All

The command that tiles all open program windows on the screen.

Subtotal command

The command that totals several rows of related data together by automatically inserting subtotals and totals for the selected cells.

And comparison operator

The comparison operator that requires each and every one of the comparison criteria to be true.

Or comparison operator

The comparison operator that requires only one of the two comparison criteria that you specify to be true.

.xlsx file name extension

The default file format used by Excel to save an Excel workbook.

Chart area

The entire chart and all of its elements.

Scaling

The group of commands by which you can reduce the horizontal and vertical size of the printed data by a percentage or by the number of pages that you specify.

Category labels

The labels that display along the bottom of a chart to identify the categories of data.

Extract area

The location to which you copy records when extracting filtered rows.

Scope

The location within which a defined name is recognized without qualification—usually either to a specific worksheet or to the entire workbook.

Sort

The process of arranging data in a specific order.

What-If Analysis

The process of changing the values in cells to see how those changes affect the outcome of formulas in a worksheet.

Auditing

The process of examining a worksheet for errors in formulas.

Extract

The process of pulling out multiple sets of data for comparison purposes.

Scale

The range of numbers in the data series that controls the minimum, maximum, and incremental values on the value axis.

Error value

The result of a formula that Excel cannot evaluate correctly.

Tickmarks

The short lines that display on an axis at regular intervals.

Detail data

The subtotaled rows that are totaled and summarized; typically adjacent to and either above or to the left of the summary data.

Ascending

The term that refers to the arrangement of text that is sorted alphabetically from A to Z, numbers sorted from lowest to highest, or dates and times sorted from earliest to latest.

Descending

The term that refers to the arrangement of text that is sorted alphabetically from Z to A, numbers sorted from highest to lowest, or dates and times sorted from latest to earliest.

Principal

The total amount that a series of future payments is worth now; also known as thePresentvalue(Pv).

Present value (Pv)

The total amount that a series of future payments is worth now; also known as theprincipal.

Compound criteria

The use of two or more criteria on the same row—all conditions must be met for the records to be included in the results.

Future value (Fv)

The value at the end of the time period in an Excel function; the cash balance you want to attain after the last payment is made—usually zero for loans.

Arguments

The values that an Excel function uses to perform calculations or operations.

Spark lines

Tiny charts that fit within a cell and give a visual trend summary alongside data.

Formula Auditing

Tools and commands accessible from the Formulas tab that help you check your worksheet for errors.