Synchronizer for Excel and SharePoint : Edit your lists in Excel

Overview
Synchronize your lists with Excel
Setup
Basic authentication over non-SSL HTTP connections
Known Limitations
Q&A – Product Features
Q&A – Product Licensing & Subscription
Q&A – Troubleshooting

1. Overview

SharePoint lists and Microsoft Lists are widely used to share data online displayed similarly to spreadsheets. However, to manipulate and analyze large sets of data, Microsoft Excel is still undisputed. This is why many people want the best of both worlds, editing SharePoint lists and Microsoft Lists in Excel.

Both SharePoint lists and Microsoft Lists will be referred as Lists in this page.

Let’s have a look at native options (offered by Microsoft) to exchange data between those 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 List data from Excel.

New Lists can also be created from existing Excel files. Here the Excel file and the 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. Of course, it was never supported for Microsoft Lists, since they were released after 2007.

SharePoint List Synchronizer for Excel brings back this feature, allowing Excel users to open SharePoint Lists and Microsoft Lists in two-way sync. Our software adds a menu in Excel:

This is also a great way to access 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.

If you are looking for another way to collect or share lists of data online, you should check RowShare, our application to gather data online, analyze and vizualize it. It is fully compatible with Excel.

2. Synchronize your SharePoint lists and Microsoft Lists with Excel

2.1 Open your List in Excel

When you install Synchronizer for Excel and SharePoint, don’t look for it in your Start menu. It doesn’t have a user interface.

Our product modifies the behavior of the Export to Excel button in SharePoint and Microsoft Lists:

In Microsoft Lists:

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 or Microsoft 365 credentials.

Then, 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 your List.

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 or Microsoft List.

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 your List.

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 (inside the blue rectangle).

2.4 Do you want the file to sync automatically?

You can configure your file (remember to save it as XLS, and NOT as XLSX) so that it will sync 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. Setup

3.1 Standard Setup

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.

$\\sfsrv01\Software\SharePoint List Synchronizer\Documentation\Visuels\3.3 - validate_key.png$

3.2 Advanced Setup Options

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 or contact us through the form on this web site home page or at [email protected].
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”.

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 or Microsoft Lists:
- 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/Microsoft Lists understanding of “Rich Text” is drastically different
- Hyperlinks
  - Hyperlinks can’t be edited in place in Excel. Either Excel will decline the edit, or the change won’t be pushed to your List. There are 2 possible workarounds: search and replace, or copy the cell somewhere else, modify it, and copy it back to where it was.
- Location:
  - Its content needs to be validated by SharePoint, which can’t be done offline in Excel, hence the read-only status in Excel.
- Computed formulas:
  - SharePoint/Microsoft Lists 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 List and use it to store a formula in Excel (you might need to change the cell format in Excel). But when syncing back, SharePoint/Microsoft Lists 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…
People fields
- Excel will only accept/validate users who are part of the site that hosts the List. Excel can not validate users against your full Active Directory.
Comments
- 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.

Content Approval

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”.

Sorting in Excel

Do not sort your List in Excel. It is unsupported and usually results in errors after the following sync. You could fix this issue creating a new View already sorted in SharePoint instead of sorting in Excel.

6. Q&A – Product Features

Which versions of Excel are supported?

Synchronizer for Excel and SharePoint supports the following versions of Excel running on Windows:

Microsoft Excel 2007,
Microsoft Excel 2010,
Microsoft Excel 2013,
Microsoft Excel 2016,
Microsoft Excel 2019,
Microsoft Excel 2021,
Microsoft Excel as part of a Microsoft 365 subscription

What versions of SharePoint are supported?

All versions of SharePoint since SharePoint 2003 are supported, both online and on premises.

Is Synchronizer for Excel and SharePoint compatible with Microsoft 365 (formerly Office 365)?

Yes, you can synchronize a list between Microsoft Lists, Microsoft SharePoint Online (Microsoft 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 each of your 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.

When a reseller purchases, the end customer name should be entetered in the Notes section to ensure the customer gets appropriate support and renewal rights.

How is the product sold?

It is sold online as a perpetual license.

Is the license for Synchronizer for Excel and SharePoint a perpetual license?

Yes.

What are the purchase and delivery methods?

The purchase method is online only on our web site. You will get the download link, invoice 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
5 users
10 users
20 users
50 users
100 users
300 users
1000 users
Unlimited

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:

Check 1

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

Check 2

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

Check 3

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.

Check 4

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

Check 5

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 your List is lost. Even if you save it back to XLS, the connection will not be restored.

Check 6

Open the List in your browser
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 List changes (more precisely, increments) all the IDs in the first ID column. Since SharePoint/Microsoft Lists 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 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)

Support

Experiencing some difficulties? Support is available via email or through the chat feature of this site.

Table of Contents