Excel for Data Analysis: A Beginner's Guide for Microsoft 365

Imagine you work at a small FMCG distributor in Indore. Every evening the billing clerk exports the day's invoices: which kirana store bought what, how many cartons, at what rate. By Saturday the owner wants three answers: which area sold the most, which retailers have stopped ordering, and whether sunflower oil is moving faster than last month.

Nobody there is a programmer. They have a laptop, a CSV export and Excel, and with the right habits that is enough.

The export is your raw data. Turning it into a clean list is data cleaning. Adding an Amount column is calculation. "Sales by area" is a summary, and the bar chart you send on WhatsApp is the presentation. That full chain is data analysis, done in a spreadsheet.

Excel for data analysis is the practice of using Microsoft Excel's tables, formulas, pivot tables and charts to turn raw business records into answers and reports.

Throughout this series you will work with one made-up business, Narmada Traders, a distributor that supplies kirana stores in Vijay Nagar, Palasia, Rajwada, Bhawarkuan and Sudama Nagar. Every article reuses its sales sheet, so the examples grow into one project.

What is Excel used for in data analysis?

Microsoft Excel is a spreadsheet application, part of Microsoft 365, that stores data in a grid of cells and recalculates formulas the moment a value changes. Almost every business already has it, which is why analysts use it daily.

Typical analyst tasks in Excel include:

• Reconciling two lists, such as invoices against payments received.
• Summarising sales by month, area, product or salesperson.
• Flagging records that break a rule, such as overdue payments or negative stock.
• Building small dashboards and MIS reports that managers open every week.

A data analyst usually pulls data with SQL, explores it in Excel or Python, and publishes it in Power BI. Excel is the one you touch every day, so start there.

The Excel data analysis workflow, step by step

Every analysis in this series follows the same five steps. Learn them once and every new task feels familiar.

1. Import. Paste or open the CSV, or load it with Power Query, then press Ctrl+T to turn the range into an Excel Table.
2. Clean. Remove duplicates, trim spaces, fix numbers stored as text and standardise names.
3. Calculate. Add columns such as Amount, Month or Discount with formulas.
4. Summarise. Build a pivot table or a SUMIFS grid to answer the question.
5. Present and automate. Chart the summary, then record a macro if you repeat the job every week.

Each step of the workflow has its own tutorial in this series. The arrow back from automate to import is the weekly repeat that macros remove.

How should you lay out data in Excel?

Most Excel trouble comes from layout, not formulas. Merged headings, blank spacer rows and subtotals in the middle look neat but confuse formulas and pivots.

Use a tidy layout instead: one header row, one record per row, one kind of value per column, and no blank rows or merged cells. Here is the Narmada Traders sales sheet in that shape, saved as a Table named Sales. Rate and Amount are in rupees (₹).

Why Excel Tables beat plain ranges

An Excel Table (Insert > Table, or Ctrl+T) is a named, structured range that grows automatically when you add rows. Formulas typed in one row fill the whole column, filters appear on every header, and pivot tables built on a Table pick up new rows when you refresh.

Tables also give you structured references. Instead of =G2*H2, the Amount column reads like plain English:

=[@Qty]*[@Rate]

And a total over the whole column stays correct however many rows you add:

=SUM(Sales[Amount])
→ 30300

Cell references: relative, absolute and mixed

When you copy a formula, Excel shifts its references. =F2*G2 copied one row down becomes =F3*G3. That is a relative reference, and it is what you want for row-by-row calculations.

Sometimes one cell must stay fixed, such as a discount rate in cell K1. Add dollar signs to lock it: $K$1 is an absolute reference. Press F4 while the cursor is on a reference to cycle through K1, $K$1, K$1 and $K1.

Copying down shifts relative references by one row. The dollar signs keep the discount cell pinned.

=H2*(1-$K$1)        K1 holds 5%
→ 4788 for invoice NT-1001

Mixed references (K$1 or $K1) lock only the row or only the column. You will use them when you build a two-way grid of SUMIFS in the formulas and functions tutorial.

