Keeping Excel as the Front End to a Database Safely

When a team moves from a workbook to a database, the first complaint is usually that people want their spreadsheet back. They know it, they are fast in it and they have pivots and charts built over years. Taking Excel away loses more than it gains. The better arrangement keeps Excel for what it does well, reading, analyzing and presenting, and moves the storage and the rules into the database. Reading is easy to arrange. Writing needs care. This post sets out the pattern, the connection choices and the safe ways to let edits reach the data. It is a spoke of when a spreadsheet has become a database.

The division of labor

The arrangement that works:

• The database stores the data, enforces the rules, controls access and keeps history.

• Excel connects, reads, pivots, charts and prints.

• A controlled path carries changes from people to the database.

The third part is where designs fail. If Excel can write straight to a table, the database's rules apply only to what Excel chooses to send, and the workbook becomes the weak point again.

Reading: the connection

Excel reads from databases through Get Data, From Database, with connectors for SQL Server, PostgreSQL, MySQL, Oracle and others, or through an ODBC data source. Three choices decide how well it holds up.

A read-only account. The connection uses a database role with SELECT only on the objects it needs. A workbook that can only read cannot damage the data, whoever opens it (giving Excel users a read only database account).

Views, not tables. Point Excel at views. A view gives a stable shape (column names, types) that the database team can keep constant while the tables underneath change. It also hides columns people should not see and does the joins once:

CREATE VIEW reporting.order_summary AS
SELECT o.id AS order_id, o.order_date, c.name AS customer, c.region,
       SUM(l.quantity * l.unit_price) AS order_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id, o.order_date, c.name, c.region;

GRANT SELECT ON reporting.order_summary TO excel_reader;

If a table changes, the view is updated and the workbooks do not notice. If workbooks read tables directly, every schema change is a broken report.

A refresh policy. Decide whether the workbook refreshes on open, on a timer or on demand (refreshing database data in Excel on open). Keep large queries off refresh-on-open for files that dozens of people open each morning, since each open runs the query.

For details of connecting to each database, see connecting Excel directly to a database and its spokes. Credentials stored in a workbook are a risk of their own (keeping database credentials out of shared workbooks).

Pivots and models on top

Build pivots on the connection or the Data Model, not on pasted values, so they refresh. Keep derived measures in the model or the view, not scattered through cell formulas. Put the logic for a figure in one place, and a number on a slide can be traced back (building a pivot table from a database query). Treat the workbook as disposable: if it is lost, rebuilding it from the connection should be an hour of work, not a week.

Writing back: the part to design

People will want to enter and correct data. The question is how. Four patterns, from safest to riskiest.

1. No write from Excel

Excel is read-only, and edits go through an application built for the purpose: a web form, an internal admin screen or the database tool. This is the safest and gives the best validation. It costs building the application, which for a few forms can be small.

2. A staging sheet with validation

People fill a template workbook, with the same columns as the target and data validation for lists and ranges. They save it to a drop folder or upload it to a form. A script reads it, validates every row against the database's rules, loads valid rows and returns a report of the rejected ones with reasons. The database never sees invalid data, and the person gets feedback.

def validate(df, conn):
    errors = []
    known = {r[0] for r in conn.execute("SELECT code FROM departments")}
    for i, row in df.iterrows():
        if row["dept"] not in known:
            errors.append((i + 2, f"unknown department {row['dept']!r}"))
        if not row["qty"].isdigit() or int(row["qty"]) <= 0:
            errors.append((i + 2, "quantity must be a positive whole number"))
    return errors

Report row numbers as they appear in the sheet (i + 2, for a header and one-based rows) so the user can find them. Apply the valid rows in one transaction, and keep the uploaded file as an audit record (watching a folder for new Excel files and importing them shows the loading side).

3. Writes through a controlled interface

Tools such as Power Apps, a web app with a grid, or a macro that calls a stored procedure can give Excel-like editing while the write goes through code that applies the rules. A procedure add_order(customer, product, qty) can check stock, price and permissions and write the audit row. The caller has no table permissions, only EXECUTE on the procedure. This keeps rules in one place whatever the front end is.

4. Direct write from Excel (avoid)

A macro that uses ADO or ODBC to UPDATE tables directly is the easiest to build and the one that goes wrong. It bypasses validation unless the database enforces everything, relies on a macro that is blocked on many machines, hands write credentials to everyone who has the file and makes concurrent edits racy (shared Excel files and the lost update problem). If you must, use parameterized statements, a role with the narrowest permissions, and constraints in the database so that a careless macro is stopped.

What to enforce in the database

Whatever the front end, the database should be the final judge. Constraints are the contract: NOT NULL for required fields, CHECK for ranges and allowed values, foreign keys for references, unique constraints for keys. Row-level security can restrict people to their own rows where the database supports it. An audit trigger records who changed what (audit trails and history that Excel cannot give). With these in place, a mistake in a front end becomes a rejected statement rather than corrupt data.

Keep the workbooks tidy

Without discipline, a database with Excel on top reproduces the original mess as dozens of workbooks with their own queries.

• Share connections, not copies. Use a template workbook or a shared connection file (.odc) so queries are defined once.

• Name views for the audience. reporting.sales_by_month says what it is for.

• Document the shape. A short data dictionary: each view, its columns, their meaning and refresh time.

• Limit what each workbook can see. Separate reader roles per audience if the data differs.

• Version the SQL. Views live in a repository with the rest of the schema.

Performance

Connections are fast when queries are selective. Filter in SQL, return only the columns the report uses and index the filter and join columns. A refresh that returns two million rows to build a six-line summary should be a summary view. If dozens of workbooks run the same heavy query at nine o'clock, cache the result in a table refreshed by a job and point the workbooks at that (slow Excel refresh from a database).

When Excel should not touch the database at all

The data is sensitive. Query results cached in a workbook travel with the file. If the audience should not hold the rows, serve aggregates only or use a controlled application.

There are many concurrent writers. A grid that several people edit and save is what you were escaping. Use a proper multi-user interface.

The audience is outside the organization. Do not give outsiders database connections. Send a report or a link to a controlled viewer.

The users will not tolerate refresh. If people need a spreadsheet that works offline with local edits, a database front end does not fit; consider whether the data should be in a database at all.

Microsoft documents the connectors on its page about importing data from external sources, and PostgreSQL covers row security policies. To convert a small extract to JSON, use the CSV to JSON converter.