6. Filtering and Exploring Data

Start with a trustworthy data table

A rectangular data table provides the structure for most spreadsheet analysis. Rows represent observations, such as orders or survey responses, while columns represent variables, such as date, region, product, quantity, or price. Each cell contains one value for one observation and one variable.

Before analyzing the table:

• Use clear and unique column names.

• Confirm that dates are stored as dates and numbers as numbers.

• Standardize category labels so values such as West, west, and West are not unintentionally treated as different categories.

• Check for missing, unusual, or duplicated values.

• Preserve an unchanged copy of the original data.

A useful mental model is to separate the data from the operations performed on it. The original table should remain available while filtering, sorting, transformations, calculations, and charts are created as separate analysis steps.

Takeaway: Reliable analysis begins with a well-structured table, consistent values, and a preserved raw dataset.

Filter and sort without losing context

Filtering narrows the records shown by applying conditions such as region equals West, units greater than or equal to $3$, or a date within a specified period. Multiple conditions can be combined with logical operators. For example, Product = A AND Units > 2 is different from Region = East OR South.

Record the exact condition rather than describing it vaguely. A rule such as Units >= 3 is reproducible; “large orders” is not. Also record how many rows remain and whether blank or unknown values were excluded. Hidden rows are not deleted rows, so a filtered view should not be mistaken for a changed dataset.

Sorting rearranges rows to make comparisons easier. It can reveal the largest values, earliest dates, latest dates, or possible outliers. A multilevel sort first applies a primary order and then a secondary order within each primary group. For example, records can be sorted by region from A to Z and then by units from largest to smallest.

When sorting, select the complete table. Sorting one column alone can detach values from their records. Check that numbers are not stored as text, that dates use the intended regional convention, and that leading spaces or inconsistent capitalization do not affect the order.

Takeaway: Filtering chooses which records are visible; Sorting changes their order. Neither operation should silently alter or disconnect the original records.

Group records and calculate meaningful summaries

Grouping creates categories for comparison, whereas sorting only places existing records in an order. Records can be grouped by region, month, grade level, or an age interval. A PivotTable can place a category such as Region in a row area and a measure such as Units in a values area. Programming tools can perform the same general process through split, apply, and combine steps.

After groups are created, an aggregation summarizes the values within each group. Common choices include:

• COUNT: number of records.

• SUM: total quantity or amount.

• AVERAGE or MEAN: arithmetic average.

• MEDIAN: middle value, often useful when extreme values exist.

• MIN and MAX: smallest and largest values.

For the example orders, revenue is calculated with $\text{Revenue} = \text{Units} \times \text{Unit Price}$. The three revenues are $\$48$, $\$40$, and $\$36$, giving a total of $\$124$.

An ordinary average gives each record equal weight. A weighted average is more appropriate when records contain different numbers of units. For prices, use $\text{Weighted average price} = \frac{\text{Total revenue}}{\text{Total units}}$. The right summary depends on the question being asked, the units of measurement, and the denominator.

Takeaway: Group first when comparisons depend on categories, then choose an aggregation that matches the question.

Explore patterns while avoiding misleading conclusions

Tables provide exact values, while charts help reveal patterns. Match the visual tool to the question:

• A bar chart compares categories, such as total units by region.

• A line chart examines change over time.

• A histogram shows the distribution of a numeric variable.

• A scatter plot examines the relationship between two numeric variables.

• A box plot compares distributions and can highlight possible outliers.

• A heat map displays values across two categorical dimensions.

Exploratory data analysis should combine visual inspection with numerical summaries. A pattern is a clue, not automatically an explanation. For example, if sales and advertising rise together, the chart shows an association but does not by itself prove that advertising caused the sales increase.

Avoid misleading displays and comparisons:

• State which regions, dates, or categories are included.

• Report counts alongside percentages or rates when group sizes differ.

• Compare subgroup summaries when a single average could conceal important differences.

• Investigate unusual values before removing them; an extreme value may be an error or a legitimate event.

• Label and explain truncated or logarithmic axes.

• Keep full precision during calculations and round only the final presentation.

• Use rates or per-unit measures when raw totals compare groups of different sizes.

Takeaway: Choose a chart that fits the data, show enough context to interpret it, and distinguish observed association from proven cause.

Build a reproducible analysis workflow

A reproducible workflow makes the same result obtainable by following the same documented steps. A practical sequence is:

1. Preserve an unchanged copy of the raw data.

2. Define the question and the population being studied.

3. Inspect column names, units, data types, duplicates, missing values, and suspicious values.

4. Standardize labels or formats carefully, and document each change.

5. Keep filtering and other transformations separate from the raw data.

6. Calculate summaries while recording the formula, aggregation, denominator, and units.

7. Create clearly labeled visualizations that identify categories, time periods, and sample sizes.

8. Save filter conditions, formulas, query text, software settings, and the analysis date.

9. Validate the result by comparing totals before and after transformations and inspecting sample records.

10. Report exclusions and explain why records were omitted.

Consider a transportation comparison with late arrivals. First exclude test records marked training = yes, then record the number of valid records. Create a late indicator equal to $1$ when the arrival delay is greater than five minutes and $0$ otherwise. Group by route and calculate the number of trips, the number of late trips, and the late-trip rate:

A route with $8$ late trips out of $10$ has a rate of $80\%$, while a route with $80$ late trips out of $1{,}000$ has a rate of $8\%$. Reporting both counts and rates prevents route size from distorting the comparison.

Takeaway: Preserve the raw data, document every important choice, validate transformations, and report both results and exclusions.