# Course 5 – Analyze Data to Answer Questions

## Organizing Data to Begin Analysis INTRODUCTION

Organizing and formatting data is a critical part of the data analysis process. In this section of Coursera’s Google Data Analytics Professional Certificate program, you’ll learn how to use SQL queries to combine different sets of data, as well as other methods for organizing and formatting your data.

You’ll also get all the support and feedback you need from your peers, which can be invaluable in helping you understand new concepts that can be applied to your own work. By the end of this course, you will understand how to organize and format data to be used in the analysis, enabling you to utilize the power of analytics fully.

## Learning Objectives

• Demonstrate an understanding of what is involved in the conversion and formatting of data
• Demonstrate an understanding of the use of spreadsheets and SQL queries to combine multiple pieces of data
• Discuss the importance of seeking feedback and support from others

## Hands-On Activity: Combine multiple pieces of data

### 1. In an empty cell in the CONCAT function exercise spreadsheet, type the function =CONCATENATE(A7, ” “, B7). What does this function return?

• #N/A

Correct: The function =CONCATENATE(A7, ” “, B7) would return “John Quincy Adams.” The correct function to use here would be CONCATENATE, which you can use to combine strings. Using CONCAT with these arguments would resolve this correctly in Microsoft Excel, but return an error in Google Sheets. Going forward, you can use this distinction to write proper spreadsheet functions.

## Test your knowledge on converting and formatting data

### 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, C, F)
• =CONVERT(-22, F, C)
• =CONVERT(-22, “C”, “F”) (Correct)
• =CONVERT(-22, “F”, “C”)

Correct: =CONVERT(-22, “C”, “F”) will display -22 °C in Fahrenheit.

### 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?

• Deny Help Text
• Reject Invalid Inputs (Correct)
• Remove Validation
• Forbid Entry

Correct: To ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake, select Reject Invalid Inputs to flag that data as invalid.

### 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
• Data validation
• Conditional formatting (Correct)

Correct: The data analyst is using conditional formatting. Conditional formatting is a spreadsheet tool that changes how cells appear when values meet specific conditions.

## Test your knowledge on combine multiple datasets

### 1. Fill in the blank: In SQL, _____ can be used to combine strings from multiple tables in order to create a new string.

• COMBINE
• CONCATENATE
• CONCAT (Correct)
• CONNECT

Correct: In SQL, CONCAT can be used to combine strings from multiple tables in order to create a create new string.

### What playlist appears in row 2 of your query result?

The Movies playlist appears in row 2 of your query result.

• Music
• Movies (Correct)
• Audiobooks
• TV Shows

Correct: The clause LIMIT 4 will return only the first 4 playlists. The complete query is SELECT * FROM playlist LIMIT 4. The LIMIT clause sets a limit on the number of rows your query returns.

### 3. 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(20, B8)
• =LEN(20)
• =LEN(B8) (Correct)
• =LEN(B8, 20)

Correct: The function =LEN(B8) will display the number of characters in cell B8. The LEN function returns the length of a string of text by counting the number of characters it contains.

Liking our content? Then don’t forget to add us to your bookmarks so you can find us easily!

## Analyze Data to Answer Questions Weekly Challenge 2

### 1. An analyst working for a British school system just downloaded a dataset that was created in the United States. The numerical data is correct but it is formatted as U.S. dollars, and the analyst needs it to be in British pounds. What spreadsheet tool can help them select the right format?

• CURRENCY
• Format as Currency (Correct)
• EXCHANGE
• Format as Pounds

Correct: Format as Currency can be used to select the right currency format.

### 2. 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?

• Conditional formatting
• Data validation (Correct)
• Find

Correct: Data validation can be used to add drop-down lists with predetermined options for each team member’s name.

### 3. 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 (Correct)

Correct: You are using conditional formatting. Conditional formatting changes how cells appear when values meet specific conditions.

### 4. 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?

• COMBINE
• GROUP
• CONCAT (Correct)
• JOIN

Correct: You can use CONCAT, which enables you to join multiple text strings from multiple sources.

### 5. 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?

• LIMIT 12 (Correct)
• LIMIT,12
• LIMIT = 12
• LIMIT_12

Correct: To return only the first 12 records, type LIMIT 12.

### 6. 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.

• values
• fields
• characters (Correct)
• substrings

Correct: They use the LEN function to count the number of characters in a text string.

### 7. Spreadsheet cell E13 contains the text string “Database”. To return the substring “Data”, what is the correct syntax?

• =LEFT(E13, 4) (Correct)
• =LEFT(4,E13)
• =RIGHT(4,E13)
• =RIGHT(E13, 4)

Correct: The function =LEFT(E13, 4) will return “Data” The LEFT function returns a set number of characters from the left side of a text string. In this case, it returns a four-character substring from the end of the string in E13, starting from the left.

### 8. When working with a spreadsheet, 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 (Correct)
• False

Correct: Find is case-sensitive, so it’s necessary to input the substring exactly how it appears.

### 9. Fill in the blank: A data professional at a junior college uses the spreadsheet _____ function to locate specific characters from a listing of course codes.

• IDENTIFY
• FROM
• FIND (CORRECT)
• WHERE

### 10. A data analyst works with a spreadsheet containing product information that often has very long text strings. To check for consistency, they use a function to count the number of characters in cell P12. What is the correct syntax of the function?

• =LEN(P,12)
• =LEN(P:12)
• =LEN(P:P12)
• =LEN(P12) (CORRECT)

