You open a spreadsheet, and you notice issues. One column contains dates in several different formats. Names use a mixture of uppercase and lowercase text. Currency values appear as both numbers and text. There may be extra spaces that are almost impossible to spot by looking at the spreadsheet.
Ask Copilot to perform an initial clean up of a messy dataset.
Upon opening a spreadsheet with messy data, a dedicated Clean Data experience appears that can identify certain issues with spacing, numbers, formatting and spelling. You can review each suggestion before applying it.
Alternatively, run a prompt in your spreadsheet with Copilot Edit mode. Instead of editing every row manually, you can describe the result you want and ask Copilot to complete the initial clean up. You should still review the result in case a change could affect the meaning of the data, but it removes much of the repetitive formatting work.
Sample prompt to clean data
Clean up the data in [table or worksheet name]. Trim extra spaces, fix inconsistent capitalisation, standardise dates to dd/mm/yyyy, apply consistent number and currency formats, and highlight any blank values in yellow. Do not change the meaning of any values. Explain what you changed when finished.
Or,
Clean up the data on this sheet.
The generic/shorter prompt is a useful starting point to complete an initial review, but the best prompt will reflect the actual issues in your workbook.
Highlight issues
If unsure, highlight issues instead of changing them. Not every data problem should be automatically corrected. Sometimes the safest approach is to ask Copilot to highlight potential issues so that you can review them yourself. For example:
For example:
Review this dataset for possible data quality issues.
Highlight blank values in yellow.
Highlight duplicate values in the Transaction ID column in orange.
Highlight dates that are not in dd/mm/yyyy format in blue.
Highlight values in the Total cost column that are not formatted as currency in purple.
Do not change any values.
Rishona Elijah 