MIS Excel chapter 4

Freeze Panes

Keeps both rows and columns above and to the left of the active cell visible as you scroll through a worksheet

Freeze Top Row

Keeps both rows and columns above and to the left

of the active cell visible as you scroll through a

worksheet.

Freeze First column

Keeps only the first column visible as you scroll through a worksheet.

Data structure

an organization method used to manage multiple data points within a dataset

Table

a group of related data organized in a series of rows and columns that is managed independently from any other data on the worksheet

Field

individual piece of data, e.g., last names or quantities sold

Record

a group of related fields representing one entity (person, place, or thing/event). A row in a table represents a record.

Table advantages

Column headings remain onscreen

• Table styles easily format table rows and columns

• Calculated columns enable the creation and editing of

formulas that copy down the columns automatically.

• Calculated total row enables the implementation of

summary functions

• Structured references can be used instead of cell

references in formulas

Header Row

Displays the header row (field names) when selected; removes field names when deselected. Header Row formatting takes priority over column formats.

Total Row

Displays a total row when selected. Total Row formatting takes priority over column formats.

First Column

Applies a different format to the first column so that the row headings stand out. First Column formatting takes priority over Banded Rows formatting.

Last Column

Applies a different format to the last column so that the last column of data stands out; effective for aggregated data, such as grand totals per row. Last Column formatting takes priority over Banded Rows formatting.

Banded Rows

Displays alternate fill colors for even and odd rows to help distinguish records.

Banded Columns

Displays alternate fill colors for even and odd columns to help distinguish fields.

Filter Button

Displays a filter button on the right side of each field name in the header row.

Structured reference

a tag or the use of a table element, such as a field heading, as a reference in a formula

Fully qualified structured reference

it identifies table data by including the table name followed by the corresponding field name.

Unqualified structured reference

Formula references used within a table that omit the table name, but refer directly to the columns.

Total row

displays below the last row of records in an Excel table and enables you to display summary statistics, e.g., a sum of values displayed in a column

SUBTOTAL function

calculates an aggregate value

=SUBTOTAL(function_num, ref1, …) where the function_num argument is a number that represents a function and the ref1 argument indicates the range of values to calculate

Filtering

the process of displaying only those records that meet specified conditions

To apply a filter

Select any cell in the range of data to be filtered

• Select Filter on the Data tab

• Select the filter arrow for the column to be filtered

• Deselect the Select All check box

• Select the check boxes for the text that is to remain

visible

Highlight Cells Rules

Highlights cells with a fill color, font color, or border if values are greater than, less than, between two values, equal to a value, or duplicate values; text that contains particular characters; or dates when a date meets a particular condition, such as in the last 7 days.

Top/Bottom Rules

Formats cells with values in the top 10 items, top 10%, bottom 10

items, bottom 10%, above average, or below average. You can change

the exact values to format the top or bottom items or percentages,

such as top 5 or bottom 15%.

Data Bars

Applies a gradient or solid fill bar in which the width of the bar represents the current cell’s value compared relatively to other cells’ values

Color Scales

Formats different cells with different colors, assigning one color to the lowest group of values and another color to the highest group of values, with gradient colors to other values.

Icon Sets

Inserts an icon from an icon palette in each cell to indicate values compared to each other

Text Contains

Formats cells that contain the text in the first selected cell. In Figure 4.39, the first selected cell contains Promotion. If a cell contains the words Fall Promotion, it would also be formatted because it contains Promotion.

Duplicate Values

Formats cells that are duplicated in the selected range.

Unique Values

Formats cells that are unique; that is, no other cell in the selected range contains the same data.

Equal To

Formats cells that are exactly like the data contained in the first selected cell.

Clear Format

Removes the conditional formatting from the selected

range.

Data bars

apply a gradient or solid fill bar in which the width of the bar represents the current cell’s value compared to other cells’ values.

The width of the data bar represents the value in a cell, with a wider bar representing a higher value and a narrower bar a lower value

Color scales

format cells with different colors based on the relative value of a cell compared to other selected cells

Icon sets

symbols or signs that classify data into three, four, or five categories, based on the values in a range