Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

work > bitm exam 3 > Flashcards

bitm exam 3 Flashcards

1
Q

grouping worksheets

A

selecting two or more worksheets so you can perform the same action at the same time

[Group] appears in the title bar

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

how do you group worksheets

A

use shift then click on another sheet to group contiguous group of sheets

right click then select all sheets

ctrl to click a non contiguous group of sheets

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Ungroup

A

click a sheet that wasnt apart of the grouped sheets

or right click then click ungroup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

hyperlink

A

an electronic marker that connects to an:
Specific cell in the same workbook
Specific cell in a different workbook
Existing file
Web page
E-mail address

When you point to a hyperlink, the pointer looks like a hand, and a ScreenTip displays

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ScreenTip

A

indicates the link’s destination (workbook, worksheet, cell reference, and instructions)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

To help you manage worksheet windows you can:

A

Split a window to see different parts of a worksheet at the same time (split into two or four visible panes)

Control worksheet visibility (hide/unhide)

Open and arrange windows

Save an Excel workspace to save the layout of open worksheet windows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

To open another window of the current workbook

A

click New Window in the Window group on the View tab.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

what becomes visible when using the split window feature

A

split bars

you can further customize the display by dragging a split bar between the panes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

synchronized scrolling

A

allows you to scroll in one pane while the other pane(s) move similarly

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

3-D workbook

A

Electronic equivalent of a 3-ring binder

3D reference
=Atlanta!B3 +Boston!B3+Chicago!B3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

3D reference

A

A reference within a formula or function on one worksheet that includes the name of another worksheet, column letter, and row number located within a workbook.

ex.
=SUM(Atlanta:Chicago!B3)

cell range must be identical in every worksheet

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Linking

A

process of creating external cell references from worksheets in one workbook to cells on a worksheet in another workbook

Source file
contains original data that you want to use in another file

Destination file
contains a link to receive data from the source files

[BookName.xlsx]SheetName!CellReference

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

error types

A

syntax error
a formula or function violates construction rules

run-time error
correct formula/function but missing or containing invalid data

logic error
correct formula but logically incorrect construction

circular reference
formula contains direct or indirect reference to the cell containing the formula

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

formula audits

A

Tools to enable you to detect and correct errors in formulas by identifying relationships among cells.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

tracing precedents and dependents (formula auditing)

A

Precedent cells
cells referenced by a formula in another cell.

Dependent cells
contain formulas that depend on other cells to generate their values

tracer arrows
Blue arrows show cells with no errors.
Red arrows show cells that cause errors

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

what does a green triangle in the top left corner of a cell indicate

A

error

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Formula auditing

A

Tools that enable you to detect and correct errors in formulas by identifying relationships among cells.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Watch Window

A

separate window that displays the workbook name, worksheet name, cell addresses, values, and formulas so you can monitor and examine formula calculations involving cells not immediately visible on the screen.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

data validation

A

enables you to control the data that can be entered into a cell

warns and prevents people from entering “wrong” data in a cell.

error alert message appears if user tries to enter a value in the cell that does not meet the validation rule

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Locked cells and passwords

A

cells that prevent users from making changes to that cell in a protected worksheet

not protected until you protect the worksheet

unlock cells you want users to be able to to modify

passwords:
up to 255 characters,
case sensitive,
Combination of letters, numbers, and symbols

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Power Query (formerly called Get & Transform)

A

enables you to connect to or import data from diverse data sources while also transforming the information to meet your standards.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

delimiters

A

special characters (such as a tab, comma and space) that separate data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Data shaping

A

process of editing information from one or more data sources to the required parameters before importing the data into an application

data is shaped using the Power Query Editor

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Power Query Editor

A

Business Intelligence tool that can connect and shape data from multiple data sources.

Power Query can:
Add columns
Split columns
Remove full columns
Change column data type

