From Excel to SQL: A Smooth Transition Guide

If you are good at Excel, you already think in SQL; you just do not know the syntax yet. Filtering rows, totalling by category, looking up values from another table and building pivot tables are exactly what SQL does, but on data that lives in a database, at any size, in a way that can be rerun exactly. The fastest way to learn is to translate what you already do. Each translation below runs against the Chinook sample database, a small digital music store with customers, invoices and tracks (Chinook database), using SQLite from Python so every result is shown.

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")

def run(sql):
    print(pd.read_sql(sql, con).to_string(index=False))

Filtering becomes WHERE

In Excel you would turn on AutoFilter, choose a country and set a number filter. In SQL, the same request is written out:

run("""
SELECT InvoiceId, BillingCountry, InvoiceDate, Total
FROM Invoice
WHERE BillingCountry = 'India' AND Total > 10
ORDER BY Total DESC""")

 InvoiceId BillingCountry         InvoiceDate  Total
       131          India 2010-07-23 00:00:00  13.86
       229          India 2011-09-30 00:00:00  13.86

SELECT chooses columns, FROM chooses the table, WHERE keeps matching rows and ORDER BY sorts; the result is a set of rows shaped by those clauses (SQLite: SELECT). Unlike a filter on a sheet, the query is a written record of exactly what was selected.

SUMIFS becomes GROUP BY

A SUMIFS formula adds values that meet criteria (Microsoft Support: SUMIFS), and you would copy it down a list of countries. SQL does the whole table in one statement:

run("""
SELECT BillingCountry, COUNT(*) AS invoices, ROUND(SUM(Total), 2) AS revenue
FROM Invoice
GROUP BY BillingCountry
ORDER BY revenue DESC
LIMIT 5""")

BillingCountry  invoices  revenue
           USA        91   523.06
        Canada        56   303.96
        France        35   195.10
        Brazil        35   190.10
       Germany        28   156.48

GROUP BY creates one row per country and the aggregate functions summarise each group. No list of countries to maintain, no formula to copy down, and new countries appear automatically.

XLOOKUP becomes a JOIN

XLOOKUP returns a value from another table for the first match (Microsoft Support: XLOOKUP). In SQL, a join combines tables on matching keys, bringing in as many columns as you need at once. The lines of the first invoice, with each track's name and genre:

run("""
SELECT t.Name AS track, g.Name AS genre, il.UnitPrice
FROM InvoiceLine il
JOIN Track t ON t.TrackId = il.TrackId
LEFT JOIN Genre g ON g.GenreId = t.GenreId
WHERE il.InvoiceId = 1""")

            track genre  UnitPrice
Balls to the Wall  Rock       0.99
Restless and Wild  Rock       0.99

A LEFT JOIN keeps every row from the first table even when there is no match, like XLOOKUP with an "if not found" value; an inner JOIN keeps only matches. One difference matters: XLOOKUP returns the first match only, while a join returns every match. If the lookup table has duplicate keys, the join duplicates rows, so always check row counts after joining.

COUNTIFS and IF become CASE

Distinct counts and conditional counts in one query, including how many invoices are 10 or more:

run("""
SELECT COUNT(DISTINCT BillingCountry) AS countries,
       COUNT(DISTINCT CustomerId) AS customers,
       SUM(CASE WHEN Total >= 10 THEN 1 ELSE 0 END) AS invoices_10_or_more
FROM Invoice""")

 countries  customers  invoices_10_or_more
        24         59                   64

CASE WHEN ... THEN ... ELSE ... END is SQL's IF. Wrapped in SUM, it counts the rows meeting a condition, like COUNTIFS. COUNT(DISTINCT ...) replaces the remove-duplicates-then-count routine.

Pivot tables become conditional aggregation

A pivot table with countries in rows and years in columns becomes one CASE expression per column. Here with a direction flag, the equivalent of an IF comparing two cells:

run("""
SELECT BillingCountry,
       ROUND(SUM(CASE WHEN strftime('%Y', InvoiceDate) = '2012' THEN Total END), 2) AS y2012,
       ROUND(SUM(CASE WHEN strftime('%Y', InvoiceDate) = '2013' THEN Total END), 2) AS y2013,
       CASE WHEN SUM(CASE WHEN strftime('%Y', InvoiceDate) = '2013' THEN Total END)
               > SUM(CASE WHEN strftime('%Y', InvoiceDate) = '2012' THEN Total END)
            THEN 'up' ELSE 'down or flat' END AS direction
FROM Invoice
WHERE BillingCountry IN ('USA', 'Canada', 'India', 'Brazil')
GROUP BY BillingCountry
ORDER BY BillingCountry""")

BillingCountry  y2012  y2013    direction
        Brazil  53.46  37.62 down or flat
        Canada  42.57  72.27           up
         India  10.89  11.89           up
           USA 127.98  85.14 down or flat

This is more verbose than dragging fields into a pivot table, which is why many analysts pull a grouped result with SQL and pivot it in Excel or a BI tool. strftime is SQLite's date function; SQL Server and PostgreSQL use different date functions, one of the few areas where dialects differ noticeably.

Cleaning functions you will reach for

Many Excel cleaning habits have direct SQL counterparts. SELECT DISTINCT replaces Remove Duplicates for the columns you list. TRIM, UPPER and LOWER work much like their Excel namesakes. COALESCE(column, 'Unknown') replaces blanks with a default, like an IF around an empty cell, and CAST converts text that looks like a number into a number. The difference is that the cleaned version lives in the query, so it is applied the same way every time the query runs.

The mindset shifts

• Tables, not cells. There are no cell references, merged cells or formatting; each table has columns with types and rows that each represent one thing.
• Know the grain. One row per invoice, or per invoice line? Most wrong answers come from joining or summing at the wrong grain.
• Queries are recipes. Rerunning a query recomputes everything from current data; there is nothing to copy down or forget to extend.
• The data stays put. You send a query to the data instead of copying data into a sheet, which is why SQL handles millions of rows.

Pitfalls Excel users meet

• NULL is not zero or blank. WHERE Company <> 'X' silently drops rows where Company is NULL; test with IS NULL.
• Integer division. In some databases, dividing two whole numbers discards the fraction; multiply by 1.0 first.
• No guaranteed order without ORDER BY.
• Text matching may be case-sensitive depending on the database and settings.

A practice plan

1. Work through free interactive lessons for the syntax (SQLBolt).
2. Take a report you build in Excel and rebuild it as queries on a sample database like Chinook.
3. Reconcile every total with your Excel version.
4. Learn joins until you can predict the row count before running the query.
5. Move on to window functions, the next big step; SQL for data analysts lists the queries used daily.

Once the query gives a clean, grouped result, keep using Excel or a BI tool for charts and last-mile formatting: Excel formulas for data analysis and pandas vs SQL cover where each tool fits best.

SQL queries, joins, aggregate functions, GROUP BY and HAVING, subqueries and window functions are covered in the Data Analytics course, and in more depth in the SQL Server course.

Sources

• SELECT (SQLite documentation) — SQLite
• Chinook Database (GitHub) — Luis Rocha
• SUMIFS function (Microsoft Support) — Microsoft Support
• XLOOKUP function (Microsoft Support) — Microsoft Support
• SQLBolt: Learn SQL with simple, interactive exercises — SQLBolt