Edit with Copilot

Edit with Excel Copilot: Clean Data, Audit Formulas, and Create Pivot Tables

If you’ve ever wished for an Excel helper that could quickly clean messy data, audit your formulas, or create robust pivot tables with a single prompt, you’re going to love Microsoft Copilot for Excel.

Video

Tutorial

In today’s walkthrough, we’ll see how to unlock Copilot’s editing mode and leverage its natural language skills to automate common spreadsheet tasks, all while keeping our workflows efficient and transparent. This methodical guide is designed to enhance Excel proficiency for analysts, managers, educators, and anyone looking to ease and accelerate routine Excel processes.

Getting Started: Licensing and Setup

Before we dive in, it’s important to note that editing capabilities in Excel Copilot require a dedicated Microsoft Copilot license. If you already have that set up, simply open Excel, click the Copilot command, and ensure editing mode is enabled:

We can toggle between Allow Editing (which lets Copilot make changes) and Chat Only (if we want to restrict Copilot to suggestions without updating the worksheet).

Step 1: Clean Up Messy Data with Copilot

Let’s begin with an all-too-familiar scenario: a worksheet full of inconsistent names, erratic date formats, and disorganized department entries, like this:

Instead of endless manual corrections, let’s tell Copilot, in plain English, what we want:

clean up this data

After processing, Copilot delivers a summary of its changes:

• Split full names into first and last name columns with proper capitalization
• Standardize date formats
• Clean and capitalize department names, trimming excess spaces
• Apply basic salary formatting

Copilot also highlights all the changed cells so we can instantly spot and review edits before clicking Done.

Step 2: Audit Worksheet for Formula Errors

In our next scenario, we’re given a legacy worksheet that may contain misapplied formulas—missing ranges, manual overrides, or even the wrong operator. For example, formulas that don’t include all the rows they should:

Each of the green flags represent a potential error. But there could be more. So, let’s ask Copilot to take a first pass at auditing the formulas:

audit this worksheet for formula errors and fix them

After a quick review, Copilot highlights changes and provides a summary of its corrections in the side panel:

• Formula ranges extended as needed
• Operator corrections (e.g., fixing accidental subtraction)
• Hard-coded values replaced with consistent formulas when relevant

The prior and new formulas are detailed, so we can spot check changes line by line. It caught values that were hard-coded (instead of formulas), inconsistent formulas, incorrect formulas, and formulas that didn’t include everything they should. If needed, undo is just a click away, allowing us to study any before/after differences. Once satisfied, we click Done to confirm Copilot’s clean-up.

Step 3: Create and Adjust Pivot Tables with a Prompt

Reporting tasks often call for a summary pivot table, turning raw sales data into a region-by-product breakdown, for example. Let’s say we’d like to summarize this data:

If we’re unsure how to set up the PivotTable fields, we can simply ask:

I want a pivot table with region in rows, product in columns, and sum of total

Copilot generates a new tab named “pivot table” (marked with a green dot), with our data perfectly summarized … region in rows, each product as a column, and totals neatly calculated.

Pretty cool!

Conclusion: How Copilot Empowers Excel Users

With Copilot in editing mode, we can dramatically reduce the time needed for mundane spreadsheet tasks. Copilot:

• Cleans, transforms, and standardizes messy data with context-aware logic
• Finds and fixes formula errors improving spreadsheet accuracy and consistency
• Automates pivot table creation and report adjustment, all from natural-language prompts

It’s not only efficient, it’s also transparent and reversible, giving us the freedom to review, undo, and fine-tune any edits as needed.

Unlocking Copilot’s editing capabilities in Excel is a powerful way to streamline routine cleansing, auditing, and reporting tasks. This helps us put our focus on analysis, insights, and supporting key business decisions. Happy analyzing!

Download Sample Files

Frequently Asked Questions

What version of Excel supports Copilot editing?

Editing with Copilot is currently available in Excel for Windows Desktop with a supported Microsoft Copilot license.

Do I need a special license?

Yes, a separate Microsoft Copilot license is required to use editing features within Excel.

Can Copilot undo or revert its changes?

Absolutely. All changes are highlighted and can be reviewed, undone, or finalized with a single click.

How does Copilot know which data to clean or fix?

Copilot uses AI to interpret natural language prompts and analyze selected worksheet data, applying logical cleaning, restructuring, or correction steps automatically.

Is it safe to let Copilot edit important files?

We recommend always saving a backup before allowing Copilot to edit a workbook, as with all bulk automation tools.

Will Copilot highlight every edit it makes?

Yes, Copilot highlights edited cells so that we can easily evaluate changes before finalizing them.

Can Copilot create more complex pivot tables or charts?

Copilot handles a wide range of report generation tasks via prompts, but more bespoke or deeply-customized layouts may still require manual finishing touches.

What if Copilot makes an undesired change?

We can use undo to revert back instantly or modify our prompt to clarify the task for Copilot.

How does Copilot handle formula errors or inconsistencies?

Copilot reviews all formulas, extends ranges, fixes operators, and replaces hard-coded values where appropriate—summarizing all corrections for our review.