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.