Skip to content
Reading settings
ExcelIntermediate~30 min practiceLesson 17 of 34

Remove true duplicates and build separate subtotals

Remove a repeated request while keeping legitimate requests with equal amounts.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Check the duplicate key, remove one repeated row and summarize the clean list on a separate sheet.

Example

Same amount, different request

Enter ID, Team and Amount in A1:C6: R01 Support 40, R02 Sales 80, R01 Support 40, R03 Support 40 and R04 Sales 20. R01 was imported twice. R03 is a different request even though its team and amount match R01.

How it works

Decide what identifies a transaction before removing duplicates. Equal amounts alone do not identify the same request; check the ID and the rest of the record.

Keep subtotal reports separate from source records. A later SUM or PivotTable could otherwise count both a transaction and its summary row.

Steps

  1. Copy the five imported records to BeforeCleanup and work on another copy. Check five rows and total 220 before removing anything.

  2. Select the ID column and use Home > Conditional Formatting > Duplicate Values. Compare the two highlighted R01 records and check that all their fields match.

  3. Select the complete working range and choose Data > Remove Duplicates. For this sample, keep all three columns selected so only the exact repeated record is removed.

  4. Read the removal message. Four records should remain, including R03 Support 40 with its distinct ID.

  5. Check clean total 180. On a separate report sheet, use SUMIFS with Support and Sales labels to calculate Support 80 and Sales 100.

  6. For an optional desktop outline, copy the clean range, sort it by Team and try Data > Subtotal. Keep this copy out of the analysis source because it now contains both records and derived totals.

Try it yourself

  1. Append R05 Sales 80 to the cleaned source. Decide whether it should be retained, and explain how its key differs from the earlier Sales record with the same amount.
  2. Create a duplicate-audit note listing the removed ID, the matching fields, the number of records before and after cleaning, and the change in the control total.
Show expected result
  • One exact repetition is removed, leaving R01, R02, R03 and R04 with cleaned total 180. Support is 80 and Sales is 100.
  • R05 is retained as a distinct transaction; the extended clean total becomes 260, and Sales becomes 180.

If something goes wrong

A legitimate same-amount request was removed.
Restore BeforeCleanup and inspect the duplicate columns. Amount, or Team plus Amount, cannot distinguish R01 from R03 in this list.
A later summary doubles the clean total.
Look for subtotal or grand-total rows inside the source range. Move the report to a separate sheet and rebuild the summary from records only.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. Which repeated record should be removed in the original sample?
2. What is the cleaned original total?
3. Why retain R05 Sales 80?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Which records repeat exactly, and why should R03 and R05 remain? State the duplicate fields to compare before proposing a deletion.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…