Chapter 9 pt 1 acis

red ellipse does not

Circle Invalid Data command: Places a _____ (an elongated circle) around each cell with invalid data Highlights cells but (does/does not) edit or correct the data

more differently same right

Consolidate by category *(More/less)* flexible Data is arranged *(the same/differently)* but must have *(the same/different)* row and/or column labels Activate the option to use the top row and/or *(left/right)* column labels

same same

Consolidate by position: Source worksheets must all have (same/different) structure Data must be in (same/different) relative position on worksheets

All references

Consolidating Data in a Summary Worksheet: All the data to be included in the summary must be added to the _____ box

human error

Greatest source of errors:

Data validation tools

Help minimize data entry errors Rules to control what can and cannot be entered in specific cells Excel checks data as it is entered to verify that it matches established requirements

after

If you set validation *(before/after)* data is entered, already-entered data that does not meet the criteria is not automatically identified.

copied after

Invalid data can occur when: Data is ____ into a range with validation settings Validation is set (before/after) data is already entered

3-d named range cannot

References same cell or range of cells across multiple worksheets Name *(can/cannot)* be defined in Name box Use Define Name in Defined Names group on Formula tab

importing data from a text file raw, no .csv .txt .prn

Text Files Common for passing data between organizations because the files can be read by many different application programs Includes ____ data with ___ formatting Data fields are separated with delimiters _____ - comma separated _____ - tab delimited _____ - space delimited Each data field is imported into a separate column

exporting

The process of saving data in a format that can be used by another program or application. It is common to save Excel data as a tab-delimited (.txt) or comma-delimited file (.csv). Only one worksheet can be saved in a text file. (Text formats do not support multiple worksheets in the same file.) All formatting is lost when saving as a text file and cells with functions will be converted to their current values.

list validation

Use ___ to create a drop-down list

dynamic

Use when source data might be edited Choose option to create links to source data Places formulas on the consolidated sheet in an outline When data on a source worksheet is edited, the formula recalculates

Text to Columns feature

Used to split data into separate columns There must be a consistent character (delimiter) at which the data can be split or the data can be split at a specific number of characters If column to the right of the column being split contains data, the data will be overwritten To avoid overwriting data, insert enough empty columns to the right before converting the data

Access

__ is a relational database program.

error message stop warning information

___ - Pop-up message that appears after invalid data is entered Types of Error Alerts: ____- User cannot enter invalid data, must cancel or retry ____- Invalid entry is allowed but can be edited or canceled ___ - Invalid entry is allowed

flash fill and text functions

____ and ____ can be used to cleanse imported data

Consolidate

____ or summarize data from multiple worksheets onto one consolidation (summary) worksheet

importing

_____ is the process of getting external data into an Excel worksheet.

input message

______ Appears when a user makes a validated cell active Comment box that contains a guideline for the person entering data

flash fill

______ Recognizes simple patterns in data as you type and automatically fills in values Can be used to: Combine data from contiguous cells into a single cell Split data from a single cell into multiple cells Add text

connection

A ______ is an identifier and a link for data that originated outside the workbook. can be edited, refreshed, named, or removed

Validation settings

- rules applied to data as it is entered

multiple queries databases

importing data from access Data are stored in (same/multiple) tables that connect to one another via related fields. ___ can be used to ask questions of the data. ____ are often used to input and store data. The data can be imported into Excel to perform statistical and what‐if analyses.

static

what type of consolidation? Use when source data is final Does not change when a value on any source worksheet is edited

Data cleansing

—fixes obvious data errors and converts data into a useful format