When a SharePoint lookup column displays items that you have deleted from the source list, site owners often find the behavior confusing. The root cause is that a lookup column stores the ID of the source item, not the item itself. If the source item is deleted, the lookup column still retains the ID, which can appear as a blank or broken reference. This article explains why deleted items show in lookup columns and what settings site owners can check to fix or prevent this issue.
Key Takeaways: Preventing and Fixing Lookup Column Issues
- Lookup column source list settings: Check if the source list item is actually deleted or only removed from a view.
- Site collection recycle bin: Restore deleted items from the second-stage recycle bin within 93 days.
- Column validation and managed metadata: Use managed metadata columns or list item validation to avoid referencing deleted items.
Why SharePoint Lookup Columns Reference Deleted Items
A lookup column in SharePoint works by storing the ID of an item from a source list. When you delete the source item, SharePoint does not automatically remove the reference in the lookup column. The lookup column keeps the ID, which shows as blank, a broken link, or the original text depending on how the column is configured.
This behavior is by design. SharePoint preserves the integrity of the lookup column to avoid data loss. For example, if a project list references a client name from a clients list and the client is deleted, the project list still shows the client name until you manually update it.
Site owners often mistake this for a bug. However, it is a side effect of how SharePoint stores relational data. The lookup column does not enforce referential integrity like a database foreign key. There is no automatic cascade delete.
Steps to Check and Fix Lookup Columns Showing Deleted Items
Follow these steps to identify why a lookup column shows deleted items and to resolve the issue.
- Verify the source list item is truly deleted
Open the source list that feeds the lookup column. Check if the item is in the list or only hidden by a view. Go to the list settings and review the current view. If the item is missing from the list, proceed to the next step. - Check the site collection recycle bin
Deleted items go to the site recycle bin first. Go to site contents and click Recycle Bin. If the item is there, select it and click Restore. After restoration, the lookup column will show the item again. - Check the second-stage recycle bin
If the item is not in the site recycle bin, check the second-stage recycle bin. Only site collection administrators can access it. Go to the site recycle bin and click Second-stage recycle bin at the bottom. Restore the item if found. - Update the lookup column manually
If the item cannot be restored, edit each list item that contains the broken lookup reference. Open the item, clear the lookup field, and select a valid item from the source list. Save the item. - Use a calculated column to show a fallback value
If you want to avoid blank values, add a calculated column to the list. Use a formula like=IF(ISBLANK([LookupField]), "Deleted item", [LookupField]). This shows a placeholder text instead of a blank. - Consider using managed metadata columns
Managed metadata columns store terms from the term store. Deleting a term from the term store does not remove it from items that use it. However, managed metadata is more stable than lookup columns because terms are not tied to list item IDs.
If Lookup Columns Still Show Deleted Items After the Main Fix
Lookup column shows old data after restoring the source item
When you restore a deleted item from the recycle bin, the lookup column may not update immediately. Refresh the page or close and reopen the browser. If the problem persists, clear the site collection cache by going to Site Settings > Site Collection Administration > Site Collection Cache. Click Reset.
Lookup column shows a number instead of text
If the lookup column shows a number, it is displaying the source item ID. This happens when the source item is deleted and the column is set to show the ID. To fix this, restore the source item or change the lookup column to show a different field from the source list.
Multiple lookup columns reference the same deleted item
If several lists use the same source list, deleting one item affects all of them. The fastest fix is to restore the source item from the recycle bin. If restoration is not possible, update each list manually or use a PowerShell script to bulk update the lookup fields.
Lookup Column vs Managed Metadata Column: Key Differences
| Item | Lookup Column | Managed Metadata Column |
|---|---|---|
| Data source | References a list item ID | References a term from the term store |
| Behavior when source is deleted | Shows blank or broken reference | Term remains visible until term store cleanup |
| Recovery after deletion | Restore item from recycle bin | Restore term from term store recycle bin |
| Performance on large lists | Slower due to cross-list lookup | Faster because terms are stored locally |
| Administration | Managed by list owners | Managed by term store administrators |
Site owners can now identify why a lookup column shows deleted items and apply the appropriate fix. Start by checking the recycle bin for the source item. If the item is gone permanently, update the lookup column manually or use a calculated column to display a fallback value. For future lists, consider using managed metadata columns to reduce the risk of broken references.