The Workbook Connect user guide

About Workbook Connect

Workbook Connect is an Excel add-in that lets you load a database table into a worksheet, edit it the way you would edit any spreadsheet, and write your changes back to the database. There is no export, no import file, and no copy and paste step in between.

It is built for the people who actually own the data, product managers, finance and operations teams, data stewards, rather than for database administrators. If you can use Excel, you can use Workbook Connect.

How it works

1. Connect to your database from the Excel ribbon.
2. Pick a table. Workbook Connect loads it into its own worksheet, formatted as a table.
3. Edit the cells you need to change. Add rows at the bottom, or mark rows for deletion.
4. Commit. Workbook Connect works out exactly which rows changed and writes only those back.

Your workbook is never the source of truth, the database is. You can close Excel, reopen it, reconnect and reload at any time.

Which databases are supported

Installing Workbook Connect

What you need

The installer checks for the .NET Framework and the VSTO Runtime before it starts. If either is missing it tells you which one and gives you the download link, so you can install it and run setup again.

Install the add-in

1. Download the installer from the download page.
2. Close Excel completely before you run it.
3. Run WorkbookConnect_Setup.exe and follow the prompts. No administrator password is needed.
4. Start Excel. You will see a new Workbook Connect tab in the ribbon.

If the tab does not appear, see Troubleshooting.

Extra step for Databricks only

Connecting to a Databricks SQL Warehouse needs the Databricks ODBC driver, which Databricks distributes separately. Workbook Connect does not install it for you, because its installer requires administrator rights and Workbook Connect deliberately does not.

1. Download the 64-bit Windows driver from databricks.com.
2. Install it. This step does need administrator rights, so ask your IT department if you do not have them.
3. Restart Excel.

The connect dialog shows you whether the driver was found, so you never have to guess.

The ribbon

Connecting to your data

Click Connect to Database on the Workbook Connect tab. The dialog changes depending on which platform you choose, so start with the Database type box at the top.

There are two entries. The first covers SQL Server, Azure SQL and Microsoft Fabric together, because they all speak the same protocol. The second is Databricks SQL Warehouse.

SQL Server, Azure SQL and Microsoft Fabric

Fill in the server name, optionally the database, and choose how you want to sign in.

Sign-in methods

Databricks SQL Warehouse

Choose Databricks SQL Warehouse and the dialog swaps to the Databricks fields.

The driver status line

Under the HTTP Path there is a line telling you whether the Databricks ODBC driver is installed. Green with the version number means you are ready to connect. Red means the driver is missing, and the line becomes a download link. See Installing Workbook Connect.

Signing in with a personal access token

A personal access token (PAT) is a long generated string that acts as a password for your Databricks workspace. Create one in Databricks under User Settings, then Developer, then Access tokens.

Paste the token into Access token. Tick Remember token on this PC and it is stored encrypted for your Windows account, so you do not have to paste it again.

Signing in with Microsoft Entra ID

This is the simpler option for most people: there is nothing to copy, paste or store.

Choose Microsoft Entra ID (MFA) and click Test or Connect. Your web browser opens a Databricks sign-in page, which hands you on to the normal Microsoft sign-in and your usual multi-factor prompt. Approve it and the browser tab can be closed, Excel picks the connection up by itself.

After the first sign-in the driver remembers you, so later connections are silent, even after restarting Excel. The dialog shows which account is cached. To sign in as somebody else, click Reset MFA Account. The next connection sends you back through the browser.

Choosing a catalog or database

Once the connection has been tested, open the catalog or database dropdown and Workbook Connect loads the list of those you are allowed to see.

Recent connections

Workbook Connect remembers your last ten connections and fills the dialog in for you next time. Pick an earlier one from any of the dropdowns and the whole set of fields is restored together.

Roles: who can do what

Workbook Connect has two roles, and they apply per database. Being a Configurator on one database does not make you a Configurator on another.

The two roles

What each role can do

Setup mode, and how roles get assigned

A database that nobody has configured yet is in Setup mode. While it is in Setup mode, anyone who connects can use Configure Table, so the first person can get things working.

As soon as somebody is added as a Configurator in Manage Access, the database leaves Setup mode and normal rules apply. From then on, only listed Configurators can change the setup.

If you connect to a configured database and you are not on the list, you are added automatically as a User. You can load and edit data straight away, you simply cannot change the setup.

Free and Pro

Everything most people do every day, connecting, loading, editing, validating and committing, works on the free version, with no row limits.

Two features need a Pro licence, and both are about managing other people rather than working with data:

• Manage Access, adding, removing and changing roles.
• Validation Designer, creating and editing validation rules.

Rules and roles that already exist are always enforced, on every licence. Pro only decides who is allowed to change them. See the pricing page for what each plan costs.

Everyday use

Choosing a table

Click Pick Table. Start typing to narrow the list, then press Enter or click OK.

Each table opens in its own worksheet, named after the table. You can have several tables open at once.

The first time a table is opened

The first time anybody in your organisation opens a particular table, Workbook Connect asks somebody to confirm which column identifies a row. It suggests an answer from the database, but asks you to confirm because getting it wrong would mean updating the wrong rows later.

This is asked once per table, not once per person. Everyone who opens it afterwards goes straight to the data.

Reading the sheet

A loaded table looks like a normal Excel table, with a few Workbook Connect additions.

Editing

Edit as you would in any spreadsheet: type over a cell, paste a block, fill down, undo. Workbook Connect keeps track in the background.

• To add a row, type into the first empty row directly below the table. Excel extends the table for you.
• To delete a row, select any cell in it and click Mark Delete. The row stays visible, marked D, until you commit.
• To undo everything, click Refresh and choose to discard your changes.

Dropdowns and lookups

