CS 235 Exam 3 Concepts Homework Quiz Terms

IFERROR function

A logical function that checks if a cell contains an error or if a formula will result in an error, will return something for an error a. =IFERROR(value, value if error) i. Ex: =INFERROR(A2/B1, "You cannot divide by zero. Change cell B1 to be a value higher than 0".)

When creating a 3D map, what is the best way to view your data from different perspectives over time?

Add scenes

In Windows 10, where would you save the template so it is available in the available templates list in Backstage view?

C:\Users\Username\Documents\Custom Office Templates

a text function that combines text strings and requires a separate argument for each delimiter to include in the combined text

CONCAT function

a database function that determines the average of values in a column that matches conditions specified in the criteria range

DAVERAGE function

a database function that counts the cells that contain numbers in a column that matches specified conditions in a criteria range

DCOUNT function

a database function that identifies the highest values in a column that matches specified conditions in a criteria range

DMAX function

a database function that identifies the lowest value in a column that matches specified conditions in a criteria range

DMIN function

a database function that adds the values in a column that matches conditions specified in a criteria range

DSUM function

Which tool should be used to locate and remove private metadata?

Document Inspector

You want to create a hyperlink to an online dataset from the United States Census Bureau. Which type of link do you create?

Existing File or Web Page

a function that returns the text version of a formula stored in another cell

FORMULATEXT function

Column A contains numbers such as 2021001. You enter 2021-001 in the adjoining cell in column B. What feature can you use to quickly complete the text pattern down column B?

Flash Fill

What function should you use to look up a specified value within a database at the intersection of a specific row and column position?

INDEX

a function that returns a value at the intersection of a specified row and column within a given range

INDEX function

a text function that extracts a specified number of characters from the left side of a text string

LEFT function

a text function that converts a text string to all lowercase letters

LOWER function

Which function identifies and returns the position of a value within an array?

MATCH

a function that searches through a range for a specific value and returns the relative position of that value within the range

MATCH function

a text function that extracts the characters from the middle of a text string, given a starting position and length

MID function

a range of cells that contains the list of records from a dataset that meets the specifie3d conditions

Output range

A column contains names in uppercase, which is hard to read. Which function displays the names where the first letter in each word is capitalized?

PROPER

a text function that capitalizes the first letter of each word in a text string

PROPER function

a hidden workbook stored in the XLSTART folder that contains macros and opens automatically when you start Excel

Personal Macro Workbook

What tool can be used to aggregate relational data from multiple sources?

Power Pivot

Which tool should be used to remove fields and format data when importing?

Power Query Editor

A column contains phone numbers such as (801)-555-1234. What function should you use to extract the last four digits?

RIGHT

a text function that extracts the specified number of characters from the right side of a text string

RIGHT function

a software or hardware problem that prevents a program from working correctly

Run-time Error

The Sales Qtr2 Workbook contains links to data in the April, May, and June workbooks. Which workbook is the destination file?

SalesQtr2

Which data validation alert style should you use to prevent the user from entering invalid data?

Stop

a test function that combines text strings and inserts a delimiter you specify in the first argument

TEXTJOIN function

a function that rearranges data from columns to rows and rows to columns

TRANSPOSE function

a text function that converts text strings to all capital letters

UPPER function

In which programming language are Excel macros written?

VBA

an application used to create, edit, execute, and debug Office Application macros using programming code

Visual Basic Editor

a robust programming language that can be used within various software packages to enhance and automate functionally

Visual Basic for Applications (VBA)

You want to monitor the results of cell formulas on a different worksheet as you change data on another worksheet. You should create a _______________.

Watch Window

What does a red triangle in the upper-right corner of a cell indicate?

a hidden comment

You use Trace Precedents for cell containing a formula. What indicates an error in the precedents used?

a red tracer arrow displays between the dependent and precedent cells

Which Power Add-In is best for visualizing geospatial data? a. 3D Maps (Power Map) b. Get & Transform c. Power Pivot d. Power View

a. 3D Maps (Power Map)

Which function identifies the lowest value in a database column based on specified criteria in a criteria range? a. DMIN b. MIN c. DMAX d. MINIFS