Which Excel version should you learn?

Learn on Microsoft 365. Its dynamic array formulas return many results from one formula, which "spill" into neighbouring cells. Older perpetual licences still run in many Indian offices, so it helps to know what they lack.

If a function shows #NAME? on a colleague's laptop, the usual cause is an older version that does not know it. Microsoft's XLOOKUP help page states the versions each function needs.

Excel vs Python, SQL and Power BI

A worksheet holds at most 1,048,576 rows, according to Microsoft's published Excel specifications. Long before that limit, a heavy workbook gets slow, which is the usual signal to move the job to Power Query or Python.

To see the overlap, here is the same "filter Palasia, sort by amount" job in pandas. You can run it right here.

import pandas as pd

sales = pd.DataFrame({
    "Invoice": ["NT-1001", "NT-1002", "NT-1003", "NT-1004", "NT-1005", "NT-1006"],
    "Retailer": ["Sharma Kirana", "Jain General Store", "Patel Provision",
                 "Sharma Kirana", "Gupta Mart", "Jain General Store"],
    "Area": ["Vijay Nagar", "Palasia", "Rajwada", "Vijay Nagar", "Bhawarkuan", "Palasia"],
    "Qty": [12, 30, 48, 10, 20, 15],
    "Rate": [420, 160, 145, 260, 230, 420],
})
sales["Amount"] = sales["Qty"] * sales["Rate"]          # like =[@Qty]*[@Rate]

palasia = sales[sales["Area"] == "Palasia"].sort_values("Amount", ascending=False)
print(palasia[["Invoice", "Retailer", "Amount"]].to_string(index=False))
print("Total sales:", sales["Amount"].sum())            # like =SUM(Sales[Amount])

In Excel 365 the same filter is one formula: =SORT(FILTER(Sales, Sales[Area]="Palasia"), 8, -1). The pandas group-by and pivot tables tutorial goes further when you are ready.

The Excel and Macros series: what you will learn

The series has 13 tutorials in two parts, best read in order.

Part 1: Excel for data analysis

1. Excel for data analysis (this guide): layout, Tables, references and versions.
2. Excel formulas and functions: SUM, AVERAGE, COUNTIFS, SUMIFS and error values.
3. IF, IFS, AND and OR: logical tests, discount slabs and flags.
4. XLOOKUP vs VLOOKUP vs INDEX MATCH: pulling values from another table.
5. Text and date functions: splitting codes, fixing dates, financial years.
6. Data cleaning in Excel: duplicates, TRIM, Flash Fill and validation.
7. Pivot tables: summaries, grouping, slicers and refresh.
8. Charts and a sales dashboard: turning summaries into a one-page report.
9. Power Query: importing, cleaning and combining files without formulas.

Part 2: Excel macros and VBA

10. Excel macros: recording, running and saving .xlsm files safely.
11. VBA basics: variables, loops and the Range object.
12. Automating reports with VBA: practical macros for weekly jobs.
13. Excel interview questions: what analyst interviews actually ask.

If you would rather learn with a mentor, Debugshala teaches Excel as part of the Data Analytics course at Debugshala, Old Palasia, and you can attend a free demo class first.

Common mistakes beginners make

• Typing totals by hand. A hard-coded 30300 never updates. Always use a formula.
• Merging cells inside the data. Merged cells break sorting, filtering and pivots. Use Center Across Selection for headings instead.
• Mixing units in one column. "12", "12 pcs" and "1 dozen" in the same column cannot be summed.
• Analysing the only copy. Keep the raw export untouched on one sheet and work on a copy.

Key takeaways

• Excel data analysis follows five steps: import, clean, calculate, summarise, present and automate.
• Keep data tidy: one header row, one record per row, no merged cells or blank rows.
• Convert data to an Excel Table with Ctrl+T so formulas and pivots grow with it.
• Use F4 to switch between relative and absolute references.
• Learn on Microsoft 365, and expect #NAME? for new functions in Excel 2016 and 2019.