Clean Messy Data with Copilot in Excel

Open Q1_Project_Expenses_MESSY.xlsx in Excel. Before you touch anything, scroll top to bottom. You're looking for problems — and this file has plenty. Notice the merged title row across the top, the blank rows acting as section dividers between projects, dates in at least three different formats, dollar amounts that are sometimes numbers and sometimes text strings with dollar signs, abbreviations like "Consltng" and "Trvl" mixed in with proper category names, a vague "Col F" header, and a hand-typed total at the bottom that's more of a guess than a number.

The bottom of the file — mixed date formats, "Consltng" and "Trvl" abbreviations, the 3 duplicate rows hiding at rows 73–75, and that red italic "~$28,000??" total.

Click into any cell with data and open Copilot from the ribbon. Your first job is to clear out the structural junk that prevents Excel from treating this as a proper data table. The file has a merged title row at the top, a blank row after it, blank divider rows between project sections, a hand-typed TOTAL (approx) ~$28,000?? at the bottom, and vague headers ("Col F" for the approver column, and a completely blank header on the notes column). Tell Copilot to identify the structural issues and make sure the data starts with a proper header row.

Copilot removes the merged title, blank divider rows, and the hand-typed total — and renames the vague headers in one pass.

The Date column is a mess. Three different formats are mixed throughout: short dates like 2/26/26, written-out dates like January 20, 2026, and ISO dates like 2026-01-20. Every one of these is stored as a text string, not an actual Excel date. Ask Copilot to convert them all to proper date values.

All three date formats unified into proper Excel dates — notice the categories and amounts are still messy. One thing at a time.

The Category column has 19 different values for what should be 5 categories. You'll find full words in mixed case (Travel, TRAVEL, travel), abbreviations (Trvl, Sftwre, Consltng, Ofc Supplies), and everything in between. Tell Copilot exactly which 5 values you want and let it figure out the mappings.

19 variations collapsed into 5 clean categories — Copilot mapped the abbreviations automatically.

The Amount column is a mix of three different data types. Some values are actual numbers (integers like 1550 or floats like 612.5). Others are plain text strings like "67.25". And about a third are dollar-formatted strings like "$3,200.00". Until they're all real numbers, no formula — and no Copilot summary — will give you an accurate total. This one doesn't need a complicated prompt.

All amounts converted to proper numbers — right-aligned, ready for math.

This file has 3 duplicate rows buried near the bottom — the kind you'd never catch on a visual scan. They're exact copies of rows that appear earlier in the data, likely from a copy-paste accident. Ask Copilot to find them.

Three duplicates identified — each an exact match of an earlier row.

Your data is clean. Now make it useful. Ask Copilot to create a summary breakdown by project and category so you can see where the money actually went. This is also your chance to see how far off that hand-typed ~$28,000?? estimate really was.

The real numbers — $43,623.96 total, a 55.80% variance from the original "~$28,000" guess.

Open Q1_Project_Expenses_FULLSEND.xlsx — a fresh, untouched copy of the original messy file. This time, write one comprehensive prompt that handles everything you just did across Steps 2–7 in a single shot. You've already learned what the problems are and how Copilot solves each one. Now combine that knowledge into one clear instruction.