Module 2: Format and adjust data

Looking answers for ‘analyze data to answer questions module 2 challenge’?

In this post, I provide accurate answers and detailed explanations for Module 2: Format and adjust data of Course 5: Analyze Data to Answer Questions Google Data Analytics Professional Certificate.

Whether you’re preparing for quizzes or brushing up on your knowledge, these insights will help you master the concepts effectively. Let’s dive into the correct answers and detailed explanations for each question.

Test your knowledge on converting and formatting data

Practice Quiz

1. A spreadsheet cell contains the coldest temperature ever recorded in New Zealand: -22 °Celsius. What function will display that temperature in Fahrenheit?

  • =CONVERT(-22, F, C)
  • =CONVERT(-22, “C”, “F”) ✅
  • =CONVERT(-22, C, F)
  • =CONVERT(-22, “F”, “C”)

Explanation:
The CONVERT function in spreadsheets is used to convert a value from one unit to another. In this case, the value -22 represents the temperature in Celsius, and we need to convert it to Fahrenheit.

  • -22 is the temperature in Celsius.
  • "C" specifies the Celsius unit as the input.
  • "F" specifies Fahrenheit as the target unit. Thus, the correct formula to convert -22°C to Fahrenheit is =CONVERT(-22, "C", "F").

2. A data analyst wants to ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake. Which data-validation menu option should they select to flag data entry errors?

  • Forbid Entry
  • Reject Invalid Inputs ✅
  • Remove Validation
  • Deny Help Text

Explanation:
The Reject Invalid Inputs option in data validation ensures that any data entered that doesn’t meet the predefined criteria is rejected, helping maintain data integrity. This option flags data entry errors by not allowing incorrect data to be entered into the cell, which helps ensure that formulas continue to work correctly.

3. A data analyst clicks on the Format Cells in drop-down menu and selects the option Text Is Exactly November. This changes the color of all the cells that contain the word November. What spreadsheet tool is the analyst using?

  • Filtering
  • CONVERT
  • Conditional formatting ✅
  • Data validation

Explanation:
The tool being used is Conditional formatting. This feature allows users to apply formatting rules based on cell content. In this case, the rule is that if the text in a cell is exactly “November,” the cell’s formatting (e.g., color) will change. Conditional formatting is often used to highlight or change the appearance of cells that meet specific criteria.

Test your knowledge on combining multiple datasets

Practice Quiz

4. Fill in the blank: In SQL, _____ is a function that can be used to combine groups of text strings in multiple cells in order to create a new string.

  • COMBINE
  • CONCAT ✅
  • CONNECT
  • IMPORT RANGE

Explanation:
In SQL, the CONCAT function is used to combine multiple text strings into a single string. This function allows you to concatenate strings from different columns or values to create a new combined string.

  • COMBINE and CONNECT are not standard SQL functions.
  • IMPORT RANGE is used in Google Sheets, not SQL.

5. You are working with a database table that contains data about playlists for different types of digital media. You are only interested in the first 4 playlists.

You write the SQL query below. Add a LIMIT clause that will return only the first 4 playlists.

What playlist appears in row 2 of your query result?

  • Music
  • Audiobooks
  • TV Shows
  • Movies ✅

Explanation:
To limit the query to the first 4 rows, you would modify the query to include the LIMIT clause:

SELECT *
FROM playlist
LIMIT 4;

The result will return the first 4 rows of data from the playlist table. Since the query asks for row 2, assuming the playlists are ordered by the default sorting (or no specific sorting is applied), Audiobooks would be the playlist in row 2.

6. What function can be used to return the number of characters in cell B8 so you can confirm that it contains exactly 20 characters?

  • =LEN(B8) ✅
  • =LEN(B8, 20)
  • =LEN(20)
  • =LEN(20, B8)

