Lesson 2 - Excel Fundamentals for Analysts

Master the essential Excel skills every analyst needs — from sorting and filtering to uncovering insights that drive real business decisions.

Estimated Read Time: 1.5-2 Hours

Learning Goals

In this lesson, you will learn the basics of MS Excel. By the end, you will:

Technical & Analytical

Navigate Excel’s interface: ribbons, worksheets, and essential operations.
Structure and organize workbooks for clarity, consistency, and analytical transparency.
Apply core operations — sorting and filtering — to extract business-relevant insights from raw data.
Format workbooks to enhance readability, reduce errors, and present data professionally.

Business Impact

Align technical steps with real business questions — from identifying top-performing stores to evaluating promotion effectiveness.
Demonstrate structured thinking in workbook setup, data organization, and presentation.

AI Literacy & AI-Proofing

Recognize Excel’s AI-powered suggestions (Analyze Data) as exploratory aids, not replacements for structured analysis.
Begin building judgment to critically evaluate AI-generated outputs — a skill expanded in later lessons.

Personal Branding & Career Development

Present your workbook as evidence of structured, business-oriented thinking.
Communicate your analytical approach confidently — whether in interviews, meetings, or written reports.

According to a 2024 research followed by U.S. Bureau of Labor Statistics data, 50.5% of employers mention MS Office Excel as an essential data analyst requirement (See Ref: The Data Analyst Job Market in 2024). Despite the rise of advanced BI tools and AI, employers consistently expect proficiency in Excel, especially for tasks like data cleaning, summarization, and quick insights.

Much of what makes an analyst effective isn’t theoretical—it’s practical, hands-on familiarity with the tools used daily in the workplace. Throughout this course, we focus on the Excel functions and workflows analysts actually use to support business decisions. By mastering these, you’ll build confidence not just for coursework, but for the challenges of real analyst roles.

1. The Rossmann Challenge

Rossmann operates over 3,000 drug stores in 7 European countries. Rossmann store managers are tasked with predicting their daily sales for up to six weeks in advance. Sales depend on promotions, competition, holidays, seasonality, and location. With thousands of individual managers predicting sales based on their unique circumstances, the accuracy of results can be quite varied.

Rossmann is challenging you to predict 6 weeks of daily sales for 100 stores located across Germany. Reliable sales forecasts enable store managers to create effective staff schedules that increase productivity and motivation. Accurate sales insights support better scheduling, operational efficiency, and customer service — core priorities for store managers.

1.1. Your Task as an Analyst

Your first task as an analyst is to deliver structured insights from Rossmann’s historical sales data. Reliable analysis forms the foundation for accurate forecasting and better business decisions.

Using Rossmann’s dataset, you will:

Identify which stores generate the highest revenue — and assess whether that reflects store size or other contributing factors.
Compare performance across store types evaluating not only total revenue, but also foot traffic and sales per customer.

These are the kinds of structured, business-driven questions analysts face daily. The clarity of your work will directly inform strategic decisions — and demonstrate your ability to approach data with discipline and insight.

1.2. The Data

Rossmann’s data is stored across two separate files — a common setup in real businesses:

rossmann-sales.xlsx — Daily sales transactions for each store over several years
rossmann-stores.xlsx — Store details like type, size, and promotional activity

You can download both files from the Lumen GitHub repository. For now, you’ll work with these files separately in Excel — just as analysts often do in the early stages of data familiarization. Later, you’ll learn how to efficiently combine them using.

A Quick Note on Data File Formats

Before we dive into Excel, note the file formats you’ll often encounter as an analyst. The files provided here include .xlsx, Microsoft’s standard format for Excel workbooks — ideal for organizing, analyzing, and visualizing data within Excel.

Beyond Excel files, you’ll frequently work with CSV files — short for Comma-Separated Values. These plain text files store data in a simple, portable format, making them useful for moving data between tools. In CSV files, each piece of data is separated by a delimiter — usually a comma, but sometimes a semicolon or tab.

Understanding these formats is essential, as real-world data often arrives from multiple sources, in different structures. We will cover more file structures later in the program.

2. What is Excel and Why Analysts Still Rely on It?

