Automated transformations and formulas

Microsoft Copilot in Excel

James Chapman

AI Curriculum Manager, DataCamp

Order! Order in the court!

The Tickets table, with the packed Ticket ID column and the mixed-case Priority column marked

Microsoft Copilot in Excel

Order! Order in the court!

The same Tickets table again below it, now with Region and Department extracted into two new columns

Microsoft Copilot in Excel

The traditional workflow

$$

The four-step loop of writing a formula by hand: identify, research, write, test

Microsoft Copilot in Excel

The Copilot workflow

$$

The four-step loop with Copilot: identify, prompt, validate, insert

Microsoft Copilot in Excel

Example: Splitting text cells

$$

Common transformations: split text, extract by pattern

 

  • Every ticket ID follows one pattern - REGION-DEPARTMENT-NUMBER
  • Keep the mode on Allow editing so Copilot can add columns
  • Ask for two new columns: Region and Department

The Tickets workbook with Copilot open in Allow editing mode

Microsoft Copilot in Excel

Example: Splitting text cells

$$

Extract the region and department out of the ticket ID into two new columns

  • Copilot writes the formula and fills both columns
  • EMEA-SALES-0042 becomes EMEA and SALES
  • Review each row before moving on
  • Correct Copilot as needed

$$

Region and Department columns added by Copilot

Microsoft Copilot in Excel

Example: Extracting text elements

 

  • The same pattern holds a third element - the number
  • Ask Copilot for just that part in its own column
  • EMEA-SALES-0042 becomes 0042, zeros kept

$$

A Ticket Number column extracted from the pattern

Microsoft Copilot in Excel

A prompting checklist

$$

A checklist with three ticked items

 

  1. Reference tables and columns so Copilot knows exactly what data to use
  2. Start a new chat if it gets long or between tasks

→ Context leakage: context from other tasks interfering with the current task

Microsoft Copilot in Excel

Let's practice!

Microsoft Copilot in Excel

Preparing Video For Download...