Explanation:
The LEN function returns the number of characters in a cell. In this case, =LEN(B8) will return the number of characters in cell B8. If you want to confirm that B8 contains exactly 20 characters, this formula will give you that count.

  • =LEN(B8, 20) and =LEN(20) are incorrect because LEN only takes one argument: the cell or text to measure.
  • =LEN(20, B8) is also incorrect because the syntax does not match the function’s requirements.

Module 2 challenge

Graded Quiz

7. An analyst notes that the “160” in cell A9 is formatted as text, but it should be Australian dollars. What spreadsheet tool can help them select the right format?

  • Format as Dollar
  • EXCHANGE
  • CURRENCY
  • Format as Currency ✅

8. You are creating a spreadsheet to help you with your job search. Every time you find an interesting job, you add it to the spreadsheet. Then, you want to indicate two possible options: Need to Apply or Applied. What spreadsheet tool will save you time by enabling you to create a dropdown list with Need to Apply and Applied as the possible options?

  • Pop-up menus
  • Data validation ✅
  • Find
  • Conditional formatting

9. You are using a spreadsheet to keep track of your newspaper subscriptions. You add color to indicate if a subscription is current or has expired. Which spreadsheet tool changes how cells appear when values meet each expiration date?

  • Data validation
  • CONVERT
  • Conditional formatting ✅
  • Add color

10. You are analyzing data about the capitals of different countries. In your SQL database, you have one column with the names of the countries and another column with the names of the capitals. What function can you use in your query to combine the countries and capitals into a new column?

  • GROUP
  • CONCAT ✅
  • JOIN
  • COMBINE

11. You are querying a database of museums to determine which ones will have a sculpture exhibit this year. For your project, you only need the first 50 records. What clause should you add to the following SQL query?

SELECT museums
FROM museum_table
WHERE exhibit = “sculpture”

  • LIMIT 50
  • LIMIT,50
  • LIMIT = 50
  • LIMIT_50

12. A data analyst is working with a spreadsheet that has very long text strings. Rather than counting the characters themselves to determine the number of characters they contain, what tool can they use?

  • The MID function
  • The CHAR function
  • The LEN function ✅
  • The COUNT function

13. Spreadsheet cell L6 contains the text string “Function”. To return the substring “Fun”, what is the correct syntax?

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

14. When working with spreadsheets, data analysts can use the WHERE function to locate specific characters in a string.

  • True
  • False ✅

Explanation:
There is no WHERE function in spreadsheets to locate characters. WHERE is used in SQL queries to filter data based on specific conditions. In spreadsheets, functions like FIND or SEARCH are used to locate specific characters or substrings within a string

15. An analyst has financial data that is formatted as Canadian dollars, but it should be formatted as U.S. dollars. What spreadsheet tool can help them select the right format?

  • Format as Dollars
  • Format as Number
  • Format as Currency ✅
  • Format as Money

16. You are preparing a project tracker spreadsheet. Next to each project task, you need to add the name of the team member responsible. What spreadsheet tool will save you time by enabling you to create a drop-down list with team members’ names as the possible options?

  • Find
  • Conditional formatting
  • Pop-up menus
  • Data validation ✅

Explanation:
Data validation allows you to create a drop-down list of options in a cell, making it easy to select from predefined entries (e.g., team members’ names). This is the most efficient way to ensure consistency and reduce errors when entering data.

  • Find and Pop-up menus are not the right tools for creating drop-down lists.
  • Conditional formatting is used to change cell formatting based on conditions, not to create lists.

17. You are working with a SQL database that contains tables for the locations for a popular fast food restaurant. In this database, you have one column with the city location and another column with the state location for each restaurant. What function can you use in your query to combine the city and state into a new column?

  • COMBINE
  • CONCAT ✅
  • JOIN
  • GROUP

18. Fill in the blank: A data analyst is working with a spreadsheet that has very long text strings. They use the LEN function to count the number of _____ in the text strings.

  • substrings
  • characters ✅
  • values
  • fields

