Sparkling-Clean Data introduction

Test your knowledge on Clean versus dirty data

1. Describe the difference between a null and a zero in a dataset.

2. What are the most common processes and procedures handled by data engineers? Select all that apply.

3. What are the most common processes and procedures handled by data warehousing specialists? Select all that apply.

4. A data analyst is cleaning a dataset. They want to confirm that users entered five-digit zip codes correctly by checking the data in a certain spreadsheet column. What would be most helpful as the next step?

5. Review the final product of the spreadsheet you cleaned during this activity. Which of the following is the rightmost column?

Test your knowledge on data-cleaning techniques

1. Fill in the blank: Every database has its own formatting, which can cause the data to seem inconsistent. Data analysts use the _____ tool to create a clean and consistent visual appearance for their spreadsheets.

2. What is the process of combining two or more datasets into a single dataset?

3. Fill in the blank: In data analytics, _____ describes how well two or more datasets are able to work together.

4. Which of the following functions divides text around a specified character or string and puts each fragment of text into a separate cell in the row?

Test your knowledge on Cleaning data in spreadsheets

1. Describe the relationship between a text string and a substring.

2. A data analyst uses the COUNTIF function to count the number of times a value less than 5 occurs between spreadsheet cells A2 through A100. What is the correct syntax?

3. Fill in the blank: To remove leading, trailing, and repeated spaces in data, analysts use the ____ function.

Process Data from Dirty to Clean Weekly Challenge 2

1. Conditional formatting is a spreadsheet tool that changes how cells appear when values meet a specific condition. Data analysts can use conditional formatting to do which of the following tasks? Select all that apply.

2. A data analyst uses the SPLIT function to divide a text string around a specified character and put each fragment into a new, separate cell. What is the specified character separating each item called?

3. For a function to work properly, data analysts must follow each function’s predetermined structure. What is this structure called?

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

In order to extract the five-digit postal code from Brandon, FL, what is the correct function?

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

6. A data analyst at an e-commerce company is working with a spreadsheet containing last month’s sales. The most expensive product their company sells costs $49.99, so they want to quickly confirm that all of the data in the Sales column is $49.99 or less. What function can they use?

7. The V in VLOOKUP stands for what?

8. Data and business objectives might not align for a number of reasons. Which of the following issues can prevent alignment? Select all that apply.

9. 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. Which of the following COUNTIF function syntaxes could be used to answer this question? Select all that apply.

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

11. Fill in the blank: When describing a SUM function, the _____ is =SUM(value 1 through value 2).

12. VLOOKUP searches for a value in a row in order to return a corresponding piece of information.

13.  A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible?

14. A data analyst wants to search for a certain value in a column, then return a corresponding piece of information. Which function should they use?

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

16.  An analyst is working on a project involving customers from Bogota, Colombia. They receive a spreadsheet with 5,000 rows of customer information. What function can they use to confirm that the column for City contains the word Bogota exactly 5,000 times? 

17. Fill in the blank: Conditional formatting is a spreadsheet tool that changes how _____ appear when values meet a specific condition. 

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

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

20. A data analyst is using a function in a spreadsheet. When they input the function, they follow a predetermined structure that includes all required information for the function and its proper placement. What aspect of a function does this describe?

21. As part of the data-cleaning process, a data analyst creates a rule to highlight any empty cells in a bright blue color. This is an example of data visualization.

22. A data analyst is working on a spreadsheet in which one of the columns contains name data. This data is formatted as lastname_firstname. The analyst splits this data at the underscore so that each piece—firstname and lastname—are contained in their own columns.

In this context, what is the underscore acting as?

23. In a spreadsheet, what is the correct function for extracting the first two characters of the string located in cell A7?

24. A data analyst in a human resources department is working with the following selection of a spreadsheet:

N/A	A	B	C	D
1	Year Hired	Last 4 of SS#	Department	Employee ID
2	2019	1192	Marketing
3	2014	2683	Operations
4	2020	1939	Strategy
5	2009	3208	Graphics

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 26832014 for the employee in row 3?

25. An analyst is cleaning a new dataset. They want to make sure the data contained from cell C4 through cell C350 contains only numbers below 40. Choose the statements that include the correct syntax for this COUNTIF function. Select all that apply.

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

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

28. Fill in the blank: A _____ is a specified text that the SPLIT function uses to determine where a text string is to be divided.

29. An analyst is working on a project involving customers from Bogota, Colombia. They receive a spreadsheet with 5,000 rows of customer information. What function can they use to confirm that the column for City contains the word Bogota exactly 5,000 times?

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

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

32. A data analyst is using a function in a spreadsheet. For the function to work correctly, they follow the function’s syntax. What does this entail?

33.  A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible prior to analyzing the data?

Sparkling-Clean Data CONCLUSION