Spreadsheets for Analysis — Excel and Google Sheets

Clean and analyse data in a spreadsheet: formulas, lookups, pivot tables, conditional formatting and charts, and when to move to SQL or Python.

Est. 40 min Updated Suggest a fix
// Part 01 — Start with the question

A Spreadsheet Is a Small Analytical Workspace

Spreadsheets are often the quickest way to inspect a small export, validate a metric, or answer a one-off question. Imagine an operations analyst asking whether late deliveries cluster by city or weekday. The goal is not to apply every feature in Excel or Google Sheets; it is to turn rows into a checked answer that someone can reproduce. Write the question, population, time period, and unit before you open a chart.

Treat each row as one observation and each column as one field. A useful table has a single header row, consistent types, and no merged cells or decorative totals inside the data range. Keep a raw sheet unchanged, do analysis in a separate sheet, and record filters and assumptions. This small bit of structure makes the workbook easier to audit.

A good analysis starts with a well-defined table and question.
Question to settleExampleWhy it matters
What is one row?One completed deliveryPrevents counting line items as orders
Which records count?Completed orders placed in the chosen monthMakes exclusions visible
What does a field mean?late_flag = delivery after promised timeAvoids silently changing definitions
What is the unit?Minutes, not secondsKeeps formulas and charts interpretable
💡 Note
A formula can be perfectly correct and still answer the wrong question. Define the row grain and inclusion rules before calculating totals or percentages.
// Part 02 — Prepare the data

Make the Table Rectangular and Check Its Quality

Convert the range to an Excel Table or use a clearly bounded range in Sheets. Tables expand when new rows are added and make formula references easier to read. Inspect blanks, duplicate IDs, inconsistent spellings, dates stored as text, and numeric values containing symbols. Do not delete an unusual observation just because it looks surprising; first determine whether it is an error or a genuine event.

Keep the original export in a read-only raw tab. Make a cleaned tab with explicit transformations, and keep a short notes area for source, export time, filters, and unresolved issues. If you normalize city names or convert a text date, retain enough information to explain what changed. A workbook is a deliverable, not just a scratchpad.

spreadsheet_quality_checks
text
Before analysis
- Confirm the header row is unique and contains no blank column names.
- Check whether one order_id appears more than once; determine why.
- Compare row count with the export or source-system summary.
- Inspect blank, error, and unexpected category values.
- Confirm date and number cells are real values, not text lookalikes.
- Record filters, exclusions, units, and the time window.
⚠️ Important
Never overwrite the source tab while cleaning. If a replacement rule is wrong, an untouched input lets you recover and compare the before-and-after counts.
// Part 03 — Formulas

Use Functions That Match the Business Question

Start with a small set of formulas: SUM, COUNT, COUNTA, AVERAGE, MIN, MAX, and IF. For conditional summaries, SUMIFS and COUNTIFS let you state the conditions next to the measure. A percentage is a numerator divided by a denominator; be explicit about whether the denominator is all orders, completed orders, or eligible orders.

formula_examples
text
Assume an Excel Table named Orders with columns: order_id, city, status,
order_value, promised_at, delivered_at.

=COUNTA(Orders[order_id])
=SUMIFS(Orders[order_value], Orders[status], "completed")
=COUNTIFS(Orders[city], "Hyderabad", Orders[status], "completed")
=IF([@delivered_at]>[@promised_at], "Late", "On time")

Late share = late completed orders / all completed orders

Structured table references expand with the table and describe what a formula uses. A cell reference such as A2 moves when copied; $A$2 is absolute; A$2 locks only the row. When copying a formula across a grid, test the first and last results instead of assuming the references moved as intended.

🎯 Pro Tip
Show counts beside rates. "12% late" means something different for 25 orders than for 25,000, and a visible denominator gives reviewers a quick sanity check.
// Part 04 — Lookups

Join a Reference Table Without Hiding Missing Matches

A lookup attaches an attribute from one table to another using a key: for example, adding a region to a city-level delivery table. In modern Excel, XLOOKUP can search a key range and return a corresponding value. In Sheets, XLOOKUP is available in current versions; older workbooks may use VLOOKUP orINDEX plus MATCH. Confirm compatibility before sharing a workbook across versions.

lookup_examples
text
With city in A2, a city-to-region table in H2:I20:

=XLOOKUP(A2, $H$2:$H$20, $I$2:$I$20, "Not found")

A VLOOKUP alternative when the key is the first column of H:I:
=IFERROR(VLOOKUP(A2, $H$2:$I$20, 2, FALSE), "Not found")

A missing match is a quality signal, not something to replace silently with the first region in the list. Check for leading spaces, inconsistent capitalization, alternate spellings, and whether the lookup key is unique. If one city maps to multiple service zones, city alone is not a valid key; use the actual business identifier or ask for a better mapping table.

  1. Check that the reference key has one row per key before looking up values.
  2. Use an explicit not-found result so gaps remain visible.
  3. Count unmatched rows and resolve them with the data owner.
  4. Keep the source and mapping table with the workbook or document their locations.
// Part 05 — Pivot tables

Summarise Groups Without Writing a Formula for Every Group

A pivot table groups records and calculates summaries, such as completed orders by city and weekday. Add a category to Rows, another to Columns if comparison is useful, and a numeric measure to Values. Check whether the measure is being summed, counted, or averaged; spreadsheets sometimes choose an unexpected default when a numeric field contains blanks or text. Apply filters deliberately and refresh the pivot after the source table changes.