Microsoft Excel is one of the most widely used tools for working with data. From finance teams to research labs, millions of professionals use it daily to organize information, perform calculations, summarize trends, and build clear, accessible reports. For data analysts, Excel offers a fast, flexible environment to explore, structure, and analyze data — making it an essential starting point for developing analytical skills.

While Excel is powerful, it isn’t designed for every task. Large, complex datasets often require more robust tools like SQL or Python. Excel, for example, has a practical row limit of just over one million records — far less than what modern databases or programming tools can handle. But in the real world, many business questions rely on datasets well within Excel’s capabilities. In these situations, its accessibility, versatility, and familiarity make it indispensable.

3. Getting Started: Downloading and Installing Excel

Before you begin working with the Rossmann dataset, you’ll need Microsoft Excel installed on your device. Excel is part of the Microsoft 365 suite, used by millions of analysts and organizations worldwide to manage, analyze, and present data.

3.1. Required Version

This course requires the desktop edition of Microsoft Excel 2016 or later. While Excel Online and mobile apps provide basic functionality suitable for beginners, the full desktop version offers a seamless and comprehensive experience, including advanced features and AI tools.

3.2. Downloading Excel

Access the latest Excel version with continuous updates and AI capabilities such as Analyze Data and Copilot (availability varies by region) at Microsoft-365 Official Page.

You have a number of options:

Free Tier: Excel for the web and mobile devices is available at no cost. While adequate for foundational tasks, the desktop version is strongly recommended for optimal learning and performance. We advise you to select this option only if you cannot go with the paid plans.

One-Time Purchase: Microsoft also offers standalone Excel licenses, though these may lack recent AI enhancements and feature updates.

Institutional Access: Many universities and employers provide complimentary Microsoft 365 access. Verify eligibility before purchasing.

3.3. Installation

Download Excel via the Microsoft link associated with your purchase or subscription.
Run the installer and follow the prompts.
Launch Excel and sign in with your Microsoft account to activate.

4. Excel’s Interface

Now that you have set up Excel , we’ll proceed to review how it is organized — from its file structure to the key tabs and commands you’ll rely on as an analyst.

4.1. Workbooks and Worksheets

A workbook is your Excel file (e.g., rossmann-stores.xlsx). Within workbooks, we have worksheets where each worksheet is arranged as a table. Columns run vertically and are labeled with letters, while rows run horizontally and are numbered. Where a row and column intersect, you have a cell, which can hold numbers, text, or formulas. Each cell has a unique reference based on its column and row — for example, the highlighted cell in Figure 1 is referred to as N24, combining column N and row 24.

Data within a worksheet can be selected in several ways. To highlight an entire column, click its letter heading; to select a full row, click its number heading. For multiple adjacent rows or columns, click the first heading, then drag to the last one you want included. Similarly, you can click and drag across cells to select a specific range.

Now open rossmann-sales.xlsx. You will note store-level daily sales data organized in columns with clear headings. Consider Figure 2 for a snapshot of sales data, with the following 9 parameters:

Column	Description
Store	Unique identifier for each Rossmann location
DayOfWeek	Day of the week (1 = Monday, 7 = Sunday)
Date	Calendar date for the record
Sales	Total store revenue for the day (€)
Customers	Number of visitors
Open	Store open status (1 = Open, 0 = Closed)
Promo	Promotion active status (1 = Yes, 0 = No)
StateHoliday	Public holiday indicator
SchoolHoliday	School holiday indicator

Before starting calculations, take a moment to scroll through the data. There are 91257 rows. With the first row being “Header” row, there are 91256 datapoints. Observe the structure, note any variables that stand out, and consider how this information connects to the business questions you’re here to answer.

4.2. Renaming Sheets for Clarity

One of the first things you should do is to rename your worksheets for clarity. For example, double click on Sheet1 at the bottom of the sheet, and change it to raw_data. This makes it immediately clear to anyone opening the file where the original data lives — and reminds you not to modify it directly.

4.3. Navigating the Ribbon

At the top of every Excel window, note the Ribbon — a streamlined set of tabs that gives you quick access to Excel’s most useful tools. As an analyst, you’ll spend a lot of time here. Key tabs relevant to analysts include:

