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