Before interpreting a pivot, compare its grand total to the source records under the same filters. A pivot count of order IDs can differ from a count of rows when an order appears more than once. Distinguish records, distinct orders, and customers; they are not interchangeable units. Name the value field so a reader sees "Average delivery minutes" rather than an unexplained "Average of duration".

Start with one grouping dimension; add complexity only when it answers the question.
Pivot areaExample fieldAnalytical use
RowsCityCompare locations
ColumnsWeekdayLook for recurring differences
ValuesOrder ID as count; delay as averageShow volume and typical delay
FiltersStatus and monthRestrict to the defined population
📌 Real World Example
A low average delay in a city with very few deliveries may be unstable. Show the order count and investigate whether the city has complete coverage before ranking it against high-volume cities.
// Part 06 — Charts

Choose a Chart That Makes the Comparison Easy

Use a bar chart to compare categories, a line chart for a measure over ordered time, and a histogram to inspect the distribution of a numeric field. A scatter plot helps inspect the relationship between two numeric variables. Keep units in axis labels, use a clear title that states the measure and period, and avoid 3-D effects that distort comparisons. Do not use a pie chart when categories are numerous or close in size.

A chart communicates the filtered data, not the entire business. State the date range, inclusion rules, and whether values are counts, rates, or averages. For a time series, show missing periods rather than connecting across gaps as though measurements existed. Sort categories intentionally: alphabetical order, descending value, or a meaningful business sequence.

chart_review_checklist
text
Chart review
1. Does the chart answer the question written above it?
2. Are axes, units, dates and category names readable?
3. Is the denominator or group size available for rate comparisons?
4. Could a zero-baseline or axis range change the visual impression?
5. Are filters and missing periods disclosed?
6. Can a reader distinguish the takeaway without relying on colour alone?
🎯 Pro Tip
Use labels and a brief written takeaway alongside colour. This improves accessibility and still works when the chart is printed in grayscale or viewed on a small screen.
// Part 07 — Reproducibility

Make the Workbook Reviewable and Know When to Move On

Give tabs descriptive names such as Raw export, Cleaned, Pivot, and Findings. Freeze the header, turn on filters, use consistent date and currency formats, and avoid manually typing results that are already calculated elsewhere. Add a source note and a short explanation of each decision that could change the answer. Protect formula cells only when it helps prevent accidental edits; it is not a substitute for access controls or backups.

A spreadsheet is a good fit for a modest, clearly bounded analysis reviewed by a small team. Move to SQL when data lives in a database, the same query must run repeatedly, or joins and transformations become hard to maintain. Use Python when analysis needs repeatable multi-step cleaning, larger files, tests, or automated reporting. Keep the spreadsheet as a presentation or review surface when that is what collaborators need.

  • Keep raw inputs separate from transformations and results.
  • Record ownership, source, refresh date, filters, and unresolved quality issues.
  • Use one definition for every repeated metric and state the denominator.
  • Reconcile totals after filtering, joining or pivoting.
  • Use appropriate permissions before sharing customer or employee data.
// Part 08 — Pitfalls

Common Spreadsheet Analysis Mistakes

Deleting rows that look unusual without checking them

Why: An extreme value may be a genuine delivery or a measurement issue; removing it changes the population.

Fix: Flag the record, inspect its source, and document a defensible inclusion rule before excluding it.

Using a lookup with an approximate match unintentionally

Why: An approximate lookup can return a plausible but incorrect category when keys are not sorted or exact matching is needed.

Fix: Choose exact matching explicitly and count not-found results.

Reporting an average without its denominator or unit

Why: Readers cannot tell which records contributed or whether the value is minutes, hours, or currency.

Fix: Show count, unit, period, population, and whether the statistic is a mean or median.

Copying a pivot result into a report without refreshing it

Why: The displayed summary can be stale after the source data changes.

Fix: Refresh, reconcile totals, note the refresh date, and link or describe the source.

// Part 09 — Practice

Analyse a Small Delivery Export

Practice

Build a checked spreadsheet answer for operations

  1. Create a raw tab with order ID, city, order date, status, promised time, delivered time, and order value. Keep the raw tab untouched.
  2. Write a one-sentence question and define whether the population includes cancelled orders or only completed orders.
  3. Add a late/on-time formula and calculate completed order count, late count, and late share with a visible denominator.
  4. Use a lookup to attach region to city. Make missing mappings visible and report how many remain unmatched.
  5. Create a pivot by city with order count and average delay; compare the pivot grand total with a formula-based check.
  6. Build a chart and write two findings plus one limitation, including the period and any small group sizes.

Hint: Use a tiny invented dataset first so you can manually verify a few formulas and pivot totals. Keep the raw data and analysis in separate tabs.

🎯 Key Takeaways

  • ✓A reliable spreadsheet analysis begins with a defined question, row grain, population, period, and unit.
  • ✓Keep raw data untouched, make cleaning decisions visible, and check duplicates, missing values, types, and totals.
  • ✓Use SUMIFS and COUNTIFS for conditional questions; show numerators and denominators for rates.
  • ✓Lookups depend on valid, unique keys; preserve unmatched results so data quality problems are visible.
  • ✓Pivot tables summarise groups quickly, but verify the aggregation, filters, distinctness, and grand total.
  • ✓Choose charts to match the question and disclose units, period, population, and missing data.
  • ✓Move repeated or complex transformations to SQL or Python while keeping a spreadsheet for review when useful.

Next

What comes next

Spreadsheets are useful for a bounded dataset. Next, query data where it lives and build repeatable summaries with SQL.

Lesson 02 · SQL for Analytics — The Queries Analysts Write Daily →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...