Skip to content
Tutorials Center
My learning

Topics

Lesson 83 of 93 ยท Real-World Projects

Project: Sales Analysis Report

Turn messy sales transactions into a report: clean the data, add product and region lookups, summarize with a PivotTable and list the top sellers.

Most real analysis starts with a file nobody designed: codes with stray spaces, numbers stored as text, product names that exist only in another sheet. In this project you take a small, messy export of sales transactions and turn it into a short report: cleaned data, product and region names from lookup tables, a PivotTable-style summary, and a top-N list built with dynamic arrays.

The steps follow the same order you would use at work: clean, enrich, summarize, rank. Excel cannot run on this site, so Excel actions (Ctrl+T, Insert > PivotTable) are described as text, while the formulas are evaluated by a small JavaScript spreadsheet engine stand-in, and a pandas TryIt shows the same pipeline in Python. The engine understands only the few functions used here; it is not Excel's own code.

From export to report

Clean Trim, case, text to numbers.
Enrich Look up product and region.
Summarize PivotTable or SUMIFS.
Rank Top N with dynamic arrays.

Never analyze data you have not cleaned first.

1 / 2

The raw data

The export has one row per sale, with a product code, a quantity and a one-letter region code. Notice the problems:

id code qty region
1 " p100 " "3" (text) n
3 p100 "5" (text) N
4 P300 1 E
8 P999 1 N

There are leading and trailing spaces, mixed case, quantities stored as text (left-aligned, with a green triangle), and a product code that is in no price list. See Cleaning Messy Data for the general checklist and text basics for the functions.

Step 1: clean in helper columns

Keep the raw columns untouched and add clean columns next to them. That way you can always compare, and you never destroy the original:

TEXT
E2  Clean code     =UPPER(TRIM(B2))
F2  Quantity       =VALUE(C2)
G2  Clean region   =UPPER(TRIM(D2))

TRIM removes extra spaces, UPPER fixes the case, and VALUE turns text like "3" into the number 3 (it leaves a real number alone). When the sheet is final you can copy the clean columns and Paste Special > Values into a new sheet, or let Power Query do the same steps and refresh them automatically (see Power Query basics).

Step 2: lookups for product and region

The price list and region list live in their own small tables. They are not sales data; they are reference data, and a lookup brings the name or price into each sales row. The modern tool is XLOOKUP (see XLOOKUP and XMATCH):

TEXT
H2  Product   =XLOOKUP(E2,$K$2:$K$5,$L$2:$L$5,"UNKNOWN")
I2  Region    =XLOOKUP(G2,$P$2:$P$5,$Q$2:$Q$5,"UNKNOWN")
J2  Revenue   =ROUND(F2*XLOOKUP(E2,$K$2:$K$5,$M$2:$M$5,0),2)

The fourth argument is what to return when the code is not found. Writing "UNKNOWN" instead of leaving #N/A means unmatched rows stay visible and countable, instead of breaking every total below them. If you must support Excel 2019 or earlier, the same job is INDEX/MATCH or VLOOKUP with FALSE as the last argument (see VLOOKUP and its limits).

Run it: clean, look up, summarize

The TryIt below loads the raw rows into the engine and writes the helper-column formulas exactly as above. It then builds the summary with SUMIFS, the formula equivalent of a PivotTable with Region in rows and Revenue in values. The engine has TRIM, UPPER, VALUE, XLOOKUP, SUMIFS, COUNTIFS and ROUND.

JS Clean, look up and summarize with formulas

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The unknown-product row contributes 0 revenue instead of an error, and the last line counts such rows so you notice them. In a real project, that count is the first thing to check after every refresh.

Step 3: summarize with a PivotTable

Formulas like the SUMIFS above are fine for a fixed small list. For exploring, use a PivotTable: click inside the cleaned data (best as an Excel Table), choose Insert > PivotTable, and drag Region to Rows and Revenue to Values. Add Product to Columns for a region-by-product grid, and a slicer or a filter for months. Pivot basics are covered in PivotTable Basics; remember to Refresh after changing the source (a pivot does not recalculate by itself). Cross-check one pivot number against a SUMIFS result once, so you trust the setup.

Run it: the same pipeline in pandas

This TryIt runs real Python in your browser. It repeats the cleaning, lookup (a merge) and summary (a pivot table), which is a good way to see that spreadsheets and DataFrames express the same operations.

Python The same report with pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Step 4: top N with dynamic arrays

For a "top three products" box that updates by itself, you need a product total list and then sort and slice it. With dynamic arrays (Microsoft 365 and Excel 2021 or later; see Dynamic Arrays and Spilling):

TEXT
Product list (spills)   N2  =UNIQUE(H2:H9)
Totals (spills)         O2  =SUMIFS(J2:J9,H2:H9,N2#)
Top 3 table             R2  =TAKE(SORTBY(HSTACK(N2#,O2#),O2#,-1),3)

N2# is the spill reference: it means "the whole spilled list starting at N2", so the totals grow with the product list. HSTACK puts product and total side by side, SORTBY(...,O2#,-1) sorts by total from highest to lowest, and TAKE(...,3) keeps three rows (see FILTER, SORT, SORTBY and UNIQUE and TAKE and DROP). In older Excel versions, use LARGE(totals, 1) to LARGE(totals, 3) with INDEX/MATCH to fetch the names; ties then need extra care.

Run it: top-N by hand

The last stand-in does what the three dynamic-array formulas do, step by step, with plain JavaScript arrays and closures, so you can see what each function contributes.

JS UNIQUE, SUMIFS, SORTBY and TAKE as plain code

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Step 5: present and protect

A report page needs only the essentials: a title, the date range, the region summary, the top three and one chart (a bar chart of revenue by region, see chart basics). Put clean data and lookup tables on their own sheets, label what is input and what is calculated, and hide nothing that someone would need to audit. If you share the file, protect the report sheet so formulas cannot be overwritten (see Collaboration, Comments and Protecting Work).

A short list of checks for any report like this:

  1. Do the region totals add up to the grand total of the cleaned data?
  2. How many rows have UNKNOWN products or regions?
  3. Are any quantities zero, negative or absurdly large?
  4. Did the PivotTable refresh after the last data change?

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why keep the raw columns and add clean helper columns instead of editing in place?

    Cleaning in new columns is reversible and auditable. If a rule is wrong, the raw data is still there.
  2. 2. What is the benefit of the fourth argument in XLOOKUP(E2,codes,names,"UNKNOWN")?

    if_not_found replaces #N/A with your own value. Rows that fail to match remain visible and can be counted with COUNTIFS.
  3. 3. What does N2# mean in a formula?

    The # is the spill operator. It follows the spill range as it grows or shrinks.
  4. 4. After you change the source data, the PivotTable still shows old numbers. What is the most likely reason?

    PivotTables keep a cached copy of the data. Use Refresh (or Refresh All) to pick up changes, or enable refresh on open.

Summary

  • Clean first: TRIM, UPPER and VALUE in helper columns beside the raw data.
  • Bring in names and prices with XLOOKUP and a visible not-found value, then count unmatched rows.
  • A PivotTable (or SUMIFS) summarizes by region and product; refresh it after changes.
  • TAKE(SORTBY(...),3) over UNIQUE and SUMIFS results builds a self-updating top-N list.
  • The same pipeline in pandas is clean, merge, pivot_table and sort_values.
  • Finish with checks: totals reconcile, unknown rows counted, outliers inspected.

Next: Project: Invoice and Quote Template.