How to clean messy spreadsheet data with AI
Clean up data in Grid by Zaweba trims, fixes capitals, sorts out dates and splits columns using your own AI account, with a preview before anything is written.
Select the messy cells and choose Clean up data. In Grid by Zaweba, that AI action trims stray spaces, puts names into proper case, rewrites dates as dd/mm/yyyy and splits two-part columns, using an AI account you've connected yourself. The result is shown as a preview first, and nothing goes into your cells until you choose Write into cells.
What counts as messy
Data typed by many people, or exported from several systems, comes in every shape. Names in capitals, names in lower case, names with a double space in the middle. Dates written as 3/4/25, 2025-04-03 and 3rd April. A single column holding both a town and a postcode. None of it is wrong exactly, but it breaks sorting, filtering, lookups and pivot tables.
Before you start
Connect an AI account through the AI menu. It can be a cloud service you already use, a free model on your own PC such as Ollama or LM Studio, or a chat website by copy and paste. If the data is personal or sensitive, a local model keeps it on your machine.
Make a copy of the sheet, or at least know that undo is there. Each write is a single undo step, so one Ctrl+Z puts the original back.
Cleaning step by step
Select the cells to tidy, including the header row if there is one, since headings help the AI understand what each column holds. Choose Clean up data. Grid sends the selection as text, up to 200 rows by 30 columns, along with the header row and a little context, and only then.
The reply comes back as a table and is shown as a preview. Read it. Check the dates especially, since a date like 03/04 could mean March or April depending on where it was written. If it looks right, choose Write into cells. If not, undo or ask again with more instructions in the Ask AI panel.
Nothing is ever written over a locked cell on a protected sheet, so a protected template stays safe.
Related AI actions
Fill down from examples lets you type the result you want in the first few rows, such as initials taken from full names, and the AI fills the rest of the column following your pattern. Translate cells... translates a selection into another language. Analyse selection summarises the data and lists trends and outliers with their cell addresses, which is a useful check after cleaning.
Cleaning without AI
Plenty of tidying needs no AI at all, and it's worth knowing the formula ways too. TRIM removes extra spaces. PROPER, UPPER and LOWER fix capitals. TEXTSPLIT, TEXTBEFORE and TEXTAFTER separate parts of a cell, and Text to Columns does it as a one-off command. Remove Duplicates clears repeated rows, and REGEXREPLACE fixes anything with a pattern, such as phone numbers written with different spacing.
For predictable, rule-based fixes, formulas are repeatable and need nothing sent anywhere. The AI action earns its place when the mess is irregular: dates in many styles, or names where a rule would trip over the exceptions.
Checking the tidied columns
After cleaning, sort each column and look at the top and bottom, where odd values tend to gather. Use UNIQUE on a column that should hold a few set values to see whether any strays remain. Data validation on the cleaned columns, with a drop-down or a date rule, stops the mess creeping back.
Cleaning data is dull work, and Grid by Zaweba lets you hand some of it to an AI you choose, with a preview, a single undo, and your sheet sent nowhere until you ask.