a. DMIN

What arguments are included in a database function (D functions)?

a. Database: entire dataset b. Field: column that contains the values operated on by the function c. Criteria: defines the conditions to be met by the function

You have a workbook that contains sales data where each sale rep's data is stored on a separate worksheet. Which task is the least likely to be done while the worksheets are grouped? a. Enter specific values for the first sales rep. b. Format the values with an appropriate number style. c. Fill the sales categories across the worksheets. d. Format the column and row labels at the same time.

a. Enter specific values for the first sales rep.

Which of the following is not a benefit of using macros? a. Macros can be used for repetitive tasks b. Macros can be saved and reused in different worksheets c. Macros cannot contain viruses d. Macros perform tasks quickly

a. Macros cannot contain viruses

Database functions such as DSUM contain all of the following arguments except __________________. a. Output b. Database c. Field d. Criteria

a. Output

Which of the following is not a benefit of shaping data with the Power Query Editor? a. Shaped data does not retain links to external file sources b. Shaped data can be added to a data model c. Data can be merged from multiple sources d. All changes can be undone

a. Shaped data does not retain links to external file sources

Data validation rule error messages

a. Stop: prevents the user from entering invalid data b. Warning: accepts invalid data but warns user that data is invalid c. Information: accepts invalid data but provides information to the user

Error types

a. Syntax - Occurs because a formula or function violates construction rules b. Run-time - Occurs when executing a syntactically correct formula or function c. Logic - Results from a syntactically correct formula but logically incorrect construction d. Circular reference- Occurs when a formula contains direct or indirect reference to the cell containing the formula

What is included in the 3D Maps?

a. longitude/latitude b. street address c. city d. county e. state/province f. zip code/postal code g. country/region

a tool that reviews a workbook to detect potential issues that could hinder users who access your public files and then alerts you to these issues so that you can address them

accessibility checker

Which function correctly adds data in cell D25 across multiple worksheets? a. =COUNT(Mon:D25, Tues:D25, Wed:D25, Thurs:D25, Fri:D25) b. =SUM(Monday,Friday:D25) c. =SUM(Monday:Friday!D25) d. =Monday:D25+Friday:D25

c. =SUM(Monday:Friday!D25)

Which of the following file formats cannot be imported using Get & Transform? a. Access Data Table b. HTML c. MP3 d. CSV

c. MP3

Which of the following statements is true about macro security? a. When you add a Macro-Enabled Workbook to the Trust Center, you must enable the content of that file each time you open it b. Macro-enabled files cannot contain viruses c. Setting your Trust Center options to include files in a specific folder and then saving macro-enabled files in that folder allows... d. Set macro security options on the Developer tab toSecured

c. Setting your Trust Center options to include files in a specific folder and then saving macro-enabled files in that folder allows...

A worksheet contains addresses in column C. The addresses use commas after the street, city, and state, such as 129 Elm Street, Burlington, NC, 27215. Column D contains the phone number. You use Text to Columns to divide the addresses into multiple columns using comma delimiters. You successfully divide the addresses into four columns. What happens to the phone numbers in column D? a. Excel phone number column is moved to the right side of all the columns containing the parts of the addresses b. The phone number column is moved to the left of the original address column c. The phone numbers are overwritten with city names d. The phone numbers are converted to commas

c. The phone numbers are overwritten with city names

Which of the following is not a dimension of a 3D Map layer? a. height b. time c. columns d. location

c. columns

Which of the following is not true about macros? a. macros can use absolute or relative cell referencing b. macros are created using VBA c. templates cannot contain macros d. macros can be saved in XLSM workbooks

c. templates cannot contain macros

PROPER function

capitalizes the first letter of each word in a text string a. =PROPER(text)

TEXTJOIN function

combines text strings to produce a list of characters a. =TEXTJOIN(delimiter, either TRUE or FALSE on if you want to ignore empty cells, text1, text2..)

a note or annotation to ask a question or provide a suggestion to another person about content in a worksheet cell

comment

a tool that evaluates the workbook's contents to identify what data and features are not compatible with previous versions

