Jun 29, 2017 - I just faced the exact same problem. Solved it by getting the link to the Excel file on Sharepoint (right click -> get a link). You then need to. Export to Excel from SharePoint Select Export to Excel from the command bar of the SharePoint list. Click OK > Open after the file download begins. Click Enable, if prompted and you trust the SharePoint site. In the Import Data dialog box, select the How you want to view this data.
Hi
Think this may have more to do with Sharepoint than excel, but not sure where this post should sit?
I have a master excel sheet (on sharepint) that is linked to around 30 excel files in different folders on sharepoint. When I update the data on the master spreadsheet, it updates all the other excel workbooks when they are opened and the update links button is chosen.
I want a summary excel spreadsheet (in sharepoint) linked to the individual worksheets that will update when the file is opened, however when I open the file and update links, the figures are replaced with #REF!. When the individual file is opened up, the figures reappear, but it's impractical to open up every excel worksheet, just to update this workbook.
Has anyone come across this before?
Any help would be greatly appreciated.
Thanks
Chock
A while back I published a post where I explained how to export SharePoint lists from SharePoint to Excel. Today, I am going to cover the opposite of this – how to import Excel to SharePoint. Say, you have an Excel list/table and want to bring it to SharePoint (because SharePoint custom list kicks ass!). How do you achieve this? Well, let me explain. You have a few options to choose from!
Option 1: Import using Quick Edit
The first option assumes that you already have a SharePoint list with all the corresponding columns, and just need to copy/paste the content from Excel to SharePoint. Here is what you need to do to achieve this:
- Build out all the columns exactly as they appear in Excel
- Make sure columns in a view are in the same order as in Excel (otherwise when you do copy/paste, you will get a mismatch)
- If you are taking advantage of metadata(drop-downs), make sure to populate all the possible choices into respective columns (otherwise, when you paste, and pasted text does not match the drop-down choice, the row will be rejected)
- Open your list in Quick Edit (Datasheet view for those in SharePoint 2010)
- Copy the rows you want to copy from Excel
- Paste into Quick Edit View
- You will notice the rows being pasted one by one. You might need to give it some time if you have lots of rows. In case you get a mismatch in data being imported, you will get a red warning just like in the image below
- When all data is successfully pasted/mismatches resolved, just click on Exit Quick Edit. You are all set!
Pros
- Allows to take advantage of metadata and different types of columns in the existing list (i.e. Text, Choice, Date, Currency, etc.)
Cons
- Takes time to set up the list and view before you can import/paste
- Might not be a good technique for importing large data sets
- Error handling capabilities are limited (if you have a row that fails, might take time to figure out the issue/field causing issue and you might need to redo the whole import)
- Import will not work if you set up metadata drop-down using Term Store (managed metadata). Click here to read more about this issue
Option 2: Import Spreadsheet Web part
Another option that is available to import Excel to SharePoint is to use a Web Part called “Import Spreadsheet”. These are the steps to follow:
- Gear Icon > Add an App
- Scroll down to Import Spreadsheet App. Click on it.
- On the next screen, give your new app/list a name, then choose an Excel file. Click Import
- You will now notice an Excel file open up with a pop-up window where you need to select a range of cells to import. Once you choose the range of cells in the pop-up, click Import.
- The table will now be imported to SharePoint. It will go ahead and create a custom list with proper column headers and values
Pros
- Relatively easy to use
- Does not require much prep time like Option 1
Cons
- Have to use Internet Explorer browser for this option. If you use Google Chrome, you will get an error message like this
- While import does a good job of recognizing some field types (i.e. numbers, dates), some fields that you would have preferred being drop-down choices are imported as text
Option 3: Import directly from Excel
Another great option is to import data right from within Excel. This is what you have to do:
- In Excel spreadsheet, select the data range and Format it as a Table
- While still in Excel, highlight the table, then choose Export from the top ribbon, then Export Table to SharePoint list
- From the pop-up that appears, specify the URL of the site you are importing to, then give the list a name, then hit Next. You can also (optionally) check the box next to Create a read-only connection to the new SharePoint list. What it does is make your Excel physically linked to SharePoint list. Any changes in the SharePoint list will propagate down to Excel when you refresh the data there.
- On the next screen, you will see all the columns with corresponding recognized column types (Currency, Date, etc.). Just click Finish
- You should be getting a success message/pop-up
- You should now see your list created in SharePoint. It will default to Datasheet view, just click Stop to view it as a normal list
Pros
Same as Option 2
Cons
- Same as with Option 2 some drop-down choices are recognized as Text
- The list created is a classical list and not a modern one (for those who are in SharePoint Online)
- By default, the list opens up in Datasheet view. You always have to click Stop Editing to see a “normal” list