Doing Proper Analysis on Excel Data With SQL in Practice

Most business analysis starts in a spreadsheet and stays there too long. The first pivot answers the first question. The second needs a lookup, the third a helper column, the fourth a copy of the sheet with a filter. A year later the answer to a simple question lives in forty formulas across six tabs, and the only way to check it is to ask the person who built it. Moving the analysis into SQL does not mean abandoning Excel. It means treating the workbook as the source of the data and putting the logic in text that can be read, rerun and checked.

This guide shows how to do that. It covers what to set up, how to load and trust the data, the questions that come up repeatedly (joins, summaries, year over year, gaps, cohorts, reconciliation) and the habits that make a SQL analysis better than the spreadsheet it replaces. The spokes linked throughout have the queries.

Why move analysis out of the grid

A spreadsheet mixes four things in one place: the data, the assumptions, the calculations and the presentation. That is what makes it quick to start and slow to trust. A calculation that lives in cell F47 has no name. A change to one input ripples through formulas you cannot see all at once. Two people with copies of the file produce two answers, and finding the difference means comparing formulas cell by cell.

SQL separates the pieces. The data sits in tables. The logic is a query, in text, with names for each step. The presentation can still be Excel. The result is analysis you can read from top to bottom, diff between versions, rerun on new data and test. None of this is about SQL being better at arithmetic. It is about the logic being visible.

There is a practical limit on when it pays off. For a one-off look at a small sheet, a pivot is faster. For anything that will be repeated, shared, audited or scaled, the query wins, and the break-even is earlier than most people expect: the second time you rebuild the same pivot, you have already paid for the query.

The tools

Three engines cover almost every case, and none needs a server.

DuckDB is an analytical database that runs inside Python or from a command line. It reads CSV, Parquet and, in recent versions, xlsx directly, and can query a pandas DataFrame by name. It is the best default for analysis of files: fast scans, full window functions, PIVOT and QUALIFY, and no setup (querying an Excel file with SQL in DuckDB).

SQLite is a file database built into Python. It suits data you want to keep and query repeatedly, with indexes and constraints, on one machine (loading an Excel file into SQLite).

PostgreSQL is the choice when several people need the same data, when it must be shared or when it is large. Excel connects to it for the results (connecting Excel directly to a database).

pandas is the glue: it reads the workbook with all the control you need over headers, types and skipped rows, and hands a DataFrame to DuckDB. Use whichever reader you already trust.

Get the data in, and trust it

The most common failure in an analysis is a quiet one at the first step: the data was read wrongly. Spend time here.

Read the sheet with explicit decisions. State which sheet, which header row, which columns. Read identifiers as text so leading zeros survive. Parse dates with a format and check the range. Convert number columns on purpose and count failures (specifying column types when reading Excel). If the sheet is a report with merged cells, subtotal rows or several header lines, clean it first; SQL cannot repair a layout (cleaning Excel data before analysis).

Then reconcile. Count the rows and total the main measure in the query, and compare with the workbook: the count of data rows and a SUM computed in Excel. Do it before the first analysis and write the numbers down. It takes two minutes, and it is the single most valuable habit in this guide. A summary of the wrong data is wrong, and no later check will find that the reading dropped the last forty rows.

Keep the raw data apart from the cleaned version. A staging table that holds exactly what was read, and a clean table or view derived from it, mean you can always return to the evidence (staging Excel data in a table).

The recurring questions

A handful of questions cover most of the analysis that analysts do on spreadsheet data. Each has a standard shape in SQL.

Joining sheets

Two sheets, one with facts and one with descriptions, are joined on a key. The join type decides what happens to rows with no match, and the check that matters is the row count: a left join to a unique lookup must not change it. An anti join lists the unmatched keys, which is the most useful by-product (joining two Excel sheets with SQL in DuckDB or SQLite). Compare with VLOOKUP and XLOOKUP versus SQL JOIN for the function-by-function view.

