A Practical Guide to Connecting Excel Directly to a Database
Connect Excel to PostgreSQL, MySQL and SQL Server with connectors and ODBC, using read only accounts, parameters, safe credentials, refresh and speed checks.
For many teams the main use of a database is feeding spreadsheets. Somebody runs a query, exports a file, opens it in Excel, builds a pivot and emails the result. Every step is manual and each repeat is a chance to export the wrong period. Connecting Excel to the database directly replaces the chain with a refresh. The numbers in the workbook then come from the same source as everything else, and a new month is one click.
It also creates new risks. A workbook with a live connection carries a way to reach production data. A refresh can load the database. A password can end up in a file that is emailed outside the company. This guide covers how to connect Excel to PostgreSQL, MySQL and SQL Server, how to do it with a read-only account and bound parameters, how to keep credentials out of files, how to choose refresh behavior and how to find out why a refresh is slow. The spokes linked throughout go deeper on each step.
The shape of a connection
Every Excel-to-database connection has the same parts, whatever the database.
A way to talk to the server. A built-in Power Query connector, or an ODBC driver on the machine.
The address. Host, port and database name.
Credentials. Windows identity, a database login or a sign-in with an identity provider.
A query. The SQL that selects the data, or a navigation to a table or view.
A destination. A worksheet table, the Data Model or just a connection that feeds a pivot.
A refresh policy. When and how the data is reloaded.
Each part is a decision. The mistakes that cost the most come from making them by default: loading a whole table because the navigator offered it, using the administrator login because it worked, saving the password because Excel asked. The rest of this guide walks through the decisions in order.
Connectors and drivers
Excel's Power Query ships connectors for the common databases: SQL Server, PostgreSQL, MySQL, Oracle, and others under Data, Get Data, From Database. A connector may rely on a driver or provider installed separately. The built-in SQL Server connector needs nothing extra. The MySQL one needs MySQL Connector/NET. The PostgreSQL one needs the Npgsql provider in some versions. When the built-in connector is missing or inconvenient, Get Data, From Other Sources, From ODBC reaches any database that has an ODBC driver.
The bitness rule decides whether any of this works. 64-bit Excel can use only 64-bit drivers, and 32-bit Excel only 32-bit ones. Windows has two ODBC administrators to match, and installing a driver of the wrong bitness is the single most common cause of "my data source does not appear" and "data source name not found" errors (fixing ODBC driver not found errors in Excel). Check File, Account, About Excel before installing anything, and standardize on 64-bit Office across a team so the question goes away.
The database-specific setups:
PostgreSQL: install psqlODBC, create a DSN with SSL mode
require, and connect through Get Data, From ODBC, or use the PostgreSQL connector where it works (connecting Excel to PostgreSQL with ODBC).MySQL: install Connector/NET for the built-in option or Connector/ODBC for the ODBC route, and handle the
caching_sha2_passworddefault of MySQL 8 by using a current connector (connecting Excel to MySQL with the official connector).SQL Server: use the built-in connector with Windows authentication where possible, keep encryption on and trust the certificate properly instead of turning encryption off (connecting Excel to SQL Server with Power Query and ODBC).
Write the query in SQL
The navigator invites you to load a table. Resist it for anything large. A query in the Advanced options box lets the database do what it is good at: joining tables, filtering by period, aggregating and returning only the columns the report needs. A pivot wants one row per fact with the descriptive columns attached, which is a join the database does in milliseconds and Excel does slowly (building a pivot table from a database query).
Two cautions about native queries. Excel shows an approval dialog for each new statement, because a workbook that runs SQL is a workbook that does something. Read the statement before you approve it, particularly in a file you did not write. And a native query can stop later Power Query steps from folding into the SQL, so do the filtering and column choice in the statement and keep later steps light.
Views are the best interface. A reporting schema of views gives workbooks a stable shape, hides sensitive columns and keeps joins in one place. When a table changes, the view absorbs the change and the workbooks keep running.
Accounts and permissions
The login Excel uses decides what a mistake or a leak can cost. Give it as little power as the reports need.
A read-only role, with SELECT on the reporting views and nothing else, cannot change data and cannot read the rest of the database. Limits on statement time and number of connections stop a careless refresh from holding the server. Network rules and encryption keep the account reachable only from where it should be. The recipes for PostgreSQL, MySQL and SQL Server are in giving Excel users a read-only database account, including how to test the account by logging in as it and trying to write.
Prefer individual access where you can. Windows authentication in a domain means no password to store and a clean story when people leave. Individual database logins make audit trails meaningful. A single shared reporting login is the weakest arrangement: it cannot be revoked for one person and it appears in the logs as the same user for everyone.
Read-only does not mean harmless. The account can still read every row its views expose, and a runaway query can load the server. Design views for the audience, and consider a read replica for heavy reporting.
Parameters
A report that always asks for the same period is a snapshot. A parameter cell lets a person choose the year or the region and refresh. It also introduces the classic risk of building SQL out of text.
Pass values to the database as bound parameters where the connector supports it: Value.NativeQuery with a record of values keeps data and SQL separate, so an apostrophe in a region name is only an apostrophe. Where binding is not available, convert to a proper type (a date formatted as ISO text, a number converted by the number formatter), validate text against a list, and use a read-only account as the backstop. Make the parameter visible and labeled, give it a sensible default and show the period on the report, so nobody reads last year's numbers as this year's (using parameters in a database query from Excel).
Credentials
A workbook is a zip file. Anyone who holds a copy can open it and read whatever it stores. Know what yours stores.
Power Query keeps credentials in the user's profile, not in the file, and prompts on first refresh on each machine. That is the behavior to preserve. Legacy connections, ODBC data source files, connection files and macros can all carry a password in plain text. Inspect a workbook by unzipping it and searching the connection parts for the words Password and Pwd before it leaves your team. If a password has ever been in a shared file, change it; deleting it from the next version does nothing about the copies (keeping database credentials out of shared workbooks).
For unattended jobs, which cannot answer a prompt, keep the secret in an environment variable, a secret manager or a protected file with strict permissions. Scheduled tasks should run as an account that has the access, ideally through integrated authentication.
Refresh
A connection is useful only if the data is current. Excel offers refresh on open, refresh at an interval while the workbook is open, and manual refresh. Each interacts with security and load.
Refresh on open works only if the workbook is trusted. A file that arrives by email opens in Protected View, and external connections stay disabled until someone clicks Enable Editing and Enable Content, or until the file sits in a trusted location. It also runs the query once per person per open. With sixty readers opening a dashboard at nine, that is sixty copies of the query at once (refreshing database data in Excel on open). Cheap queries and summary tables make this harmless; heavy queries do not survive it.
Background refresh helps people work during a refresh and hurts scripts that save right after. For scheduled, unattended refreshes there are separate concerns about the account, drivers and a visible log (refreshing Power Query data on a schedule).
Make staleness visible. Show the time of the last successful refresh, or the latest date present in the data, at the top of the report. A refresh that failed leaves old data on the screen, and nothing else announces it.
Speed
A refresh that takes minutes is a design problem more often than a hardware one. The method is to measure in the right order.
Run the same SQL in a database client. If it is slow there, tune the query and indexes.
If it is fast there, count what Excel receives: rows, columns and wide text. Cutting
SELECT *to the needed columns and aggregating on the server is often the whole fix.Check query folding. A step that does not fold makes Excel download everything and process it locally.
Check privacy levels and merges. Joining a database table with a local file may force a client-side join.
Check the network and the load destination: Data Model instead of a sheet, 64-bit Excel, background refresh settings.
Use Power Query's diagnostics to see which step takes the time, and keep notes of timings before and after each change (slow Excel refresh from a database and what to check). When the data is genuinely large and the question simple, change the architecture: a summary table refreshed by a job, a read replica, or a scheduled export that readers open.
Writing back: a different decision
Everything above is about reading. Writing from Excel into a live database deserves its own decision, because it bypasses the rules the database is there to enforce. If edits must reach the database, use a path that validates them: a form or application, a staging workbook that a script checks and loads, or stored procedures that apply the rules and write an audit record. Direct updates from a macro are the pattern to avoid (keeping Excel as the front end to a database). If a workbook has become the place where people edit shared records, consider whether the spreadsheet has become a database in its own right (when a spreadsheet has become a database).
A setup checklist
Before sharing a connected workbook, confirm that:
The driver or connector is installed on every machine that will refresh, with matching bitness.
The account is read-only, scoped to reporting views, with timeouts and a connection limit.
Encryption is on, and the server certificate is trusted properly.
The workbook contains no password, and the unzipped file has been searched to prove it.
The query selects only what the report uses, and the result has been checked against the database for counts and totals.
Parameters are typed, bound or validated, and labeled.
Refresh behavior is chosen deliberately, with the refresh time shown on the report.
The refresh duration has been measured, and it is acceptable at the number of people who will use it.
Someone owns the account, the views and the workbook, and knows how to rotate the password.
A second person has opened the workbook on their own machine and refreshed it.
The tenth item catches the most. Almost every connection works on the machine where it was built, and fails on the next one for a reason nobody documented.
Operating it over time
A connected workbook keeps working until something under it changes. A column is renamed, a view is dropped, a password is rotated, a server is moved. Treat workbooks that matter like small applications: keep a list of them, note which views and accounts each depends on, and have a place to announce database changes before they land. Views make this manageable because the database team can keep the interface constant.
Review access twice a year. Remove accounts of people who have left, disable ones nobody uses and look at what the reporting account has been running. Rotate the shared password if one exists. And retire reports that nobody opens: each live connection is a small standing risk.
A worked example: from a monthly export to a refresh
A distributor's finance analyst spends the second working day of every month exporting orders from the database tool, pasting them into a template and fixing the pivot. The goal is to replace that with a workbook that refreshes. Here is how the decisions above play out.
First the analyst and the database owner agree what the report needs: orders for the last twenty-four months, by region and product category, with revenue and order counts. The database owner creates a view in a reporting schema that joins orders, customers, lines and products and returns exactly those columns at order-line grain. A role named finance_reader gets SELECT on the schema, a sixty-second statement timeout and a limit of three connections. The view's definition is saved in the schema repository, so a future change goes through review.
On the analyst's machine, the IT team has installed the 64-bit ODBC driver that matches Office. The analyst connects with Get Data, enters the server and chooses the view in the navigator. Because the view already has the right shape, no native SQL is needed, and filtering the date column in Power Query folds into a WHERE clause on the server. The analyst loads the result to the Data Model, builds the pivots on it, and adds a cell that shows the maximum order date in the data.
The first refresh asks for credentials. The analyst uses their own Windows identity, which the database trusts through a group, so no password is stored. Colleagues in the group open the same workbook and are prompted for nothing. The workbook sits in a trusted SharePoint folder, so connections are enabled without the warning bar.
Before sharing, the analyst checks three things. The view's row count and total revenue match a direct query for the same period. An unzipped copy of the workbook contains no Pwd text. And a colleague has refreshed it on their own machine. One bug surfaces: the colleague's refresh takes ninety seconds because their laptop is on the VPN. The team changes the view to return monthly totals by default and offers a second view with detail for the few people who need it. Refresh drops to five seconds.
A month later, the process takes ten minutes, most of which is looking at the numbers. The same pattern, a reporting view, a read-only role, a connection that folds and a visible refresh time, has become the template for the next four reports.
Questions a security review will ask
If your organization reviews data connections, expect these questions and have the answers ready.
What data does the workbook cache, and who can open the file? A pivot cache and a loaded table are copies of the rows. The answer should name the columns and the audience. Which account does it use, and what can that account do? The answer should be a read-only role, with the list of views. Where do credentials live? In the user's profile or through integrated authentication, never in the file. Is the connection encrypted, and is the server certificate validated? It should be, in both cases. How does a person lose access when they leave? Through the directory group or by disabling their login. What load can the workbook put on the production database? The answer should include the timeout, the connection cap and whether reporting uses a replica. Is there a log of who ran what? The database's connection and statement logging should show it.
Teams that can answer these quickly get their connections approved quickly. Teams that cannot usually discover the gaps in the answers, and fix them, which is the purpose of the review.
Conventions that help a team
A few conventions make a collection of connected workbooks manageable.
Name views by purpose and audience, such as reporting.sales_by_month and reporting.orders_detail, and not by the table they read. Keep a short catalog of views, their columns and their owners. Use one connection template workbook that already has the data source set up, and ask people to start new reports from it, so queries are defined in one way. Put a notes sheet in every workbook that says what it reads, who owns it, when it was last checked and how to refresh it. Put the refresh time at the top of every report. Agree where workbooks live, ideally a SharePoint library or a share with controlled access, so they are not scattered as email attachments.
When a view must change, announce it ahead of time, and prefer adding columns to renaming them. When a column must be removed, leave the old one in place for a release and say so. Workbooks are consumers of an interface, and an interface needs a change process, however light.
Pitfalls seen repeatedly
A handful of mistakes account for most of the support questions. The wrong bitness of the driver. A user DSN that a scheduled task cannot see. A native query approved without reading it. A password pasted into a connection string "just to test" and then saved. SELECT * on a table that grew. A refresh on open for a dashboard sixty people open at nine. A parameter built by joining text. A workbook emailed outside the company with cached rows inside. A pivot that quietly reads a stale cache because a refresh failed and nobody noticed. Each has a short fix, and each is cheaper to prevent with a template and a checklist than to repair after the fact.
Finding the connections you already have
Most organizations discover that connected workbooks already exist, built by people who needed a number and found a way. Before writing standards, find out what is out there. Ask the database team for the list of accounts that connect from desktop tools and the addresses they connect from. Ask the file-share owners for workbooks that contain connection parts, which a script can detect by looking for xl/connections.xml inside each zip. Ask a handful of heavy users what they have built.
The first inventory is usually sobering and useful. It shows shared passwords in use, workbooks that hit production at the busiest hour, and reports that nobody reads. Resist the urge to switch things off at once. Rank the findings by risk, fix the ones that expose credentials or write to production first, and give owners a date to move the rest onto the standard pattern. People accept a change that comes with a ready template and a deadline much more readily than a ban.
Document the outcome of the inventory in one page: the list of workbooks, their owners, the views they need and the date each will move. That page is also the evidence, later, that the problem was found and handled.
When Excel should not connect to the database directly
The data is highly sensitive or regulated. A workbook caches rows and travels. Use a controlled reporting tool with proper access control and logging.
The audience is large or external. Hand them a published report, not a database login or a connected file.
The load is heavy and the question is simple. Pre-compute the answer and let Excel read the summary.
You need an unattended pipeline. A script with a client library, a service account and logging is more reliable than desktop Excel on a schedule.
The users will write. If the workflow is data entry, build the entry path properly instead of connecting a worksheet to live tables.
Where to go next
If you are setting up a first connection, start with the spoke for your database and then read giving Excel users a read-only database account. If the connection works and the report is slow, go to slow Excel refresh from a database and what to check. If a workbook is about to be shared, read keeping database credentials out of shared workbooks first. For the other direction, moving the data into a database in the first place, see getting Excel data into a database.
The references are Microsoft's pages on importing data with Power Query connectors, its documentation of query folding and the PostgreSQL documentation. To convert a small result to JSON, use the CSV to JSON converter.