Home: Your starting point for everyday tasks — formatting, inserting or deleting rows, and quick sorting or filtering.
Insert: Where you’ll find charts, visual elements, and PivotTables — essential tools for turning raw data into clear insights.
Draw: Offers pen and ink tools for freehand annotations or quick sketches within your worksheet. It’s rarely essential for analysts, but can be useful for highlighting or marking up reports in collaborative settings.
Data: The hub for managing and preparing your data — sorting, filtering, and (in later lessons) tools like Power Query.
Formulas: A dedicated space for Excel’s full library of built-in functions. Here, you’ll find everything from simple sums to more complex date, text, and lookup functions. In this course, you’ll apply formulas that help transform raw data into meaningful business metrics.

Other tabs like Page Layout, Review, and View help with formatting, printing, collaboration, and working with large datasets.

5. Core Operations: Sorting

Sorting may seem like a basic operation, but in the hands of a skilled analyst, it becomes a powerful tool for uncovering business insights. The key is knowing what to sort — and why.

5.1. Sorting Data: Ranking Stores with Business Relevance

Consider the scenario where Rossmann Leadership requires sales performance ranked by store.

At first glance, this may seem like a quick task in Excel. But a thoughtful analyst knows — you don’t just click buttons. You begin by understanding the business context, identifying the correct data points, and applying Excel tools with purpose.

In this case, the Sales column represents total daily revenue for each store — a direct measure of financial performance. Sorting this column allows you to rank stores by their contribution to overall revenue, helping leadership pinpoint high-performing locations.

5.2. Steps to Sort Data by Sales in Excel

You can follow along with the written instructions, or watch the video walkthrough.

5.2.1. Select the Full Dataset

Click anywhere inside your dataset and press Ctrl + A to select all rows and columns. This ensures your sorting applies to the entire dataset — not just one column.

5.2.2. Access the Sort Function

Navigate to the Data tab on the Ribbon and click Sort. This opens Excel’s sorting options.

5.2.3. Sort by Sales, Largest to Smallest

In the Sort dialog box:

Under Column, select Sales.
Under Sort On, keep the default Values.
Under Order, choose Largest to Smallest.
Click OK to apply the sort.

5.3. Why Sort by Sales?

Revenue is the ultimate performance indicator for stores — it’s what the CFO, and the business as a whole, care about most. Sorting by Customers, for instance, could be misleading. A store may attract high foot traffic but generate low revenue per visitor — a potential red flag rather than a success story.

5.4. Additional Sorting Scenarios

5.4.1. Alphabetical or Ascending Sorting

You’re not limited to sorting by revenue. You might need to sort:

Store names alphabetically (A to Z) to organize reports.
Dates in ascending order to observe trends over time.
Regions or store types to group similar locations together.

5.4.2. Sorting When There’s No Header Row

If your dataset lacks headers (e.g., the first row contains data, not column names), Excel may misinterpret the data when sorting.

Tip: In the Sort dialog box, uncheck the option “My data has headers” to ensure Excel treats all rows equally.

Headers add clarity — that’s why, as part of your workbook structure, you should always clearly label columns. It prevents errors and makes sorting straightforward.

5.5. Analytical Reflection: Look Beyond the Obvious

Sorting by total sales provides useful insight, but a critical analyst goes further:

Are large stores naturally generating higher revenue simply because of their size?
Should leadership also consider sales per customer or sales per square meter to get a fairer comparison?
Do promotions or store types influence the sales ranking? Should those be factored in?

Sorting is not just about reordering data — it’s about drawing meaningful conclusions that align with real business questions.

Tip for Interviews and Stakeholder Presentations

When asked how you approached a task like this, don’t just say, “I sorted the data.”

Instead, explain why you sorted by Sales, how that ties to business priorities, and what additional metrics you considered. That’s the mark of a thoughtful, business-minded analyst.

6. Core Operations: Filtering

Consider a strategically vital inquiry: “How do promotions impact sales performance across our stores?” Answering this does not require additional data, rather a refined focus on your existing dataset. Filtering empowers you to isolate relevant subsets of data and compare them meaningfully.

6.1. Filtering the Rossmann Dataset by Promotions

6.1.1. Select the Dataset

Click within your dataset and press Ctrl + A to highlight all data.

6.1.2. Enable Filtering

