Using Copilot in Excel to convert survey responses into an overall score

Greetings, We’re continuing our series of posts to show you some of the things that are possible to do with Copilot in Excel. Today I have the results from a recent survey for a training session. I’d like to calculate a satisfaction score for each participant based on the responses for each of the 4 questions.

 

Survey results table with columns for Email, Overall this session was valuable, Opportunities to interact and engage during the session, The content was valuable, The presentation of the session was effective. Responses for the last 4 columns have a number followed by a word. An example is 4 – Agree.

 

I use the following prompt to ask Copilot to create a score:

 

Add a column to calculate the overall satisfaction from the session

 

Copilot in Excel pane showing the above prompt and the response suggesting a formula column to solve the question.

 

Looking at B2:F10, here's 1 formula column to review and insert in Column G:

Overall Satisfaction

Calculates the average satisfaction score for each survey response by converting the first character of each rating to a numeric value, summing these values, and then dividing by 4 (number of questions).

=SUM(VALUE(LEFT([@[Overall, this session was valuable.]],1)), VALUE(LEFT([@[I had opportunities to interact and engage during the session.]],1)), VALUE(LEFT([@[The content was valuable.]],1)), VALUE(LEFT([@[The presentation of the session was effective.]],1)))/4

 

I hover over the Insert column button to see a preview of the formula that is suggested:

 

Survey response table with a preview of the “Overall Satisfaction” calculated column

 

I click on the insert column button, getting the following table which quickly completed my task.

Survey response table with Overall Satisfaction column added

 

Over the coming weeks I will be sharing 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.