'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix SharePoint Lookup Column Exporting as Numbers in Excel
Question details
The user needs to export a SharePoint list to Excel, but the lookup column displays numeric IDs instead of the associated text, such as the company name.
- Product
- SharePoint / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Exporting or querying a SharePoint list in Excel where a lookup column is involved.
- Observed behavior
- The lookup column exports as numeric identifiers (e.g., Company Name ID) rather than the actual display text of the related item.
Confirm whether you are extracting data using SharePoint's native 'Export to Excel' button or Excel's Power Query (Get Data) function, as the troubleshooting steps differ based on the method.
Expand the Lookup Record in Power Query
If you are using Power Query to pull data from SharePoint, lookup columns are imported as records by default. You must manually expand the record to display the text field.
Power Query connects to the backend of the SharePoint database. Because a lookup column links to another list, Power Query initially pulls the raw relational data, which includes the numeric ID. Expanding the record allows you to select the exact display column you need.
In Excel, go to the 'Data' tab and click 'Queries & Connections'. Double-click your SharePoint query to open the Power Query Editor.
Scroll through your data preview until you find the column representing your lookup data (e.g., Company Name). It will likely say 'Record' in every cell.
Click the small split-arrow icon in the top right corner of the column header. A dropdown menu will appear.
Uncheck the 'ID' box and check the box for the specific text field you want to display (e.g., Title or Company Name). Uncheck 'Use original column name as prefix', then click 'OK'. Load the data back into Excel.
Verify SharePoint Lookup Column Settings
Check your SharePoint list settings to ensure the lookup column is properly configured to display text and is not unnecessarily appending the ID field.
Export as CSV as a Workaround
If maintaining a live, refreshable connection is not strictly required, exporting directly to a CSV file often bypasses the numeric ID issue.
Manage Your Exported Data Seamlessly with WPS Office
While SharePoint data connections require specific configuration in the Microsoft ecosystem, handling your exported CSV and spreadsheet data is effortless with WPS Office. It provides a highly compatible, lightweight, and completely free alternative for all your spreadsheet analysis tasks.
- 1. Download WPS Office: Get the free WPS Office installer from the official website and complete the quick installation process.
- 2. Open Your Exported Data: Launch WPS Spreadsheet and open the .csv or .xlsx file you successfully exported from your SharePoint list.
- 3. Analyze and Format: Use WPS Spreadsheet's built-in data tools to filter your text lookup columns, apply formatting, and analyze your data without subscription costs.
Frequently Asked Questions
Why does my exported Excel file show 'Company Name ID' instead of the company name?
This happens when Excel queries the SharePoint database directly. A lookup column stores the relational database ID of the linked item, not the text itself. Depending on your export method (like Power Query), it pulls this raw ID instead of the linked text value.
Does exporting to CSV fix the SharePoint lookup column issue?
Yes, exporting to CSV usually bypasses the issue because it exports a flat representation of your current list view. However, a CSV file is static and will not maintain a live, refreshable connection to the SharePoint list.
How do I maintain a live connection to my SharePoint list while displaying text?
You should use Excel's Power Query (Get Data > From Online Services > From SharePoint Online List). Once imported, open the Power Query Editor, locate the column (which will say 'Record'), and expand it to select the text field while unchecking the ID field.