Navigate to the Data tab on the Ribbon and select Filter. Dropdown arrows will now appear atop each column.

6.1.3. Apply the Promotion Filter

In the Promo column dropdown, select only rows where Promo = 1. These represent days with active promotional campaigns.

6.2. The Rationale for Filtering by Promotions

Promotions constitute deliberate business interventions aimed at stimulating sales. By filtering to isolate these specific days, you obtain a focused view to evaluate their effectiveness:

Did sales volumes increase?
Was there a rise in customer visits?
Are certain store formats more responsive to promotional efforts?

Filtering sharpens your analytical lens, providing clarity and precision in answering these critical business questions.

6.3. Insight: Filtering with Purpose

Filtering isn’t about hiding information — it’s about structuring your view to align with specific business questions.
AI Tools like Excel’s Analyze Data (formerly Ideas) may suggest filters automatically, but thoughtful analysts apply human judgment to ensure filters make business sense.
Be deliberate. Always know why you’re filtering — and how it sharpens your analysis.

6.4. Remove filters and view all your data again

Sometimes your subsequent analysis will require you to continue with the filter. However, often you need to remove the filter before proceeding. To clear a filter:

6.4.1. Locate the Filter Arrows

You’ll see small dropdown arrows next to each column header where filtering was applied.

6.4.2. Clear the Filters

Option 1: Click the Filter icon on the Data tab to toggle all filters off. This restores the full dataset view and removes the dropdown arrows.
Option 2: Click the dropdown arrow on a filtered column, then select Clear Filter from [Column Name] to remove the filter from that specific column only.

Tip: Before sharing your workbook or conducting further analysis, always double-check that unnecessary filters are cleared. Sharing filtered data can lead to incomplete reports or incorrect conclusions — an easy mistake that reflects poorly in a professional setting.

Interview Tip: Show Strategic Thinking, Not Just Technical Steps

When walking through your analysis in an interview, don’t get lost in button clicks. Focus on the why behind your approach.

Example Response:
“The leadership team wanted to assess the true impact of promotions on sales. I filtered the dataset to isolate promotional days, then compared revenue and customer metrics to non-promotional days. This helped highlight whether promotions were driving meaningful business results — beyond just temporary spikes.”

This demonstrates not just technical ability, but structured, business-driven thinking — precisely what sets elite analysts apart.

7. Presenting Data Clearly: Essential Formatting in Excel

As an analyst, your work doesn’t end with correct formulas or calculations — your insights must be readable and understood. Well-formatted data is not just visually appealing; it signals attention to detail and enhances the credibility of your analysis.

Excel provides a wide range of formatting tools that help transform raw tables into clear, structured, and professional presentations. Let’s walk through the core formatting techniques, using the Rossmann dataset as an example.

Step 1: Emphasizing Column Headers

Clear column headers help your audience navigate your data quickly. It’s good practice to visually distinguish them from the rest of the dataset. To format your headers:

Select the entire header row (Row 1).
On the Home tab, click the Bold
While the row is still selected, increase the font size for added emphasis — we recommend setting it to 14pt, ensuring headers stand out but remain professional.

Step 2: Adjusting Column Widths

Data that’s cut off or hidden due to narrow columns looks unprofessional and is harder to interpret. You have a couple of options to adjust column widths:

Manual adjustment: Hover over the right edge of a column header, then click and drag to the desired width.

Auto-fit: Double-click the right edge of the column header to automatically resize it to fit the longest cell entry.

Step 3: Aligning Data in Cells

Consistent alignment makes your dataset easier to scan. To align content:

On the Home tab, use the alignment buttons to position data:
- Horizontally: Left, Center, or Right.
- Vertically: Top, Middle, or Bottom.

For most datasets, left-align text and right-align numbers to follow standard conventions.

Step 4: Adding Borders for Structure

Borders help define the boundaries of your dataset, particularly when printing or sharing with others. To add borders:

Select the data range.
Click the Borders button in the Home
Choose Outside Borders or other options based on your preference.

However, use borders carefully – too many lines can clutter your spreadsheet.

Step 5: Using Background Color for Clarity

Color can subtly guide the reader’s attention. A light background for headers improves distinction without overwhelming the data. To apply background color:

