Module 07: Copilot in Excel Foundations
Lesson 07.2: How to Prepare Data So Copilot Can Understand It
Lesson Promise
Prepare spreadsheet data so Copilot can read it more reliably and produce useful formulas, summaries, charts, and analysis.
Real-World Scenario
An operations coordinator has a messy export with blank rows, inconsistent dates, duplicate column names, and totals mixed into the data.
Core Concept
Data preparation is the unglamorous step that makes Copilot useful. Microsoft Support notes that Copilot in Excel works with data formatted as a table or supported range, and requirements can change as the product evolves.
A clean table has clear unique headers, consistent data types, no empty rows or columns inside the range, no merged cells, and no subtotals pretending to be records.
Do not ask Copilot to solve business questions until the workbook structure is readable. First ask it to inspect data quality, then fix or document the issues.
Step-By-Step Workflow
- Save the workbook in a supported modern Excel format and location for your organization.
- Turn the data into an Excel table or verify it meets supported-range requirements.
- Remove blank rows, blank columns, duplicate headers, merged cells, and subtotals inside the data range.
- Standardize dates, currency, percentages, categories, and IDs.
- Ask Copilot for a data quality checklist using the actual column names.
- Document every cleanup change so another human can audit it.
Prompt Lab
Bad Prompt
Clean this data.
Better Prompt
Review this table for data quality issues. Focus on headers, blanks, date formats, categories, duplicate records, and columns that need clarification.
Expert Prompt
Act as a data quality reviewer for Excel. Inspect this workbook before analysis. Create a table with issue type, affected column, why it matters, recommended fix, whether the fix changes meaning, and how I should verify it manually. Do not change business values unless you clearly explain the risk.
Hands-On Exercise
Take a sample export and create a before/after data preparation checklist. Keep a short change log.
Deliverable
A clean analysis-ready table plus a data quality note listing issues fixed and issues still unresolved.
Excel Review Checklist
Common Mistakes
- Asking one broad analysis question and accepting the first answer.
- Using messy data with duplicate headers, blank columns, hidden assumptions, subtotals, or merged cells.
- Treating Copilot's formula, chart, or insight as correct before manual verification.
- Mixing private or regulated data into an unapproved workflow.
- Promising a feature before checking the learner's license, account, app version, file state, and tenant settings.
Quiz / Checkpoint
Why should you clean table structure before asking for insights?
Copilot depends on readable structure; messy headers, blanks, merged cells, subtotals, or inconsistent data can produce weak or misleading results.
Official Sources To Verify
- Get started with Copilot in Excel
- Format data for Copilot in Excel
- Frequently asked questions about Copilot in Excel
- Generate formula rows and columns with Copilot in Excel
- Understand formulas with Copilot in Excel
- Visualize your data with Copilot in Excel
- Get insights about numerical data with Copilot in Excel
- Edit with Copilot in Excel
- Copilot Chat in Microsoft 365 apps
- What Copilot license do I have
Want your AI product explained to a large AI-native audience?
Kingy AI helps AI companies turn complex products into clear, useful YouTube videos that drive awareness, product understanding, demos, clicks, and search visibility.