19. Spreadsheet cell H8 contains the text string “Marketing”. To return the substring “market”, what is the correct syntax?

  • =RIGHT(6,H8)
  • =LEFT(H8, 6) ✅
  • =RIGHT(H8, 6)
  • =LEFT(6,H8)

20. You are querying a database of restaurant locations to determine how many fast food companies have restaurants located in Texas. For your project, you only need the first 20 records. What clause should you add to the following SQL query?

SELECT fast_food
FROM restaurant_table
WHERE location = “Texas”

  • LIMIT,20
  • LIMIT_20
  • LIMIT 20 ✅
  • LIMIT = 20

Explanation:
The LIMIT 20 clause is used in SQL to restrict the number of records returned in the result set. In this case, it limits the result to the first 20 records.

  • LIMIT_20, LIMIT,20, and LIMIT = 20 are incorrect syntax for SQL.

21. A data analyst is working with a spreadsheet that has very long text strings. They use a function to count the number of characters in cell B9. What is the correct syntax of the function?

  • =LEN(B9) ✅
  • =LEN(“B9”)
  • =LEN(B,9)
  • =LEN(B:B9)

22. You are working with a data set that contains string data. Cell C4 contains the string “Oct 13, 2004”. What does the function FIND(“,”, C4) output?

  • 4 ✅
  • 6
  • 8
  • 7

23. An analyst notes that the “235” in cell B8 is formatted as text, but it should be Euros. What spreadsheet tool can help them select the right format?

  • Format as Euros
  • Format as Money
  • Format as Number
  • Format as Currency ✅

24. A utility company uses a spreadsheet to track the number of consecutive months each customer has paid their bill on time. They use a spreadsheet tool to apply color to the cells when the number of consecutive months is 12 or greater. What tool are they using?

  • Data validation
  • Add color
  • CONVERT
  • Conditional formatting ✅

Explanation:
Conditional formatting is used to apply formatting, like colors, to cells based on certain conditions. In this case, the formatting is applied when the number of consecutive months is 12 or more.

  • Data validation is used for input constraints, not for formatting cells based on values.
  • CONVERT is a function for changing units of measurement, not for formatting.
  • Add color is not a specific tool in spreadsheets.

25. Spreadsheet cell F2 contains the text string “Dashboard”. To return the substring “board”, what is the correct syntax?

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

26. You are using the FIND function to identify the position of the whitespace in the string in cell A6. Which of the following is the correct function syntax for this purpose?

  • FIND(“_”, A6)
  • FIND(A6, _ )
  • FIND(A6, “ “)
  • FIND(“ “, A6) ✅

27. You are analyzing employee data for your company. In your SQL database, you have one column with the first names of the employees and another column with their last names. What function can you use in your query to combine the employee first names and last names into a new column?

  • CONCAT ✅
  • COMBINE
  • JOIN
  • GROUP

28. An analyst is working with a dataset of financial data. The data is formatted as U.S. dollars, and the analyst needs it to be in Japanese yen. What spreadsheet tool can help them select the right format?

  • Format as Currency ✅
  • Format as Money
  • Format as Number
  • Format as Yen

29. Which of the following are appropriate uses for a spreadsheet’s data validation tool? Select all that apply.

  • Avoiding invalid inputs to functions ✅
  • Adding drop down menus on cells ✅
  • Merging two or more columns.
  • Protecting structured data ✅

30. You are working with a spreadsheet that records the running time of various songs. What spreadsheet tool can you use to change how the cells appear when their value is less than 20 seconds?

  • CONVERT
  • Data validation
  • Conditional formatting ✅
  • Add color

31. A data analyst wants to write a SQL query to combine data from two columns and into a new column. What function can they use?

  • GROUP 
  • CONCAT ✅
  • JOIN
  • COMBINE

Explanation:
The CONCAT function is used in SQL to combine or concatenate the values from two or more columns into one.

  • COMBINE is not a standard SQL function.
  • JOIN is used to combine tables, not columns.
  • GROUP is used to group rows in SQL queries, not for combining columns.

