'%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 Paste Multiple Excel Rows into a SharePoint List with Auto Unique IDs
Question details
The user wants to paste multiple rows of data from an Excel spreadsheet into a SharePoint list and have the system automatically generate the read-only Unique ID column for each new entry.
- Product
- SharePoint Online, Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying and pasting bulk spreadsheet data into a SharePoint Online list without encountering validation errors related to the Unique ID.
- Observed behavior
- The user needs to successfully transfer bulk data into editable columns, allowing SharePoint to auto-generate the read-only Unique ID for each new row.
Ensure that the columns in your Excel spreadsheet exactly match the order of the editable columns in your SharePoint list view, and intentionally omit the Unique ID column from your copied data.
Paste Excel Rows Using Edit in Grid View
Use SharePoint's Grid View to paste bulk data directly into editable columns, bypassing the Unique ID field so it generates automatically.
The Unique ID column in SharePoint is a system-generated, read-only field. When adding new items in bulk, attempting to paste data into this column will result in an error. The solution is to use the Grid View feature and only paste data into the editable fields.
Navigate to your SharePoint list and click on the 'Edit in grid view' button located at the top command bar.
Ensure that your current grid view includes all required columns for the list. You must not include or select the Unique ID column during the paste process.
Open your Excel spreadsheet and copy the multiple rows of data, ensuring the columns align perfectly with your SharePoint list's editable columns.
In SharePoint, select the first empty cell in the first editable column of the grid. Press Ctrl+V (or Cmd+V on Mac) to paste your data.
Click 'Exit grid view' on the top command bar. SharePoint will now process the new rows and automatically generate the Unique IDs for each entry.
Troubleshoot Browser Issues and Contact Support
If pasting in Grid View fails or produces an unexpected error, rule out browser caching issues or escalate the problem to Microsoft 365 support.
Prepare Your Bulk Data Seamlessly with WPS Spreadsheet
While SharePoint lists are managed within your Microsoft 365 environment, you don't need a paid Microsoft Office subscription to prepare, manage, and copy your bulk spreadsheet data. WPS Office provides a free, lightweight, and highly compatible alternative for handling all your data entry tasks.
- 1. Install WPS Office: Download and install WPS Office Free on your computer.
- 2. Open Your Data: Launch WPS Spreadsheet and open the Excel file containing the data you want to migrate.
- 3. Copy and Paste: Easily organize your columns to match your SharePoint view, copy the data, and paste it into SharePoint's Grid View.
Frequently Asked Questions
Why do I get an error when pasting rows into a SharePoint list?
Errors usually occur if you accidentally include the read-only Unique ID column in your copied data, or if required columns are missing from the current grid view. Ensure your copied data perfectly matches the editable columns in SharePoint.
Can I manually edit or assign the Unique ID column in SharePoint?
No, the Unique ID column is a read-only, system-generated field managed automatically by SharePoint. It cannot be manually edited, assigned, or updated via a paste operation.
How do I collect HAR logs for Microsoft Support if the paste continues to fail?
You can collect HAR logs by opening your browser's Developer Tools (F12), navigating to the Network tab, ensuring 'Preserve log' is checked, reproducing the paste error in SharePoint, and then exporting the network activity as a HAR file.