Where a column points at another table, a category, a status, a customer, a Configurator can turn it into a dropdown. You then choose a readable name instead of remembering a number, and Workbook Connect stores the correct underlying value for you.

Pick from the dropdown rather than typing, so the value always matches something that exists.

Validation

Your organisation may define rules about what is acceptable, a price that cannot be negative, a code that must follow a pattern, a field that cannot be left blank. These are checked before anything is sent to the database.

Click Validate at any time to check without committing. Committing runs the same checks automatically. If anything fails, the commit stops and nothing is written. Fix the cells listed and commit again.

Loading fewer rows with the Filter Builder

Large tables do not have to be loaded whole. Click Filter to build a condition, and only matching rows are loaded.

1. Choose a column.
2. Choose an operator, contains, starts with, equals, is null, and so on.
3. Type a value and click Add Condition.
4. Repeat for as many conditions as you need, then click Apply.

The SQL is shown as you build it, and you can edit it directly if you need AND/OR logic the buttons do not cover.

Committing your changes

Click Commit Changes and choose Commit Current Sheet. Workbook Connect first tells you exactly what it is about to do.

Check the numbers. If they are not what you expect, click Cancel, nothing has been sent yet.

Validation runs first, across the whole sheet. If any row fails, the commit is blocked and nothing at all is written, so an invalid row can never reach your database.

On SQL Server, Azure SQL Database and Fabric SQL Database the write itself runs as a single transaction, so if the database rejects a row the whole commit is rolled back and your edits stay on the sheet for you to correct. Fabric Warehouse and Databricks SQL Warehouse do not offer that transaction, so there a failure part-way can leave earlier rows written. Workbook Connect reports what was applied. See Known limitations with Databricks.

Commit All Sheets

If you have several tables open, Commit All Sheets commits them in one action.

• Only sheets belonging to your current connection are committed. Sheets loaded from a different server or database are skipped and reported, not silently ignored.
• If any sheet fails validation, nothing is committed. Fix it and try again.
• Each sheet is committed separately, so the outcome is reported per sheet.

Refreshing, and unsaved changes

Click Refresh to reload the sheet from the database. If you have uncommitted edits, Workbook Connect warns you before discarding them.

When something goes wrong

For Configurators

This section applies only if you are a Configurator. If Configure Table and Manage Access are greyed out or blocked for you, you are a User and can skip it.

Configure Table

Configure Table decides what everybody else sees and can change. Your choices are stored centrally and shared with all users of that database.

Some columns cannot be made editable no matter what you tick. Workbook Connect locks the checkbox and the tooltip explains why: the column is computed, is a concurrency stamp, is generated by the database, or the connected account has no permission to update it.

Use Validation Rules to define the checks that run before commit, and Advanced Columns to map optional columns such as created and updated stamps.

Manage Access

Manage Access lists everyone who has connected to this database and lets you set each person to Configurator or User. It also shows a history of who changed what.

Adding the first Configurator takes the database out of Setup mode and switches on the normal rules, so do this once your setup is working.

You can also export the whole configuration, column setup, lookups, rules and roles, to a file, and import it into another database. This is the quickest way to promote a setup from test to production.

Troubleshooting

The Workbook Connect tab is missing

1. Close Excel completely and reopen it.
2. Check File, then Options, then Add-ins. If Workbook Connect is listed under Disabled Application Add-ins, choose COM Add-ins from the Manage box, click Go, and tick it.
3. Confirm the VSTO Runtime is installed, see What you need.

The connection fails

Databricks: the driver is missing

If the driver status line in the connect dialog is red, the Databricks ODBC driver is not installed. Follow Extra step for Databricks only. The line becomes a download link you can click directly.

If you installed the driver and the line is still red, check that you installed the 64-bit version and restarted Excel.

Databricks: the browser sign-in does not complete

The Databricks sign-in returns to Excel through a local port on your machine. If another program is already using that port, the sign-in never finishes. Close other database or developer tools and try again, or use a personal access token instead.

The licence has expired or is not valid

Workbook Connect checks your licence when you connect. If it reports an expired or inactive licence, your access has been switched off centrally. Contact whoever manages Workbook Connect in your organisation, or use Get In Contact on the ribbon.

Excel seems frozen during a load or commit

Large tables can take a while, and a Databricks warehouse that has gone to sleep needs a few seconds to wake. Give it a moment before deciding it has hung. If Excel is genuinely stuck, close it without saving. Because your changes are only written on commit, nothing is left half-applied in the database.

Getting help

Use Get In Contact on the ribbon to reach the Workbook Connect team directly, or Share an Idea to suggest an improvement. Both open a short form inside Excel. You can also contact us here.

Known limitations with Databricks

Databricks support is newer than the SQL Server support, and one difference is worth knowing about. It does not affect whether your data is written correctly.

Simultaneous-edit detection works differently

On SQL Server, Azure SQL and Fabric SQL Database, Workbook Connect detects when somebody else changed a row while you had it open and refuses to overwrite their work.

On Databricks it does this too, by a different route: before it writes anything, it checks that every row you are updating or deleting is still there. If one has gone, the whole commit is refused, nothing is written, and your edits stay in the sheet for you to look at.

The difference is that this is a check taken just before writing rather than a lock held over your rows, because Databricks does not hold rows still between statements. It catches the case that actually happens, where you loaded the sheet a while before committing. A change made in the same moment as your commit can still get through.

A commit is not one transaction

Databricks SQL runs each statement on its own, without the wrapping transaction that SQL Server, Azure SQL and Fabric SQL Database provide. Validation still gates the whole sheet, so nothing is written unless every row passes, but once writing starts a failure part-way can leave earlier statements applied. Workbook Connect reports that state rather than claiming a clean rollback, so check the reported counts before you retry.