Use Microsoft Copilot to Clean Excel Data Automatically

# Excel Copilot Cleanup Guide: CRM Contact List

1. **Back up your data first.** Duplicate the sheet (right-click tab → Move or Copy → check "Create a copy") so Copilot edits happen on a working copy, not your original export.

2. **Convert the range to a Table.** Select your data and press `Ctrl+T`; Copilot works far more reliably on structured tables than raw ranges.

3. **Standardize the Name column into First and Last.** Open the Copilot pane and prompt: *"Split the Name column into two new columns called First Name and Last Name. If a value contains a comma, treat it as 'Last, First'; otherwise treat it as 'First Last'. Trim any extra spaces."* Manual fallback: insert two columns and use `=TRIM(IFERROR(MID(A2,FIND(", ",A2)+2,99),LEFT(A2,FIND(" ",A2)-1)))` for First and a mirrored formula for Last.

4. **Normalize phone numbers to a clean format.** Prompt Copilot: *"In the Phone column, remove all parentheses, dashes, spaces, and dots, then reformat every value as (XXX) XXX-XXXX. Leave blanks as blanks."* Manual fallback: use a helper column with `=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")",""),"-","")," ","")),"(000) 000-0000")`.

5. **Lowercase all emails and trim whitespace.** Prompt: *"Convert every value in the Email column to lowercase and trim leading or trailing spaces."* Manual fallback: helper column with `=LOWER(TRIM(C2))`, then paste-values back over the original.

6. **Remove duplicate emails.** Prompt: *"Remove duplicate rows based on the Email column, keeping the first occurrence."* Manual fallback: Data tab → Remove Duplicates → check only the Email column.

7. **Flag missing critical fields for mail merge.** Prompt: *"Add a column called Issues that lists any of the following problems per row: missing first name, missing last name, missing or invalid email, phone not 10 digits."* Review and fix flagged rows manually.

8. **Sort and finalize for mail merge.** Prompt: *"Sort the table alphabetically by Last Name, then First Name, and hide any helper columns."* Save the file as a new `.xlsx` specifically for the merge.

---

**Verification checklist**
- ✅ Spot-check 10 random rows: names split correctly regardless of original format, and phone numbers all match `(XXX) XXX-XXXX`.
- ✅ Run `=COUNTA(Email)` vs `=SUMPRODUCT(1/COUNTIF(Email,Email))` — the two numbers should now be equal (no duplicates).
- ✅ Confirm the Issues column is empty (or resolved) for every row before starting the mail merge.

# Excel Copilot Data Cleanup Guide: Customer Contacts

## Step-by-Step Workflow

**Step 1: Assess and Organize Your Data**
First, select your entire dataset (Ctrl+A) and apply AutoFilter (Data > Filter) to see column headers clearly. Type this prompt into the Copilot pane: *"Show me a summary of the data quality issues in my customer contacts sheet—count duplicates, inconsistent formats, and missing values."*

**Step 2: Standardize Name Format**
Create two helper columns next to your Name column labeled "First" and "Last." Type: *"Split the Name column into First and Last name, handling both 'Last, First' and 'First Last' formats. Put results in columns B and C."* If Copilot struggles, manually use Data > Text to Columns on a subset first to show the pattern.

**Step 3: Merge Names Back to Consistent Format**
In a new column, type: *"Create a formula that combines the First and Last name columns into 'First Last' format for all 4,000 rows, then convert the formulas to values."* Copy the new column and paste as values over your original Name column, then delete the helper columns.

**Step 4: Standardize Phone Numbers**
Select your Phone column and prompt: *"Remove all dashes, spaces, and parentheses from phone numbers and format them consistently as (XXX) XXX-XXXX for a US phone list."* If Copilot uses formulas, convert results to values before deleting the original column.

**Step 5: Remove Duplicate Emails with Case-Insensitive Matching**
Highlight your Email column and type: *"Identify and flag duplicate emails regardless of uppercase/lowercase differences, keeping only the first occurrence of each unique email address."* Manually review flagged rows before deletion, then use Data > Remove Duplicates if needed for final cleanup.

**Step 6: Trim Whitespace from All Text Columns**
Select all text columns (Name, Email, Phone, Address) and prompt: *"Remove leading and trailing spaces from all cells in these columns across all 4,000 rows."* Verify the action completed by clicking a few cells to confirm no extra spaces remain.

**Step 7: Handle Missing Values**
Type: *"Show me which rows have blank Email or Phone fields and highlight them for review."* Manually decide whether to delete incomplete records or request missing data from your CRM; mark any retained blanks with "N/A" for consistency.

**Step 8: Convert Email to Lowercase**
Select the Email column and prompt: *"Convert all email addresses to lowercase to ensure consistency for mail merge."* Convert formulas to values once complete.

