Share via

SharePoint 2016: three OOTB methods to make a spreadsheet available in SharePoint

  • Article

Introduction

There are three OOTB methods that anyone can use to make a spreadsheet available in SharePoint as a SharePoint list: 

  • Copy and paste: create a list in SharePoint that has columns of the same data type as the spreadsheet and in exactly the same order; and then you simply copy and paste from the spreadsheet and into the list.  This method allows you to control every aspect of the process, from naming the SharePoint list and its individual columns, to the exact cells and cell ranges that you want to copy over. Creating the new list requires at least Edit permission level, but copying requires Contribute.
  • SharePoint Import: uses SharePoint's Import Spreadsheet tool.  Minimum permission level to perform this activity is Edit.
  • Excel publish: this uses the ability of Microsoft Excel to save a spreadsheet or even an entire workbook directly to SharePoint.  Minimum permission level to perform this activity is Edit, since a new list is effectively being created.

Each of these methods will be presented here.  Note: to create new lists, your account needs to be granted, at a minimum, the Edit permission level.

Copy and Paste

01) Create a new list in SharePoint.

02) Add column types to this list of the same data types as are in the spreadsheet.  Use the Single line of text column type for all alphanumeric data, and use the Number (1, 1.0, 100) column type for data that is numbers only, etc. 

03) Once the list has been fully prepared, click on the Quick Edit button on the LIST ribbon.  This changes the view of the list into a sort of spreadsheet view, enabling you to paste data directly into list cells.  It used to be called Datasheet view in earlier versions of SharePoint.

04) Now, on the spreadsheet, select exactly those cells that you want to copy over.  Don't select entire rows, because they extend off the page and actually capture far more cells than are actually in the list.  Instead, if your spreadsheet has five data columns, then select five cells across and then as many deep as you want to copy.

05) On the keyboard, press CTRL+C.  This copies the selected cells to the clipboard.

06) On the list's Quick Edit view, select the leftmost cell in the bottom row.  This row will always be empty and is a special row in that anything you enter here goes to create a new row in the list.  Note: you need to SELECT the cell, not place the active cursor in it.

07) On the keyboard, press CTRL+V.  This copies the cells saved to the clipboard into the corresponding cells in the list.  If you selected cells across multiple spreadsheet rows, a new row in the list will be created for each row of cells that you copied.  You can do this for 1, 50, 100, 500 or more cell rows at a time, and SharePoint will them into the list, creating as many new list rows as you copied from the spreadsheet.  If things didn't work out right for some reason, just delete those rows in the list that turned out wrong, and try again.

SharePoint Import

01) Ensure that your spreadsheet has column headers.

02) Navigate to the SharePoint site that you want to import the spreadsheet to.

03) Navigate to the site's Site Contents page.

04) Click on the add an app item.

05) Look for the Import Spreadsheet item, and then click on it.  You may need to scroll to the next page to find it.

06) Enter a name for the new list, navigate to the spreadsheet file, and then click Import. The Import to Windows SharePoint Services list dialog appears.  Note that the Import button  is not enabled.

07 On the dialog, select Range of Cells from the Range Type dropdown, and then click on the down arrow on the Select Range dropdown.  This will shrink the dialog and also enable you to select a cell range on the spreadsheet.  As you do, the selection range may not be highlighted, but don't worry: look in the dialog and you'll see it instantly being updated as your selected range changes.

08) On the currently shrunk dialog, select the down arrow.  The dialog will instantly expand back to its original size.  Note that now the Import button is enabled.

09) Click the Import button.  The cursor will change into a busy icon momentarily.  After a few moments, the dialog will disappear, and the browser will be displayed again, still presenting the Import Spreadsheet interface.  After a few moments longer, the browser will be navigated to the new list.  Note that the column names (except for the first one) are the same ones that you had in the spreadsheet and are also present with the same capitalization.  The first one will be numbered and will not display all capitals if that was the original column name format.  The import even is able to distinguish columns displaying text from columns displaying numbers only and configure the list accordingly.

Excel Publish

01) Open the spreadsheet in Excel.

02) Select all of the cells that you want to publish to a new list in SharePoint.

03) On the Home ribbon, in the Styles group, click on Format as Table, and then select the table style that you want to use. The Format As Table dialog appears.

04) On the dialog, verify the cell range, and the header option, and then click OK.  Note that now the Design tab appears in the ribbon; and you'll see Table Tools tab above it.

05) Select the Design tab, and then in the External Table Data group, click on the Export button and select Export Table to SharePoint List...  The Export Table to SharePoint List dialog appears.

06) Enter the URL to the site you want to publish the spreadsheet to and a name for the new list, and then click Next.

07) On the second page of the dialog, verify column formats, and then click Finish. The dialog closes, and a prompt appears that provides a link to the published list.

08) Click the link.  A browser tab or window opens and navigates to the new list.  Note that it opens in Quick Edit view.  That's the default list view when using this method to a make a spreadsheet available in SharePoint.  If you want the regular view, just create a new one.

References

Notes

  • Note that the list's Title column is special in that it presents a link to edit capabilities. It is of type Single line of text. It requires data, but you can re-configure it so that you don't need to use it if you don't want to.  I usually try to use it for one of the spreadsheet columns, like a primary one if possible.
  • When using SharePoint's Import Spreadsheet app, the header of the first column will be used as the list column header but a number will be appended to it; and if the header of the first column in the spreadsheet is all capitals, the import process will edit this and only capitalize the first letter.  All other spreadsheet column headers are imported as-is if they are unique.  Note that this is essentially creating a new list, so, the minimum permission level needed is Edit.