To convert the temperature in cell B2 in a Google spreadsheet from degrees Celsius to degrees Fahrenheit, what is the correct syntax for the CONVERT function?

• =CONVERT(B2, "Fahrenheit", "Celsius")
• =CONVERT(B2, "Celsius", "Fahrenheit")
• =CONVERT(B2, "C", "F")
• =CONVERT(B2, "F", "C")

Correct. In Google Sheets, to convert the temperature in cell B2 from degrees Celsius to degrees Fahrenheit, the correct syntax for the CONVERT function is =CONVERT(B2, "C", "F").

In this reading, you will learn about converting data from one format to another. One of the ways to help ensure that you have an accurate analysis of your data is by putting all of it in the correct format. This is true even if you have already cleaned and processed your data. As a part of getting your data ready for analysis, you will need to convert and format your data early on in the process.

As a data analyst, there are lots of scenarios when you might need to convert data in a spreadsheet:

• How to convert text to date in Excel: Transforming a series of numbers into dates is a common scenario you will encounter. This resource will help you learn how to use Excel functions to convert text and numbers to dates, and how to turn text strings into dates without a formula.
• Google Sheets: Change date format: If you are working with Google Sheets, this resource will demonstrate how to convert your text strings to dates and how to apply the different date formats available in Google Sheets.

• How to convert text to number in Excel: Even though you will have values in your spreadsheet that resemble numbers, they may not actually be numbers. This conversion is important because it will allow your numbers to add up and be used in formulas without errors in Excel.
• How to convert text to numbers in Google Sheets: This resource is useful if you are working in Google Sheets; it will demonstrate how to convert text strings to numbers in Google Sheets. It also includes multiple formulas you can apply to your own sheets, so you can find the method that works best for you.

• Convert text from two or more cells: Sometimes you may need to merge text from two or more cells. This Microsoft Support page guides you through two distinct ways you can accomplish this task without losing or altering your data. It also includes a step-by-step video tutorial to help guide you through the process.
• How to split or combine cells in Google Sheets: This guide will demonstrate how to to split or combine cells using Google Sheets specifically. If you are using Google Sheets, this is a useful resource to reference if you need to combine cells. It includes an example using real data.

• Format numbers as percentages: Formatting numbers as percentages is a useful skill to have on any project. This Microsoft Support page will provide several techniques and tips for how to display your numbers as percentages.
• TO_PERCENT: This Google Sheets support page demonstrates how to use the TO_PERCENT formula to convert numbers to percentages. It also includes links to other formulas that can help you convert strings.

Pro tip: Keep in mind that you may have lots of columns of data that require different formats. Consistency is key, and best practice is to make sure an entire column has the same format.

If you find yourself needing to convert other types of data, you can find resources on Microsoft Support for Excel or Google Docs Editor Help for Google Sheets.

Converting data is quick and easy, and the same functions can be used again and again. You can also keep these links bookmarked for future use, so you will always have them ready in case any of these issues arise. Now that you know how to convert data, you are on your way to becoming a successful data analyst.

• Add dropdown lists with predetermined options
  • Select column to add the dropdown menus to → (menu) Data - Data validation → Criteria: 'List of items', Enter items separated by a comma → Save
• Create custom checkboxes
  • Select column to add the checkbox → (menu) Data - Data validation → Criteria: 'Checkbox' → (option) Check 'Use custom cell values' → Enter items in checked/unchecked boxes
• Protect structured data and formulas
  • On Data validation menu, select 'Reject input' option

Data analysts usually need to convert data from one format to another to complete an analysis. But what if you are using SQL rather than a spreadsheet? Just like spreadsheets, SQL uses standard rules to convert one type of data to another. If you are wondering why data transformation is an important skill to have as a data analyst, think of it like being a driver who is able to change a flat tire. Being able to convert data to the right format speeds you along in your analysis. You don’t have to wait for someone else to convert the data for you.

In this reading, you will go over the conversions that can be done using the CAST function. There are also more specialized functions like COERCION to work with big numbers, and UNIX_DATE to work with dates. UNIX_DATE returns the number of days that have passed since January 1, 1970 and is used to compare and work with dates across multiple time zones. You will likely use CAST most often.

The following table summarizes some of the more common conversions made with the CAST function. Refer to Conversion Rules in Standard SQL for a full list of functions and associated rules.

CAST is an American National Standards Institute (ANSI) function used in lots of programming languages, including BigQuery. This section provides the BigQuery syntax and examples of converting the data types in the first column of the previous table. The syntax for the CAST function is as follows:

CAST(expression AS typename)

Where expression is the data to be converted and typename is the data type to be returned.

The following CAST statement returns a string from a numeric identified by the variable MyCount in the table called MyTable.

SELECT CAST(MyCount AS STRING) FROM MyTable

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table
• CAST indicates that you will be converting the data you select to a different data type
• AS comes before and identifies the data type which you are casting to
• STRING indicates that you are converting the data to a string
• FROM indicates which table you are selecting the data from

The following CAST statement returns an integer from a string identified by the variable MyVarcharCol in the table called MyTable. (An integer is any whole number.)

SELECT CAST(MyVarcharCol AS INT) FROM MyTable

n the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table
• CAST indicates that you will be converting the data you select to a different data type
• AS comes before and identifies the data type which you are casting to
• INT indicates that you are converting the data to an integer
• FROM indicates which table you are selecting the data from

The following CAST statement returns a string from a date identified by the variable MyDate in the table called MyTable.

SELECT CAST(MyDate AS STRING) FROM MyTable

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table
• CAST indicates that you will be converting the data you select to a different data type
• AS comes before and identifies the data type which you are casting to
• STRING indicates that you are converting the data to a string
• FROM indicates which table you are selecting the data from

Datetime values have the format of YYYY-MM-DD hh: mm: ss format, so date and time are retained together. The following CAST statement returns a datetime value from a date.

SELECT CAST(MyDate AS DATETIME) FROM MyTable

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table
• CAST indicates that you will be converting the data you select to a different data type
• AS comes before and identifies the data type which you are casting to
• DATETIME indicates that you are converting the data to a datetime value
• FROM indicates which table you are selecting the data from

Using the CAST function in a query that fails returns an error in BigQuery. To avoid errors in the event of a failed query, use the SAFE_CAST function instead. The SAFE_CAST function returns a value of Null instead of an error when a query fails.

The syntax for SAFE_CAST is the same as for CAST. Simply substitute the function directly in your queries. The following SAFE_CAST statement returns a string from a date.

SELECT SAFE_CAST(MyDate AS STRING) FROM MyTable

Browse these resources for more information about data conversion using other SQL dialects (instead of BigQuery):

• CAST and CONVERT: SQL Server reference documentation
• MySQL CAST Functions and Operators: MySQL reference documentation
• How to: SQL Type Casting: Blog about type casting that has links to other SQL short guides

TOTAL POINTS 3

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, C, F)
• =CONVERT(-22, F, C)
• =CONVERT(-22, "F", "C")

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

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
• Remove Validation
• Deny Help Text
• Reject Invalid Inputs

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.

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?

• CONVERT
• Data validation
• Filtering
• Conditional formatting

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