Header Ads Widget

Responsive Advertisement

Rinki... April 04, 2026

 

About Lesson

📘 Day 2: Getting Started with Excel for Data Analysis – Basics to Smart Techniques

Welcome to Day 2! Today marks your first hands-on experience with Microsoft Excel, one of the most widely used tools in data analytics. While Excel might look like just a spreadsheet tool, it’s a powerful platform for cleaning, transforming, exploring, and visualizing data.

In this session, you’ll explore Excel from the ground up—understanding its interface, formulas, and formatting tools that turn raw data into usable insights. By the end of the day, you’ll not only be familiar with Excel’s interface but also confident working with datasets, formulas, and smart techniques like sorting, filtering, and conditional formatting.

🔍 Why Excel Matters for Data Analysts

Excel is the foundation stone for data analysis. No matter how advanced a tool you end up using (like SQL, Power BI, or Python), Excel is the go-to application for:

  • Quick exploratory data analysis (EDA)

  • Creating reports and dashboards

  • Data cleaning and wrangling

  • Ad-hoc analysis

  • Communicating results to non-technical stakeholders

And the best part? Most organizations already use Excel, so your skills will be immediately applicable.

🧑‍🏫 Exploring the Excel Interface (For Beginners and Refreshers)

When you first open Excel, take a moment to explore its major components:

  • Workbook: A file containing multiple sheets.

  • Worksheet: A single tab within the workbook (like “Sheet1”).

  • Cell: Each intersection of a row and column (like A1 or C3).

  • Formula Bar: Where you write formulas or see cell contents.

  • Ribbon: The top toolbar with Home, Insert, Formulas, Data, and other tabs.

Spend 10–15 minutes just clicking around and getting comfortable. Try renaming a worksheet, adjusting row height/column width, and entering text and numbers.

📊 Loading and Exploring a Sample Dataset

Let’s work with a sample dataset—say, an eCommerce order table. You can download this kind of dataset from Kaggle or use the following columns as an example:

Order ID Date Customer Name Product Category Quantity Price Total
1001 2024-01-15 Alice Smith Mouse Electronics 2 500 1000
1002 2024-01-15 Bob Johnson Monitor Electronics 1 7500 7500

Paste similar data into your Excel sheet.

Now:

  • Explore the data

  • Adjust column widths

  • Format the Date column

  • Set currency for Price and Total

  • Align headers and apply bold formatting

This is your first taste of data formatting—making data readable and presentable.

🧮 Basic Excel Formulas Every Analyst Must Know

Let’s start with formulas, the heart of Excel analysis. Here are some beginner-friendly but essential ones:

1. SUM()

  • Purpose: Adds up numbers in a range.

  • Example: =SUM(G2:G100) → Adds up the “Total” column.

2. AVERAGE()

  • Purpose: Calculates the average.

  • Example: =AVERAGE(F2:F100) → Average quantity sold.

3. COUNT() / COUNTA()

  • COUNT counts numbers, COUNTA counts non-blank cells.

  • Example: =COUNT(A2:A100) → Number of orders.

4. MIN() / MAX()

  • MIN() finds the smallest value; MAX() finds the largest.

  • Use to find highest/lowest price, quantity, etc.

5. IF()

  • Purpose: Logical conditions.

  • Example: =IF(F2>3, "Bulk", "Standard")
    → Labels orders with quantity > 3 as “Bulk”

6. CONCATENATE() / TEXTJOIN()

  • Combine text from multiple columns.

  • =CONCATENATE(B2, " - ", D2)
    → Combines customer name and product.

Practice Challenge:

Add 10 new rows of fictional data to your dataset, then use the formulas above to:

  • Calculate the total revenue

  • Find the average price per product

  • Count how many orders were placed

  • Label all high-quantity orders as “Bulk”

🧠 Mastering Sorting & Filtering

With real-world data, you’re often looking for specific answers like:

  • Which customers placed the largest orders?

  • What’s the trend by product or category?

Start using:

Sort

  • Go to the Data tab → Sort

  • Sort by Quantity (Descending) to find highest orders

  • Sort by Category to group similar products

Filter

  • Apply a filter to your header row

  • Select specific values (like only “Electronics”)

  • Combine multiple filters (Category = “Clothing” AND Quantity > 2)

Filters are a powerful tool for targeted analysis. It helps you narrow the data without deleting anything.

✨ Conditional Formatting – See the Story at a Glance

Conditional Formatting helps you highlight key patterns visually.

Try these:

  • Highlight all Total values greater than 5000

  • Use Color Scales to shade based on Quantity

  • Use Data Bars to visually compare prices

Conditional formatting makes data interpretation fast, especially in large datasets.

📋 Hands-On Exercise: Mini Data Report

Create a basic order summary report using the tools you’ve learned.

Instructions:

  1. Add a new worksheet named “Summary”

  2. Use formulas to calculate:

    • Total revenue

    • Number of orders

    • Average order value

    • Number of “Bulk” orders

  3. Use conditional formatting on the original dataset to highlight:

    • High revenue orders

    • Large quantities

  4. Use sorting and filters to find:

    • Top 3 customers by total order value

    • Most frequently sold product

📚 Bonus Learning Tips

Spend 30–60 minutes exploring Excel shortcuts to speed up your workflow. A few favorites:

  • Ctrl + Arrow Keys – Navigate large datasets

  • Ctrl + Shift + L – Toggle filters

  • Alt + E, S, V – Paste special

  • Ctrl + T – Turn data into a formatted table

Also, learn how to freeze panes, use named ranges, and protect sheets—all critical for professional reporting.

🧠 Reflection & Homework Task

  1. Reflect:
    Write 300–400 words on the following:

    • What did you learn about Excel today?

    • Which formula or feature surprised you the most?

    • How would you use Excel to solve a real-world problem?

  2. Explore:

    • Watch a YouTube video titled: “Top 10 Excel Tips Every Analyst Should Know”

    • Try to implement at least 2 new tips in your dataset.

  3. Optional Challenge:
    Create a simple dashboard layout using:

    • Summary stats (Revenue, Orders, etc.)

    • A table of top 5 products

    • Use colors, borders, and formatting for professional presentation

🔚 Wrap-up

You’ve just taken a major step today. Excel might seem simple on the surface, but when used well, it’s a powerhouse for data analysis. You now know how to:

  • Load and format data

  • Use formulas for calculations

  • Sort and filter

  • Apply conditional formatting

  • Summarize key insights

 

Post a Comment

0 Comments