Week 2: Formatting and adjusting data

1. 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

 

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

 

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
  • Add color

 

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?

  • GROUP
  • CONCAT
  • JOIN
  • COMBINE

 

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

 

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

 

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

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

  • True
  • False

9. 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

10. 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

11. 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

12. 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

13. 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)

14. 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

15. 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)

16. 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

17. 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

18. 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

19. 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)

20. 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)

21. 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

22. 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

23. 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

24. 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

25. 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

26. 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

27. 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

28. 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)

29. 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)

30. 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

31. 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

32. 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)

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

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

35. 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)

Leave a Reply