32. Fill in the blank: When working with spreadsheets, data analysts can use the _____ function to locate specific characters in a string.

  • IDENTIFY
  • FROM
  • WHERE
  • FIND ✅

33. A data analyst at a symphony orchestra uses a spreadsheet to keep track of how many concerts require more than 80 musicians. They use a spreadsheet tool to change how cells appear when values equal 80 or more. What tool are they using?

  • CONVERT
  • Add color
  • Conditional formatting ✅
  • Data validation

34. A data analyst is working with a spreadsheet that has very long text strings. They use a function to count the number of characters in cell G11. What is the correct syntax of the function?

  • =LEN(“G11”)
  • =LEN(G11) ✅
  • =LEN(G,11)
  • =LEN(G:G11)

35. Spreadsheet cell C2 contains the text string “Deviation”. To return the substring “Dev”, what is the correct syntax?

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

36. When working with spreadsheets, data analysts use the find function to locate specific characters in a string. Find is case-sensitive, so it’s necessary to input the substring exactly how it appears.

  • True ✅
  • False

37. You are querying a database of keynote speakers to determine who has expertise in zoology. For your project, you only need the first 12 records. What clause should you add to the following SQL query?

SELECT speakers
FROM keynote_table
WHERE expertise = “zoology”

  • LIMIT,12
  • LIMIT_12
  • LIMIT 12 ✅
  • LIMIT = 12

38. A data analyst is working with a spreadsheet that has very long text strings. They use a function to count the number of characters in cell K98. What is the correct syntax of the function?

  • =LEN(“K98”)
  • =LEN(K,98)
  • =LEN(K:K98)
  • =LEN(K98) ✅

Explanation:
The LEN function counts the number of characters in a text string. =LEN(K98) is the correct syntax for counting the characters in cell K98.

  • =LEN("K98") is incorrect because the formula needs to reference the cell (K98), not the text string "K98".
  • =LEN(K:K98) and =LEN(K,98) are also incorrect formats.

39. You are using a spreadsheet to organize a list of upcoming home repairs. Column A contains the list of repairs, and column B notes the priority of each item on the list: High Priority or Low Priority. What spreadsheet tool can you use to create a drop-down list of priorities for each cell in column B?

  • Pop-up menus
  • Data validation ✅
  • Find
  • Conditional formatting

40. You are querying a database of automobile sales to determine how many SUV models were available in green. For your project, you only need the first 50 records. What clause should you add to the following SQL query? SELECT SUV FROM vehicle_table WHERE color = “green”

  • LIMIT_50
  • LIMIT,50
  • LIMIT 50 ✅
  • LIMIT = 50

41. Spreadsheet cell B5 contains the text string “Fixed Price”. To return the substring “Price”, what is the correct syntax?

  • =LEFT(B5, 7)
  • =LEFT(7,B5)
  • =RIGHT(B5, 5) ✅
  • =RIGHT(7,B5)

Explanation:
The RIGHT function extracts a specified number of characters from the right end of a string. In this case, we want the last 5 characters from cell B5, which is “Price.” Therefore, =RIGHT(B5, 5) will return the substring “Price.”

  • =LEFT(B5, 7) would return “Fixed Pr” (the first 7 characters), which is not what we’re looking for.

42. An analyst has financial data that is formatted as Euros, but it should be formatted as Pesos. What spreadsheet tool can help them select the right format?

  • Format as Peso
  • Format as Currency
  • Format as Money
  • Format as Euros

Explanation:
To change the currency format, the “Format as Currency” option is the appropriate tool. This will allow you to choose the currency symbol you need (in this case, Pesos), ensuring the correct format for the financial data.

  • Format as Peso is not a valid option in most spreadsheet tools.
  • Format as Money is also not the correct option.
  • Format as Euros would not help in this case, as the goal is to change from Euros to Pesos.

Leave a Reply