Excel data cleaning: sort out a 2000-row table with a single sentence
💡 One-sentence summary: Use WorkBuddy to build a table-cleaning pipeline — first diagnose the data issues, then unify formats in one sentence, deduplicate, filter, and summarize, and finally export a new file.
Searching for "AI for Excel" mostly turns up two kinds of things: tutorials teaching you to write SUBSTITUTE functions, and websites that say "upload your table to an online tool." With the former you can't memorize the formulas; with the latter you have to upload your entire client list to someone else's server. This lesson walks through the complete Excel data-organization workflow: drag the table into WorkBuddy, run a data health check first, then unify formats, deduplicate, filter, and summarize in one sentence, and finally export a clean new file — the original file stays untouched throughout.
Let's be clear about the factual boundaries first: the 2000-row phone number cleaning instruction in this lesson comes from the original example in the official document "10 tips for getting started"; the two pieces of experience — "drag in a file and finish cleaning, filtering, and summarizing in one sentence" and "diagnose before cleaning" — come from third-party hands-on tests and summaries by Tencent Cloud community authors, not official promises; for anything involving product capabilities (supported formats, local processing, execution modes), the official documentation prevails.
The previous lesson, A5 PPT optimization in practice, wrapped up the five document lessons of Route 1; this lesson starts Route 2 — for you who deal with Excel and data every day. Clean the table first; only when it's clean can you do statistics, charts, and conclusions. This is lesson 1 of the "Scenario practice" Route 2, based on WorkBuddy v5.9.3.
- How credits are deducted and how much the trial version includes: 2.6 Credits and pricing
- A general way to write structured prompts: 3.1 The requirements methodology
What you'll be able to do after this lesson
- Run a data health check on a table: drag it in with one sentence and get back a diagnostic report on missing values, duplicate rows, and messy formats
- Unify 2000 rows of phone numbers into the xxx-xxxx-xxxx format in one sentence, with the area code rules written right into the instruction (the official example wording can be copied as-is)
- Deduplicate, filter, and summarize in one go: duplicate rows removed and a sales table summarized by salesperson produced in one shot
- Export the cleaned new Excel file, leave the original untouched, spot-check it and use it directly
Why use WorkBuddy instead of a web-based AI?
| Capability | Web-based AI | WorkBuddy |
|---|---|---|
| Reads the entire table | ❌ Paste 2000 rows over and it either gets truncated or the formatting breaks | ✅ Drag Excel / CSV / TSV files straight into the chat area and they're read automatically (officially supported formats) |
| What you get after the changes | ❌ A block of text or a function formula, which you still have to paste back into Excel and run column by column | ✅ Modified per the rules, with the cleaned new file output directly |
| Multi-step cleaning done in sequence | ❌ Can only handle the pasted snippet one at a time; once the context breaks you start over | ✅ Unifying formats, deduplicating, filtering, and summarizing can continue in one task (third-party hands-on test: done in one sentence) |
| Internal data security | ❌ The entire client list gets uploaded to the cloud, with an unclear destination | ✅ File processing is done locally by default and raw data never goes to the cloud (official statement) |
| Outsourcing mechanical labor | ❌ Re-paste the data in every conversation | ✅ Standardized actions like formatting and organizing data are outsourced in one sentence, with rules written into the instruction and executed automatically |
Your current predicament
- Exporting tables from various systems every week: some phone numbers have area codes and some don't, dates come in three formats, plus duplicate rows and empty values mixed in
- Want to clean it yourself with functions, but you can't memorize the formulas and one mistake ruins an entire column; VBA is even more off-limits
- Finding format issues by eye across 2000 rows of data — your eyes glaze over and you still haven't found them all
- Paste it into a web-based AI and ask it to "clean this up," and it replies with a block of text — pasting it back is manual work again
When to use this technique
When you need to:
- AI batch format changes: phone numbers, dates, ID numbers — hundreds to thousands of rows, as long as you can state the rules clearly
- Deduplicate, fill gaps, filter by conditions, then summarize by dimension into one clean table
- Clean the same kind of report every week: the exported structure is fixed and the rules are the same each week
And you don't want to:
- Learn a pile of Excel functions and VBA
- Check formats row by row with your own eyes
- Upload your entire internal dataset to an online cleaning website
🎒 Preparation before you start
- Completed 1.4 Your first task, and know how to send tasks and where to find the output
- Have a table that needs cleaning on hand (.xlsx / .csv / .tsv are all supported)
- Have half a plan for the cleaning rules: which columns' formats need unifying, what counts as a duplicate row — it's fine if you're not sure yet; decide after the diagnosis in step 2
- Have credits left: every conversation consumes credits, see 2.6 Credits and pricing for the rules
- 15 minutes
Follow along with me
Step 1: Drag the table into the chat area (AI processing of Excel starts with reading the file)
Why: WorkBuddy is a desktop AI workbench that reads files on your computer directly — no need to copy and paste 2000 rows into the chat box.
How to do it: Find your table file, drag it straight into the input box area, or click the upload button. Excel, CSV, and TSV are officially supported (along with other common formats like PDF, Word, and PPT), and you can drag in up to 50 files at a time.
You should see: the file attached above the input box. After sending, WorkBuddy reads the spreadsheet, and the reply shows the row/column counts and column names—proof that it actually read the file.
- My column names appear in WorkBuddy's reply, and the row count matches
Step 2: Diagnose before cleaning—have the AI produce a data health report
Why: Raw data is not clean data. Skipping diagnosis and jumping straight into cleaning makes it easy to miss duplicate rows and mixed formats—Garbage in, garbage out (a practical tip summed up by the third-party community; official practice examples likewise stress previewing before acting).
Action: After uploading the file, don't rush to make changes—send this diagnostic instruction first:
Please first check the data quality of this spreadsheet: how many missing values each column has, whether there are duplicate rows, and which columns have inconsistent formats. Output only the diagnostic report; do not modify any data yet.You should see: a data health report—missing value statistics, the number of duplicate rows, and a list of columns with messy formats, usually with handling suggestions attached.
- The diagnostic report matches what I know about the spreadsheet (e.g., I know the phone number column is indeed messy)
Step 3: Give the cleaning instruction in one sentence—standardize the phone number format across 2000 rows
Why: With the diagnostic report in hand, you can write a specific instruction. The official "10 getting-started tips" example does exactly this: target format + special rules, all spelled out in a single instruction.
Action: The official example, quoted verbatim (replace the row count, column name, and area code with your own):
Help me standardize the phone numbers in the 2000 rows of data in this Excel into the xxx-xxxx-xxxx format; if the area code starts with 0, keep it, otherwise add the default area code 0755 to all of them.You should see: WorkBuddy processes it row by row, the phone number column is entirely converted to a uniform format, and area codes are filled in according to the rules you set.
💡 This is WorkBuddy's unique value
A web-based AI will at most reply with something like "write it using the SUBSTITUTE function like this," and you still have to paste the formula back into Excel and drag it down 2000 rows. WorkBuddy reads the original file directly, modifies it according to the rules, and outputs a new file—moreover, file processing is done locally by default and the raw data never goes to the cloud (per the official statement), so your client list doesn't need to be uploaded to any website.
- The target format and special rules (such as the area code rule) are written into the instruction, rather than just a vague "help me clean this"
Step 4: Deduplicate, filter, and summarize—just keep going with more instructions
Why: Straightening out formats is only the first step; duplicate rows, missing values, and summarization are the bulk of cleaning. The context is still there, so just keep talking—no need to re-describe the spreadsheet.
Action: How a third-party hands-on author used it—drag the Excel into the chat box and say one sentence:
Summarize sales by salesperson for me and generate a bar chart.It automatically completes data cleaning, filtering, summarization, and chart generation (chart techniques are covered in detail in the next lesson). For cleaning instructions, follow this sentence pattern and fill in your own rules:
Delete rows that are fully duplicated in [Customer Name + Phone Number], keeping the earliest one; list the rows where [Sales Amount] is empty separately for me to confirm; then summarize [Sales Amount] by [Salesperson].You should see: duplicate rows removed, rows with empty values listed separately awaiting your sign-off, and a table summarized by salesperson.
- For deletion-type operations, WorkBuddy provided a list or preview first and only executed after I confirmed
Step 5: Verify the change list and export a clean new file
Why: Third-party testing found cases of deviation—with complex rules, the AI may get it wrong the first time, requiring the rules to be broken down and repeatedly verified. Check once more before exporting, and only then can you trust the cleaning results.
Action: When asking WorkBuddy to hand in its work, spell out two things:
List all the changes made in this cleaning pass (which columns were changed, how many rows were deleted, and under what rules), and export the cleaned data as a new Excel file, leaving the original file untouched.You should see: a change list + a cleaned new file (downloadable in the artifacts area). Spot-check 10 rows of key data, and hand it off once everything checks out. If it over-corrected, you can roll back to a previous round and start over—see 2.1 Conversation Advanced for how.
- The new file has been exported and the original file is still there; I spot-checked a few rows of key data
📋 Magic Prompt
Turn the process above into three copyable templates: diagnose first, then clean, then summarize. Placeholders are marked with [ ]; swap in your own data and they're ready to run.
📝 Pre-cleaning diagnosis (data health check)
Expected result: First get a clear picture of the three problem types—missing values, duplicates, and formats—confirm the cleaning rules before acting, and avoid Garbage in, garbage out.
## Role
You are a data cleaning consultant who excels at health-checking messy spreadsheets.
## Task
Check the data quality of the spreadsheet I uploaded, output a diagnostic report, and do not modify any data yet.
## Input Information
### Required Fields
- Spreadsheet file: [the dragged-in .xlsx / .csv file]
### Optional Fields
- Biggest concerns: [e.g., messy phone number formats?]
## Output Specifications
- Missing values: how many are missing in each column
- Duplicate rows: how many there are, and which records they cluster in
- Format issues: which columns have multiple written forms, with 3 examples of each
## Constraints
- ✅ Diagnose only, do not modify data
- ✅ Sort issues by severity
- ❌ Do not fabricate problems that aren't in the spreadsheet
- ❌ Do not clean or fill in data on the side📝 One-sentence cleaning (uniform format + embedded rules)
Expected result: A templatized version of the official example's sentence pattern—target format plus special rules in one clear statement, applicable from hundreds to thousands of rows.
## Role
You are a data processing specialist who excels at batch-organizing spreadsheets according to explicit rules.
## Task
Help me standardize the [Phone Number] column in the [2000] rows of data in this Excel into the [xxx-xxxx-xxxx] format; [if the area code starts with 0, keep it, otherwise add the default area code 0755 to all of them].
## Input Information
### Required Fields
- Spreadsheet file: [the dragged-in file]
- Target column: [e.g., Phone Number]
- Target format: [e.g., xxx-xxxx-xxxx]
- Special rules: [e.g., keep area codes starting with 0, otherwise add 0755]
### Optional Fields
- Other columns to standardize at the same time: [e.g., dates unified as yyyy-mm-dd?]
## Output Specifications
- The complete cleaned spreadsheet, exported as a new Excel file
- Attach a change explanation: how many rows were changed and how the rules were applied
## Constraints
- ✅ Only modify the target column; keep all other columns as-is
- ✅ List uncertain records separately; do not handle them on your own
- ❌ Do not delete any rows
- ❌ Do not modify the original file📝 Cleaning + summarization in one go
Expected result: After cleaning, directly produce a summary table by dimension—the templated version of that third-party "done in one sentence" approach.
## Role
You are a data processing specialist who excels at cleaning spreadsheets and outputting summary results.
## Task
Clean the spreadsheet I uploaded: delete fully duplicated rows, standardize the [Phone Number] column format, then summarize [Sales Amount] by [Salesperson] and output the summary table.
## Input Information
### Required Fields
- Spreadsheet file: [the dragged-in file]
- Summary dimension: [e.g., Salesperson]
- Summary metric: [e.g., Sales Amount]
### Optional Fields
- Duplicate row retention rule: [e.g., keep the earliest one?]
- Filter condition: [e.g., only 2026 Q3 data?]
## Output Specifications
- The cleaned detail table + summary table, exported as a new Excel file
- Attach a change list: how many duplicate rows were deleted and which formats were changed
## Constraints
- ✅ Explain what you are about to do before each processing step
- ✅ Keep the original file untouched
- ❌ Do not fabricate summary numbers
- ❌ Ask me first when unsure about the rules; do not act on your ownCheckpoints ✅
- The spreadsheet has been dragged into the chat area, and WorkBuddy's reply shows the column names and row count
- Received the diagnostic report: went through missing values, duplicate rows, and columns with messy formats
- The cleaning instruction specifies the target format and special rules (such as the area code rule), not just a vague "help me clean this"
- Deletion and overwrite operations: reviewed the list or preview first, and only executed after confirmation
- The cleaned new file has been exported, the original file is untouched, and key data has been spot-checked
Pitfall Reminders
❌ Skipping diagnosis and cleaning directly
Acting without diagnosing means duplicate rows get missed and mixed formats slip through, so the later statistics and conclusions are wrong at the root—as the third-party community puts it, Garbage in, garbage out.
Correct approach: Send the diagnostic instruction first to get the health report, confirm the problem list, and only then clean (Step 2).
❌ Not spelling out the rules and making the AI guess
How to handle area codes, what to fill in for missing values, which duplicate row to keep—if you don't spell it out, the AI can only guess. Third-party testing has also stumbled here: with complex rules, the first calculation may come out slightly off.
The right approach: like the official example, write the rules into the instruction (keep the 0 prefix, otherwise pad with 0755), break complex rules into separate steps, and verify repeatedly.
❌ Confirming delete/overwrite operations without reviewing the list
A painful third-party lesson: asking the AI to clean up files in bulk and confirming without carefully reviewing the list resulted in config files being deleted by mistake.
The right approach: for any operation involving deletion, moving, or overwriting, have the AI list the items first and review them before confirming (steps 4 and 5); if things break, use rollback—see 2.1 Conversation Advanced.
❌ Editing the original file directly
Something goes wrong with the cleaning and you want to compare against the original data, only to find the original file has already been overwritten—there's no undo.
The right approach: have WorkBuddy output a new file and leave the original untouched (step 5); the official getting-started tips document likewise stresses backing up before touching real files and having a rollback plan ready.
💡 How many credits will it cost
Every conversation consumes credits. The official docs don't give specific numbers for cleaning tasks like this lesson's; the third-party experience is that repeatedly generating several versions of the same task burns credits noticeably faster—try one version first to see the result, then fine-tune once you're satisfied. Credits are issued monthly and are valid for the current month only, with no carryover; see the rules at 2.6 Credits and Pricing.
Advanced Tips (Optional)
- Same cleaning rules every week? Hand it to scheduled tasks: the official docs list "aggregate data weekly and generate a report" as a typical automation scenario—set it up once and it runs automatically on schedule; see 3.3 Scheduled Tasks.
- Want big cleaning jobs to be more reliable: switch the execution mode to "Plan"—WorkBuddy presents the plan first and executes after you confirm (one of the three official execution modes). It's the same idea as "diagnose first, then act"; see 2.4 Execution Modes.
- Beyond Excel: the office-doc four-piece skill set has built-in PDF / DOCX / PPTX / XLSX commands, where xlsx covers cleaning spreadsheets, filling in formulas, organizing columns, and outputting summary results (official skills page); the official usage advice is "use the matching Skill for whatever file you encounter." For a curated list of zero-cost skills, see D2.
Lesson Summary
In this lesson you walked through the complete cleaning workflow for processing Excel with WorkBuddy AI:
- ✅ Diagnose first: drag in the spreadsheet to get a data health report, and get a clear picture of missing values, duplicates, and formatting issues
- ✅ Clean in one sentence: write the target format plus special rules into the instruction, and tidy up 2000 rows of phone numbers in one go
- ✅ Keep going: dedupe, filter, and summarize in one continuous task, then export a clean new file
- ✅ Safety boundaries: review the list before deleting, leave original files untouched, and spot-check after export
| Key point | Details |
|---|---|
| Core method | Diagnose first, then clean, then verify (the diagnosis and "done in one sentence" steps come from third-party testing) |
| Official example | Standardize 2000 rows of phone numbers into xxx-xxxx-xxxx, keeping the 0 prefix and padding with 0755 otherwise |
| Supported formats | Excel, CSV, TSV (official statement), up to 50 files dragged in at once |
| Data security | File processing is done locally by default, and original data is not uploaded to the cloud (official statement) |
Next Lesson Preview
The spreadsheet is clean—now let it speak: B2 Excel Data Analysis: Monthly Statistics, Charts, and Conclusions in One Go.
You will learn:
- One instruction to have WorkBuddy compute monthly sales and profit by product line
- Bar chart + line chart + conclusions generated in one go; follow up with sorting requests or add a pie chart
- Export analysis results into a file ready to present