• Call Toll Free: 1-855-909-3300

Office 365 Guide Series: Create a New List from an Excel Spreadsheet

This task is modest if everything goes smoothly.But if you have never done it before, or if you have tried and got stuck you may encounter, then this is a perfect office 365 support for you.

Start by creating your Excel spreadsheet. Make sure that it is an xlsx file. You might use an existing spreadsheet with your data; copy from an old file, and save another format as xlsx.

In the spreadsheet, follow the following rules:

  • Make sure that you do not have any empty column headers between columns. This will cause all headers to be imported as row 1 instead of being headers, new column headers will be created as 1, 2, 3 and so on.
  •  Keep the spreadsheet ‘clean’, remove empty rows, empty columns and any text above the header row and to the right or under of the table or cell range.
  • The first column to the left will be the default edit field in the SharePoint list. Make sure that it has values on all rows.
  • For best result, avoid formulas with calculated values.

Use Excel 2013.In a SharePoint Online site, do this:

  1. Decide on a name for the list, it will be the URL and name of the list. Go to the ‘cogs’/Settings and select Add an app
  1. You will now see a list of all available apps (installed). Search for Import or scroll down until you find the app called Import Spreadsheet. Click on Import Spreadsheet
  1. Type in the Name of the new list. Type in an optional Description. At the File location field, click Browse. Browse toyourxlsx file on your computer. Click on Import
  1. The Import Wizard starts, by default, the Range type is set to Table Range. Change this to Range of cells. Click in the Select Range field. In the Spreadsheet, select the top left cell and then make sure that you select the entire table of data you want imported. It should now read something similar to: Sheet1!$A$1:$G$400 (top left : bottom right)
  1. Now you click on Import. You may now get a logonprompt from Excel, enter the email address for the SPO account and click next. Enter the account password and click on Sign in. Excel will now create the list and start importing the data, you will see a little progressbar at the bottom of the Excel application. When the import is done, the new list will open and you will see the columns from the top.  Note: You may note sometimes that a ‘mailto:’ has been added to the email column values.
  1. This is added in excel but hidden when a cell is formatted as an email address. In the example above, note that the first row has the value of ‘Email’ which made the list field be formatted as single line of text instead of email.
    The trick is most of the time to make sure that all field values in a column are consistent in format. One cell with a different format will cause the entire column to get a different formatting.
  1. Done!

Hoping this office 365 support guide makes your excel tasks easier.

Leave a Reply

Techarex NetWorks Products