compatibility checker

LOWER function

converts all upper case letters in a text string to lowercase a. =LOWER(text)

UPPER function

converts text strings to all uppercase letters a.=UPPER(text)

DCOUNTA function

counts empty cells or non-numeric data in a column that match specified conditions in a criteria range a. =DCOUNT(database, field, criteria)

a range of two or more adjacent cells that specifies the conditions used to control the results of a filter

criteria range

You are performing an advanced filter, in which argument box do you enter the range for the dataset? a. Copy to b. Criteria range c. Database d. List range

d. List range

Which of the following Power BI tools is best suited for editing data before import? a. PivotTables b. 3D Maps (Power Map) c. Power Pivot d. Power Query Editor

d. Power Query Editor

Which of the following would cause a run-time error? a. adding a comment to a procedure b. saving with the incorrect file type c. closing a template without saving d. running a macro on a protected worksheet

d. running a macro on a protected worksheet

a set of functions that provide statistical calculations for a database

database functions

A special character that is used to separate a text file is called a__________.

delimiter

a tool that reviews a workbook for hidden or personal data stored in the workbook or personal document properties, such as author, and then informs you of these details

document inspector

DGET function

extracts a single value from a field or column within a dataset that matches specified conditions a. =DGET(database, field, criteria)

LEFT function

extracts characters from the beginning, or left side, of a text string a. =LEFT(text, number of characters)

LEN function

identifies the number of characters (length) in a text string a. =LEN(text)

FIND function

identifies the starting position of one text string within another text string a. =FIND(text, within text, start number)

CONCAT function

joins text strings into one text string a. =CONCAT(text1,text2...)

a word or symbol reserved within a programming language for specific purposes

keyword

MATCH function

looks for a value within a range and returns the relative position of that value within the range a. =MATCH(lookup value, lookup array, [match type: 0 for exact match, 1 for not])

a set of instructions that executes a sequence of commands to automate repetitive or routine tasks

macro

a tool that records a series of commands in the sequence performed by a user and converts the commands into programming syntax

macro recorder

an object that stores sub procedures and functions

module

a basic documentations tool that annotates a cell but does not display as a discussion thread

note

an element that holds both data and code and represents an element of Excel

object

Square brackets [ ] mean what?

optional argument

a named sequence of statements executed as a unit

procedure

linking

process of creating external cell references from worksheets in one workbook to cells on a worksheet in a different workbook a. Source file: contains original data that you want to use in another file b. Destination file: contains a link to receive data from another file i. =[WorkbookName]WorksheetName!RangeofCells

3-D reference

reference on one worksheet that includes the name of another worksheet, column letter, and row number located within the workbook a. ='Worksheet Name'!RangeofCells i. ex. =SUM('FirstWorksheet: LastWorksheet'!A23)

TRIM function

removes leading and trailing spaces in a text string and extra spaces between words but maintains one space between each word in a text string a. =TRIM(text)

MID function

returns the specified number of characters from the middle of a text string, based on a starting position and the number of characters. a. =MID(text, start number, number of characters)

RIGHT function

returns the specified number of characters starting at the right (end) of a text string a. =RIGHT(text, number of characters)

INDEX function

returns the value at the intersection of a specific row and column within a given range a. =INDEX(array, row number: found by MATCH function, [column number: start column is what start at and count from there]

a group of Visual Basic statements that performs actions on a workbook but do not return specific values

sub procedure

SUBSTITUTE function

substitutes or replaces an old text string for a new text string a. =SUBSTITUTE(text, old text, new text, instant number) i. instant number specifies which occurrence of old text you want to replace with new text (only this occurrence is changed,; if don't include instant number: all occurrences are changed)

a special workbook file used as a model to create similarly structured workbooks

template

Where can you search and download office templates?

template gallery

data type

tool that changes the data type for a column

group by

tool that groups rows in a table based on the values in the currently selected columns

use first rows as headers

tool that promotes the first row of a table into column headers

replace values

tool that replaces existing values in the current column with a specified new value

split column

tool that separates elements of a column into multiple columns based on a delimiter