Clean Messy Columns and Build a Summary Table

Real spreadsheets are messy. The same region is typed as "North", "north" and "North " with a space. Some rows appear twice. Dates come in three formats. Before any summary is right, the data has to be cleaned. In Excel, that is a long list of manual steps. With a script, it is a few lines you can run again next month.

In this lesson you will ask AI for a cleaning script, see the common cleaning steps in action, and build a summary table like a pivot table.

What You'll Learn

• The most common kinds of mess in Excel data
• How to describe cleaning rules to the AI
• How to fix spaces, capitals, duplicates and dates
• How to build a pivot-style summary with groupby and pivot_table
• How to save the clean data and the summary in one Excel file

Common mess, and the fix

If you want to understand cleaning without code first, the AI Data Cleaning micro course covers the ideas with no Python.

Describe your cleaning rules

The AI cannot see your file. Show it a few real rows and say exactly what "clean" means for you. You can copy the first 5 to 10 rows from Excel and paste them into the chat. Use made-up values if the data is private.

Two details matter here:

• Say the date order. "03/01/2026" is 3 January in India and the UK, but March 1 in the US. Always tell the AI which one you mean.
• Order of steps. Duplicates often only become visible after cleaning, because "north " and "North" look different until spaces and capitals are fixed. So remove duplicates last.

See the cleaning steps work

Run this to see each step change the table. Watch the row count drop as empty and duplicate rows are removed.

The first two rows were the same order typed two ways. After fixing spaces and capitals, they become exact duplicates and one is removed.

Build a summary table

A summary by group is what most people use pivot tables for. In pandas there are two common ways:

• groupby: "for each Region, add up Revenue." Good for simple totals.
• pivot_table: a true pivot, with groups down the side and across the top, like Region by Month.

Now you try

Check before you share

Use the checks from the last lesson:

• Does the summary's grand total match the total of the clean data?
• Did the number of rows drop by about what you expected? A drop of half the rows means a rule was too strict.
• Open the Clean Data sheet and look at a few rows. Are the regions spelled the same everywhere now?

Key Takeaways

• Most messy data has the same problems: extra spaces, mixed capitals, duplicates, mixed dates, numbers as text and empty cells.
• Show the AI a few real or fake rows and write clear cleaning rules, including the date order.
• Fix spaces and capitals before removing duplicates.
• Use groupby for simple totals and pivot_table for a true pivot.
• Save clean data and the summary together, and check totals before you share.