Clean a spreadsheet and check the merge without losing the original
Understand the columns, agree on cleanup rules, and cross-check rows and totals before delivery.
Let's understand the idea
A spreadsheet may have dates in different formats, duplicate rows, or two files needing a merge. Don't start by automatically deleting duplicates or filling gaps. Two similar rows could be different transactions. Keep the original and understand each column and the key linking the files.
Agree on formatting, missing-value, and conflict rules, then have the assistant apply them to a copy. Check row counts, a sample of merged data, and totals. Keep unresolved rows in an exceptions list. If the file has formulas, open it and confirm calculations use the required formulas rather than fixed values.
Tools for this workflow
Choose from what's available to you. You can use these prompts with several tools. What they can carry out depends on supported features and files.
See tool details and uses in the Tools library →Examples to expand the idea
An employee has a customer spreadsheet combined from several sources, with varied name spellings and duplicate customers. She asks it to flag similar names instead of merging them, reviews them herself, then requests the clean version.
Dates, text, and numbers
Specify the intended format and unit before converting a value. Interpreting 03/04 requires knowing the date convention.
Reconcile two files
Define the matching key and what happens when it repeats. Reconcile rows with multiple possible matches.
Prepare data for presentation
After approving calculations, export a version suited to the dashboard. Keep a record of column sources.
Steps from start to delivery
Open any step by its heading. Each explains what to do, why, and what to check before continuing.
1Understand files before editingA data description and known rules.Details
A column's meaning determines how to clean it.
What should I do?
- Save an original copy and define columns and time period.
- Ask about the matching key, duplicates, and missing data.
2Approve cleaning rulesRepeatable rules.Details
Deciding how to fill a missing value is more than formatting.
What should I do?
- Define date formats, number formats, and units.
- Agree on duplicates, conflicts, and exceptions instead of guessing.
3Test a sample, then applyAn understandable transformation you can trace back.Details
A sample reveals unclear column meanings.
What should I do?
- Show before and after for a reviewed sample.
- Apply changes to a separate copy and log them.
4Reconcile rows and calculationsAn exceptions log instead of a hidden problem.Details
Missing or duplicated rows may not be visually obvious.
What should I do?
- Compare counts and totals before and after.
- Check multiple matches in merges, unmatched rows, and formulas.
5Open it and test its useA usable spreadsheet with an explanation of changes.Details
A technically valid file may still be unsuitable for the work.
What should I do?
- Open the version and check filtering, formatting, and calculations.
- Deliver the original, revised copy, and log as needed for review.
Prompts for your work
Open a prompt and fill in your details. Empty fields remain visible so you don't forget them.
Inspect and plan cleaningOpen it to copy or tailor the prompt
Prepare the listed inputs and start with the prompt in an available tool. Open the output and review it against your work.
Copy the prompt as it is, or tailor it with your details first.
Tailor the prompt to your workOptional: add your details and the prompt adjusts itself
View full prompt
Why is the prompt written this way?
Understanding columns and calculations matters more than the dashboard's appearance. Testing filters confirms interactions work.
Apply the rules and deliver the logOpen it to copy or tailor the prompt
Copy the prompt as it is, or tailor it with your details first.
Tailor the prompt to your workOptional: add your details and the prompt adjusts itself
View full prompt
Did the result serve your original goal?
Return to the inputs and sources, and open the output where you'll use it. Check these points before relying on it.
- Was the original preserved?
- Were merging and duplicate rules approved?
- Were counts, totals, and exceptions checked?
If there's a problem, name the unsuitable part, show an example, and request a specific edit.