Clean a spreadsheet received from someone else and spot what is wrong

The tools in this recipe

• Gemini dans SheetsPlugin

  Native AI in Google Sheets: formulas, cleanup, explanation

  Paid · from 12 €Data with the vendorSecurity: nothing to flag

  See the card →
• Copilot dans ExcelPlugin

  Same role in Excel (optional)

  Paid · from 15.6 €Data with the vendorSecurity: nothing to flag

  See the card →
• Formula BotApp

  Generate a formula from a sentence, without an office subscription (optional)

  FreemiumData with the vendorSecurity: one caveat, see the card

  See the card →

Step by step

1. 1

   Duplicate the sheet

   Always work on a copy. The first sheet stays the source as received; you will come back to it to check.

2. 2

   Ask for a diagnosis, not a fix

   Starting prompt: “Describe the problems in this table without changing anything”. You get the list: dates stored as text, trailing spaces, probable duplicates, amounts with and without VAT mixed.

3. 3

   Fix with formulas

   For each problem, ask for the formula (TRIM, VALUE, UNIQUE, XLOOKUP…) and apply it in a new column. An overwritten value can no longer be checked; a formula can.

4. 4

   Check the totals

   Ask for three cross-checks: total before/after cleanup, row count, sum per category. An unexplained gap is an unsolved problem.

5. 5

   Document

   Add a “Cleanup” sheet listing the corrections. Whoever picks up the file in six months will thank you, and it may be you.