How to Import an Excel Yes or No Column into a SharePoint List
Question details
The user needs to import a spreadsheet column containing "Yes" or "No" values into a SharePoint list without the formatting changing.
- Product
- Spreadsheet / SharePoint
- Device & OS
- not provided
- Scenario
- Importing spreadsheet data into a SharePoint list using the automatic import tool.
- Observed behavior
- SharePoint detects the "Yes/No" values as "True/False" or "Number" data types, and the system does not allow mapping the Excel column to a native SharePoint Yes/No column.
Ensure you have list editing permissions in your target SharePoint site and that your spreadsheet data contains only clean 'Yes' or 'No' values without blank cells in that specific column.
Manually Create the SharePoint Column and Paste Data
Bypassing the automatic spreadsheet import tool prevents SharePoint from incorrectly converting your Yes/No values into True/False or Choice types.
When using SharePoint's native 'Create from Excel' feature, boolean-style values are often mishandled during data type detection. The most reliable workaround is to set up the list structure directly in SharePoint first, manually configure the Yes/No column, and then bulk-paste your spreadsheet records into the grid.
Upload your spreadsheet to SharePoint to create the initial list, but choose to skip or ignore the Yes/No column during the mapping process.
Once the SharePoint list is generated, click '+ Add column' in the list header, select the 'Yes/No' data type, and name it to match your original spreadsheet column.
Click on 'Edit in grid view' (or Quick Edit) at the top of your SharePoint list. This transforms the list into an editable, spreadsheet-like format.
Open your spreadsheet, highlight and copy (Ctrl+C) the cells from your Yes/No column. Return to SharePoint, click the top empty cell of the newly created Yes/No column, and paste (Ctrl+V) the values.
Click 'Exit grid view' at the top left of the screen to save your changes. SharePoint will now correctly read and display the Yes/No data.
Prepare Your Spreadsheet Data Easily with WPS Office
While SharePoint import formatting quirks are inherent to the Microsoft ecosystem, managing and cleaning your source spreadsheets doesn't have to be. WPS Office offers a highly compatible, robust alternative for organizing your lists before moving them to the web. Enjoy a familiar interface and seamless migration without the heavy subscription fees.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing workbook containing the Yes/No data intended for SharePoint.
- 2. Format and verify: Use WPS Data Validation or Filters to ensure only clean 'Yes' and 'No' values exist in your column, removing any mixed data types.
- 3. Copy to clipboard: Highlight the required data range, press Ctrl+C, and paste it directly into your SharePoint list's grid view for a flawless transfer.
Frequently Asked Questions
Why does SharePoint change my Excel Yes/No values to True/False?
SharePoint's data importer interprets boolean-style values from spreadsheets as standard True/False logic by default. It lacks a direct mapping option for native Yes/No list columns during the automatic upload process.
Can I change a SharePoint Choice column to a Yes/No column after importing?
Usually, no. If the column was imported automatically from a spreadsheet, SharePoint might lock the underlying data type or remove the Yes/No drop-down option in the column settings to prevent data corruption.
How do I ensure my spreadsheet Yes/No column doesn't contain errors before copying?
Use the Data Validation feature in your spreadsheet application to restrict cell entries to only 'Yes' or 'No'. This prevents accidental text entries or blank formatting that could cause list synchronization errors when pasting into SharePoint.
