Using Copilot for Excel to convert one column to several

[EricPatterson]

Hi everyone, over the last few weeks we have had a series of posts to show you some of the things that are possible to do with Copilot in Excel. We will continue this week with more copilot examples. Today I will start with the survey results for an upcoming company potluck. Here is the data I have collected thus far:

3 column table of Potluck survey responses with columns for Name, RSVP (Yes, No, Maybe) and Potluck dish (Main Course, Appetizer, Side Dish, Dessert)

I want to be able to count how many people are bringing each type of dish and I would like to do that by converting the one column with all the responses separated by semicolons into multiple columns. To do that, I will ask Copilot:

Create new columns for each of the 4 types of potluck dishes in the potluck dish column

Picture of the Copilot in Excel pane showing the above prompt with 4 new formula column suggestions to be reviewed and inserted.

Looking at B2:D34, here are 4 formula columns to review and insert in Columns E, F, G, and H:

1. Appetizer

Identifies whether each participant is bringing an appetizer by checking if the word "Appetizer" is mentioned in their potluck dish list. =IF(ISNUMBER(SEARCH("Appetizer",[@[Potluck Dish]])),"Yes","No")

2. Main course

Identifies whether each person is bringing a main course dish by checking if "Main course" is mentioned in their potluck dish list and marking it as "Yes" or "No". =IF(ISNUMBER(SEARCH("Main course",[@[Potluck Dish]])),"Yes","No")

3. Dessert

Identifies whether each participant is bringing a dessert by checking if the word "Dessert" is mentioned in their potluck dish list and marking it as "Yes" or "No". =IF(ISNUMBER(SEARCH("Dessert",[@[Potluck Dish]])),"Yes","No")

4. Side Dish

Identifies whether each participant is bringing a side dish by checking if "Side Dish" is mentioned in their potluck dish list and marking it as "Yes" or "No". =IF(ISNUMBER(SEARCH("Side Dish",[@[Potluck Dish]])),"Yes","No")

Hovering over the “Insert Columns” button in the copilot pane shows this preview:

Table of potluck survey responses with preview of 4 new columns to be added for Appetizer, Main course, Dessert and Side Dish with Yes or No in each cell to indicate the response.

The proposed formula columns look correct in the preview. I will click on the Insert Columns button and I get this updated table:

Table of potluck survey responses with 4 new columns added for Appetizer, Main course, Dessert and Side Dish with Yes or No in each cell to indicate the response.

Over the coming weeks I will continue to share more examples of what you can do with Copilot in Excel.

Thanks for reading,

Microsoft Excel Team

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