Learn Data Analytics with Excel: Your First Hands-On Project for Beginners
Introduction
Reading about data analytics is a good first step, but real learning begins when you work with data yourself.Data Analytics with Excel: Beginner Project
In this practical tutorial, you will learn how to analyse a small sales dataset using Microsoft Excel. You will calculate total sales, compare products, identify the best-selling item by revenue, and create a simple report.
You do not need programming knowledge to complete this exercise. You only need Excel or a compatible spreadsheet application.
By the end of this lesson, you will understand how a basic data analyst approaches a business question and turns raw figures into useful information.Data Analytics with Excel: Beginner Project
1. Understand Your First Dataset
Imagine that a company sells LED drivers to different customers. The company records the product name, quantity sold, selling price per unit, and sales date.
Our sample dataset contains five transactions.
Copy the following data into Excel, starting from cell A1.
| Date | Product | Quantity | Unit Price (₹) |
|---|---|---|---|
| 01-Oct-2026 | Driver A | 10 | 500 |
| 02-Oct-2026 | Driver B | 15 | 800 |
| 03-Oct-2026 | Driver C | 20 | 300 |
| 04-Oct-2026 | Driver A | 12 | 500 |
| 05-Oct-2026 | Driver B | 8 | 800 |
Each row represents one transaction.
The columns describe the information recorded for each transaction.
This is a simple structured dataset, and understanding its layout is the first step in analysis.
2. Step One: Open Excel and Enter the Data
Follow these steps:
Open Microsoft Excel.
Select a blank workbook.
Enter the four column headings in cells A1 through D1.
Enter the five transactions underneath the headings.
Check that the quantities and prices are stored as numbers.
Format the date column as dates and the price column as currency or numbers.
Save the workbook as Data_Analytics_Day_1.xlsx.
Practice task: Make sure every transaction has a date, product name, quantity, and unit price.
3. Step Two: Calculate Total Sales for Each Transaction
Our first business question is:
How much revenue did each transaction generate?
The calculation is:
Total sales = Quantity × Unit price
Add a new heading called Total Sales in cell E1.
In cell E2, enter:
=C2*D2
Press Enter.
The first transaction contains 10 units at ₹500 per unit, so the total is ₹5,000.
To calculate the remaining rows, select E2 and drag the fill handle down to E6.
Your results should be:
| Product | Quantity | Unit Price | Total Sales |
|---|---|---|---|
| Driver A | 10 | ₹500 | ₹5,000 |
| Driver B | 15 | ₹800 | ₹12,000 |
| Driver C | 20 | ₹300 | ₹6,000 |
| Driver A | 12 | ₹500 | ₹6,000 |
| Driver B | 8 | ₹800 | ₹6,400 |
You have now created your first calculated field.
Remember that this represents sales revenue, not profit. To calculate profit, you would also need cost information.Data Analytics with Excel: Beginner Project
4. Step Three: Calculate Total Revenue
Now answer another question:
How much revenue did the company generate across all five transactions?
In cell E7, enter:
=SUM(E2:E6)
The result should be ₹35,400.
The SUM function adds the values in the selected range.
You can use the same formula to calculate total quantity sold. If quantities are in column C, enter:
=SUM(C2:C6)
The expected result is 65 units.
These are two important summary metrics:
Total quantity sold: 65 units.
Total sales revenue: ₹35,400.
5. Step Four: Find the Average Transaction Value
Suppose the manager wants to know the average revenue generated by each transaction.
The formula is:
=AVERAGE(E2:E6)
The expected result is ₹7,080 per transaction.
Average transaction value can help businesses compare sales activity over time.
However, an average does not show how evenly sales are distributed. For a fuller picture, an analyst may also examine the smallest and largest transactions.Data Analytics with Excel: Beginner Project
Try these formulas:
Minimum transaction value:
=MIN(E2:E6)
Maximum transaction value:
=MAX(E2:E6)
Expected results:
Minimum: ₹5,000.
Maximum: ₹12,000.
6. Step Five: Compare Sales by Product
The dataset includes multiple transactions for the same product. To compare products properly, combine their sales.
For example, Driver A generated ₹5,000 in the first transaction and ₹6,000 in the second.
Its total sales revenue is ₹11,000.
You can calculate revenue by product using SUMIF.
Create a summary table in columns G and H.
| Product | Total Revenue |
|---|---|
| Driver A | |
| Driver B | |
| Driver C |
Enter the product names in G2, G3, and G4.
In H2, enter:
=SUMIF($B$2:$B$6,G2,$E$2:$E$6)
Copy the formula down to H4.
Excel will calculate the revenue for each product.
Your expected results are:
| Product | Total Revenue |
|---|---|
| Driver A | ₹11,000 |
| Driver B | ₹18,400 |
| Driver C | ₹6,000 |
Driver B generates the highest revenue in this sample dataset.
This is a simple example of descriptive analytics because we are summarising historical sales to understand what happened.Data Analytics with Excel: Beginner Project
7. Step Six: Identify the Best-Selling Product
The phrase “best-selling product” can mean different things.
It might refer to the product with the highest revenue or the product with the highest quantity sold.
In our example:
Highest revenue: Driver B, with ₹18,400.
Highest quantity sold: Driver C, with 20 units.
Driver B generates more revenue, but Driver C sells more units.
This distinction is important because revenue and volume measure different aspects of business performance.Data Analytics with Excel: Beginner Project
For a better decision, the company should also consider product costs, gross margins, customer demand, and inventory availability.
8. Step Seven: Create a Simple Chart
A chart helps people understand the results quickly.
To create a product revenue chart:
Select the product summary table in cells G1.
Open the Insert tab.
Select a column or bar chart.
Give the chart the title
Sales Revenue by Product.Check that the product names appear as category labels.
Format the values as currency if appropriate.
Your chart should show Driver B as the highest-revenue product, followed by Driver A and Driver C.
Do not add a chart simply to decorate a report. Choose a visualisation that answers a specific question.
9. Step Eight: Interpret Your Findings
A data analyst must explain what the results mean.
Based on this sample dataset, we can report:
Finding 1: Total sales revenue was ₹35,400.
Finding 2: The company sold 65 units across five transactions.
Finding 3: Driver B generated the highest revenue at ₹18,400.
Finding 4: Driver C had the highest sales quantity at 20 units.
Finding 5: The average transaction value was ₹7,080.
These observations describe the dataset. They do not establish why one product performs better than another or whether the company made a profit.
To investigate further, the analyst could request product cost data, customer information, and sales records from previous months.Data Analytics with Excel: Beginner Project
10. Step Nine: Turn Your Analysis into a Business Report
A basic report should include a clear title, key metrics, a product comparison, a chart, and a short explanation.
Example:
October Sales Summary — Sample Data
Total revenue: ₹35,400
Total quantity sold: 65 units
Average transaction value: ₹7,080
Highest-revenue product: Driver B
Highest-volume product: Driver C
Recommendation: Review the cost and gross margin of each product before making inventory or marketing decisions. Compare these results with additional months of sales data before assuming the observed pattern will continue.
This is how an analyst moves from calculations to business communication.
11. Common Excel Mistakes to Avoid
Incorrect cell references
Check that your formulas refer to the correct columns and rows.
Numbers stored as text
If Excel treats a quantity or price as text, calculations may not work as expected.
Incorrect formulas copied down
Use the fill handle carefully and verify the results.
Duplicate transactions
Check for duplicate records before calculating totals. Do not delete apparent duplicates without confirming whether they represent genuine transactions.
Confusing revenue and profit
Revenue is the amount generated from sales. Profit requires costs and other relevant expenses to be considered.Data Analytics with Excel: Beginner Project
Poorly labelled charts
Give charts meaningful titles and label values clearly.
12. Your Day 1 Homework
Complete these exercises using the same dataset.
Calculate the total quantity sold.
Find the total sales revenue.
Calculate the average transaction value.
Identify the product with the highest revenue.
Identify the product with the highest quantity sold.
Create a bar chart showing sales revenue by product.
Write three sentences summarising your findings.
Explain what additional information you would need to calculate gross profit.
Try to solve the questions before checking the answer key below.
Day 1 Answer Key
Total quantity sold: 65 units.
Total sales revenue: ₹35,400.
Average transaction value: ₹7,080.
Highest revenue: Driver B, ₹18,400.
Highest quantity: Driver C, 20 units.
The bar chart should show Driver B with the highest revenue.
Your summary should mention total revenue, the leading product by revenue, and the leading product by quantity.
You need the relevant cost of goods sold for the products; a fuller profit analysis may also require other expenses.Data Analytics with Excel: Beginner Project
Interview Questions for Beginners
1. What is data analytics?
It is the process of examining and interpreting data to answer questions and support decisions.
2. What is the SUM function in Excel?
SUM adds values within a specified range.
3. What is the AVERAGE function?
AVERAGE calculates the arithmetic mean of numerical values.
4. What is SUMIF used for?
SUMIF adds values that meet a specified condition.
5. What is the difference between revenue and profit?
Revenue represents sales income, while profit accounts for relevant costs and expenses.
6. Why is data cleaning important?
It helps identify and correct errors and inconsistencies that could affect analysis.
7. What is descriptive analytics?
It summarises historical data to explain what happened.
8. Why do analysts use charts?
Charts help communicate patterns, comparisons, and trends.
Frequently Asked Questions
1. Can I practise data analytics using Excel at home?
Yes. A spreadsheet application and a small dataset are enough to begin practising basic calculations and reporting.
2. Do I need to know coding for this project?
No. This exercise uses standard Excel formulas and a chart.
3. What is the purpose of the SUMIF formula?
It calculates the sum of values that meet a specified condition, such as total sales for one product.
4. Is this a real company sales report?
No. The figures are illustrative sample data designed for learning.
5. What should I learn next?
Continue practising Excel, including sorting, filtering, data cleaning, conditional formulas, and PivotTables. Then move towards SQL and Power BI.
Conclusion
Congratulations on completing your first practical data analytics exercise.
You have entered a dataset, calculated transaction revenue, summarised total sales, compared products, created a chart, and written a basic report.Data Analytics with Excel: Beginner Project
These are foundational tasks that analysts perform in many business environments.Data Analytics with Excel: Beginner Project
The most important lesson is that data analytics is not simply about calculating numbers. It is about asking the right questions, checking the quality of the data, interpreting the results carefully, and communicating useful findings.
Keep your workbook because you can improve it as you learn more Excel functions and dashboard techniques in the following lessons.Data Analytics with Excel: Beginner Project
){kind=link}
0 Comments