PRACTICE QUIZ
Conditional formatting is a spreadsheet tool that changes how cells appear when values meet a specific condition. Data analysts can use conditional formatting to do which of the following tasks? Select all that apply.
Data analysts use conditional formatting to identify blank cells or missing information and to make cells stand out for more efficient analysis.
A data analyst uses the SPLIT function to divide a text string around a specified character and put each fragment into a new, separate cell. What is the specified character separating each item called?
When using the SPLIT function, the specified character separating each item is called a delimiter.
For a function to work properly, data analysts must follow each function’s predetermined structure. What is this structure called?
This structure is called syntax. Syntax is a predetermined structure that includes all required information and its proper placement.
You are working with the following selection of a spreadsheet: (Image S1Q4) In order to extract the five-digit postal code from North Wales, PA, what is the correct function?
The correct syntax is =RIGHT(B2,5). The RIGHT function returns a set number of characters from the right side of a text string. B2 is the specified cell. And 5 is the number of characters to return.
A data analyst in a human resources department is working with the following selection of a spreadsheet: (Image S1Q5) They want to create employee identification numbers (IDs) in column D. The IDs should include the year hired plus the last four digits of the employee’s Social Security Number (SS#). What function will create the ID 20142683 for the employee in row 3?
To create the ID 20142683 for the employee in row 3, the function is =CONCATENATE(A3,B3). CONCATENATE joins together two or more text strings. (A3,B3) are the locations of the strings to be joined.
An analyst is cleaning a new dataset containing 500 rows. They want to make sure the data contained from cell B2 through cell B300 does not contain a number greater than 50. Which of the following COUNTIF function syntaxes could be used to answer this question? Select all that apply.
One possible syntax is =COUNTIF(B2:B300,">50"). This returns the number of cells that are greater than 50. Another option is =COUNTIF(B2:B300,"<=50"). This returns the number of cells that are less than or equal to 50. Either one can confirm that the data does not contain a number greater than 50.
The V in VLOOKUP stands for what?
The V in VLOOKUP stands for vertical. VLOOKUP is a spreadsheet function that vertically searches for a certain value in a column to return a corresponding piece of information.
To evaluate how well two or more data sources work together, data analysts use data mapping.
To evaluate how well two or more data sources work together, data analysts use data mapping.
A delimiter is a character that indicates the beginning or end of a data item. The split text to columns tool uses a delimiter to accomplish what task?
The split text to columns tool uses a delimiter to specify where to split a text string.
Fill in the blank: When describing a SUM function, the _____ is =SUM(value 1 through value 2).
When describing a SUM function, the syntax is =SUM(value 1 through value 2).
You are working with the following selection of a spreadsheet: (Image S1Q11) In order to extract the five-digit postal code from Burlington, MA, what is the correct function?
The correct syntax is =RIGHT(B3,5). The RIGHT function returns a set number of characters from the right side of a text string. B3 is the specified cell. And 5 is the number of characters to return.
A data analyst at an e-commerce company is working with a spreadsheet containing last month's sales. The most expensive product their company sells costs $49.99, so they want to quickly confirm that all of the data in the Sales column is $49.99 or less. What function can they use?
They can use COUNTIF, which is a function that returns the number of cells that match a specified value or parameter.
VLOOKUP searches for a value in a row in order to return a corresponding piece of information.
VLOOKUP searches for a value in a column in order to return a corresponding piece of information.
A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible?
Data analysts use data mapping to note differences in data sources in order to ensure the data is compatible.
You are working with the following selection of a spreadsheet: (Image S1Q15) In order to extract the five-digit postal code from Brandon, FL, what is the correct function?
The correct syntax is =RIGHT(B4,5). The RIGHT function returns a set numbe) r of characters from the right side of a text string. B4 is the specified cell. And 5 is the number of characters to return.
A data analyst in a human resources department is working with the following selection of a spreadsheet: (Image S1Q16) They want to create employee identification numbers (IDs) in column D. The IDs should include the year hired plus the last four digits of the employee’s Social Security Number (SS#). What function will create the ID 20093208 for the employee in row 5?
To create the ID 20093208 for the employee in row 5, the function is =CONCATENATE(A5,B5). CONCATENATE joins together two or more text strings. (A5,B5) are the locations of the strings to be joined.
A data analyst wants to search for a certain value in a column, then return a corresponding piece of information. Which function should they use?
VLOOKUP is used to search for a certain value in a column, then return a corresponding piece of information.
Fill in the blank: Data mapping is the process of _____ fields from one data source to another.
Data mapping is the process of matching fields from one data source to another.
An analyst is working on a project involving customers from Bogota, Colombia. They receive a spreadsheet with 5,000 rows of customer information. What function can they use to confirm that the column for City contains the word Bogota exactly 5,000 times?
They can use COUNTIF, which is a function that returns the number of cells that match a specified value.
Fill in the blank: Conditional formatting is a spreadsheet tool that changes how _____ appear when values meet a specific condition.
Conditional formatting is a spreadsheet tool that changes how cells appear when values meet a specific condition.
A data analyst in a human resources department is working with the following selection of a spreadsheet: (Image S1Q21) They want to create employee identification numbers (IDs) in column D. The IDs should include the year hired plus the last four digits of the employee’s Social Security Number (SS#). What function will create the ID 20201939 for the employee in row 4?
To create the ID 20201939 for the employee in row 4, the function is =CONCATENATE(A4,B4). CONCATENATE joins together two or more text strings. (A4,B4) are the locations of the strings to be joined.
A data analyst suspects that there are many blank cells in their spreadsheet corresponding to missing information. What spreadsheet tool can they use to identify only those cells containing the null values?
A data analyst is working on a spreadsheet in which one of the columns is name data. This data is formatted as lastname, firstname. The analyst chooses to divide this data into two new columns, one containing the firstname data and the other containing the lastname data. What spreadsheet tool would they use to do this?
A data analyst is using a function in a spreadsheet. When they input the function, they follow a predetermined structure that includes all required information for the function and its proper placement. What aspect of a function does this describe?
As part of the data-cleaning process, a data analyst creates a rule to highlight any empty cells in a bright blue color. This is an example of data visualization.
A data analyst is working on a spreadsheet in which one of the columns contains name data. This data is formatted as lastname_firstname. The analyst splits this data at the underscore so that each piece—firstname and lastname—are contained in their own columns.
In a spreadsheet, what is the correct function for extracting the first two characters of the string located in cell A7?
An analyst is cleaning a new dataset. They want to make sure the data contained from cell C4 through cell C350 contains only numbers below 40. Choose the statements that include the correct syntax for this COUNTIF function. Select all that apply.
In this case, the analyst can use the inequality ">=40" to count any cells containing the value 40 and above that exist between the cell range C4 through C350. If the return value of the COUNTIF function is greater than 0, the analyst can then take the necessary steps to locate and clean the unwanted cell or cells.
Before analyzing a dataset, an analyst maps the data. What is the reason for doing this?
Fill in the blank: In order to make your spreadsheet easier to analyze, you choose to alter the way cells appear if their values meet certain conditions. The spreadsheet tool that you use to do this is called _____.
Fill in the blank: A _____ is a specified text that the SPLIT function uses to determine where a text string is to be divided.
Fill in the blank: The function _____ is used to return information in a column that contains a specified value.
An analyst is cleaning a new dataset. They want to make sure the data contained from cell B2 through cell B100 does not contain a number smaller than 10. Which COUNTIF function syntax can be used to answer this question?
A data analyst is using a function in a spreadsheet. For the function to work correctly, they follow the function’s syntax. What does this entail?
Ready to test your recall?
Conditional formatting is a spreadsheet tool that changes how cells appear when values meet a specific condition. Data analysts can use conditional formatting to do which of the following tasks? Select all that apply.
How confident are you in this answer?