1. 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.
- To make cells stand out for more efficient analysis.
- To sort data in series of cells into a meaningful order
- To identify blank cells or missing information
- To calculate mathematical equations
2. 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?
- Delimiter
- Partition
- Unit
- Substring
Explanation: In this particular instance, the character that is designated to separate each item is referred to as the "delimiter." The SPLIT function takes a text string and divides it into many pieces by using the delimiter. Each fragment is then placed into a new cell that is completely independent from the others.
3. For a function to work properly, data analysts must follow each function’s predetermined structure. What is this structure called?
- Summary
- Algorithm
- Validation
- Syntax
Explanation: The "syntax" of a function refers to the framework that data analysts are required to adhere to in order for the function to perform with the desired results. The syntax provides an overview of the right arrangement of items, such as the name of the function, arguments, and other parameters, that are necessary for the function to execute correctly.
4. You are working with the following selection of a spreadsheet:
In order to extract the five-digit postal code from Brandon, FL, what is the correct function?
- =RIGHT(5,B4)
- =LEFT(5,B4)
- =LEFT(B4,5)
- =RIGHT(B4,5)
5. A data analyst in a human resources department is working with the following selection of a spreadsheet:
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?
- =CONCATENATE(A4*B4)
- =CONCATENATE(A4!B4)
- =CONCATENATE(A4,B4)
- =CONCATENATE (A4+B4)
6. 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?
- SUMIF
- COUNT
- SUM
- COUNTIF
Explanation: In order to rapidly verify if all of the information included in the Sales column is equal to or lower than $49.99, the data analyst might make use of the "MAX" function. The MAX function returns the value that is the highest among a group of numbers; if the maximum value is $49.99 or below, it shows that all of the values are within the range that was supplied.
7. The V in VLOOKUP stands for what?
- Visual
- Vertical
- Variable
- Virtual
Explanation: The "V" in "VLOOKUP" is an abbreviation for "Vertical." When using spreadsheet software (such as Excel), the VLOOKUP function gives you the ability to search for a value in a vertical column and then return a value that corresponds to that value from the same row in a different column inside the same column.
8. Data and business objectives might not align for a number of reasons. Which of the following issues can prevent alignment? Select all that apply.
- True
- False
9. 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.
- =COUNTIF(B2:B300,>50)
- =COUNTIF(B2:B300,<=50)
- =COUNTIF(B2:B300,”>50″)
- =COUNTIF(B2:B300,”<=50”)
10. 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?
- To change the format of a column of text
- To split one column into two
- To specify where to split a text string
- To split duplicate substrings
Explanation: The split text to columns tool makes use of a delimiter to separate a single column of text into numerous columns depending on the character that is supplied as the delimiter. The original text is effectively divided into data that is better ordered and structured, and each fragment of text that is separated by the delimiter is put into a distinct column.
11. Fill in the blank: When describing a SUM function, the _____ is =SUM(value 1 through value 2).
- Standard
- Structure
- script
- syntax
Explanation: Whenever an SUM function is being described, the structure that is being used is =SUM(value 1 through value 2).
12. VLOOKUP searches for a value in a row in order to return a corresponding piece of information.
- True
- False
Explanation: Certainly not! When you use the VLOOKUP function, it will really search for a value in the first column of a table and then return a value from a specified column that is located in the same row. Rather than looking in a horizontal row, it is more about searching in a vertical column to locate information that is linked to it.
13. 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?
- Merge the data
- Use a data visualization
- Map the data
- Apply a data structure
Explanation: Performing data normalization or standardization is something that a data analyst might do in order to guarantee compatibility between two datasets that are stored in distinct techniques. The process of changing the data into a standard format or structure is required in order to facilitate the efficient combination of the data. Standardizing units of measurement, displaying dates in a consistent manner, dealing with missing information, and fixing any other anomalies in data structure are all examples of things that might fall under this category. Implementing a standard or data format that is universally accepted would make the process of integrating and analyzing the combined information much simpler.
14. 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?
- MATCH
- FIND
- VALUE
- VLOOKUP
15. Fill in the blank: Data mapping is the process of _____ fields from one data source to another.
- extracting
- linking
- matching
- merging
Explanation: The practice of aligning or matching fields from one data source to another sources of data is referred to as data mapping.
16. 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?
- COUNT
- SUMIF
- SUM
- COUNTIF
17. Fill in the blank: Conditional formatting is a spreadsheet tool that changes how _____ appear when values meet a specific condition.
- queries
- charts
- filters
- cells
Explanation: One of the tools available for use in spreadsheets is called conditional formatting, and it alters the appearance of cells when certain conditions are met.