How to find users without profile pictures in SharePoint

(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

Press the From SharePoint List option within Get Data in Excel.

Enter your root site collection URL here.

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

Make sure you select the UserInformationList and that the Picture column is visible.

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 🙂