### 11. You prepare a project tracker spreadsheet. Next to each project is the name of the team member responsible. What spreadsheet tool will create a drop-down list with team member names to save you time when assigning the projects?

• Data validation (CORRECT)
• Conditional formatting
• Find

### 12. An analyst works with a dataset of financial data that is formatted in U.S. dollars. However, it should be formatted as Japanese yen. What spreadsheet tool should they use to correct the format?

• Format as currency (CORRECT)
• Format as yen
• Format as money
• Format as amount

### 13. You use a SQL database to evaluate rideshare efficiency. The database table contains one column of pick-up locations and another column of drop-off locations. What function will combine the pick-up and drop-off locations into a new column?

• COMBINE
• GROUP
• JOIN
• CONCAT (CORRECT)

### 14. A data professional uses a spreadsheet to track the total purchases made by each member of the company’s procurement department. What spreadsheet tool can they use to change how the cells appear when someone has made more than \$5,000 in purchases?

• Conditional formatting (CORRECT)
• CONVERT
• Data validation

### 15. Fill in the blank: A junior data analyst working with interest rate data uses the SQL function _____ to adjust 6.23456789012345678901234567890% to just one decimal place.

• SHORTEN
• DECIMAL
• TRUNCATE
• ROUND (CORRECT)

### 16. Spreadsheet cell L2 contains the text string Function. To return the substring Fun, what is the correct syntax?

• =LEFT(L2, 3) (CORRECT)
• =RIGHT(3, L2)
• =LEFT(3, L2)
• =RIGHT(L2, 3)

### 17. A data analyst works with a spreadsheet containing medical record numbers that must be exactly 30 digits long. To ensure accuracy, they use a function to count the number of characters in cell H10. What is the correct syntax of the function?

• =LEN(30)
• =LEN(H10, 30)
• =LEN(H10) (CORRECT)
• =LEN(30, H10)

### 18. You use a spreadsheet to help you with an upcoming gift exchange. Every time you find an interesting gift idea, you add it to the spreadsheet. What spreadsheet tool will create a drop-down list with Need to Purchase and Purchased as the possible options?

• Conditional formatting
• Find
• Data validation (CORRECT)

### 19. An analyst works with financial data that is formatted as Euros, but it should be formatted as pesos. What spreadsheet tool should they use to correct the format?

• Format as Euros
• Format as money
• Format as currency (CORRECT)
• Format as peso

### 20. You use a SQL database to analyze airline flight routes in Africa. The database table contains one column of departure airports and another column of arrival airports. What function will combine the departure and arrival airport names into a new column?

• JOIN
• CONCAT (CORRECT)
• COMBINE
• GROUP

### 21. A data analyst at a symphony orchestra uses a spreadsheet to track how many concerts require more than 65 musicians. What spreadsheet tool can they use to change how cells appear when values equal 65 or more?

• Conditional formatting (CORRECT)
• Data validation
• CONVERT

### 22. Spreadsheet cell H9 contains the text string Tableau. To return the substring Tab, what is the correct syntax?

• =LEFT(3, H9)
• =RIGHT(H9, 3)
• =RIGHT(3, H9)
• =LEFT(H9, 3) (CORRECT)

### 23. Fill in the blank: A data analyst at a logistics company uses the spreadsheet _____ function to locate specific characters from a shipping confirmation email.

• IDENTIFY
• WHERE
• FROM
• FIND (CORRECT)

### 24. You use a spreadsheet to organize a list of upcoming tasks at work. Column A contains the list of tasks, and column B notes their priority: high, medium, or low. What spreadsheet tool will create a drop-down list of the priorities in column B?

• Data validation (CORRECT)
• Conditional formatting
• Find

### 25. A data analyst in human resources uses a spreadsheet to track employee work anniversaries. What spreadsheet tool can they use to add color to the cells containing the name of any employee who has worked for the business for at least five years?

• Conditional formatting (CORRECT)
• Data validation
• CONVERT

### 26. Fill in the blank: A data analyst analyzing the boiling points of various liquids uses the SQL function _____ to adjust 356.727801 °C to just three decimal places.

• DECIMAL
• ROUND (CORRECT)
• SHORTEN
• TRUNCATE

### 27.  You use a spreadsheet to help you with an upcoming gift exchange. Every time you find an interesting gift idea, you add it to the spreadsheet. What spreadsheet tool will create a drop-down list with Need to Purchase and Purchased as the possible options?

• Find
• Conditional formatting
• Data validation (CORRECT)

### 28. An analyst notes that the “85” in cell A3 is formatted as text, but it should be Australian dollars. What spreadsheet tool should they use to correct the format?

• Format as currency (CORRECT)
• Format as money
• Format as dollars
• Format as amount

### 29.  Fill in the blank: A data professional analyzing gasoline prices uses the SQL function _____ to adjust the number \$3.5698726 to just two decimal places.

• ROUND (CORRECT)
• DECIMAL
• TRUNCATE
• SHORTEN

## Organizing Data to Begin Analysis CONCLUSION

As you move closer to analyzing your data, it is important to have the data formatted and ready to go. In this part of the course, you will learn all about converting and formatting data, including how SQL queries can help you combine data.

You will also find out the value of feedback and support from your colleagues and how it can lead to new learning that you can apply to your work. Joining the Coursera community will allow you to access these resources and put them into practice in your own work.

#### Subscribe to our site

Get new content delivered directly to your inbox.

## Quiztudy Top Courses

Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!