If you are comfortable in Excel, you already understand most of data analysis. Filtering, looking up values from another sheet, summarising with a pivot table: these are the same operations analysts do in SQL and pandas. Only the syntax changes. This article gives you the translation table.
Why move beyond Excel at all?
Excel is excellent for small, one-off work and for sharing with non-technical colleagues. It starts to struggle when:
- The data is too large to open comfortably, or lives in a database you cannot export in full.
- The same analysis must be repeated every week or month.
- Several people need to check exactly how a number was calculated.
- You need to combine many tables reliably.
SQL and pandas solve these problems because your steps are written down as code: repeatable, checkable and fast. Most analyst roles expect Excel and SQL, and many expect Python too.
Filter โ WHERE / boolean mask
In Excel you click the filter arrow and pick "City = Pune" and "Amount > 500".
SELECT * FROM orders WHERE city = 'Pune' AND amount > 500;orders[(orders["city"] == "Pune") & (orders["amount"] > 500)]Sort โ ORDER BY / sort_values
SELECT * FROM orders ORDER BY amount DESC;orders.sort_values("amount", ascending=False)VLOOKUP / XLOOKUP โ JOIN / merge
This is the big one. In Excel you write XLOOKUP to pull a customer's city from the Customers sheet into the Orders sheet. In SQL and pandas, you join the two tables:
SELECT o.*, c.city
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;orders.merge(customers[["customer_id", "city"]], on="customer_id", how="left")A LEFT JOIN behaves like a lookup that returns blank (NULL) when there is no match. Read our SQL joins guide for the details, especially the part about duplicate keys: a lookup table with repeated keys multiplies rows in SQL, where Excel would just return the first match.
Pivot table โ GROUP BY / pivot_table
A pivot table with City in rows and Sum of Amount in values:
SELECT city, SUM(amount) AS revenue, COUNT(*) AS orders
FROM orders
GROUP BY city;orders.groupby("city").agg(revenue=("amount", "sum"), orders=("order_id", "count"))With a column field as well (City in rows, Month in columns), use pandas pivot_table, or in SQL use SUM(CASE WHEN ...) for each column.
IF โ CASE WHEN / np.where
=IF(B2>40, "Late", "On time") becomes:
SELECT order_id,
CASE WHEN delivery_mins > 40 THEN 'Late' ELSE 'On time' END AS status
FROM orders;import numpy as np
orders["status"] = np.where(orders["delivery_mins"] > 40, "Late", "On time")SUMIFS / COUNTIFS โ conditional aggregates
=COUNTIFS(City, "Pune", Status, "Late") is a filtered count:
SELECT COUNT(*) FROM orders WHERE city = 'Pune' AND status = 'Late';Or, for every city at once:
SELECT city, SUM(CASE WHEN status = 'Late' THEN 1 ELSE 0 END) AS late_orders
FROM orders GROUP BY city;Remove duplicates, TRIM, text functions
| Excel | SQL | pandas |
|---|---|---|
| Remove Duplicates | SELECT DISTINCT | drop_duplicates() |
TRIM | TRIM(col) | df["col"].str.strip() |
UPPER / LOWER | UPPER(col) | df["col"].str.upper() |
LEFT(A2, 3) | SUBSTRING(col, 1, 3) | df["col"].str[:3] |
IFERROR, blanks | COALESCE(col, 0) | fillna(0) |
A good way to switch
Take a report you already know how to build in Excel and rebuild it in SQL, then in pandas. Because you know what the right answer looks like, you can focus on the syntax. Then practise each operation on its own: our SQL track and pandas merge and pivot sets are a good place to start.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: