Study guide
14.1 Analyzing Data with Spreadsheets: Cleaning, Transforming, and Visualizing
Praxis Computer Science 5652
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Praxis 5652 Exam Overview and ETS Pseudocode
1.1 Praxis 5652 Test Facts, Scoring, and Registration 1.2 Official ETS Pseudocode Notation and Tracing Strategy
2 Chapter 2: Impacts of Computing: Innovation, Access, and Effects
2.1 Computing for Creativity, Problem Solving, Communication, and Innovation 2.2 Equal Access, the Digital Divide, and Accessibility 2.3 Beneficial and Harmful Effects of Computing Innovations
3 Chapter 3: Impacts of Computing: Intellectual Property, Ethics, Privacy, and Security
3.1 Intellectual Property: Copyright, Open Source, and Creative Commons 3.2 Ethical and Unethical Computing Practices and Their Implications 3.3 Privacy, Security, Data Collection, and Local vs. Cloud Storage
4 Chapter 4: Algorithms and Computational Thinking: Abstraction, Decomposition, Number Bases, and Representation
4.1 Abstraction and Abstraction Hierarchies 4.2 Decomposition, Pattern Recognition, and Developing Algorithms 4.3 Number Bases: Binary, Octal, Decimal, and Hexadecimal 4.4 Algorithm Formats: Natural Language, Flowcharts, Pseudocode, and Tracing
5 Chapter 5: Algorithms: Efficiency, Limits of Computing, and Randomization
5.1 Algorithm Efficiency: Linear, Quadratic, Exponential, and Logarithmic Growth 5.2 Limits of Computing: Unsolvable Problems, Intractability, and Heuristics 5.3 Randomization, Random vs. Pseudorandom Numbers, and Scaling to a Range
6 Chapter 6: Algorithms: Searching, Sorting, and Recursion
6.1 Searching Algorithms: Linear Search and Binary Search 6.2 Sorting Algorithms: Tracing, Comparisons, Correctness, and Efficiency 6.3 Recursion: Base Cases, Tracing, Errors, and Iterative Equivalents
7 Chapter 7: Programming: Variables, Data Types, Strings, and Expressions
7.1 Variables, Data Types, and Type Conversion 7.2 Strings and String Operations 7.3 Operators, Precedence, Expressions, and Formulas 7.4 Boolean Expressions, Truth Tables, and Boolean Algebra
8 Chapter 8: Programming: Selection, Iteration, and Writing Code
8.1 Sequence and Selection: if, else, Chained and Nested Conditions 8.2 Iteration: for, while, do … while, and repeat … until Loops 8.3 Reading, Completing, and Modifying Code
9 Chapter 9: Programming: Procedures, Arrays, and Data Structures
9.1 Procedures, Parameters, Return Values, Nested Calls, and Scope 9.2 Arrays, Lists, and Two-Dimensional Arrays 9.3 Stacks, Queues, and Dictionaries (Maps)
10 Chapter 10: Programming: Correctness, Errors, Testing, and Code Quality
10.1 Program Correctness: Preconditions, Postconditions, Invariants, and Testing vs. Proof 10.2 Error Types and Debugging Techniques 10.3 Choosing Useful Test Cases: Boundaries, Partitions, Coverage, and Regression 10.4 Extensibility, Modifiability, and Reusability: Constants and Parameterization 10.5 Documentation, Code Reviews, Peer Feedback, and End-User Feedback
11 Chapter 11: Programming: Events, Usability, Input Validation, and APIs
11.1 Event-Driven Programming, Asynchronous Events, and Concurrency 11.2 Usability, Meaningful Error Messages, and Input Validation vs. Verification 11.3 Libraries and APIs: Reading Definitions, Making Calls, and Weighing Trade-offs
12 Chapter 12: Programming: Languages, Tools, Paradigms, and Object-Oriented Design
12.1 Low- and High-Level Languages, Compilers and Interpreters, and IDEs 12.2 Programming Paradigms: Procedural, Object-Oriented, and Others 12.3 Classes, Objects, Instance Variables, Methods, and Encapsulation 12.4 Inheritance, Polymorphism, and Overloading vs. Overriding
13 Chapter 13: Data: Bits, Encoding, Compression, and Encryption
13.1 Bits and Bytes: Why Binary, Storage Calculations, and Signed Integers 13.2 Encoding Text, Images, and Sound 13.3 Lossless and Lossy Compression: Run-Length Encoding, Huffman Coding, and Media Formats 13.4 Encoding vs. Encryption, Symmetric and Public-Key Encryption, and Their Trade-offs
14 Chapter 14: Data: Analysis Tools, Simulation, Storage, and Collection
14.1 Analyzing Data with Spreadsheets: Cleaning, Transforming, and Visualizing 14.2 Simulation and Modeling 14.3 Files, Databases, and Storage Requirements: Scale, Redundancy, and Backup 14.4 Collecting, Aggregating, and Generating Data: Public Data Sets, Surveys, Sensors, and Crowdsourcing
15 Chapter 15: Computing Systems: Hardware, Operating Systems, and Computing Environments
15.1 Hardware Components and the Fetch–Decode–Execute Cycle 15.2 Operating Systems, Software Components, and Resource Issues 15.3 Embedded Systems, the Internet of Things, and Types of Computing Systems 15.4 Local, Network, and Cloud Computing and Storage; How Binary Data Are Stored
16 Chapter 16: Networks, the Internet, the Web, and Cybersecurity
16.1 Network Types and Wired and Wireless Communication 16.2 Network Hardware and Layered Network Models (OSI and TCP/IP) 16.3 Network Performance: Bandwidth, Latency, Load, and Estimating Needs 16.4 How the Internet Works: Protocols, IP Addressing, DNS, URLs, and Reliability 16.5 The Web: HTTP, HTML and Markup Languages, Browsers, Servers, and Clients 16.6 Cybersecurity: The Five Pillars, Strong Passwords, Security Strategies, and Trade-offs
14.1 Analyzing Data with Spreadsheets: Cleaning, Transforming, and Visualizing
Key Takeaways Explain This Topic AI
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.
Last updated: September 2026
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.