Table of Contents
- Synchronize your lists with Excel
- Basic authentication over non-SSL HTTP connections
- Known Limitations
- Q&A – Product Features
- Q&A – Product Licensing
- Q&A – Troubleshooting
SharePoint lists are widely used and offer web and grid types of user interface. However, to manipulate and analyze large sets of data, Microsoft Excel is still undisputed. Let’s have a look at native options (offered by Microsoft) to exchange data between SharePoint lists and Excel:
Lists can be exported to Excel. This is great to analyze data. Moreover, this export can be refreshed in Excel so that you can maintain pivots or charts that can be updated over time. However, the link is one way only: there is no way to modify your SharePoint data from Excel.
New lists can be created from existing Excel files. Here the Excel file and the SharePoint list keep no connection after the list is created.
Microsoft used to offer a way to synchronize Excel files with SharePoint lists, but removed this feature in Excel 2007 and future versions.
SharePoint List Synchronizer for Excel brings back this feature, allowing Excel users to open SharePoint Lists in two-way sync.
This is also a great way to access SharePoint lists offline. Sync it when you’re online, change what you want in Excel while offline, and sync it back next time you’re online.
2. Synchronize your Lists with Excel
2.1 Open your SharePoint list in Excel
When you install Synchronizer for Excel and SharePoint, don’t look for it in your Start menu or even in Excel. It doesn’t have a user interface.
Our product modifies the behavior of the Export to Excel button in SharePoint.
Depending on your browser and its settings, two options are possible:
- Click “Open” to open the owssvr.iqy file directly in Excel.
- You might get a message that you need a compatible application to export a list. Click on OK. You will be displayed a text file in the browser. Right click, Save As, and save the file without changing its name (owssvr.iqy). Then browse your computer to find this file, double click it, it will open in Excel.
Depending on your credentials and authentication mechanism, you might be prompted by Excel to enter your SharePoint credentials.
In Excel, right click on a cell in your list, select the Table menu and click Synchronize with SharePoint to synchronize your Excel file with SharePoint.
Yes, it’s so easy!
Note: To retain this functionality, should you want to save your file as an Excel file, you need to save the workbook as Excel 2003 .XLS file format, not .XLSX.
2.2 How to add rows in Excel
You can either insert rows in your Excel list or add rows at the bottom of your existing list.
2.2.1 How to Insert Rows
You need to manipulate the Excel “table”, not the whole Excel worksheet. This means that you can’t right click on the row number (outside of the grid) to insert a row. You need to right-click in the table, then Insert / Table Rows Above.
Add as many rows as needed and synchronize your file back to SharePoint.
2.2.2 How to Add Rows at the Bottom of your Table
If you look closely, you will notice that in Excel your table is enclosed in a thin blue rectangle showing the boundaries of the table that is synchronized with SharePoint.
When you add rows immediately after the last row of your table, Excel will most often pick it up and extend the zone. If it’s not the case, then you can extend it yourself by dragging the bottom right corner.
2.3 How to Delete Rows
First, ensure that no filter is applied to your list.
Select a cell in the row you want to delete, right click and select Table / Delete. Similarly, drag a selection over several rows to delete multiple rows at once.
Do not select the entire Excel row. Your selection should fit within the table itself.
2.4 Ensure the file is synced automatically
You can configure your file (remember to save it as XLS, and NOT as XLSX) so that it will sync with SharePoint automatically each time you open it. That way, you minimize the risk of working on a version that does not reflect the latest changes.
Here are the simple steps to follow to configure this auto-sync:
- Right-click on your data in Excel, click on Table/External Data Properties
- Click on the small box at the end of the Connection name
- Check the box “Refresh data when opening the file”
3.1 Standard Installation
- Download the installation file: visit our home page and fill the form to download the setup file. If you have already requested a trial or purchased a full license, you have also received a download link by email.
- Launch the installer XlSyncSetup.msi. If you have already installed a trial before and need to install your purchased license, you need to uninstall and reinstall.
- If you have a purchased license, enter your license key during the setup. For trial, skip the license.
3.2 Silent Installation
To install the tool silently you can use the following command line:
$>msiexec /i XlSyncSetup.msi /qn XLSYNC_KEY=<provided key>
Note: Don’t forget to replace the “<provided key>” tag by your actual key.
Note: The key is per user, not per device. It is automatically installed for the user running the installation. If you need to register your license key for another user, you need to plan for it, running the following command under that user session. For example, using a GPO.
$>”C:\Program Files (x86)\SoftFluent\XlSync3\XlSync.exe” /i /k:<provided key> /q
Check Setup Status
If you have a doubt whether SharePoint List Synchronizer is correctly installed, launch “SharePoint List Synchronizer for Excel – Configuration” from your start Menu.
From there on, there are 4 possible options:
- If you can’t find it… our product is not installed. Try again.
- If you see that current association is Synchronizer For Excel and SharePoint, you’re all set.
- If you see that association is “Microsoft Excel…”, then our product is installed but inactive. Click on Associate to finalize the process.
- If you see anything else, contact us at [email protected]
4. Basic authentication over non-SSL HTTP connections
Using Microsoft Excel 2010
By default, Excel 2010 doesn’t prompt for user name and password when accessing basic authentication over non-SSL (HTTP instead of HTTPS) protected sites. Therefore, when trying to access a list on one of those access protected sites results in the following error:
The Synchronizer for Excel and SharePoint supports such scenario thanks to its “Basic authentication over non-SSL connections” feature.
In a nutshell, activate this feature if you are using Excel 2010 and your SharePoint Lists are hosted on a site with basic authentication activated over non-SSL HTTP connections.
To activate the feature:
- Launch “Synchronizer For Excel and SharePoint – Configuration” from the start menu
- Click on “Advanced”
- Then check “Enable Microsoft Office Excel 2010 Basic authentication over non-SSL HTTP connections”.
Note: To disable the feature, repeat the same steps and uncheck it.
By activating this feature, Excel will now prompt users for their credentials and pass them to the site. This way, Excel 2010 users will be able to open SharePoint Lists from access protected sites in two-way sync over again.
Using Excel 2013 and higher.
With Excel 2013, 2016, 2019 or higher, for security reasons, you must use SSL if you use basic authentication.
Important note: Even if you uninstall Excel 2013/2016/2019 and revert to previous versions, you won’t be able to use basic authentication without SSL.
5. Known Limitations
Unsupported Column and Data Types
Most column types are supported. Here are the known limitations:
- The following type of columns are Read-Only in Excel when synchronized with SharePoint:
- Multiple selection drop down lists:
- Excel doesn’t support drop downs with multiple selections.
- Attachments: Pictures/Files
- Due to Excel lack of support of attachments.
- In Excel, these columns will display text information, including the URL of the picture/file
- Enhanced rich text
- Excel and SharePoint understanding of “Rich Text” is drastically different
- Hyperlinks can’t be edited in place in Excel. Either Excel will decline the edit, or the change won’t be pushed to SharePoint. There are 2 possible workarounds: search and replace, or copy the cell somewhere else, modify it, and copy it back to where it was.
- Computed formulas:
- SharePoint formulas only show their result in Excel and can not be modified.
- It is a challenge to add formulas in Excel, but here are your options:
- You can add a text column in your SharePoint list and use it to store a formula in Excel (you might need to change the cell format in Excel). But when syncing back to SharePoint, SharePoint will replace the formulas by their result. So you will need to reenter your formula after every sync.
- You could also add your formulas aside from the Excel Table that actually contains the synced material. This works, but you have to pay attention as this is not sorted, filtered…
- Multiple selection drop down lists:
- People fields
- Excel will only accept/validate users who are part of the site that hosts the SharePoint list. Excel can not validate users against your full Active Directory.
Also, although Comments are not really a column, they show up as part of your SharePoint lists, and unfortunately, they can not be accessed or edited from Excel with Synchronizer for Excel and SharePoint.
Also, regardless of column types, if your list requires content approval, Excel will not be allowed to submit content. If you receive the message “You don’t have permission to modify this SharePoint list, so your changes won’t be saved” although you know you have the permissions, this might be the reason.
To solve this issue, under Library Settings > Versioning, uncheck “Require Content Approval for Submitted Items”.
6. Q&A – Product Features
Which versions of Excel are supported?
SharePoint list Synchronizer supports:
- Microsoft Excel 2007,
- Microsoft Excel 2010,
- Microsoft Excel 2013,
- Microsoft Excel 2016,
- Microsoft Excel 2019
What versions of SharePoint are supported?
All versions of SharePoint since SharePoint 2003 are supported.
Is Synchronizer for Excel and SharePoint compatible with Office 365?
Yes, you can synchronize a list between Microsoft SharePoint Online (Office 365) and Microsoft Excel.
Can I save a workbook that contains a synchronized list?
Yes you can. To keep the bi-directional synchronization, you have to use the Excel 97-2003 Workbook format (*.xls) and not xlsx file format:
How to insert a row from a synchronized list in Excel?
Right click on one cell of the table, then Insert / Table Row Below or Table Rows Above.
How to delete a row from a synchronized list in Excel?
Right click on one cell of the row you want to delete, then Delete / Table Rows. It does not work if your table is currently filtered in Excel.
Can I have multiple synchronized lists in one Excel workbook?
You can have more than one list by Excel file, but you can have only one synchronized list by worksheet. Here’s the procedure to create an Excel workbook with many lists:
- Export SharePoint lists to Excel => you now have one workbook by list.
- Copy or move worksheets to the first workbook
- Refresh list data by right-clicking in the list,then Table / Synchronize with SharePoint
- Save the workbook with all lists
Are PowerShell-created columns supported?
Yes, if they expose the same information as manually created columns.
You might need to add an attribute List=”UserInfo” in the XML file you use to create your column.
What happens when two people edit the same row in Excel and then synchronize?
Basically, if ever there’s a conflict, a conflict window pops-up and the user is in charge of resolving it. Regarding synchronization, after the first sync, it only syncs the items you’ve changed. Therefore, when working with synchronized lists, what you should do is start by doing a “Discard and Refresh” on your list to ensure your data is up-to-date and then start working from there.
Can I automate the synchronization with VBA?
Yes, you can use the UpdateChanges method documented on Microsoft Docs site. It even offers the option to decide to automatically accept or reject conflicts.
7. Q&A – Product Licensing
Do you work with resellers?
Of course. However only active resellers promoting our products qualify for reseller discounts.
How is the product sold?
Both end customers and resellers but online on our web site. If you purchase for another company, please enter the name and contact details of your end customer in the Comment field during the checkout, to ensure that your customer has access to updates and support.
Is the license for Synchronizer for Excel and SharePoint a perpetual license?
Yes! Synchronizer for Excel and SharePoint is a perpetual license.
Does the price include maintenance/upgrades?
Not only the license is perpetual, but when a new version is released, your license key will still be valid, allowing you to download and activate it.
What are the purchase and delivery methods?
The purchase method is online only on our web site. You will get the download link and license key on our web site and by email immediately after your purchase.
Is the product available per user or per machine?
The product is a client product on the desktop and its license is available per user with the following variations:
- Single user
- Up to 5 users
- Up to 20 users
- Up to 50 users
- Up to 100 users
- Up to 300 users
- Up to 1000 users
8. Q&A – Troubleshooting
I am using Microsoft Excel and have installed the tool but not able to get “Synchronize with SharePoint” option?
The menu is available when you right click a synchronized table (as explain in the documentation).
So you have to export the SharePoint list to Excel and then you’ll have access to the menu. If the menu is not available:
The first thing to check is the level of security of your Excel:
- Click on File/Options/Trust Center
- Click the Trust Center Settings Button
- Select External Content
- Make sure you have “enable” or “prompt” for both
The installation of our tool is on a per user basis. If it was installed using another windows account, the association is not done.
The tool below allows to check the association:
- Menu All Programs\Softfluent\Sharepoint List Synchronizer For Excel\Sharepoint List Synchronizer For Excel – Configuration
- Click on the About… button to check the license expiration date
You can also click on Disassociate then back on Associate before testing again
If the same user is used to install and use it, can you check the *.iqy association with our product is present here.
- “Start menu > Control Panel > Programs > Default Programs > Set Associations
If it was not in the list, something on your computer prevented the installation from adding this association (see next to get detailed logs during installation).
You can do the association yourself and check again.
Microsoft Office stores information about your SharePoint server in the registry. You can delete them:
- Open regedit.exe
- Navigate to: “HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Internet\Server Cache” (Replace “12.0” by your version of Office)
- Delete keys that refers to your SharePoint server
When you save the file, it must be in the XLS format (Excel 97-2003). If you use the XLSX format (Excel 2007+), the synchronize menu won’t be available. If you save it to any format other than XLS, the connection to SharePoint is lost. Even if you save it back to XLS, the connection will not be restored.
- Open the list in SharePoint
- Click “Export to Excel” and download the .iqy file
- Run the following command line:
Windows 64bits (x64):
“c:\Program Files (x86)\SoftFluent\XlSync3\XlSync.exe” “/f:<Full path to the downloaded iqy file>”
Windows 32bits (x86):
“c:\Program Files\SoftFluent\XlSync3\XlSync.exe” “/f:<Full path to the downloaded iqy file>”
Excel should open and the synchronized list should be there.
The synchronization process sometimes duplicates rows
The Synchronizer for Excel and SharePoint only opens Excel in the right setting for the synchronization feature to be accessible, and does nothing else regarding the synchronization between data. The synchronization process is still handled by Excel and SharePoint, and it is done using the ID column, which may not appear on the list (on either Excel or SharePoint). Duplicated rows usually mean that the IDs do not match; thus, the rows are added instead of being updated.
Here are some troubleshooting guidelines:
- A suggestion would be to display this ID column and make sure everything works normally.
- An Excel sheet linked to a SharePoint list changes (more precisely, increments) all the IDs in the first ID column. Since SharePoint updates existing rows based on the ID passed by the Excel sheet, it becomes confused when the IDs do not correspond to the original item ID and might cause the errors described in the Issue Description section.
It thus appears that sorting a SharePoint list in Excel is unsupported and usually results in errors. You could fix this issue creating a new View already sorted in SharePoint instead of sorting in Excel
- If you try to copy rows that don’t have consecutive row numbers in Excel. It appears that Microsoft Excel does not support the ability copy such rows into new rows in the same linked table.
- Anything configured on the server side, for example, SharePoint event handlers may impact what will ultimately will be persisted on the database (http://blogs.msdn.com/b/brianwilson/archive/2007/03/05/part-1-event-handlers-everything-you-need-to-know-about-microsoft-office-sharepoint-portal-server-moss-event-handlers.aspx)
Experiencing some difficulties? Support is available via email or through the chat feature of this site.