Module 2: Clean Data for More Accurate Insights Answers (Part 3: Q31–46)

This is Part 3 of the Module 1 quiz answers for “Introducing Data Analytics and Analytical Thinking” from the Google Data Analytics Professional Certificate on Coursera.

Here, we’ll walk through questions 31 to 46 with detailed explanations to support your learning.

To find answers to the remaining questions, check out the full module breakdown below:

  • Part 1 (Q1–15)
  • Part 2 (Q16–30)
  • Part 3 (Q31–46)

31. 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 last four digits of the employee’s Social Security Number(SS#) plus the year hired. What function will create the ID 32082009 for the employee in row 5?

  • =CONCATENATE(B5,A5) ✅
  • =CONCATENATE(A5!B5)
  • =CONCATENATE(A5+B5)
  • =CONCATENATE(B5+A5)

Explanation:
The goal is to join the last 4 digits of the SS# (from B5) with the year hired (A5). CONCATENATE() merges both cell values into one string, creating the employee ID.

32. Before analyzing a dataset, an analyst maps the data. What is the reason for doing this?

  • The analyst wants to know what attributes the data has.
  • The analyst thinks the dataset might have some null values.
  • The dataset has no visualizations.
  • The dataset contains data from different sources. ✅

Explanation:
Data mapping ensures that fields from different datasets match up correctly, especially when integrating from multiple sources. It helps avoid misalignment in analysis.

33. 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?

  • Conditional ranking
  • Conditional formatting ✅
  • Cell querying
  • Cell filtering

Explanation:
Conditional formatting can apply visual cues (like color) to cells that meet a condition—such as being empty—making it easy to spot missing data.

34. 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?

  • The MID function
  • The SPLIT function ✅
  • Substring formatting
  • Conditional formatting

Explanation:
The SPLIT() function breaks a text string into separate parts using a delimiter (in this case, a comma). It’s ideal for separating lastname, firstname into distinct columns.

35. Fill in the blank: The function _____ is used to return information in a column that contains a specified value.

  • VALUE
  • MATCH
  • VLOOKUP ✅
  • FIND

Explanation:
The VLOOKUP function searches for a value in the first column of a range and returns data from the corresponding row in a specified column.

36. In a spreadsheet, what function would you use to extract the last three characters of the string located in row 4, column C?

  • =RIGHT(3,C4)
  • =LEFT(C4,3)
  • =LEFT(3,C4)
  • =RIGHT(C4,3) ✅

Explanation:
RIGHT(C4,3) grabs the last 3 characters from the string in C4.

37. 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 _____.

  • conditional formatting ✅
  • cell querying
  • cell filtering
  • conditional ranking

Explanation:
This tool visually highlights data that meets a rule or condition, like coloring negative values red or highlighting top performers.

38. 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?

  • =COUNTIF(B2:B100,”<9″)
  • =COUNTIF(B2:B100,”>=10”) ✅
  • =COUNTIF(B2:B100,>50)
  • =COUNTIF(B2:B200, ”<=50”)

Explanation:
COUNTIF() counts how many cells meet a condition. ">=10" checks for values 10 and above. Note the quotation marks around the condition.

39. A data analyst is adding new data to the dataset. This data comes from a different source than the current data. What should the data analyst do to ensure the compatibility of the current and new data?

  • Use a data visualization
  • Map the data ✅
  • Spot check for null values
  • Apply a data structure

Explanation:
Mapping ensures that fields like “Customer ID” from both datasets refer to the same concept, making integration and analysis accurate.

40. 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 specify where to split a text string ✅
  • To split duplicate substrings
  • To format a string to numeric
  • To change the format of a column of text

Explanation:
The delimiter tells the tool where to break the text into new columns—such as splitting Smith, John into Smith and John.

41. You are working with the following selection of a spreadsheet:

In order to extract the five-digit postal code from North Wales, PA, what is the correct function?

  • =LEFT(5,B2)
  • =LEFT(B2,5)
  • =RIGHT(5,B2)
  • =RIGHT(B2,5) ✅

Explanation:
This extracts the 5 digits at the end of the string in cell B2, which is likely the zip code.

42. 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 20142683 for the employee in row 3?

  • =CONCATENATE(A3*B3)
  • =CONCATENATE(A3,B3) ✅
  • =CONCATENATE(A3+B3)
  • =CONCATENATE(A3!B3)

Explanation:
This merges the year and last 4 SS# digits into a single string like 20142683.

43. To evaluate how well two or more data sources work together, data analysts use data mapping.

  • True ✅
  • False

Explanation:
Yes, data mapping checks whether fields across sources align, helping ensure consistency and accuracy when merging data.

44. 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. Choose the statement that includes the correct syntax for this COUNTIF function?

  • =COUNTIF(B2:B300,”>50″) ✅
  • =COUNTIF(B2:B300,”<50”)
  • =COUNTIF(B2:B300,>50)
  • =COUNTIF(B2:B300,<=50)

Explanation:
The COUNTIF function counts cells within a range that meet a specified condition. The condition must be enclosed in quotation marks when using operators like > or <.

45. Fill in the blank: Data mapping is the process of _____ fields from one data source to another.

  • matching ✅
  • extracting
  • inserting
  • transforming

Explanation:
Data mapping involves linking or matching fields from one dataset to corresponding fields in another, ensuring proper alignment for analysis or integration.

46. 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) ✅

Explanation:
The CONCATENATE function combines text from multiple cells. Here, it merges the year from A4 with the last four digits of the SS# in B4.

Part 3 (Q16–30)

Congratulations! You’ve completed all questions. Share this post if it helped you, and check out other Coursera quiz answers below.

Related contents:

Module 1: The importance of integrity
Module 3: Data cleaning with SQL
Module 4: Verify and report on cleaning results
Module 6: Course wrap-up

You might also like:

Course 1: Foundations: Data, Data, Everywhere
Course 2: Ask Questions to Make Data-Driven Decisions
Course 3: Prepare Data for Exploration
Course 5: Analyze Data to Answer Questions
Course 6: Share Data Through the Art of Visualization
Course 7: Data Analysis with R Programming
Course 8: Google Data Analytics Capstone: Complete a Case Study

Leave a Reply