Every Monday someone exports last week's orders from MySQL, emails a CSV, and five people paste it into their own copies of the same workbook. By Wednesday nobody agrees on the numbers. Connecting Excel directly to MySQL fixes this: the workbook holds a query, not a pasted copy, and one click on Refresh pulls the current data from the database.
This page covers the live connection. If you only need a one-time file, the methods in exporting MySQL data to Excel are simpler.
Step 1: prepare the database side
Do not point Excel at raw tables with an admin login. Give the workbook a small, stable view and a user that can read only that view. Here is a sample orders table and a monthly summary view:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
city VARCHAR(20) NOT NULL,
product VARCHAR(30) NOT NULL,
qty INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL
);
INSERT INTO orders VALUES
(1,'2026-09-03','Lucknow','USB keyboard',3,649.00),
(2,'2026-09-11','Indore','Webcam',1,1899.00),
(3,'2026-09-19','Lucknow','Webcam',2,1899.00),
(4,'2026-10-02','Kochi','USB keyboard',5,649.00),
(5,'2026-10-14','Indore','Monitor stand',2,1250.00),
(6,'2026-10-27','Lucknow','Monitor stand',1,1250.00),
(7,'2026-11-05','Kochi','Webcam',1,1899.00),
(8,'2026-11-09','Indore','USB keyboard',4,649.00);
CREATE VIEW v_monthly_city_sales AS
SELECT DATE_FORMAT(order_date,'%Y-%m') AS sale_month,
city,
SUM(qty) AS units,
SUM(qty * unit_price) AS revenue
FROM orders
GROUP BY sale_month, city;
SELECT * FROM v_monthly_city_sales ORDER BY sale_month, city;
| sale_month | city | units | revenue |
|---|---|---|---|
| 2026-09 | Indore | 1 | 1899.00 |
| 2026-09 | Lucknow | 5 | 5745.00 |
| 2026-10 | Indore | 2 | 2500.00 |
| 2026-10 | Kochi | 5 | 3245.00 |
| 2026-10 | Lucknow | 1 | 1250.00 |
| 2026-11 | Indore | 4 | 2596.00 |
| 2026-11 | Kochi | 1 | 1899.00 |
This is exactly what Excel will receive. Doing the grouping in MySQL means Excel downloads 7 summary rows instead of every order, which matters once the table has lakhs of rows. Now create the read-only login (the database here is called xl; use your own name and a strong password):
CREATE USER 'excel_reader'@'%' IDENTIFIED BY 'Ch4nge-Me-Now!';
GRANT SELECT ON xl.v_monthly_city_sales TO 'excel_reader'@'%';
SHOW GRANTS FOR 'excel_reader'@'%';
| Grants for excel_reader@% |
|---|
| GRANT USAGE ON *.* TO `excel_reader`@`%` |
| GRANT SELECT ON `xl`.`v_monthly_city_sales` TO `excel_reader`@`%` |
USAGE means "can log in, nothing else". The second line is the only real permission. Replace '%' with an office IP or subnet if you can. The guide to MySQL users and privileges explains host patterns in more detail.
Step 2: connect from Excel
Excel for Windows offers two routes. Menu names shift a little between Microsoft 365 builds, but the flow is the same.
Route A: ODBC (works in most Excel versions)
- Download and install MySQL Connector/ODBC from the official MySQL downloads site. Pick the build that matches Excel's bitness (64-bit Excel needs the 64-bit driver). Check it in Excel under File → Account → About Excel.
- Open ODBC Data Sources (64-bit) in Windows, add a DSN with the MySQL driver, and enter the server, port (3306 by default),
excel_readerand the database name. Click Test. - In Excel: Data → Get Data → From Other Sources → From ODBC, choose the DSN, then choose
v_monthly_city_salesin the Navigator.
Route B: Excel's MySQL connector
Data → Get Data → From Database → From MySQL Database asks for the server and database directly. It depends on MySQL Connector/NET being installed. If the option is missing or complains about a provider, use Route A.
Either way you land in Power Query. Use Transform Data to rename columns, set revenue as a decimal type and filter months. Then click Close & Load to a table, or Close & Load To… → PivotTable Report.
Step 3: keep it fresh
- Data → Refresh All reruns every query in the workbook.
- In Query Properties you can tick "Refresh data when opening the file" or set "Refresh every N minutes".
- A PivotTable built on the loaded table updates when you refresh. You no longer need to rebuild it every week.
Problems people actually hit
| Symptom | Usual cause and fix |
|---|---|
| "Data source name not found" or driver missing | The driver bitness does not match Excel's. Install the matching Connector/ODBC and create the DSN in the matching ODBC tool. |
| Access denied for user | Wrong password, or the user's host part does not match the PC's IP. Check SHOW GRANTS and the host in CREATE USER. |
| Can't connect to server | A firewall or a bind-address=127.0.0.1 setting on the server blocks remote connections. Ask the DBA. Do not open 3306 to the whole internet. |
| Load stops at about a million rows | One worksheet holds at most 1,048,576 rows. Aggregate in a view, or load to the Data Model instead of a sheet. |
| Dates arrive as text | Set the column type in Power Query, or return a real DATE column from the view, not a formatted string. |
| Colleague's refresh fails | Credentials are saved per user, not inside the file. Each person enters the login once, under Data source settings. |
When this is the wrong tool
Live Excel connections work well for a handful of people and summary-sized data. If many people need shared charts, scheduled refresh and access control, a BI tool fits better. See connecting MySQL with Power BI or connecting MySQL with Tableau. If you are still deciding how much work should move out of spreadsheets at all, read MySQL vs Excel for data analysis.