Tutorial: Connect SharePoint List To Excel - Part 2 - Display Person Type Column Values

Iron Contributor

YT Thumb - Connect SP List Part 2.png


This video is a follow up to one of my earlier and more popular tutorials where I demonstrate how to connect a SharePoint Online list to a Microsoft Excel spreadsheet. In my earlier tutorial, I demonstrate how you can connect SharePoint and Excel, but I did not cover how to display the values of complex column types such as the email of a user selected in a person type column, or even their name. You will note that when you establish the connection, these complex column types will display as [List] or [Value]. Since publishing that tutorial, I've received countless questions asking how to do this. And so in my latest tutorial, I demonstrate how you can use Microsoft Power Query in Excel to display the values of complex column types after connecting a SharePoint List to Excel. Check it out below!

2 Replies

@LuiIacobellis This was a great tutorial! I have a SharePoint List with 'Person or Group' fields that allow multiple selections. These fields output to Excel as a "[Table]". When I use the approach you suggested within Power Query to expand the values, a new row (duplicate other than the Person field) is created for each person selected in the SharePoint List field. Do you have a recommendation for how to display multiple selected Person values in one cell rather than duplicating each of the rows in Excel?

@LuiIacobellis great tutorials! I only have one question: Is there a way to bring the display name for the columns instead of the "true" name (Field1, Field2). I can see that excel doesn't bring the columns' display name by default and it can be challenging to switch back manually when you have many columns.