Select the header row or desired cells.
Click the Fill Color button (paint bucket icon) in the Home
Choose a light, neutral color — for example, Blue, Accent 1, Lighter 60%.

Avoid using multiple or intense colors, which can distract rather than clarify.

Step 6: Formatting Numbers

Raw numbers can be misleading or confusing without appropriate formatting. For example, a sales figure of 13500 is clearer when shown as $13,500.00.

Common number formats you’ll use:

Currency (Sales values):
1. Select the Sales column.
2. On the Home tab, click the Accounting Number Format
3. Choose € or the relevant currency for your context.

Percentages (Close rates, growth metrics):
1. Select the relevant column.
2. Click the Percent Style button from the Number group on the Home tab.

Consistent number formatting reinforces precision and prevents misinterpretation.

Clean, professional formatting is not cosmetic — it reflects your attention to detail and respect for your audience. Whether sharing your analysis with colleagues, leadership, or clients, polished presentation enhances both understanding and credibility.

8. AI in Excel

Modern versions of Excel now include AI-powered tools that can assist with basic data tasks — from identifying patterns to suggesting quick summaries. These features can speed up your work, but they do not replace the role of human judgment or structured analysis.

At this early stage, you won’t be diving deep into AI tools or advanced analytics — that comes later in the course. But it’s helpful to be aware of what’s available.

8.1. Excel’s Analyze Data Feature

If you’re using Microsoft 365, you may notice a button called Analyze Data (formerly called Ideas) on the Home tab. This tool scans your dataset and suggests trends, summaries, or visualizations.

8.1.1. Accessing Analyze Data Feature

Open your Rossmann dataset (if not open already).
Click the Analyze Data button on the Home tab.
Explore the suggestions if you’re curious.

8.1.2. A word of caution

These suggestions can be useful starting points, but they are generated by an algorithm — not by someone with Rossmann’s business context in mind.

For now, treat these insights as exploratory, not as final answers. You’ll learn structured, reliable analysis techniques in upcoming lessons.

Summary

In this lesson, you’ve learned:

How to navigate Excel’s interface efficiently, using ribbons, worksheets, and shortcuts like a professional analyst.
How to structure workbooks for clarity, collaboration, and analytical credibility.
The importance of version control, documentation, and disciplined workbook organization in building trust and avoiding costly errors.
How core Excel operations — sorting and filtering — reveal meaningful patterns when applied with business intent.

Excel’s AI features, understanding their potential — and their limits
How to think like a business analyst: focusing not just on tools, but on clarity, judgment, and structured problem-solving.

You’ve taken an important step beyond theory — practicing the tools, habits, and judgment that distinguish analysts in real business environments. In the next lesson, you’ll build on this foundation by learning how to clean and prepare real-world data for analysis.

Exercise

Estimated Time to Complete: ~1 hour

Walmart operates 45 stores across different regions, each varying in size, type, and departmental structure. You’ve been provided with anonymized sales and store data to begin an initial performance assessment.

In the long run, Walmart leadership wants to understand two things:

Which stores are generating the highest revenue — and whether that relates to store size or type.
How sales behave during major holiday weeks — particularly whether promotional activity (markdowns) drives meaningful performance changes.

As an analyst, your immediate task is to prepare structured, clear insights from the provided dataset — using only the tools introduced so far: workbook organization, sorting, filtering, and thoughtful presentation.

The Data

You’ll find three files under Walmart dataset in your Lumen GitHub repository:

walmart-stores.xlsx — Store details (type, size)
walmart-sales.xlsx — Historical department-level weekly sales
walmart-features.xlsx — Additional factors like temperature, fuel prices, markdowns, holidays

For this exercise, focus only on walmart-stores.xlsx and walmart-sales.xlsx. You’ll integrate the additional data in later lessons.

Task 1: Identify Peak Sales Periods

Determine which weeks show the highest sales volumes. Analyze which stores and departments consistently contribute to these peaks. What patterns emerge in relation to holidays and store types?
Prepare your findings in a clear, logically structured worksheet – Record your thoughts in a document.

Task 2: Holiday Impact Assessment