**Step 9: Final Deduplication and Sort**
Type: *"Remove any remaining duplicate rows based on Email address, and sort the final dataset alphabetically by Last Name."* Review the row count before/after to confirm duplicates were removed.

**Step 10: Verification and Export**
Prompt: *"Verify data quality: confirm no blank emails exist, all phone numbers follow (XXX) XXX-XXXX format, and all names are in 'First Last' format."* Save as .xlsx and test a sample mail merge with 10 rows to confirm formatting works.

---

## Verification Checklist

- ✓ **Name Format**: All names display as "First Last" with consistent capitalization and no commas
- ✓ **Phone Numbers**: All phones match (XXX) XXX-XXXX format with no dashes, spaces, or parentheses irregularities
- ✓ **Email Duplicates Removed**: No duplicate emails exist (case-insensitive), and all emails are lowercase with no leading/trailing spaces

Of course. Here is a step-by-step Copilot workflow to clean your customer contact data for a mail merge.

***

1.  **Select your data and trim whitespace.**
    First, click on any cell within your data range and press Ctrl+A to select it, then click "Format as Table" on the Home tab. Next, ask Copilot: `"Trim whitespace from all columns in the table."` This removes leading or trailing spaces that can cause issues with duplicates and formatting.

2.  **Standardize and deduplicate emails.**
    Ask Copilot to normalize the email addresses to prevent case-sensitive duplicates. Prompt: `"Create a new column named 'Clean_Email' that converts the 'Email' column to all lowercase, then remove duplicate rows based on this new column, keeping the first instance."`

3.  **Split the mixed-format Name column.**
    Copilot can recognize different name patterns and separate them correctly. Prompt: `"Split the 'Name' column into two new columns, 'First_Name' and 'Last_Name'."` If Copilot struggles, try giving it an example: `"Split the 'Name' column like this: 'Smith, John' becomes 'John' in 'First_Name' and 'Smith' in 'Last_Name'."`

4.  **Standardize all phone numbers.**
    This step will strip out all formatting, leaving only digits for a consistent phone list. Ask Copilot: `"Create a new 'Clean_Phone' column by removing all non-numeric characters from the 'Phone' column."`

5.  **Identify any missing contact information.**
    Find empty cells that could cause errors in your mail merge before you proceed. Prompt: `"Highlight all blank cells in the table in yellow."` For a manual fallback, use the Home tab > Find & Select > Go To Special > Blanks.

6.  **Finalize your clean dataset.**
    To finish, hide the old, messy columns, leaving only the clean data for your mail merge. Prompt: `"Hide the original 'Name', 'Email', and 'Phone' columns."`

***

### Verification Checklist:

*   The `First_Name` and `Last_Name` columns are correctly separated for all rows.
*   The `Clean_Phone` column

1. **Trim whitespace across all columns**: In the Copilot pane, type: "Trim leading and trailing spaces from all text columns in my table." If Copilot doesn't apply to all, manually select data > Data tab > Text to Columns > Finish (as a delimiter reset), then use Find & Replace for spaces.

2. **Standardize emails to lowercase**: In Copilot, type: "Create a new column converting the Email column to lowercase using LOWER function, then copy values back to original." Verify by sorting the Email column for consistency.

3. **Remove duplicate emails**: In Copilot, type: "Remove duplicate rows based on the Email column, keeping the first occurrence." Manually fallback: Data tab > Remove Duplicates > select Email column only.

4. **Clean phone numbers**: In Copilot, type: "Standardize the Phone column by removing dashes, spaces, parentheses, and formatting as (XXX) XXX-XXXX using SUBSTITUTE and TEXT functions in a new column." Paste values back and delete original if needed.

5. **Split Name column into First and Last**: In Copilot, type: "Split the Name column into First Name and Last Name: if comma present, split after comma and reverse; otherwise split on last space. Create new columns." Manually fallback: Add columns, use formulas like =TRIM(LEFT(Name,FIND(",",Name)-1)) for Last if comma.

6. **Fill missing values (if any)**: In Copilot, type: "Identify rows with missing values in key columns (Name, Email, Phone) and suggest filling with 'Unknown' or averages where appropriate." Manually: Use Go To Special > Blanks, then type formula like ="Unknown".

7. **Final deduplication and sort**: In Copilot, type: "Remove any remaining duplicates based on Email and Phone, then sort by Last Name ascending." Convert table to range if needed for mail merge.

**Verification checklist**:
- All emails are lowercase with no duplicates (check unique count).
- Phones match (XXX) XXX-XXXX format (spot-check 10 rows).
- Names split correctly into First/Last with no 'Last, First' leftovers (sort and scan).