Summarizing

A GROUP BY is a pivot table written as text. Carry sums and counts, not averages, so a summary can be combined later. Add ROLLUP for totals and window functions for shares and ranks. Verify that the grand total equals the reconciliation figure (summarizing a sales workbook with GROUP BY in SQL queries). The field-by-field translation of a pivot is in writing the same Excel pivot table as a GROUP BY query.

Comparing periods

Year over year needs one row per month with no gaps, and a comparison that matches on the date, not on a row offset, so a missing month shows as null and not as a wrong number. Guard percent change against zero, compare like with like for partial years, and be honest about calendar effects (year over year comparison from a monthly Excel sheet).

Finding duplicates and gaps

Repeated keys are found with GROUP BY ... HAVING, gaps in a sequence with LAG or the gaps-and-islands trick, and missing dates by joining a generated calendar. Whether a gap is an error is a business question; the query only makes it visible (finding duplicates and gaps in an Excel sheet with SQL).

Cohorts

Cohort tables compare groups of customers at the same age rather than at the same date. The ingredients are each customer's first month, the months since it for every order and a count of distinct customers per cell divided by cohort size. The triangle's empty corner is unknown and must not be shown as zero (cohort tables from an Excel export with SQL).

Forecast baselines

Before anything clever, compute the simple methods: the last value, the same month last year, a moving average and a trend. Hold out recent months and compare. A model that cannot beat the best baseline is not an improvement (building a forecast baseline from Excel history in SQL).

Reconciling

When two reports disagree, work top down: grand totals, group totals, then rows with a full outer join, and classify each difference by cause. The output is a bridge from one number to the other, and the tie-out that the row differences add to the total difference proves the explanation is complete (checking totals between two Excel reports with SQL).

Replacing a model, not just a report

Some workbooks are not reports but models: chains of calculations on assumptions that produce a number the business uses. These are the most fragile files in an organization, and the strongest case for SQL. Map the inputs, assumptions, calculations and outputs. Move the data to tables and the assumptions to a dated table. Translate the chain into a view of named steps. Run old and new in parallel across a full cycle and explain every difference. Keep a thin Excel front end that reads the view (replacing a fragile Excel model with a query and a view). The differences found in the parallel run usually include bugs in the old model, which is the most persuasive argument for the change.

Habits that make the analysis better than the spreadsheet

SQL itself does not make analysis correct. These habits do.

Name the steps. Use common table expressions with names that say what they compute: monthly_revenue, first_order, unmatched_orders. A reader should be able to follow the query without running it.

Check as you go. Every query that matters has a companion check: a row count, a total, a uniqueness test. Keep checks in the same file, labeled.

Make assumptions explicit. A hard-coded rate in a WHERE clause is the same hidden number as in a spreadsheet formula. Put it in a named parameter or a table, with a comment.

Handle nulls on purpose. Nulls in sums, averages, joins and comparisons behave in ways that surprise people. Decide for each column what a null means and write it down.

Prefer decimals for money. Floating-point arithmetic produces cents of drift. Cast to a decimal type for amounts.

Order every result you show. A query returns rows in no guaranteed order. If the order matters, say so.

Keep the query with the output. A chart in a deck should be traceable to a file and a date. The query is the provenance.

Version it. Put the SQL in a repository. A change to a definition becomes a diff someone can review.

A small end-to-end example

An analyst is asked: "How did revenue by region change this year compared with last year, and which regions lost customers?" The workbook has an Orders sheet with a customer id, a date, a region and an amount.

First she reads the sheet with pandas, with the id as text and the date parsed, and records the row count (6,412) and the total (1,204,310.55). In DuckDB the same figures appear. They match, so she proceeds.

She groups to month and region and fills missing months from a calendar, so a region with a quiet month shows zero and not nothing. A self join on the date shifted by a year gives the comparison, with null where there is no prior year. Percent change is guarded against zero. She finds that the South region is down 11 percent, driven by two months.

