Copilot in Excel: Working with Columns and Formulas

Excel Copilot can write complex formulas, including ones that work with text. In this blog post, we’d like to demonstrate Excel Copilot’s ability to work with complex formulas with a dataset containing noteworthy individuals.

Here's what we will be showcasing:

• Organize data by splitting a single column into multiple columns
• Calculate age by extracting the years from a string of text

1. Organize data by splitting a single column into multiple columns

We have data that comes in this format:


However, we would like to make our table cleaner by splitting the data into multiple columns.


Here is the prompt we ask Copilot in Excel to help us with this task: "Split the first column into three: Name, Years alive, and Country. Insert after the first column."

Copilot is able to take our natural language input, make an educated guess as to what part of the original column we mean by 'Years alive' for example, and perform the task by generating a formula that works on our data. Watch the video demo below.


2. Calculate age by extracting the years from a string of text

Now we have data that looks like this:


We want to ask Copilot to add a column with the individual's age. In the end, we want something that looks like this:


Prompt: Add a column that calculates how long each person was alive for. If they are still alive, write "Alive today".


*Disclaimer: If you try these prompts and they do not work as expected, it mostly likely is due to our gradual feature rollout process. Please try again in a few weeks.

Your feedback helps shape the future of Excel. Please leave a comment below with thoughts or questions on content related to this blog post. Additionally, please let us know how you like a particular feature and what we can improve upon—"Give a compliment" or "Make a suggestion".  You can also submit new ideas or vote for other ideas via Microsoft Feedback.