## Advanced Excel Skills That Get You Hired in India 2026
Microsoft Excel remains one of the most valuable tools in the Indian job market across industries — accounting, data analytics, operations, banking, insurance, e-commerce, manufacturing. In job listings across Naukri.com and LinkedIn, "Advanced Excel" appears in requirements for data entry roles, data analyst positions, business analyst jobs, accounting roles, and operations positions.
This guide covers what "Advanced Excel" actually means in the Indian job market and which functions you need to learn.
---
The Skill Gap: What Companies Mean by "Advanced Excel"
When a company in Punjab says "Advanced Excel required," they typically mean:
Basic Excel (not enough): - Opening files, entering data, basic formatting - Simple SUM, AVERAGE functions - Basic charts
Intermediate Excel (minimum for most jobs): - VLOOKUP / XLOOKUP (the most important lookup function) - Pivot Tables (the most important summarization tool) - IF, COUNTIF, SUMIF functions - Basic charts and data formatting
Advanced Excel (required for data analyst/senior positions): - Nested formulas - Power Query (automated data import and transformation) - Power Pivot (data modeling beyond standard pivot tables) - Macros / VBA (automating repetitive tasks) - Advanced dashboards - Index-Match (more powerful than VLOOKUP) - Array formulas
---
The 10 Excel Skills That Matter Most in India
1. VLOOKUP and XLOOKUP
The most frequently tested Excel skill in Indian interviews.
VLOOKUP: searches the leftmost column of a table for a value and returns a value in the same row from another column.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: "Find the salary for employee ID 1042 in the employee database." =VLOOKUP(1042, A2:D100, 3, FALSE) This looks up 1042 in column A, and returns the value from the 3rd column (salary), using exact match (FALSE).
XLOOKUP (Excel 365, Excel 2019+): More flexible successor to VLOOKUP. =XLOOKUP(lookup_value, lookup_array, return_array) Can search in any direction, returns multiple columns, better error handling.
Interview questions: "What's the difference between VLOOKUP and INDEX-MATCH?" VLOOKUP can only look in the leftmost column. INDEX-MATCH can look in any column and return from any column — more flexible.
---
2. Pivot Tables
The most powerful data summarization tool in Excel.
A Pivot Table lets you summarize a large dataset in seconds — total sales by region, average salary by department, count of customers by city.
To create: Select your data → Insert → PivotTable → Drag fields to Rows, Columns, Values, Filters.
Example: Dataset of 10,000 sales transactions. Pivot Table to summarize: total revenue by city, by month, by product — in 30 seconds.
Pivot Table skills employers want: - Grouping dates (by week, month, quarter) - Calculated fields (creating new metrics from existing data) - Slicers (interactive filters for dashboards) - Pivot Charts (charts linked to pivot table)
---
3. Conditional Formatting
Automatically format cells based on their value — highlight cells above/below threshold, color-code performance, show data bars.
Interview question: "How would you visually identify all sales reps who are below their monthly target?" Answer: Conditional Formatting → Highlight Cells Rules → Less Than [target value] → choose red fill.
---
4. IF, NESTED IF, IFS, SUMIF, COUNTIF
IF: =IF(logical_test, value_if_true, value_if_false) Example: =IF(A2>80, "Pass", "Fail")
Nested IF: IF inside another IF for multiple conditions. Example: =IF(A2>=90, "A Grade", IF(A2>=75, "B Grade", IF(A2>=60, "C Grade", "Fail")))
IFS (Excel 365): Cleaner alternative to nested IF. =IFS(A2>=90, "A Grade", A2>=75, "B Grade", A2>=60, "C Grade", TRUE, "Fail")
SUMIF: Sum values that meet a condition. =SUMIF(range, criteria, sum_range) Example: =SUMIF(B:B, "Amritsar", C:C) — total sales in Amritsar
COUNTIF: Count cells that meet a condition. =COUNTIF(A:A, "Digital Marketing") — count how many times "Digital Marketing" appears
---
5. INDEX-MATCH
More powerful and flexible than VLOOKUP. Frequently tested in analyst interviews.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example: Find salary for employee named "Arjun Singh" (even though name is in column C, not column A like VLOOKUP requires). =INDEX(D:D, MATCH("Arjun Singh", C:C, 0))
Advantage over VLOOKUP: can look up in any column, not just leftmost.
---
6. Power Query
Power Query is the biggest game-changer in modern Excel. It automates data import and transformation.
What it does: - Import data from multiple sources (Excel files, CSV, databases, web) - Clean messy data (remove duplicates, split columns, standardize formatting) - Combine multiple files automatically - Refresh imported and cleaned data with one click
Why it matters: In data jobs, 60–80% of time is data cleaning. Power Query automates repetitive cleaning steps so you run them again with one click on new data.
Power Query tasks employers want: - Connecting to a folder of files (automatically combines all CSV files in a folder) - Unpivoting data (converting wide format to long format) - Splitting columns, trimming whitespace, standardizing dates - Merging queries (equivalent to JOIN in SQL)
---
7. Charts and Data Visualization
Basic charts: bar, column, line, pie (know when each is appropriate). Advanced: combo charts, waterfall charts, sparklines. Dashboard charts: use slicers to make charts interactive.
What interviewers evaluate: Can you choose the right chart type for the data? A pie chart with 12 categories is wrong. A stacked bar is right for part-to-whole over time. A line chart shows trends.
---
8. SUMPRODUCT
Extremely versatile function — can count, sum, or average based on multiple conditions.
=SUMPRODUCT((condition1)*(condition2)*(values))
Example: Total sales in Amritsar in March: =SUMPRODUCT((City="Amritsar")*(Month="March")*(Sales))
---
9. Macros and VBA Basics
Macros record repetitive actions and play them back. VBA (Visual Basic for Applications) lets you write custom automation.
For most data roles: Understanding what macros do and being able to run existing macros is usually sufficient. Actual VBA coding is a bonus.
For MIS (Management Information Systems) roles and operations roles: Macro recording + basic VBA is often required.
---
10. Named Ranges and Data Validation
Named Ranges: give a range a name (Sales_Data) so formulas are readable. =VLOOKUP(A2, EmployeeDB, 3, FALSE) is more readable than =VLOOKUP(A2, Sheet2!A:D, 3, FALSE).
Data Validation: restrict what users can enter in a cell (dropdown list, date range, number range). Essential for building data entry forms that don't get corrupted.
---
Excel Skills by Role in India
Data Entry Operator: Basic formatting, VLOOKUP, SUMIF, COUNTIF, basic charts. Excel test typically 15–20 minutes.
Accounts Executive: VLOOKUP, Pivot Tables, IF functions, basic Macros for report generation. Tally integration often required too.
MIS Executive: Advanced Pivot Tables, Power Query, Macros, automated dashboards. This is a specialized role — Excel is the primary tool.
Data Analyst (entry level): Pivot Tables, Power Query, INDEX-MATCH, advanced charts, basic VBA, understanding of statistical functions. Power BI and SQL are also required.
Business Analyst: Full Advanced Excel + Power Query + Power Pivot + ability to build reporting dashboards from scratch.
---