For the second half she builds a small customer-level query: first order year, and a flag for ordering this year and last year. A region's lost customers are those who ordered last year and not this year. A count by region gives the answer. She spot-checks one lost customer by looking at the raw rows, confirming that the last order was in November of last year.

She saves one .sql file with the reconciliation at the top, the two queries below, and a comment on each saying what it answers. She exports the two result tables to a workbook, adds a chart and sends it with the query. Next January, she repoints the file at the new workbook and gets the update in minutes, and a colleague can review the logic without her.

None of it required a server, and none of it was possible to check in the pivots she would otherwise have built.

Excel idioms and their SQL counterparts

When people move, they carry habits. Matching them to SQL idioms eases the transition.

The longer equivalence list, with the pitfalls of each, is in translating Excel formulas into SQL.

Pitfalls

A few mistakes recur when people start.

Reading the sheet wrongly and never noticing: dropped rows, text numbers, dates read as numbers. Reconciliation catches it. Joining on keys that differ in type or spacing and getting silent non-matches: check unmatched counts. A lookup table with duplicate keys multiplying rows: check uniqueness. Averaging averages: carry sums and counts. Treating NULL as zero or as an empty string. Comparing floating-point numbers with =. Forgetting that hidden rows in Excel are still data to a query. Reading cached formula values that are stale. Treating the first result as the answer without asking whether it is plausible.

Beyond the technical: building an analysis nobody can follow. Name steps, comment the intent and keep it short.

Moving gradually

Nobody switches overnight. A reasonable path: keep your pivots and build the SQL for one recurring report, running both and comparing. Move reconciliation checks to SQL next, since they are the most tedious in a spreadsheet. Then the models and joins that were the worst to maintain. Keep Excel for exploration and presentation. Over a few months the logic that matters ends up in files that can be reviewed, and the workbooks shrink to viewers.

If the data is large, the move is also a matter of capacity: SQL on files works past the million-row limit (analyzing more than a million rows without Excel limits), and SQL scales to data a workbook cannot hold (why SQL beats formulas once the data gets large).

What to put in the analysis file

A SQL analysis is only as reproducible as the file that holds it. A good file has a short header and a fixed order. The header says what the file answers, who wrote it, the date, the source workbook and sheet, and the version of the tools. Below it come the setup statements: loading the data, with the choices about types and ranges written out. Then the reconciliation, with the expected counts and totals recorded as comments or asserted in code. Then the analysis itself, in steps with names. Last come the exports, naming the files that other people will read.

Keep the file small enough to read in one sitting. If it grows past a few hundred lines, split it: one file for loading and cleaning, one for each analysis. Put shared definitions, such as the fiscal calendar or the exclusion rule for internal accounts, in a view or a file that the others include, so that two pieces of analysis cannot disagree about what a month is.

Add a short note to every result you share: the filter in force, the period, the row count behind the numbers and the date of the extract. A table without those is a claim without a basis, and the first question it gets will be about the basis.

Working with other people's workbooks

Often the workbook is not yours and cannot be fixed. The sender is a vendor, a branch or another department, and the file arrives monthly with whatever quirks it has. Plan for it.

Write down the shape you expect: sheet name, header row, column names and types. Check each new file against it before analysis, and fail with a readable message when it differs. That turns a silent wrong answer into a conversation with the sender. Keep every received file, unchanged, in a dated folder. When a number is challenged in six months, the received file is the evidence of what you were given.

Where the file's layout varies, write a small adapter for each sender: a function that reads their workbook and returns the standard table. The analysis then works on the standard table and does not change when a sender changes format. A new sender is a new adapter.

Treat the contents as untrusted. A workbook can contain formulas that call out, macros and values that look like instructions. Read it with a library that does not run macros, read values and not formulas, and do not follow links inside it.

Sharing the results

