14.1 Analyzing Data with Spreadsheets: Cleaning, Transforming, and Visualizing
Key Takeaways
- Before analysis, data usually need cleaning: remove duplicates, correct entry errors and impossible values, standardize formats and units, handle missing values, and remove or protect personal information.
- Sorting a column puts the smallest and largest values at the ends, which makes improbably high or low values from data-entry errors easy to spot.
- Spreadsheet formulas such as SUM, AVERAGE, COUNTIF, IF, MAX, and MIN compute results that update automatically when the data change.
- Filters show only rows that meet a condition, sorts reorder rows, and pivot tables summarize data by group.
- Match the chart to the question: a line chart for trends over time, a bar chart for comparing categories, a scatter plot for relationships between two variables, and a histogram for distributions.
What this competency asks
ETS asks you to know how to use computational tools, including spreadsheets, to analyze data in order to discover, explain, and visualize patterns, connections, and trends:
- Transform data to make it more useful.
- Identify specific data, or characteristics of data, that need to be removed or modified before an entire data set can be used.
- Describe the use of spreadsheet operations (for example, formulas, filters, sorts, charts, and graphs) to analyze and visualize data.
ETS's sample question asks which spreadsheet function would be most useful for detecting improbably high or low values caused by data-entry errors. The answer is a sort, which moves extreme values to the top and bottom of the column where they are easy to see.
Step 1: Clean the data
Raw data are rarely ready to analyze. Look for:
| Problem | Example | Fix |
|---|---|---|
| Duplicates | The same survey response submitted twice | Remove duplicate rows |
| Entry errors and impossible values | Age 250; a test score of 950 on a 100-point test | Correct from the source, or exclude |
| Missing values | Blank cells in "hours studied" | Exclude those rows, fill in with care, or report them separately |
| Inconsistent formats | "9/1/2026," "Sept 1," and "2026-09-01"; "NY," "N.Y.," and "New York" | Standardize to one format |
| Inconsistent units | Heights in inches and centimeters mixed together | Convert to one unit |
| Wrong data type | Numbers stored as text, so they will not sum | Convert the type |
| Personal information | Names and student IDs in a data set being shared | Remove or anonymize them |
| Irrelevant or test rows | "TEST STUDENT" records | Remove |
Cleaning decisions affect conclusions, so document them. Removing every unusual value can hide real phenomena. An outlier may be an error, or it may be the most interesting data point.
Step 2: Transform the data
Transforming data means reshaping it to answer your question.
| Transformation | Purpose | Example |
|---|---|---|
| Filter | Keep only relevant rows | Only 9th-grade students |
| Sort | Order rows to see extremes or rankings | Highest to lowest score |
| Derive a new column | Compute a needed value | =C2/B2 for points per attempt; a percentage |
| Aggregate | Summarize groups | Average score by class period |
| Categorize (bin) | Group numeric values into ranges | Scores into A–F bands |
| Combine data sets | Link information from two sources | Match attendance to grades by student ID |
| Normalize or scale | Make values comparable | Convert raw points to percentages |
| Reshape | Change the layout | Pivot table: rows = teachers, columns = quarters |
Step 3: Analyze with spreadsheet operations
Formulas and functions
| Formula | Result |
|---|---|
=SUM(B2:B31) | Total of B2 through B31 |
=AVERAGE(B2:B31) | Mean |
=MAX(B2:B31), =MIN(B2:B31) | Largest and smallest values |
=COUNTIF(B2:B31, ">=70") | How many values are at least 70 |
=IF(B2>=70, "Pass", "Fail") | A label based on a condition |
=XLOOKUP(…) or =VLOOKUP(…) | Look up a value in another table |
Formulas recalculate automatically when data change. Copying a formula down a column adjusts relative references (B2 becomes B3). Absolute references such as $B$1 stay fixed, which is useful for a constant such as a passing score stored in one cell.
Filters, sorts, and conditional formatting
- Filter: temporarily hides rows that do not meet a condition. The data are not deleted.
- Sort: reorders rows by one or more columns. Sort the whole table together, not a single column, or rows become scrambled.
- Conditional formatting: colors cells that meet a rule, such as highlighting scores below 60.
Pivot tables
A pivot table summarizes a large table by groups, such as the average score for each teacher by quarter, without writing formulas for every group.
Step 4: Visualize
| Question | Best chart |
|---|---|
| How did something change over time? | Line chart |
| How do categories compare? | Bar or column chart |
| Is there a relationship between two numeric variables? | Scatter plot |
| How are values distributed? | Histogram |
| What are the parts of a whole? (few categories) | Pie chart, or a stacked bar |
Good visualizations label their axes and units, start numeric bar axes at zero, and avoid decoration that distorts the data.
Interpreting patterns carefully
- Correlation is not causation. Students who sleep more may score higher, but a scatter plot alone cannot show that sleep causes higher scores.
- Check sample size and representativeness before generalizing (Section 14.4).
- Aggregates can hide groups. An overall average can rise while every subgroup's average falls, if the mix of groups changes.
Classroom connection
Spreadsheets are an accessible entry to data science. Students can collect data about their own school, clean it, compute summaries with formulas, and chart trends, practicing abstraction (which columns matter?) and algorithmic thinking (what formula answers the question?) without writing a program.
A teacher suspects that a few scores in a 500-row spreadsheet were mistyped (for example, 890 instead of 89). Which operation makes these improbable values easiest to find?
A school wants to show how its average daily attendance changed month by month over three years. Which chart type is most appropriate?
Cells B2 through B6 contain the scores 88, 67, 70, 92, and 55. What does =COUNTIF(B2:B6, ">=70") return?
Before combining survey data from several schools, an analyst notices that one school recorded heights in centimeters and the others in inches. What should the analyst do?