Copilot in Excel: Working with Columns & Formulas
Published Jun 24 2024 12:01 PM 5,018 Views
Microsoft

Copilot in Excel can help write complex formulas, including ones that work with text. In this blog post, we demonstrate Copilot in Excel’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: 

Alan Turing (1912-1954, United Kingdom):

 

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

Alan Turing

1912-1954

United Kingdom

 

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: 

Alan Turing 1912-1954 United Kingdom
George Takei born 1937 United States

 

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

Alan Turing 1912-1954 42 United Kingdom
George Takei born 1937 Alive today United States

 

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.

 

Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

 

3 Comments
Co-Authors
Version history
Last update:
‎Jul 02 2024 09:41 AM
Updated by: