See what’s possible with Copilot in Excel (part 8)

Hello everyone, over the past few weeks we have been series of posts Let me show you a few things you can do with Copilot in Excel.

I have work to finish quickly this week. You need to import and analyze meeting attendance data from recent business partner meetings. Before performing the analysis, we need to clean up the data a bit.

Partner meeting table with columns for meeting name, attendee ID, join time, and leave time

To start, make sure the Join and Leave Time columns are not actual dates and are just entered as text. I ask the co-pilot:

Add a calculated column formula that converts the join time column to a date/time value.

Copilot in Excel window with prompt above and response below.

Looking at B2:E137, there is one column of formulas to review and insert into column F.

Convert each attendee’s attendance time to a numeric value by extracting the date and time components from the “Attendance Time” column. This helps you perform time-based calculations and comparisons within a table.

=Date value(left([@[Join Time]],find(“,”,[@[Join Time]])-1))+time value (median([@[Join Time]],find(“,”,[@[Join Time]])+2,Ren([@[Join Time]])))

It inserts a formula that works and asks you to repeat the same for the vacation time column.

Now add a column for vacation time using the same type of formula.

Copilot in Excel window with prompt above and response below.

Looking at B2:F137, there is one column of formulas to review and insert into column G.

Convert each attendee’s check-off time to a date-time numeric value to make time-based calculations and comparisons easier.

=Date value (left ([@[Leave Time]],find(“,”,[@[Leave Time]])-1))+time value (median([@[Leave Time]],find(“,”,[@[Leave Time]])+2,Ren([@[Leave Time]])))

After adding the two new columns my table looks like this:

Partner meeting table with columns for meeting name, attendee ID, join time, clock out time, join time value, and clock out value.

I renamed the columns and hid the original join and exit time columns, and the table now looks like this:

Partner meeting table with columns for meeting name, attendee ID, join time, and leave time.

Next, we want to replace the attendee ID with the attendee name. The same workbook also contains the following table containing attendee IDs and names:

Attendees table with ID and name columns

I ask the co-pilot:

Look for names in other tables on this sheet and insert a Name column to the left of Attendee ID.

Copilot in Excel window with prompt above and response below.

Looking at B2:G137, there is one column of formulas to review and insert into column C.

Retrieve the name of each conference attendee by matching their name and ID in the Partner Conference table (Table219).

=XLOOKUP([@[Attendee ID]],Table 219[ID],Table 219[Name])

Copilot suggests an XLOOKUP formula that, after insertion, makes the table look like this:

Partner meeting table with columns for Meeting Name, Attendee Name, Attendee ID, Join Time, and Exit Time.

Finally, I want to add a column that uses join and leave times to calculate how long each attendee has been in each meeting. I ask the co-pilot:

Add column to display duration using joining and leaving times

Copilot in Excel window with prompt above and response below.

Looking at B2:H137, there is one column of formulas to review and insert into column I.

Calculate how long each attendee has been in the meeting by subtracting their participation time from their vacation time.

=[@[Leave Time]]-[@[Join Time]]

After inserting the suggested formulas, my table looks like this and is all ready for analysis.

Partner meeting table with columns for Meeting Name, Attendee Name, Attendee ID, Join Time, Exit Time, and Meeting Duration.

Stay tuned.

Microsoft Excel Team

*Disclaimer: If you’ve tried these types of messages and they don’t work as expected, it’s most likely due to our gradual feature rollout process. Please try again in a few weeks.