Create a list from Microsoft Excel
Published May 28 2020 08:00 AM 103K Views
Microsoft

Every month, tens of millions of people turn to SharePoint lists to track and manage critical business and team data. Lists enable organizations and teams to store and visualize rows of data to share and collaborate on scenarios like inventory management, status reporting, deal milestones and more. They are easy to use, secure, with high capacity up to 30 million items in a single list.

 

Today, we’re pleased to announce lists from Excel is now rolled out worldwide to customers in Microsoft 365. This is a new way to create a list, saving you time while putting the data in a location that opens new scenarios.

 

Let’s dive into the details…

 

Lists from Excel

To create a list from Excel, you can select a table from the Excel files in the SharePoint site, or from your device. You can change the field type of the column if needed, and all your table data will be copied to the new list.

 

When you create a list from Microsoft Excel, you map table data to a new list – adjusting column types before you click Create.When you create a list from Microsoft Excel, you map table data to a new list – adjusting column types before you click Create.

A quick step-by-step ‘how to’

  1. From within your SharePoint site, click the upper-right gear icon and select Site contents*
  2. At the top of the Site contents page, Click New > List
  3. Click the From Excel tab
  4. Enter a name for your new list
  5. Click Upload file to select from your local device or pick from files already in Microsoft 365
  6. Update the column type headers; for example, change "Number" to Date and time; choice fields work, too, automatically aggregating unique values.
  7. Click Create to import the Excel table data and create a new SharePoint list

Adjust the SharePoint column types before you import the Excel table data into Microsoft 365.Adjust the SharePoint column types before you import the Excel table data into Microsoft 365.

Your Excel data is now intact; your SharePoint list ready to extend in numerous ways:

  • Further assign and adjust data types to columns in your list
  • Enhance visuals by designing list row and column formatting
  • Use conditional formatting rules to make the list data intuitive and helpful
  • Set reminders on list items
  • Build productivity apps with Power Automate (custom flows) and Power Apps (custom forms); your list becomes the foundational data source.

Further refine your list with conditional formatting on rows and columns.Further refine your list with conditional formatting on rows and columns.

Note: You can analyze SharePoint list items from a view of the list to Excel – to work with the data in a spreadsheet; simply click Export to Excel. Note: Excel creates an Excel table with a one-way data connection based on a web query file. To bring a fresh copy of the SharePoint list to Excel, select Refresh All on the Data tab from within Excel. Changes made to the Excel table will not be sent to the SharePoint list. Learn more how to export to Excel from SharePoint.

 

In Summary

It is easier than ever to get started with SharePoint lists – using Excel as shown above or based on existing lists. No matter how you start, it’s then easy to further configure lists by using views, filters, rules and reminders to increase the usefulness of your data – especially as data changes or is missing.

 

You can further customize lists, too. It is possible to further extend with native integrations leveraging Power Apps and Power Automate. And when your forms and workflows get more complex or pull from multiple sources lean on the Power Platform tools directly available from your list.

 

Note | We recently announced Microsoft Lists and how it is an evolution of SharePoint lists. For the list from Excel announcement above, we want to emphasize that you will be able to create a list from Microsoft Excel today and in the future when Microsoft Lists begins to roll out and broaden the lists story.

 

Learn more about how to create a list from Microsoft Excel and see it in action, via demo video:

 

 

... or in this SharePoint list: "Create list from Excel" click-thru demo.

 

Additional resources:

 

Thanks, Mark Kashman, senior product manager - Microsoft 

 

*Frequently Asked Question

Q: When will I see the new list creation user interface from a team site home page (New > List) drop-down menu?

A: We are planning to include the New > List entry point from a site home page early Summer 2020. Until then, you can accomplish the above actions from the site’s Site Contents page, accessed from the upper-right gear icon menu.

22 Comments

That's great, but no sync

Copper Contributor

Yes. sync is needed. 

Iron Contributor

Also, I have tried this in 2 separate tenants and there are 2 problems:

 

  • The upload option does not work - it just spins and spins and spins
  • The select from files on this site option, only allows you to grab a file from the default shared docs library

If you import the excel file, do the vies already existing in the workbook get imported also or wil I need to re-create them?

Copper Contributor

Can I input multiple line(multiple index) using 1 form rather than input one by one in list share point? Or if can't, can I do duplicate line from list to speed up user input the list?

Steel Contributor

When importing from a table that includes date and time columns, only the date is imported, not the time. That's really not helpful.

 

Also, to those who say that sync is needed...where would these lists sync to? It makes sense for Document Libraries to sync because files are files. There is no concept of "Lists" in Windows (or Mac OS, or Linux) - at least not in the same sense as Lists in SharePoint. In classic SharePoint, there were some list templates that could be synced to Outlook, like Calendars, Contacts and Tasks. While this was an occasionally useful function, it had serious limitations that frustrated many end users. It made people think of their SharePoint Calendar as an Outlook Calendar, and it wasn't, which ultimately confused and annoyed people.

 

