The first thing most people do when they need to bring a database into Excel is copy the results from SQL Server Management Studio (or Access) and paste them into Sheet1. That’s not just tedious — it breaks refreshability, strips data types, corrupts dates like '2024-03-15' into serial numbers, and turns '£47,890.50' into text you can’t sum. Worse? You’ll re-copy every time the source changes.
The Problem
You’re handed a weekly sales report pulled from your company’s Access backend. It’s exported as a CSV — but the person who sent it didn’t check encoding, and now names like 'José García' show up as 'José GarcÃa'. The 'Order Date' column (Column C) imported as General format, so Excel misreads '2024-02-29' as February 29, 2000 — because Excel doesn’t validate ISO dates on paste. And the 'Revenue' column (D) contains commas and currency symbols, forcing you to run SUBSTITUTE(), VALUE(), and TEXT() just to get a number you can chart.
| ID | Customer | Order Date | Revenue | Region |
|---|---|---|---|---|
| 10482 | José GarcÃa | 45352 | $24,560.00 | EMEA |
| 10483 | Sarah Chen | 45353 | $18,920.50 | APAC |
| 10484 | Miguel Torres | 45354 | $32,175.25 | LATAM |
| 10485 | Amina Patel | 45355 | $41,030.75 | EMEA |
| 10486 | David Kim | 45356 | $27,650.00 | APAC |
| 10487 | Lena Dubois | 45357 | $35,410.20 | EMEA |
That table lives in A1:E7 right now — but notice what’s broken: Column C is numeric (Excel’s date serial), Column D is text (because of the $ and comma), and ‘José’ tells us UTF-8 wasn’t respected. This isn’t a data problem. It’s an import problem.
The Solution
What makes this elegant is that Excel doesn’t just *read* databases — it *connects* to them. You get live links, type-aware parsing, and one-click refresh. No more CSV detours. Here’s how to do it cleanly for SQL Server, Access, and generic ODBC sources — all using Data > Get Data.
- Go to Data tab → Get Data → From Database → From SQL Server Database. Enter server name (e.g.,
prod-sql01.internal) and database name (SalesDB). Click OK. Authenticate with Windows or SQL credentials. - In the Navigator window, expand the
Orderstable. Check the box next to it. Don’t click Load yet — click Transform Data instead. This opens Power Query Editor — where you fix structure *before* it hits your worksheet. - In Power Query, select the 'Order Date' column → right-click → Change Type → Date. Then select 'Revenue' → right-click → Change Type → Currency. For 'Customer', go to Transform tab → Clean → Trim (removes invisible spaces). These steps become part of the query — repeatable and auditable.
- Click Close & Load To… → choose Table, place it in
Sheet2!A1, and check Add this data to the Data Model if you plan pivot tables later. Done.
The result? A clean, typed, refreshable table — no manual cleanup required. And yes, 'José García' renders correctly because Power Query respects UTF-8 natively.
| ID | Customer | Order Date | Revenue | Region |
|---|---|---|---|---|
| 10482 | José García | 2024-02-29 | $24,560.00 | EMEA |
| 10483 | Sarah Chen | 2024-03-01 | $18,920.50 | APAC |
| 10484 | Miguel Torres | 2024-03-02 | $32,175.25 | LATAM |
| 10485 | Amina Patel | 2024-03-03 | $41,030.75 | EMEA |
| 10486 | David Kim | 2024-03-04 | $27,650.00 | APAC |
| 10487 | Lena Dubois | 2024-03-05 | $35,410.20 | EMEA |
That new table starts at Sheet2!A1. Try right-clicking any cell → Refresh. Or press Alt+F5 — Excel will pull fresh rows from the live database, preserving all formatting, formulas referencing it (like =SUM(Sheet2!D2:D100)), and even relationships if you added it to the Data Model.
Going Further
You don’t need admin access to the database server to import. If you only have a local .accdb file, go to Data → Get Data → From Database → From Microsoft Access Database. Browse to C:\Reports\Inventory.accdb, pick the Products table, and transform in Power Query just like before.
For legacy systems or custom APIs, use From Other Sources → From ODBC. You’ll need a DSN configured first — but once it’s set, Excel treats it like any other database connection. Bonus tip: In Power Query, after loading, go to Home → Advanced Editor. You’ll see M code like Source = Sql.Databases("prod-sql01.internal"). You can edit that directly to add WHERE clauses — e.g., append & " WHERE OrderDate >='2024-01-01'" — and avoid pulling 2 million rows just to filter down to last quarter.
And here’s the counterintuitive one: Don’t use 'Load To Worksheet' if your dataset has over 50k rows and you only need summaries. Instead, load to the Data Model, then build a PivotTable. Excel compresses the data in memory — often using 1/10th the RAM and enabling instant slicing by Region or Month.
When NOT to Use This
This method fails — silently — if your database user lacks SELECT permissions on the target table. Excel won’t warn you; it’ll just return zero rows or throw a vague “Expression.Error” in Power Query. Always test your connection outside Excel first (e.g., with Azure Data Studio or Access).
Avoid importing directly from production transactional tables (like Orders with 12M rows) unless you’ve applied filters in Power Query first. Loading raw, unfiltered OLTP data into Excel crashes older machines and makes file sizes explode — a 1M-row import can balloon your .xlsx to 120MB+ with no benefit.
Also skip this entirely if your source is a flat-file export someone emails weekly. Use Data → Get Data → From File → From Text/CSV instead — and enable Detect data types automatically and UTF-8 encoding in the import dialog. It’s faster, safer, and gives you the same Power Query editing surface.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Power Query Editor | Alt+A, P | From Data tab → Get Data dropdown |
| Refresh all queries | Alt+F5 | Works even if focus is in a formula bar |
| Open Advanced Editor in PQ | Ctrl+Shift+E | Edit M code directly — great for adding filters |
| Toggle Query Settings pane | Ctrl+Q | Shows applied steps — click any to edit or delete |
