Lookup Column Shows Deleted Items: Fix

When you use a lookup column in SharePoint, you expect it to show only valid items from the source list. But sometimes the lookup column displays items that have been deleted from the source list. This can cause confusion for users who see old or irrelevant data in forms, views, and reports. The root cause is how SharePoint handles the relationship between the lookup column and its source list: it stores the item ID, not the item text, and when the source item is deleted, the ID becomes orphaned. This article explains why this happens and provides step-by-step methods to remove or hide those orphaned entries.

Why Deleted Items Still Appear in a Lookup Column

A lookup column in SharePoint does not store the text value of the source item. Instead, it stores the internal integer ID of the item in the source list. When you delete an item from the source list, SharePoint does not automatically update the lookup column in the target list. The lookup column still holds the old ID. When SharePoint renders the lookup column, it tries to find the source item with that ID. If the item is gone, SharePoint returns the ID as a plain number, which looks like a blank or broken entry. In some cases, especially with site columns used as lookups, SharePoint may cache the old text value and keep showing it.

The behavior depends on how the lookup column was created. A lookup column created directly in a list (list-scoped) can have relationship enforcement settings. A lookup column created as a site column (site-scoped) does not support relationship enforcement. Site column lookups are more likely to show orphaned items because there is no way to tell SharePoint to delete or restrict the source item.

Relationship Enforcement in List-Scoped Lookups

When you create a lookup column at the list level, you have three options under “Enforce relationship behavior”:

• Cascade Delete: Deleting a source item also deletes all items in the target list that reference it. This removes the orphaned lookup value entirely.
• Restrict Delete: SharePoint prevents you from deleting a source item if any target item references it. This keeps the lookup column clean.
• None: No enforcement. Deleting a source item leaves orphaned IDs in the target list. This is the default for site columns.

Steps to Fix a Lookup Column That Shows Deleted Items

Use the method that matches your situation. If the lookup column was created at the list level, the easiest fix is to change the relationship enforcement. If it is a site column, you must use PowerShell to clean up the orphaned items.

Method 1: Change Relationship Enforcement on a List-Scoped Lookup

1. Open the list settings
   Go to the list that contains the lookup column. Click the gear icon in the top right and select “List settings.”
2. Locate the lookup column
   Under the “Columns” section, click the name of the lookup column that shows deleted items.
3. Change relationship enforcement
   Scroll down to the “Relationship” section. For “Enforce relationship behavior,” select either “Cascade Delete” or “Restrict Delete.” Click OK.
4. Verify the change
   Go back to the list view. The orphaned items may still appear in existing rows until you manually refresh the list or re-save those items. For cascade delete, you must delete the source item again to trigger the cleanup.

Method 2: Remove Orphaned Items Using PowerShell (Site Column Lookups)

This method works for any lookup column, but it is required when the lookup is a site column. You need SharePoint Online Management Shell or PnP PowerShell installed.

1. Connect to SharePoint Online
   Open PowerShell as an administrator. Run Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive.
2. Get the target list and lookup column
   Run $list = Get-PnPList -Identity "YourTargetListName" and $field = Get-PnPField -List $list -Identity "YourLookupColumnName".
3. Find items with orphaned lookup values
   Run a script to iterate through all items in the target list. For each item, check if the lookup field value points to a valid ID in the source list. If the source item does not exist, delete the target item or clear the lookup field. A full script example is available on Microsoft’s documentation site.
4. Delete or clear the orphaned items
   Use Remove-PnPListItem -List $list -Identity $item.Id to delete the target item, or set the lookup field to null and update the item.

Method 3: Recreate the Lookup Column as a List-Scoped Column

If the lookup column is a site column, you cannot enforce relationship behavior. The permanent fix is to delete the site column lookup and create a new list-scoped lookup column with the same settings.

1. Remove the site column lookup from the list
   In list settings, click the lookup column and select “Delete.” Confirm the deletion.
2. Create a new list-scoped lookup column
   Click “Create column” in list settings. Choose “Lookup” as the column type. Set the source list and source column. Under “Relationship,” select “Cascade Delete” or “Restrict Delete.”
3. Map the old data to the new column
   Use PowerShell or a quick edit in Datasheet view to copy the lookup values from the old column to the new one. This step is necessary only if you need to preserve the existing data.

If the Lookup Column Still Shows Deleted Items After the Fix

Lookup column shows old text even after source item is restored

SharePoint caches the text value of a lookup column. If you restore a deleted source item, the lookup column may still display the old text for up to 24 hours. To force an immediate update, open the target item in edit mode, clear the lookup field, save, then re-select the correct value and save again.

Orphaned items appear in views but not in the list

This happens when the lookup column uses a site column and the view has a filter that hides the valid items. Check the view settings and remove any filter that might be hiding the valid lookup values. Then use PowerShell to delete the orphaned items.

Deleting the source item does not cascade delete

Cascade delete only works if the lookup column is list-scoped and the enforcement is set to “Cascade Delete.” If you set the enforcement after the orphaned IDs already exist, the cascade delete will not apply retroactively. You must delete the source item again after changing the enforcement to trigger the cleanup.

Item	List-Scoped Lookup	Site Column Lookup
Relationship enforcement	Cascade Delete, Restrict Delete, or None	None only
Shows orphaned items after source delete	Only if set to None	Always
Fix method	Change enforcement setting	PowerShell cleanup or recreate column
Data loss risk	Low if using Restrict Delete	High if using cascade delete on a site column lookup

You can now identify why a lookup column shows deleted items and apply the correct fix. For list-scoped lookups, change the relationship enforcement to Cascade Delete or Restrict Delete. For site column lookups, use PowerShell to remove orphaned items or recreate the column as a list-scoped lookup. To prevent this issue in the future, always create lookup columns at the list level and set the enforcement to Restrict Delete. This keeps your data clean without automatically deleting related items.