Automated transformations and formulas

1. Automated transformations and formulas

Welcome back! In this video, we'll let Copilot write the formulas that clean up messy text.

2. Order! Order in the court!

Raw data rarely arrives in the shape your analysis needs. Take the Ticket ID column here: each code packs three facts into one cell - a region, a department and a number. That is fine for looking up one ticket, but it stops you counting tickets by region. The Priority column has a different problem, with the same handful of values written in a mix of cases. In both cases, you'll need to

3. Order! Order in the court!

transform the data to extract what you actually need—for example, separating a region and a department out of a support ticket ID.

4. The traditional workflow

Instead of tracking down the right functions, writing a formula, and then working out why it doesn't return what we expected -

5. The Copilot workflow

Copilot can turn that messy data into clean columns without the headache of manually writing formulas. Notice there's still a validation step here, but instead of writing and debugging formulas by-hand, it's a conversation with Copilot focused on ensuring the inputs and outputs are correct. We'll see this workflow evolve throughout the course. Let's see this in action!

6. Example: Splitting text cells

In this lesson, we'll clean text data through common transformations like splitting text and extracting based on patterns. The same process works for any data type. We're using text here because the problems are easy to see. In our support ticket list, every ticket ID follows the same standardized form: the region, then the department, then a number, separated by hyphens. Because we want Copilot to edit our worksheet to add the new columns, we keep the mode on "Allow editing". From there we ask for two new columns, naming them explicitly: Region and Department.

7. Example: Splitting text cells

Here, we'll ask Copilot to pull the region and department out of the ticket ID into two new columns. Copilot works out the formula, then applies it directly to the worksheet to create the columns. EMEA-SALES-0042 becomes EMEA in one column and SALES in the next. Our job is to review the results to check everything worked as expected. Scan a few rows and make sure each region and department matches the ticket ID it came from - it is much easier to catch a mistake now than after you have built a report on top of it. If something is wrong, we'd feed our observations on what went wrong back to Copilot, which would then act to fix its mistake.

8. Example: Extracting text elements

Let's step this up a gear and pull a single element out of the pattern. The ticket number sits at the end, after the department. Extracting just that part traditionally means nesting a few text functions, which usually means a few searches and documentation checks. Instead, we'll ask Copilot for one more column holding just the numeric part. Copilot works out the formula, applies it, and adds the column directly to the worksheet. It also keeps the leading zeros, which converting to a number would have stripped. Worth a look before you rely on it: compare the new column against the original and check a few rows match.

9. A prompting checklist

Here's a few things to watch out for. First, make sure you're clear about which tables and columns Copilot should act on. Without this information, Copilot may take an educated guess, and that might not always be right. Second, if your chats are getting long, or you've switched tasks multiple times in the same chat, try creating a new chat. Carrying context over from previous tasks is often called "context leakage", and that extra context can pull Copilot toward the wrong task.

10. Let's practice!

Time to begin automating data transformations and formulas with Copilot!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.