can remove a single column or several all at once.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
when data is imported
it is considered a query
26
merging queries (a join)
you can combine values from multiple sources or queries into one singular dataset. must be a common field between the two data sources and loaded in the power query editor
27
When data is imported using Power Query
Excel creates a link to the original data source so the data is quickly updated when the original source is modified. The Queries & Connections pane displays active queries and external program connections.
28
Power Pivot
a COM Add in offers key functionality that is not included in standard PivotTable options, plus a variety of useful features for advanced business users.
29
DAX
used to create two types of calculations in Power Pivot: Calculated column -creates a new column that iterates the calculation row by row Measure - calculated field that can be used in a Power PivotTable or PivotChart fully qualified structured references MUST be used if referencing data from a different table in the data model.
30
difference between explicit and implicit measure
-explicit (created manually by entering a formula or function) -implicit (created automatically when a field is placed in the value area of a PivotTable)
31
difference between power query and power pivot
Power Query obtains and prepares the data, and Power Pivot analyzes and visualizes it.
32
relationship
association between two related tables where both tables contain a common field of data, such as IDs.
33
Data visualization (3D Maps)
method of summarizing data graphically to better understand the significance of the information if data contains geographic information, it can be visualized using 3D Maps. -Power Map add in
34
text string/string
a group of characters that are used as data in a spreadsheet program. By default, text strings are left-aligned in a cell while number data is aligned to the right.
35
flash fill
used for data manipulation by recognizing a pattern and then filling in the remaining rows using that pattern. can only manipulate data one column at a time.
36
Another way to convert text in one column into multiple columns is using
Text to Columns
37
Using the space delimiter would separate Los Angeles into two cells and Salt Lake City into three cells. so you should
use the Convert Text command twice
38
CONCAT function
joins text strings into one text string up to 254 texturings and 32K characters does not ignore empty cells
39
TEXTJOIN function
combines text strings to produce a result of characters ignores empty cells
40
proper, upper and lower functions
PROPER function capitalizes the first letter of each word in a text string UPPER function converts text strings to uppercase letters LOWER function converts all uppercase letters in a text string to lowercase
41
Substitute and Trim function
sub: replaces an old text string with a new text string trim: removes leading and trailing spaces in a text string and extra spaces between words but maintains one space between words within a text
42
advanced filter
finding data that meets two or more complex criteria, extracting matches and differences between two columns , filtering rows that match items in another list, finding exact matches including uppercase and lowercase characters, and more.
43
AND & OR functions (advanced filter)
Filtering with AND is done with conditions being applied on the same row in the criteria range. Filtering with OR is done with conditions being applied on separate rows in the criteria range
44
database function arguments
Database argument the entire dataset Field argument the column that contains the values operated on by the function Criteria argument defines the conditions to be met by the function
45
DSUM function
adds the values in a column that match conditions specified in a criteria range
46
DAVERAGE function
determines the average of values in a column that match conditions specified in a criteria range
47
DMAX function
identifies the highest value in a column that matches specified conditions in a criteria range
48
DMIN function
identifies the lowest value in a column that matches specified conditions in a criteria range
49
DCOUNT function
counts cells that contain numbers in a column that match specified conditions in a criteria range
50
DCOUNTA function
counts empty cells or non-numeric data in a column that match specified conditions in a criteria range
51
DGET function
extracts a single value from a field or column within a dataset that matches specified conditions
52
ARRAY
a row or column of values, or a combination of rows and columns of values ARRAY FORMULAS can return either multiple results, or a single result.
53
Spill
54
XMATCH function
searches through a range for a specific value and returns the relative position of that value (or the next closest larger or smaller value) within the range
55
INDEX function
returns the value at the intersection of a specific row and column within a given range
56
Each macro is called a sub routine, which is the start of a macro, and end sub at the end and you can leave comments in your macro
57
TEMPLATE
a partially completed document used as a model to create other documents that have the same structure and purpose.
58
Template creation guidelines
Use formatted, descriptive labels and formulas Avoid values, when possible, in formulas Use appropriate functions to trap errors Include data-validation Include template instructions Turn off worksheet gridlines Apply appropriate formatting Give worksheets meaningful names
59
DOCUMENT INSPECTOR
a tool that reviews a workbook for hidden or personal data stored in the document. then informs you of these details so that you can select what data to remove.
60
ACCESSIBILITY CHECKER
a tool that reviews a workbook to detect potential issues that could impede users who access your files and alerts you to these issues so they can be addressed.
61
Accessibility Checker provides three types of feedback for each issue:
Error—content that creates extreme difficulty or impossibility for persons with disabilities to view correctly Warning—content that is difficult for users to comprehend Tip—Content that is understandable but could be presented or organized differently to maximize comprehension
62
COMPATIBILITY CHECKER
a tool that evaluates the workbook’s contents to identify what data and features are not compatible with previous versions By default, Compatibility Checker selects all Excel versions 97 through the current version. The summary organizes issues into groups, such as Significant loss of functionality and Minor loss of fidelity.
63
comment
an annotation to ask a question or offer a suggestion to another person about content in a worksheet cell. not printed by default
64
MACRO
a set of instructions that executes a sequence of commands to automate repetitive or routine tasks
65
A macro can be created by using the
Macro Recorder or by typing instructions using Visual Basic for Applications (VBA)

work (25 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions