Excel Tutorial for Beginners India 2026
Learn Excel for Data Analyst Jobs — Step by Step

Why Excel Still Matters for Data Analysts in India

Essential Functions — IF, IFERROR, and Nested Logic

=IF(A2>50000, "High Value", "Standard")
-- If amount in A2 is above 50,000 → "High Value", otherwise "Standard"

=IF(C2="Delhi", IF(D2>100000, "Priority Delhi", "Delhi"), "Other")
-- Nested IF: two conditions. Avoid more than 3 levels of nesting.

=IFS(D2>=100000, "Platinum", D2>=50000, "Gold", D2>=10000, "Silver", TRUE, "Bronze")
-- IFS: cleaner multi-condition logic — use instead of nested IF

=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Product not found")
-- Wrap VLOOKUP in IFERROR to handle missing matches gracefully

Lookup Functions — VLOOKUP, XLOOKUP, INDEX MATCH

-- VLOOKUP: bring product name from a Products sheet using Product ID
=VLOOKUP(A2, Products!$A:$C, 2, FALSE)
-- A2 = lookup value | Products!$A:$C = table | 2 = column index | FALSE = exact

-- XLOOKUP: more flexible — lookup and return ranges are separate
=XLOOKUP(A2, Products!$A:$A, Products!$B:$B, "Not found")
-- A2 = lookup value | Products!$A:$A = where to search | Products!$B:$B = what to return

-- INDEX MATCH: works in older Excel, does not break on column inserts
=INDEX(Products!$B:$B, MATCH(A2, Products!$A:$A, 0))
-- MATCH finds row position of A2 | INDEX returns value from that row

-- Two-criteria XLOOKUP (match on Product ID AND Colour)
=XLOOKUP(1, (Products!$A:$A=A2)*(Products!$C:$C=B2), Products!$D:$D, "Not found")

Conditional Aggregation — SUMIFS, COUNTIFS, AVERAGEIFS

-- Total sales in Delhi in March 2025
=SUMIFS(Sales[Amount], Sales[City], "Delhi", Sales[Month], "March 2025")

-- Count orders above ₹50,000 from the North region
=COUNTIFS(Sales[Amount], ">50000", Sales[Region], "North")

-- Average order value in Noida (case-insensitive, wildcard)
=AVERAGEIFS(Sales[Amount], Sales[City], "Noida")

-- SUMIFS with wildcard: sum all cities containing "Delhi"
=SUMIFS(Sales[Amount], Sales[City], "*Delhi*")

-- SUMIFS with date range: sales in Q1 2025
=SUMIFS(Sales[Amount],
  Sales[Date], ">="&DATE(2025,1,1),
  Sales[Date], "<="&DATE(2025,3,31))

-- Dynamic: current month sales (no hard-coded month)
=SUMPRODUCT((MONTH(Sales[Date])=MONTH(TODAY()))*(YEAR(Sales[Date])=YEAR(TODAY()))*Sales[Amount])

Pivot Tables — The Fastest Way to Summarise Data

Keyboard shortcut to create pivot table: Alt + N + V + T (Windows)

Key pivot table operations:
• Group dates by month: right-click any date cell → Group → Months + Years
• Show % of total: right-click value → Show Values As → % of Grand Total
• Calculated field: PivotTable Analyze → Fields, Items & Sets → Calculated Field
  Example: Margin % = Revenue / Cost
• Refresh after source data changes: right-click → Refresh (or Alt + F5)
• Change data source (if rows added beyond original range):
  PivotTable Analyze → Change Data Source

Data Cleaning in Excel — Handling Real Messy Data

-- Remove extra spaces (leading, trailing, multiple internal spaces)
=TRIM(A2)

-- Standardise case: Title Case for names, UPPER for codes
=PROPER(A2)    -- "priya sharma" → "Priya Sharma"
=UPPER(A2)     -- "del001" → "DEL001"

-- Remove ₹ and commas from amount columns stored as text
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"₹",""),",",""))
-- "₹1,50,000" → 150000

-- Check if a cell contains text (to flag non-numeric amounts)
=ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(A2,"₹",""),",","")))

-- Flag duplicates (TRUE = duplicate of an earlier row)
=COUNTIF($A$2:A2, A2) > 1

-- Split city and state from "Noida, UP" format
=LEFT(A2, FIND(",", A2) - 1)    -- "Noida"
=TRIM(MID(A2, FIND(",", A2)+1, LEN(A2)))  -- "UP"

-- Convert text date "31-08-2026" to a real Excel date
=DATEVALUE(A2)    -- then format the cell as Date

Power Query — Automate Your Data Cleaning

Common Power Query transformations (applied via the visual editor):

1. Remove blank rows:
   Home → Remove Rows → Remove Blank Rows

2. Promote headers (first row becomes column names):
   Home → Use First Row as Headers

3. Change data type:
   Right-click column header → Change Type → (Date, Number, Text...)

4. Split column by delimiter:
   Right-click column → Split Column → By Delimiter → choose comma or space

5. Unpivot month columns into rows:
   Select ID/Name columns → Right-click → Unpivot Other Columns
   Result: Attribute column (month name) + Value column (the data)

6. Combine all Excel files from a folder:
   Data → Get Data → From File → From Folder → select folder → Combine & Transform
   New files added to the folder appear automatically on next Refresh

7. Merge queries (equivalent of VLOOKUP / SQL JOIN):
   Home → Merge Queries → select the lookup table and matching columns → OK

Dynamic Arrays — FILTER, UNIQUE, SORT (Microsoft 365)

-- FILTER: extract all orders from Delhi above ₹50,000
=FILTER(A2:E100, (B2:B100="Delhi")*(D2:D100>50000), "No results")
-- Spills matching rows into as many cells as needed

-- UNIQUE: distinct list of cities from a column with duplicates
=UNIQUE(B2:B100)

-- SORT: alphabetically sorted list of product names
=SORT(UNIQUE(C2:C100))

-- SORTBY: sort orders by amount descending
=SORTBY(A2:E100, D2:D100, -1)  -- -1 = descending

-- Dynamic top 5 products by sales (updates as data changes)
=TAKE(SORTBY(C2:C100, D2:D100, -1), 5)

-- XLOOKUP returning multiple columns (spills right)
=XLOOKUP(A2, Products!A:A, Products!B:D)
-- Returns 3 columns at once: name, category, price

Is Excel still important for data analysts in India in 2026?

Yes — Excel remains the most widely used data tool across Indian companies in 2026. Most data analyst interviews include an Excel practical test. Finance teams, HR departments, operations, and mid-size companies run on Excel. Power BI and Python are built on top of Excel skills, not instead of them. Advanced Excel (Power Query, XLOOKUP, dynamic arrays) is tested at mid-level roles and is the fastest way for a beginner to demonstrate practical data skill.

What Excel skills are needed for a data analyst job in India?

Indian data analyst interviews test: (1) VLOOKUP and XLOOKUP for data joining; (2) SUMIFS and COUNTIFS for conditional aggregation; (3) Pivot tables for summarising large datasets; (4) Basic data cleaning — removing duplicates, TRIM, Text to Columns; (5) Power Query for automating repetitive data imports and transformations. Dynamic array functions (FILTER, UNIQUE, SORT) are increasingly tested at companies using Microsoft 365.

How long does it take to learn Excel for data analysis?

With daily practice of 1 hour, most beginners can learn the Excel skills required for a data analyst interview in 4–6 weeks. Basic functions (SUM, IF, VLOOKUP, pivot tables) take 2 weeks. Intermediate skills (XLOOKUP, SUMIFS, Power Query basics) take another 2–3 weeks. Power Query automation and dynamic arrays take a further 1–2 weeks. Excel is learned fastest by working on real datasets — not following along with sample files — because realistic data is always messier than tutorial examples.

Should I learn Excel before SQL and Power BI?

Yes — Excel is the right starting point for most beginners in India. It has no installation friction, is visually intuitive, and teaches the core concepts of data analysis (filtering, aggregating, joining tables) that SQL and Power BI formalise. Most live data analytics courses in India start with Excel for the first 3–4 weeks before introducing SQL. Learning Excel first also makes Power Query — which bridges Excel and Power BI — significantly easier to grasp.