Identify the weeks corresponding to the following holidays:
- Super Bowl: 12-Feb-22, 11-Feb-23, 10-Feb-24
- Labor Day: 10-Sep-22, 9-Sep-23, 7-Sep-24
- Thanksgiving: 26-Nov-22, 25-Nov-23, 23-Nov-24
- Christmas: 31-Dec-22, 30-Dec-23, 28-Dec-24
Filter your dataset to compare weekly sales during holiday weeks vs. non-holiday weeks (Hint: Do we need two separate sheets?).
Do holidays consistently lead to higher sales? Is the effect uniform across all stores or types?
Reflection: How might AI tools assist or mislead in interpreting holiday sales data? Briefly reflect.

Task 3: Professional Relevance — Excel in the Job Market

Search for two real job listings (on LinkedIn and another job portal) where Excel is explicitly mentioned as a requirement for data analysts or similar roles.
For each:
- Note the job title, company, and location.
- Summarize how Excel is expected to be used in that role.

This exercise connects your technical work to your career development — demonstrating that even at this foundational stage, your skills are market-relevant.

Submission Guidelines

Submit a single, well-organized Excel workbook containing:

Your revenue ranking analysis (Task 1) clearly structured and formatted.
Your holiday sales comparison (Task 2), with appropriate filtering.
Any supporting calculations relevant to your analyses.

Additionally, include a concise written summary (either as a separate document or a worksheet within your Excel file) that:

Synthesizes your key business observations from Tasks 1 and 2.
Presents the two job listings from Task 3, including:
- Job title, company, and location.
- A brief summary of the Excel requirements and how they relate to your skills.

When you’re ready, submit your completed exercise to the designated folder in OneDrive. Drop your mentor a note about submission.

Important: Please scan your files for viruses before uploading.

Submission & Resubmission Guidelines

1. When submitting your exercise, use this naming format:

YourName_Submission_Lesson2

If you need to revise and resubmit, add a version suffix:

YourName_Submission_Lesson2_v2

YourName_Submission_Lesson2_v3

2. Do not overwrite or change the original evaluation entries in the rubric.

Instead, enter your updated responses or corrections in a new “v2” (or later) column in the rubric for mentor review.

Evaluation Rubric

Criterion	Exceeds Expectation	Meets Expectation	Needs Improvement	Incomplete / Off-Track
Workbook Setup & Presentation (All Tasks)	Workbook impeccably structured; sheets clearly labeled (e.g., “Revenue Analysis”, “Holiday Impact”). Professional formatting consistently applied (bold headers, currency formats). Precise version control in file name. Insights documented within workbook or summary sheet.	Clear, logical workbook structure; sheets appropriately named. Formatting neat and consistent. Presentation meets professional business standards.	Workbook structure inconsistent or unclear. Formatting incomplete or inconsistent.	Workbook disorganized or default sheet names used. Formatting missing or unprofessional. File naming generic or inappropriate. Difficult to navigate. Plagiarized or clearly thoughtless AI generated content.
Task 1: Revenue Ranking & Business Context	Revenue ranking flawless along with aggregated store data; insightful, nuanced comparison to store size/type. Analysis goes beyond surface-level data, demonstrating deep business understanding.	Accurate revenue ranking. Clear and relevant comparison to store size/type. No significant errors.	Revenue ranking partially incorrect or incomplete. Business comparison weak or vague. Lacks depth.	Revenue ranking missing or incorrect. No meaningful insight or business context provided.
Task 2: Holiday Sales Analysis	Filtering executed flawlessly. Provides sophisticated, well-reasoned comparison of holiday vs. non-holiday sales. Demonstrates strong business insight, noting store/type variations.	Correct filtering applied. Clear, accurate comparison made with relevant business observations.	Filtering incomplete or partially incorrect. Comparison vague or lacking in insight. Limited business relevance.	Filtering absent or incorrect. No meaningful comparison or business insight.
Task 3: Professional Branding	2+ relevant job listings identified with precise Excel expectations. Demonstrates thoughtful connection between course skills and career opportunities.	Two job listings provided with clear, accurate summaries of Excel usage.	Less than 2 listings provided or Excel relevance unclear/incomplete.	No relevant listings or task skipped. Listings unrelated to data analysis roles.

Student Submissions

Check out recently submitted work by other students to get an idea of what’s required for this Exercise: