Skip to content

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

Gulab Chand Tejwani

Gulab Chand Tejwani

PMP · PL-300 · Microsoft Certified Trainer

· 9 min read

ExcelSQLData AnalysisPractice ExercisesData AnalystSQL Basics
Excel and SQL Practice Exercises for Data Analyst Beginners (With Answers)

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.

QuestionExcelSQL
Total of a columnSUMSUM
Total with a conditionSUMIFSSUM with WHERE or GROUP BY
Count with conditionsCOUNTIFSCOUNT(*) with WHERE
Average by groupAVERAGEIFSAVG with GROUP BY
Look up a valueINDEX MATCH or XLOOKUPJOIN or subquery
Filter after groupingPivotTable filterHAVING
Share of total=value/totalSUM(...) OVER ()
The same question in Excel and SQL

How should a beginner practise?

  1. Type every formula and query yourself. Copying teaches very little.
  2. Check each answer two ways, as in exercises 1, 2 and 10. Analysts are paid for numbers that are right.
  3. Change the question slightly: revenue for Nadiad, orders in March, the smallest order. Predict the answer before you run it.
  4. 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.

Choose optional cookies

We use necessary cookies for account security. With your permission, we also use analytics and advertising cookies on marketing pages. See our Privacy Policy.