Or, do you mean you want two-way sync between the Excel file and the SharePoint list? That would be cool, but I'm sure is much more complicated than we would imagine. 

@Chad_V_Kealey , yes, two-way sync will be quite useful.

Copper Contributor

Beware!!!

I uploaded an excel sheet with list of part time coaches insluding pay rates. As soon as the Sharepoint List was created I set permissions so only my PowerAutomate account had access. ALl good.

A user went to the main Sharepoint Site for the tenancy which lists all the sites they have access to. Also 'popular' documents available on the site.

 

My Excel sheet had been stored in Site Assets.

 

I have deleted it now and removed form recycle bin but talk about having a heart attack. I was really confused as to why anyone could see the data in the list. They couldn't as the permissions were set  properly.
I had no idea that excel sheet had been put into site assets and left there for anyone with read access to the site to read.

Microsoft

Hi @ScottJohnJohnson - the permissions would be based on the team site. If they visit the site and have the ability to see content, the list and Excel file would viewable. If you create the list as a personal list ("My lists" in UI when creating the list), one not bound by the team membership, rather you as the sole owner until you share the list, the Excel file would follow these same permissions. Hope that helps, Mark 

Copper Contributor

Hi Mark,

I understand that and all users have to have read access to site assets.

I wasn't aware that the Excel sheet would be stored in Site Assets though. It was sensitive data. Looking through the guides on how to use this, I couldn't see any warning and didn't want others to be in the same boat. Luckily I found out before it went everywhere.

Just warning others. 

 

When you say create a blank personal list, do you mean in your OneDrive? I can't see this as an option in the Sharepoint list dialogue. Would be useful.

Microsoft

Hi @ScottJohnJohnson - I'll send a note to our documentation team to provide your feedback about what happens to the .xlsx sheet when importing data from Excel to Lists - so it's clear. To create a personal list, you need to do this from the Lists app in Microsoft 365 - aka, click on the Lists app icon in the Office 365 app launcher; some people call this "Lists home." When you create a new list, including from Excel, you can choose to create it as a personal list or in a team space. When you create from a team space (in SharePoint or Teams), the service assumes you are creating it in that space - so it doesn't give you the option for "personal." Hope that helps, Mark

@Mark Kashman How can I get a date/time field into SharePoint with the time that I have in Excel? When I load the list, I see the Excel time stamp in General format, with the time as decimals. When I select the data type for the SharePoint column, the time gets cut off. I need to be able to import dates WITH THEIR times as one value. Excel supports date/time, SharePoint supports date/time, but they don't seem to speak the same language. What's your advice how to solve this?

2021-10-07_09-47-55.png

OK, I just saw that the preview shows 12 AM, but the data is actually imported with the correct time. All good.

Brass Contributor

I did this but there is no option for PowerApps forms.  How to I get that option?

SteveHendy_0-1638460253221.png

I need it to look like this.

SteveHendy_1-1638460343325.png

 

Brass Contributor

I created a new list in the Lists App and saved the list in SharePoint and now I see the options I wanted.

Steel Contributor

@SteveHendy , is the list in SharePoint Online (not an on-prem SharePoint server)? If so, it could be a tenant configuration or a licensing issue.

Copper Contributor

For those asking for a sync funtion below is a worksheet that does somthing similar. It does have quite a few things it will not do like being able to choose 2 options for a column or use the rich text portion of multi line text. Once the SharePoint list is make it can then be synced to the excel. This is not a lot of instruction but I hope people will find a use or make it better.

 

https://github.com/gif-Drabek/SharePointListEditor

Copper Contributor

Hi - I am developing our team SharePoint internal portal. Note - our team handles only Major Incident or Special Severity incident. Therefore, ticket volume is very less - but each individual ticket is very crucial with respect to problem management. Moreover, management needs a both open & close ticket summary. 

 

I am looking for a support -- that if one excel or list is created with fields like Account , Issue Details, Reported Date & Time & Resolution Date & time. Can I create a view of this list in two separate tab 1) Open Tickets 2) Past Tickets. If so , then how ?

 

A support on this is highly appreciated!! :smile: I am new to SharePoint . since when , I started working on SharePoint  - I found it has lot of features. 

Silver Contributor

@Arup_roych in SharePoint you don't have tabs in the way Excel does, but you just create 2 views and can then access each ofd them from the dropdown at the top right of the list. 

 

viewsList.PNG

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Copper Contributor

How to add excel data in exiting Sharepoint Lists?

Silver Contributor

@andrewlu copy the items from Excel, In SharePoint edit the list in grid view, click on the Add new item link at the bottom of the list, click in the first column and paste. It's a bit hit & miss; you might need to click in the second column then back in the first column and paste to make it work. The items will be pasted in, then exit grid view.

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

 

 

Copper Contributor

This was helpful, thanks for sharing!

Version history
Last update:
‎Jun 13 2020 06:14 PM
Updated by: