Excel and SQL Practice Exercises for Data Analyst Beginners (With Answers)

Gulab Chand Tejwani
PMP · PL-300 · Microsoft Certified Trainer
· 9 min read

The fastest way to practise Excel and SQL for a data analyst role is to answer real questions on one small table, in both tools, and check your answers against known results. This page gives you that: one ten-row sales table, 11 Excel exercises and 11 SQL exercises, each with the formula or query and the exact answer it should return.
I'm Gulab Chand Tejwani from TechNet Consultancy. I teach data analysis in Anand and online, and this is the warm-up set I give learners in their first weeks. Every SQL answer below was run in PostgreSQL 16 and every Excel result was calculated in a spreadsheet, so if your number differs, the mistake is in the formula, not the key. The data is made up for illustration.
What is the practice table?
Ten orders from January to March 2026, amounts in rupees.
order_id order_date customer city category qty price
1 2026-01-04 Asha Anand Laptops 1 52000
2 2026-01-09 Bhavin Nadiad Phones 2 18000
3 2026-01-15 Chirag Anand Accessories 5 800
4 2026-02-02 Asha Anand Phones 1 21000
5 2026-02-11 Diya Vadodara Laptops 1 61000
6 2026-02-19 Esha Anand Accessories 3 1200
7 2026-03-03 Bhavin Nadiad Laptops 1 48000
8 2026-03-10 Farhan Vadodara Phones 1 15000
9 2026-03-21 Asha Anand Accessories 2 800
10 2026-03-28 Diya Vadodara Phones 3 9000
In Excel, put the headers in row 1 and the orders in rows 2 to 11, columns A to G. Type them in, or paste the block and use Data, Text to Columns with Space as the delimiter and "Treat consecutive delimiters as one" ticked, setting column B to Date (YMD). Check that the dates are real dates, not text, or the date exercises will miss rows. Add column H, value, with =F2*G2 copied down. Row 2's value is 52,000 and row 3's is 36,000.
For SQL, create the table and load the same rows. Use a fresh practice database, and stop if CREATE TABLE fails because a table called orders already exists:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer VARCHAR(20),
city VARCHAR(20),
category VARCHAR(20),
qty INT,
price INT
);
INSERT INTO orders VALUES
(1,'2026-01-04','Asha','Anand','Laptops',1,52000),
(2,'2026-01-09','Bhavin','Nadiad','Phones',2,18000),
(3,'2026-01-15','Chirag','Anand','Accessories',5,800),
(4,'2026-02-02','Asha','Anand','Phones',1,21000),
(5,'2026-02-11','Diya','Vadodara','Laptops',1,61000),
(6,'2026-02-19','Esha','Anand','Accessories',3,1200),
(7,'2026-03-03','Bhavin','Nadiad','Laptops',1,48000),
(8,'2026-03-10','Farhan','Vadodara','Phones',1,15000),
(9,'2026-03-21','Asha','Anand','Accessories',2,800),
(10,'2026-03-28','Diya','Vadodara','Phones',3,9000);
Excel practice exercises
Try each one first. The answer and the result follow.
1. What is the total revenue?
=SUM(H2:H11) returns 269,200. Check it a second way without column H: =SUMPRODUCT(F2:F11,G2:G11) also returns 269,200.
2. What is the revenue from Anand?
=SUMIFS(H2:H11,D2:D11,"Anand") returns 82,200.
3. How many Phones orders are there?
=COUNTIFS(E2:E11,"Phones") returns 4.
4. How many Phones orders came from Vadodara?
=COUNTIFS(E2:E11,"Phones",D2:D11,"Vadodara") returns 2. Each extra pair of arguments adds one more condition.
5. What is the average Laptops order value?
=AVERAGEIFS(H2:H11,E2:E11,"Laptops") returns 53,666.67. Wrap it in ROUND(...,0) to show 53,667.
6. What is the largest order from Nadiad?
=MAXIFS(H2:H11,D2:D11,"Nadiad") returns 48,000. MAXIFS needs Excel 2019 or Microsoft 365.
7. Who placed the largest order?
=INDEX(C2:C11,MATCH(MAX(H2:H11),H2:H11,0)) returns Diya. MATCH finds the row of the largest value and INDEX returns the customer in that row.
8. Label each order High or Low, with High meaning 20,000 or more.
=IF(H2>=20000,"High","Low") returns High for row 2. To count the High orders, use =COUNTIF(H2:H11,">=20000"), which returns 6.
9. Show each order's month as text.
=TEXT(B2,"yyyy-mm") returns 2026-01 for row 2. A helper column like this makes monthly PivotTables and SUMIFS easier.
10. What is the revenue for February 2026?
=SUMIFS(H2:H11,B2:B11,">="&DATE(2026,2,1),B2:B11,"<"&DATE(2026,3,1)) returns 85,600. Use "on or after the 1st" and "before the next 1st" so no day is missed.
11. What share of total revenue is order 5?
=H6/SUM($H$2:$H$11) returns 0.2266, which is 22.7% with the Percentage format set to one decimal place. The Percent Style button and Ctrl+Shift+% use no decimals and show 23%. The $ signs keep the total fixed when you copy the formula down. Excel percentage formulas covers this in more depth.
SQL practice exercises
1. What is the total revenue?
SELECT SUM(qty * price) AS revenue FROM orders;
Returns 269200, the same as Excel.
2. What is the revenue by city, highest first?
SELECT city, SUM(qty * price) AS revenue
FROM orders
GROUP BY city
ORDER BY revenue DESC;
city | revenue
----------+--------
Vadodara | 103000
Nadiad | 84000
Anand | 82200
Anand matches Excel exercise 2.
3. How many orders and units are there per category?
SELECT category, COUNT(*) AS orders, SUM(qty) AS units
FROM orders
GROUP BY category
ORDER BY category;
category | orders | units
-------------+--------+------
Accessories | 3 | 10
Laptops | 3 | 3
Phones | 4 | 7
4. Which customers ordered more than once?
SELECT customer, COUNT(*) AS orders
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1
ORDER BY orders DESC, customer;
Returns Asha with 3, then Bhavin and Diya with 2 each. HAVING filters after grouping; WHERE cannot use COUNT(*).
5. What is the revenue per month?
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(qty * price) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;
month | revenue
---------+--------
2026-01 | 92000
2026-02 | 85600
2026-03 | 91600
February matches Excel exercise 10. TO_CHAR is PostgreSQL; MySQL uses DATE_FORMAT(order_date, '%Y-%m').
6. What are the three largest orders?
SELECT order_id, customer, qty * price AS order_value
FROM orders
ORDER BY order_value DESC
LIMIT 3;
Returns order 5 (Diya, 61000), order 1 (Asha, 52000) and order 7 (Bhavin, 48000). SQL Server uses SELECT TOP 3 in place of LIMIT.
7. What is the average order value per category?
SELECT category, ROUND(AVG(qty * price), 0) AS avg_order_value
FROM orders
GROUP BY category
ORDER BY avg_order_value DESC;
Returns Laptops 53667, Phones 24750 and Accessories 3067. Laptops matches Excel exercise 5.
8. Which customers bought both Laptops and Phones?
SELECT customer
FROM orders
WHERE category IN ('Laptops', 'Phones')
GROUP BY customer
HAVING COUNT(DISTINCT category) = 2
ORDER BY customer;
Returns Asha, Bhavin and Diya. Counting distinct categories per customer is a common interview pattern.
9. What share of revenue does each category bring?
SELECT category,
SUM(qty * price) AS revenue,
ROUND(100.0 * SUM(qty * price) / SUM(SUM(qty * price)) OVER (), 1) AS pct
FROM orders
GROUP BY category
ORDER BY revenue DESC;
category | revenue | pct
-------------+---------+-----
Laptops | 161000 | 59.8
Phones | 99000 | 36.8
Accessories | 9200 | 3.4
SUM(...) OVER () puts the grand total on every row, so you can divide by it.
10. What is the running total of revenue by month?
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
SUM(qty * price) AS revenue,
SUM(SUM(qty * price)) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')) AS running_total
FROM orders
GROUP BY 1
ORDER BY 1;
month | revenue | running_total
---------+---------+--------------
2026-01 | 92000 | 92000
2026-02 | 85600 | 177600
2026-03 | 91600 | 269200
The last running total equals the grand total from exercise 1, which is a good check.
11. Which customers never bought a laptop?
SELECT customer, city
FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM orders x
WHERE x.customer = o.customer AND x.category = 'Laptops'
)
GROUP BY customer, city
ORDER BY customer;
Returns Chirag (Anand), Esha (Anand) and Farhan (Vadodara). A simple WHERE category <> 'Laptops' would wrongly include Asha, who bought a laptop and other items.
| Question | Excel | SQL |
|---|---|---|
| Total of a column | SUM | SUM |
| Total with a condition | SUMIFS | SUM with WHERE or GROUP BY |
| Count with conditions | COUNTIFS | COUNT(*) with WHERE |
| Average by group | AVERAGEIFS | AVG with GROUP BY |
| Look up a value | INDEX MATCH or XLOOKUP | JOIN or subquery |
| Filter after grouping | PivotTable filter | HAVING |
| Share of total | =value/total | SUM(...) OVER () |
How should a beginner practise?
- Type every formula and query yourself. Copying teaches very little.
- Check each answer two ways, as in exercises 1, 2 and 10. Analysts are paid for numbers that are right.
- Change the question slightly: revenue for Nadiad, orders in March, the smallest order. Predict the answer before you run it.
- Move to a bigger, messier dataset once these feel easy. Cleaning comes first in real work; removing duplicates in Excel is a good next step.
For more SQL, SQL queries for data analysis and SQL joins explained go further, and the free company-style question set in the SQL interview practice store runs in the browser. For Excel, see Excel skills for data analysts.
If you want guided practice with feedback, the data analysis course in Anand and live online works through Excel, SQL, Power BI and Python with projects. Ask about batches and fees before you enrol.
FAQ
Where can I practise Excel and SQL for data analysis for free?
Use one small table in both tools, like the ten-order sales table on this page, and check your answers against the results given. Then move to free public datasets for larger practice.
Which Excel functions should a data analyst beginner practise first?
SUM, SUMIFS, COUNTIFS, AVERAGEIFS, IF, INDEX with MATCH (or XLOOKUP), TEXT for dates, and PivotTables. These cover most beginner reporting tasks.
Which SQL topics should a beginner practise first?
SELECT with WHERE, GROUP BY with SUM, COUNT and AVG, HAVING, ORDER BY with LIMIT, joins, and one window function such as a running total or share of total.
How do I check if my SQL answer is right?
Calculate the same number another way: in Excel, with a different query, or by checking that parts add up to the total. On this page, the city totals add up to the grand total of 269,200.
Should I learn Excel or SQL first?
Excel first, because it lets you see every row while you learn to summarise data. Move to SQL once SUMIFS and PivotTables feel easy, since the same ideas carry over to GROUP BY.