Skip to content
Tutorials Center
My learning

Topics

Lesson 89 of 93 ยท Practice and Review

Data Analysis Exercises with Solutions

Ten Excel data analysis exercises on cleaning, duplicates, PivotTables, charts and dynamic arrays, each with a worked answer and a runnable pandas stand-in.

Real data analysis is not one formula; it is a sequence. You receive a messy export, clean it, summarize it, pick a picture and say what you found. These ten exercises follow that sequence on a small order list, using the tools from the cleaning, PivotTable, chart and dynamic array chapters. Each exercise has a task, the Excel way to solve it, and the answer.

Excel cannot run on this site, so the Excel steps and formulas are shown in plain blocks (that is what you do in Excel). The runnable TryIts are stand-ins in Python with pandas, which does the same operations on the same data. They show what each Excel step produces so you can check your own work.

The analysis pipeline

Clean Trim, convert, de-duplicate.
Summarize PivotTables and formulas.
Rank Sort, filter, unique.
Show Pick the right chart.
1 / 2

The data

Here is the raw export. Type it into a sheet, or just read along. Note the problems: stray spaces, mixed capitalization, amounts stored as text with $ and commas, one blank amount and one repeated order.

order_id date region product customer amount
1001 2026-01-05 east Desk Ann 1,200
1002 2026-01-17 WEST Chair Bo $450
1003 2026-02-03 North Lamp Cy 85.50
1003 2026-02-03 North Lamp Cy 85.50
1004 2026-02-20 east Desk Ann 1,150
1005 2026-03-02 West Chair Dee (blank)
1006 2026-03-15 EAST Lamp Bo 90
1007 2026-03-28 " north" Desk Cy $1,300

Part 1: cleaning (exercises 1 to 4)

Exercise 1. Standardize the region names so that east, EAST and east are the same value.

Excel: in a helper column, =PROPER(TRIM(C2)). TRIM removes extra spaces and PROPER fixes the capitalization. Copy the result and paste it as values over the original column. See cleaning messy data and text functions.

Exercise 2. Turn the amounts into real numbers. Texts such as $1,200 cannot be summed.

Excel: =VALUE(SUBSTITUTE(SUBSTITUTE(F2,"$",""),",","")). Remove the symbols first, then convert. Or use Text to Columns or Power Query's change-type step. Watch for locale: in some regions the comma is the decimal separator (number-as-text problems).

Exercise 3. Find the blanks. How many orders have no amount?

Excel: =COUNTBLANK(F2:F9) counts empty cells. Decide what a blank means before you fix it: an unknown value should stay empty, not become 0, otherwise averages are wrong.

Exercise 4. Remove the duplicate order.

Excel: Data, Remove Duplicates, tick only order_id, or use =UNIQUE(A2:F9) to produce a de-duplicated list that leaves the source untouched. Check which columns define a duplicate first: two real orders can share a customer and a product (removing duplicates).

Run the pandas twin of all four exercises:

Python Exercises 1 to 4 in 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.

The sum in step 2 is distorted by the repeated order, which is why the cleaning order matters: remove duplicates before you total anything.

Part 2: summarizing and showing (exercises 5 to 10)

From here on we use the clean table: seven orders, one with a blank amount.

Exercise 5. Revenue by region and product in a grid.

Excel: Insert, PivotTable. Put region in Rows, product in Columns, amount in Values (Sum). Blank cells in the grid can show 0 via PivotTable Options, "For empty cells show". See PivotTable basics. A formula alternative: =SUMIFS($F:$F,$C:$C,$H2,$D:$D,I$1).

Exercise 6. Revenue per month.

Excel: add the date field to the Rows area and group by Months (grouping dates). A formula alternative that does not need a pivot: =SUMPRODUCT((MONTH($B$2:$B$8)=1)*$F$2:$F$8) for January.

Exercise 7. Share of total by region (percentages).

Excel: in the PivotTable, right-click a value, Show Values As, "% of Grand Total". Or =SUMIF(C:C,H2,F:F)/SUM(F:F).

Exercise 8. Top two products by revenue.

Excel (dynamic arrays): =TAKE(SORTBY(prodList, revList, -1), 2), where prodList is =UNIQUE(D2:D8) and revList sums each product. In older Excel, sort the pivot by Sum descending and use the Top 10 filter. See FILTER, SORT and UNIQUE.

Exercise 9. Customers who placed at least one order over 1,000, each listed once.

Excel: =UNIQUE(FILTER(E2:E8, F2:F8>1000)). FILTER keeps the matching rows and UNIQUE removes repeats. If no row matches, add the third argument: =UNIQUE(FILTER(E2:E8, F2:F8>1000, "none")).

Exercise 10. Pick the chart. Which chart type fits (a) revenue per month, (b) each region's share of total, (c) revenue by product, sorted?

Answers: (a) a line or column chart, because time runs left to right; (b) a pie or, better, a bar chart of shares, because there are only three regions, but a bar compares sizes more accurately; (c) a sorted bar chart, because the categories are names and sorting makes the ranking readable. See choosing the right chart type.

The pandas twin of exercises 5 to 10, including a text bar chart for exercise 10(a):

Python Exercises 5 to 10 in 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.

Notice that the blank amount (order 1005) was ignored by every sum. That is also Excel's behavior: SUM and PivotTables skip blanks and text. It is correct when "blank" means "unknown", and wrong if it meant "free" or "zero". Say which in your report.

Scoring yourself

  • 9 to 10 solved without the hints: you can analyze real data in Excel.
  • 6 to 8: revisit the tools you skipped. Pivot grouping and dynamic arrays are the usual gaps.
  • Below 6: redo the cleaning lesson and the PivotTable basics first, then retry.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should you remove duplicate orders before building totals?

    Totals add up every row they see, so a duplicate order doubles its amount in the result.
  2. 2. Which formula lists the customers with amount above 1,000, each only once?

    FILTER first keeps the rows that match, then UNIQUE removes repeated customers from that result.
  3. 3. What does a blank amount do inside a PivotTable Sum?

    Blanks and text are ignored by sums. Decide what a blank means in your data and document it.
  4. 4. Which chart suits revenue per month over one year?

    Time series read best with time on the horizontal axis, as a line or a column chart.

Summary

  • Analysis is a pipeline: clean, summarize, rank, show. Clean first, because mistakes in raw data flow into every result.
  • TRIM, PROPER, SUBSTITUTE, VALUE and Remove Duplicates (or UNIQUE) are the cleaning toolbox.
  • PivotTables, SUMIFS and dynamic arrays (FILTER, SORTBY, UNIQUE, TAKE) answer most business questions.
  • Match the chart to the question: line for time, sorted bars for rankings.
  • The pandas code is a stand-in that shows what each Excel step produces; the Excel steps are done on your computer.

Next: Performance and Spreadsheet Best Practices.