(this post was written using a SharePoint 2010 environment)

A while ago I was trying to find a way of getting a list of all user profiles that didn’t have a profile picture uploaded. I searched around online a bit and could only find examples that involved writing PowerShell which I thought was way to complicated a thing to do!

So this post will show how you can easily export user profile data from SharePoint, apply some excel filters to find all users without a profile picture.

This method applies to all on-premises versions of SharePoint, but also will work for SharePoint Online.

Get user profile data

• To get started, open the Excel desktop app
• Press Get Data > From Other Sources > SharePoint List
• Enter the root site collection URL for your SharePoint environment
• Press OK

This will open a navigator window, which will display all the available lists within the root site collection.

• Scroll down until you find the UserInformationList
• Click on it and a preview will load, scroll across the preview and make sure you can see the Picture column
• Press Load

NOTE: at first I was using the Get Data > From Web option but that only brought back the first 30 rows from the UserInformationList.

I didn’t want to increase the view limit as there are thousands of rows to display but I found that the way described doesn’t have that issue.

Now this will take a little while to download all the rows if you have a fair few users, but you’ll see when its finished in the right-hand  Queries and Connections pane as it shows you the number of rows downloaded once complete.

Filter your data

The first thing to note about this data is that it does also contain some stuff you wont need. With my list data I noted that I had SharePoint security and domain groups, service accounts and other general use/ non-user accounts listed. Here are some of the filters and logic I applied:

• Filter the Picture column to only show blanks
• Filter the Content Type column to only show Person
• Or filter the ContentTypeID to only show the corresponding Person content type ID
• Filter SIPAddress to only show rows with an organisational email address

That’s all there is to it, a pretty quick and super easy way to get a list of all user profiles without a picture 🙂