Skip to content
Reading settings
ExcelBeginner~30 min practiceLesson 18 of 34

Tables and basic data cleaning

Clean a weekly sales export and explain the change in its total.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Make a named table, clean spacing and case, then remove the repeated record.

Practice material

Start with the practice file. Open the worked answer after trying the exercise. CSV and text briefs are also available. All resources and import notes →

Example

Clean Ben’s weekly export

Ben’s sales export has extra spaces, inconsistent capitals and a repeated row. Clean the sample without changing its sales amounts, then compare the total before and after removing the duplicate.

Weekly export.xlsx — Table: SalesData
D2=PROPER(TRIM([@Rep]))
RegionRepRegionCleanRepClean
" west""maya chen "WestMaya Chen
"EAST""jordan lee"EastJordan Lee
"north""SAM PATEL"NorthSam Patel
Teaching diagram, not a Microsoft screenshot.

How it works

An Excel table keeps the range, headers and calculated columns together. It helps formulas fill down, but you still need to check the record types and duplicate keys.

TRIM removes ordinary excess spaces and PROPER changes capitalization. These functions clean this sample; real names may need different spelling or capitalization, and nonbreaking spaces need separate handling.

Steps

  1. Enter the four rows listed in the exercise, or use messy-sales-csv. Keep an untouched copy. Check raw sales total 4050 before cleaning.

  2. Click in the data and press Ctrl+T on Windows, or choose Insert > Table. Confirm My table has headers and select OK.

  3. Open Table Design and type SalesData in the Table Name box.

  4. Insert RepClean to the right of Rep and enter the formula below. Check that the calculated column fills every record.

    =PROPER(TRIM([@Rep]))

    English function names. Formula settings →

  5. Add RegionClean and enter the formula below. Compare each cleaned region with its original.

    =PROPER(TRIM([@Region]))

    English function names. Formula settings →

  6. Copy RepClean and RegionClean and Paste Special > Values over the same cells. Check that their formula bars now show text values. Delete the original Rep and Region columns, then rename the cleaned columns.

  7. Choose Table Design > Remove Duplicates. Keep all columns selected and select OK.

  8. Open the Region filter and check the available labels: East, North and West.

  9. Enter =SUM(SalesData[Sales]) outside the table and check 2850. Add a temporary row to see the table extend, then remove it to return to the three-record result.

Try it yourself

  1. Enter these four rows under the headers Region, Rep, Sales: “ west | maya chen | 1200”, “EAST | jordan lee | 950”, “West | Maya Chen | 1200”, “north | SAM PATEL | 700”.
  2. Clean them with the steps above, paste values, then remove duplicates.
  3. Compare raw total 4050 with cleaned total 2850, identify the 1200 duplicate that explains the difference, and write the duplicate decision in a short audit note.
Show expected result
  • Excel reports that 1 duplicate value was removed.
  • Three rows remain: West · Maya Chen · 1200, East · Jordan Lee · 950, North · Sam Patel · 700.
  • The table is named SalesData and new rows pick up its formatting.
  • The original four records total 4050; removing the repeated 1200 record leaves 2850. No real person-name cleanup is implied by the invented exercise.

If something goes wrong

The repeated row remains after cleaning.
Compare its cleaned Rep and Region values. Check that Remove Duplicates used all the intended fields and that the clean columns were pasted as values.
RepClean shows #REF! after Rep was deleted.
Undo the deletion or restore the working copy. Paste the clean formulas as values and inspect their formula bars before deleting their source columns.

Quick check

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

1. How many records remain after cleaning the four stated rows?
2. What is the cleaned sales total?
3. Why paste clean columns as values before deleting raw columns?

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.

Review SalesData for spaces, inconsistent capitals and repeated rows. Suggest cleaning formulas and tell me which fields to compare before deleting a duplicate.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…