An analysis that nobody understands is a wasted analysis. The last step is presenting the answer so that a reader can use it and trust it.

Lead with the answer in a sentence, then the table or chart, then the basis. Keep the number of figures small. A reader remembers one or two. Show the comparison that matters, such as this year against last, and label the axes and units. If the answer rests on a judgment (that a gap is a void, that a month is partial), say so where the number appears, not on page twelve.

Offer the workbook of results with a "Notes" sheet that lists the query file, the source files and the checks that passed. People who want to challenge the work can do so with a path to follow. People who do not will rarely open it, and its existence changes how they trust the rest.

When the same analysis recurs, automate the delivery as well as the calculation (automating weekly Excel reports with Python and cron). The goal of the whole approach is that producing the answer becomes boring, and the time goes into understanding it.

Training a team

If you are introducing this to colleagues, the order of teaching matters more than the content. Start with reading, not writing. Give people a query that answers a question they already ask, with its output, and ask them to predict what a change would do. Reading a GROUP BY and its result builds the habit of thinking in sets, which is the biggest shift from cell-by-cell thinking.

Then teach the checks before the clever parts. A person who learns to reconcile row counts and totals on day one will catch their own mistakes for years. A person who learns window functions first will produce impressive wrong numbers.

Pair people on the first analysis that matters to them, with one query written from their own workbook. Real data with real stakes holds attention that exercises do not. Keep a shared folder of worked queries with comments, so the second person starts from the first person's pattern.

Finally, be patient with the transition of habits. Someone who has built models in Excel for fifteen years has real skill, much of which transfers: they know what the numbers should look like and where the business hides its exceptions. The query is a new way to write down what they already know.

Governance for analysis that people rely on

When an analysis feeds decisions, a little governance pays for itself. Decide who owns each recurring query and who reviews a change to it. A one-line rule is enough: no change to a published definition without a second pair of eyes. Keep a changelog in the file's header, saying what changed and why, because a definition that shifts silently is the most common reason that this month's figure cannot be compared with last month's.

Agree the definitions that everyone uses. What is revenue (gross or net of returns)? What is an active customer? Which date decides the month? Put them in a shared view or a short document, and refer to them from the queries. When the finance team and the sales team report different numbers, the cause is almost always two definitions that were never reconciled, and a shared definition ends the argument before it starts.

Plan for the day the owner leaves. The query file, the data dictionary and the list of source workbooks should be in a place the team can reach, with enough notes that a competent colleague can run the analysis without a call. If you cannot hand it over in an afternoon, it is not yet documented.

A last point on scale: start with the reports whose errors cost the most, not the ones that are easiest to convert. The first migrated analysis should be one where a mistake once mattered, so that the checks it gains are visible to the people who were hurt by the old version.

When SQL on an Excel file is not worth it

The question is one-off and small. Build the pivot, answer, move on.

The audience wants to explore. Interactive slicing is what a pivot or a BI tool is for.

The workbook is the product. If the deliverable is a formatted model that others will edit, replacing it with a query changes what they receive. Do it when the editing is the problem, not when it is the point.

Nobody on the team reads SQL. A query that only its author can read is as opaque as a spreadsheet. Plan for training or keep it simpler.

The data quality is the real problem. If the sheet is full of inconsistencies, cleaning comes before analysis (cleaning Excel data before analysis).

Where to go next

If you have never queried a workbook, begin with querying an Excel file with SQL in DuckDB, then summarizing a sales workbook with GROUP BY in SQL queries. If two numbers disagree, checking totals between two Excel reports with SQL is the one to use. If a fragile model worries you, read replacing a fragile Excel model with a query and a view. And if the move is from pivots, pivot tables and their SQL equivalents is the map.

The references are DuckDB's documentation, the SQLite documentation, the PostgreSQL window function tutorial and Microsoft's page on Excel specifications and limits. To convert a small result to JSON, use the CSV to JSON converter.