Why clean data enables analysis in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

🎯 Scenario

intermediate

Identify the impact of inconsistent date formats on analysis

You have a sales data sheet where the Date column contains dates in mixed formats: some as MM/DD/YYYY, others as text like January 5, 2024. What problem will this cause when you try to calculate monthly sales totals?

A The totals will be correct but the chart colors will be wrong.

B The sheet will automatically convert all dates correctly, so no problem occurs.

C The monthly totals will be incorrect because some dates won’t be recognized as dates.

D The sheet will delete rows with text dates automatically.

💡 Hint

Think about how formulas like SUMIF or PIVOT TABLE use dates.

Mixed date formats cause some dates to be treated as text, so formulas that rely on date values miss those rows, leading to wrong totals.

📊 Formula Result

intermediate

Result of cleaning text with TRIM and PROPER

Given cell A1 contains the text " john DOE " (with extra spaces and mixed case), what is the result of the formula =PROPER(TRIM(A1))?

A "JOHN DOE"

B " John DOE "

C "john doe"

D "John Doe"

💡 Hint

TRIM removes extra spaces, PROPER capitalizes the first letter of each word.

TRIM removes leading and trailing spaces, PROPER capitalizes first letters, so the output is "John Doe".

❓ Function Choice

advanced

Choose the best function to find duplicates in a list

You want to highlight duplicate entries in a column of product IDs. Which function can you use in a new column to mark duplicates with TRUE?

A =IF(A2=A1, TRUE, FALSE)

B =COUNTIF(A:A, A2)>1

C =VLOOKUP(A2, A:A, 1, FALSE)

D =ISERROR(MATCH(A2, A:A, 0))

💡 Hint

Think about counting how many times the current value appears in the whole column.

COUNTIF counts occurrences of the value; if more than 1, it is a duplicate. Other options either compare only adjacent cells or return errors.

❓ data_analysis

advanced

Effect of missing data on average calculation

You have a column of test scores with some empty cells. What happens if you use =AVERAGE(B2:B10) without cleaning the data?

A The average ignores empty cells and calculates correctly.

B The formula returns an error because of empty cells.

C Empty cells are treated as zeros, lowering the average incorrectly.

D The formula counts empty cells as ones, raising the average.

💡 Hint

Check how AVERAGE treats empty cells by default.

AVERAGE ignores empty cells automatically, so the result is correct without cleaning.

🧠 Conceptual

expert

Why consistent data types matter for pivot tables

When creating a pivot table from a dataset, why is it important that all values in a column have the same data type (e.g., all numbers or all text)?

A Because mixed data types cause pivot tables to group data incorrectly or show errors.

B Because pivot tables only work with text data, not numbers.

C Because pivot tables automatically convert all data to dates regardless of type.

D Because pivot tables ignore columns with mixed data types completely.

💡 Hint

Think about how pivot tables group and summarize data.

Mixed data types confuse pivot tables, causing wrong grouping or errors. Consistent types ensure correct summaries.