Forum Discussion
Import Excel to Sharepoint List *with choices*
I have a large table I want to import from an Excel table. Most of the columns should be "Choice" types with Yes/No/NA options. I can import the table successfully, but it appears I have to change the column type for each column manually to "Choice" and also manually edit the options. Is there a way to import this information from the Excel table or is there a way to automate the input of the choice options?
- I wanted to comment that I have the same issue and I think the other reply doesn't actually understand the problem at hand.
The problem is that if you have an Excel table pre-populated with data and you create a list from this table, the pre-existing values in a column that was imported as the "Choice" type land in a sort of super-position.
The reason I say it's in a super position is because if you add a new item to the list, the pre-existing values for the choices *do* show up. But if you go to *edit* a column you cannot edit the column because no choices are present. It's a really clunky interface and I'm not sure how this got past QA testing at Microsoft.
The import of all the columns is definitely a bit annoying too, though in my case I don't have many columns to work with. You have to drop-down each column from the excel table and select the type as "Choice". This interface would be improved if there were a way to bulk-edit columns to be imported by say, using checkboxes to select multiple (and/or ranges) of columns and set the type at once across all.- I am having the same issue as yours. It is very strange that the list of choices was successfully imported when you try editing existing rows but when you edit the column settings, the choices are just not there. Looks like a bug to me
This is a known limitation/issue with the SharePoint lists while creating a new list by importing an excel file.
Choices/Options in the choice column settings will not be added automatically. Also, choices will not be shown in Grid view (quick edit view) even when those are shown while creating new lists.
You have to get the unique values from excel column and add those manually to your choice column settings. Follow: Excel - filter for unique values or remove duplicate values
You can raise a support ticket with Microsoft directly and report this behavior at: Get M365 Support - Online Support
And add new feedback/idea on SharePoint feedback portal to save choices in choice column as well so that Microsoft will implement it in near future: SharePoint Feedback Portal
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
vanme55 when you select New list and From Excel, once you upload your list you will presented with a "preview" where you confirm the data type. By default it is Single line of text, so for each of your choice columns just change it to Choice and it will preserve the choice options from the Excel column for existing items and will allow you to select the choice for new items.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)- If you separate the values like this, "Value1;#Value2;#Value3" it will automatically format them as choices in a "Choice" column. I only figured this out because when you select the values the "normal" way, this delimiter format shows up in the field for a split second!
