This chapter covers 7 skill areas — from lookup functions and pivot tables beginners learn first, to Power Query and dashboard design that separate strong candidates in interviews. Each section includes the exact formula or steps, a real Indian business example, and a common mistake to avoid.
VLOOKUP & XLOOKUP — Matching Data Across Sheets
BeginnerLookup functions are the most tested Excel skill in Indian data analyst interviews. They match data from one table to another — for example, adding product names to an order list that only has product IDs.
VLOOKUP — the classic lookup
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])EXAMPLE
Match product names to an orders sheet using Product ID:
=VLOOKUP(A2, Products!$A:$C, 2, FALSE)
A2 = Product ID in orders sheet
Products!$A:$C = lookup range in Products sheet
2 = return column 2 (product name)
FALSE = exact match (always use FALSE for data analysis)
ANALYST NOTE: Always use FALSE (exact match) in data analysis. TRUE (approximate match) is only for range lookups like tax brackets — it requires a sorted column and is rarely what you want.
XLOOKUP — the modern replacement (Excel 365)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])EXAMPLE
Same task as above but cleaner:
=XLOOKUP(A2, Products!$A:$A, Products!$B:$B, "Product not found")
A2 = Product ID to find
Products!$A:$A = where to look
Products!$B:$B = what to return
"Product not found" = show this if no match
ANALYST NOTE: XLOOKUP advantages: can look left (VLOOKUP cannot), handles not-found natively without IFERROR, uses column references not numbers so it does not break when you insert columns.
INDEX-MATCH — works in all Excel versions, highly flexible
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))EXAMPLE
Return city name given a customer ID:
=INDEX(Customers!$C:$C, MATCH(B2, Customers!$A:$A, 0))
Customers!$C:$C = city column (what to return)
B2 = customer ID to find
Customers!$A:$A = customer ID column (where to look)
0 = exact match
ANALYST NOTE: INDEX-MATCH is preferred by experienced analysts because: (1) works in any Excel version, (2) can look in any direction, (3) faster than VLOOKUP on large files. Learn it alongside VLOOKUP.
SUMIF, COUNTIF & AVERAGEIF — Conditional Aggregations
BeginnerThese functions sum, count, or average only the rows that meet a condition — the spreadsheet equivalent of SQL's WHERE clause combined with SUM/COUNT/AVG.
SUMIF — sum rows matching one condition
=SUMIF(range, criteria, sum_range)EXAMPLE
Total revenue from Noida orders:
=SUMIF(C:C, "Noida", F:F)
C:C = city column (where to check)
"Noida" = condition
F:F = amount column (what to sum)
Total electronics sales:
=SUMIF(D:D, "Electronics", F:F)
ANALYST NOTE: SUMIFS (plural) handles multiple conditions: =SUMIFS(F:F, C:C, "Noida", D:D, "Electronics") sums amount only where city=Noida AND category=Electronics.
COUNTIF — count rows matching a condition
=COUNTIF(range, criteria)EXAMPLE
Count returned orders:
=COUNTIF(G:G, "returned")
Count orders above ₹2,000:
=COUNTIF(F:F, ">2000")
Count unique cities (with SUMPRODUCT trick):
=SUMPRODUCT(1/COUNTIF(C2:C1000, C2:C1000))
ANALYST NOTE: COUNTIFS (plural) for multiple conditions: =COUNTIFS(C:C, "Delhi", G:G, "delivered") counts delivered orders from Delhi.
AVERAGEIF — average for rows matching a condition
=AVERAGEIF(range, criteria, average_range)EXAMPLE
Average order value by city:
=AVERAGEIF(C:C, "Mumbai", F:F)
Average order value for orders above ₹500:
=AVERAGEIF(F:F, ">500", F:F)
ANALYST NOTE: Combine with IF and IFERROR to handle empty results: =IFERROR(AVERAGEIF(C:C, "Noida", F:F), 0) returns 0 if no Noida orders exist instead of a #DIV/0! error.
Pivot Tables — Summarise Any Dataset Instantly
Beginner–IntermediatePivot tables are the most powerful analytical tool in Excel for a data analyst. They let you summarise, group, and compare data across thousands of rows in seconds — without writing a single formula.
Creating a pivot table — step by step
Insert → PivotTable → Select data range → Choose placementEXAMPLE
To summarise monthly sales by product category from an orders table:
1. Click anywhere in your data → Insert → PivotTable
2. Drag "Category" to Rows
3. Drag "Order Date" to Columns → Group by Month
4. Drag "Amount (₹)" to Values → Summarise as Sum
5. Add "City" to Filters to filter by city
Result: A cross-tab showing revenue by category × month, filterable by city.
ANALYST NOTE: Always convert your data to an Excel Table (Ctrl+T) before creating a pivot table. Tables auto-expand when new rows are added, so your pivot table updates when you refresh it.
Calculated fields — add metrics inside pivot tables
PivotTable Analyze → Fields, Items & Sets → Calculated FieldEXAMPLE
Add a "Return Rate" calculated field:
= returned_amount / total_amount
Add a "Contribution %" field:
= revenue / total_revenue
Add a "AOV" (Average Order Value) field:
= revenue / order_count
ANALYST NOTE: Calculated fields in pivot tables operate on the already-aggregated values — not the raw rows. This means a calculated field "revenue / orders" gives you total_revenue / total_orders for each group, not a row-by-row average. Understand this before presenting numbers.
Slicers — interactive filters for dashboards
PivotTable Analyze → Insert Slicer → Select field(s)EXAMPLE
Add slicers for City, Category, and Month to let anyone filter the report without touching the pivot table settings.
Connect one slicer to multiple pivot tables:
Right-click slicer → Report Connections → select all pivot tables that should respond to this filter.
ANALYST NOTE: Slicers work on pivot tables and Excel tables (as of Excel 2013+). Connecting one slicer to multiple pivots is the key technique for building interactive dashboards in Excel.
Data Cleaning in Excel
IntermediateBefore any analysis, data must be cleaned. In Excel, this means removing duplicates, fixing formatting, standardising text, handling blanks, and separating combined fields.
Remove duplicates
Data → Remove Duplicates → select columns to checkEXAMPLE
Remove duplicate order IDs:
1. Select the data range
2. Data → Remove Duplicates
3. Check only "Order ID" (not all columns)
4. Excel shows how many duplicates were removed
Formula to identify duplicates before removing:
=COUNTIF($A:$A, A2) > 1
→ TRUE means this Order ID appears more than once
ANALYST NOTE: Always work on a copy of your data before removing duplicates — you cannot undo after saving. Check WHICH column defines uniqueness — sometimes two rows with the same order ID but different timestamps are both valid.
TRIM, PROPER, UPPER, LOWER — fix text formatting
=TRIM(text) =PROPER(text) =UPPER(text) =LOWER(text)EXAMPLE
Fix city names that have extra spaces:
=TRIM(A2) → " Noida " becomes "Noida"
Standardise city names to title case:
=PROPER(A2) → "NOIDA" or "noida" becomes "Noida"
Combined:
=PROPER(TRIM(A2)) → fixes spaces AND capitalisation
ANALYST NOTE: TRIM removes leading, trailing, and double internal spaces. PROPER capitalises the first letter of each word. Use UPPER for product codes and IDs that should always be uppercase.
TEXT to COLUMNS — split combined fields
Data → Text to Columns → Delimited or Fixed WidthEXAMPLE
Split "Aarav Sharma" into First Name and Last Name:
1. Select the name column
2. Data → Text to Columns → Delimited → Next
3. Choose Space as delimiter → Finish
Using formulas instead:
First name: =LEFT(A2, FIND(" ", A2)-1)
Last name: =MID(A2, FIND(" ", A2)+1, 100)
Split "Delhi-NCR" into city and region:
=LEFT(A2, FIND("-", A2)-1) → "Delhi"
=MID(A2, FIND("-", A2)+1, 100) → "NCR"ANALYST NOTE: Text to Columns is destructive — it overwrites the original column. Use the formula approach (LEFT, MID, FIND) if you need to keep the original column intact.
Handle blanks and errors
=IF(ISBLANK(A2), "Unknown", A2) =IFERROR(formula, fallback)EXAMPLE
Replace blank city with "Unknown":
=IF(ISBLANK(C2), "Unknown", C2)
Prevent VLOOKUP errors from showing:
=IFERROR(VLOOKUP(A2, Products!$A:$C, 2, FALSE), "Not Found")
Count blanks in a column:
=COUNTBLANK(C:C)
Highlight blanks with conditional formatting:
Home → Conditional Formatting → New Rule → Format only cells that contain → Blanks
ANALYST NOTE: IFERROR catches any error (#N/A, #REF!, #VALUE!, #DIV/0!) and replaces it with your fallback. Use it on every VLOOKUP in a production report — you do not want a single missing row to break a whole dashboard.
Date Functions — Time-Based Analysis
IntermediateDate analysis is central to every business report — daily revenue trends, age of inventory, employee tenure, overdue invoices. Excel has a rich set of date functions that every analyst must know.
DATE arithmetic — calculate days, months, years between dates
=DATEDIF(start_date, end_date, unit) =NETWORKDAYS(start, end)EXAMPLE
Employee tenure in years and months:
=DATEDIF(B2, TODAY(), "Y") & " years " & DATEDIF(B2, TODAY(), "YM") & " months"
Age of each invoice in days:
=TODAY() - A2
Delivery time excluding weekends:
=NETWORKDAYS(order_date, delivery_date) - 1
Overdue invoices (more than 30 days old):
=IF((TODAY() - A2) > 30, "Overdue", "Current")
ANALYST NOTE: DATEDIF is an undocumented but fully functional Excel function. Unit options: "Y" (complete years), "M" (complete months), "D" (days), "YM" (months after ignoring years), "MD" (days after ignoring months). Used extensively in Indian HR analytics for tenure and appraisal cycle calculations.
YEAR, MONTH, DAY, WEEKDAY — extract parts of a date
=YEAR(date) =MONTH(date) =DAY(date) =TEXT(date, "format")EXAMPLE
Extract financial year from an order date:
=IF(MONTH(A2) >= 4, YEAR(A2), YEAR(A2)-1) & "-" & IF(MONTH(A2) >= 4, YEAR(A2)+1, YEAR(A2))
→ Returns "2025-2026" for April 2025 orders
Get month name for a report:
=TEXT(A2, "MMMM YYYY") → "August 2026"
Get day of week:
=TEXT(A2, "DDDD") → "Monday"
ANALYST NOTE: Indian financial year runs April to March. Always extract the financial year correctly in reports — a March 2026 order belongs to FY 2025-26, not FY 2026-27. Use the formula above.
Power Query — Import & Transform Data Automatically
Intermediate–AdvancedPower Query (Get & Transform) lets you import data from files, databases, or web sources and apply transformations automatically — so every time you refresh, the entire clean-up runs again without manual work.
What Power Query does that manual cleanup cannot
Data → Get Data → From File / From Database / From WebEXAMPLE
Scenario: You receive a new sales file from 5 regional teams every Monday. Each file has slightly different column names, extra header rows, and inconsistent formatting.
With Power Query:
1. Import all 5 files from a folder at once
2. Apply transformations: remove top 2 rows, rename columns, filter out blank rows, standardise city names
3. Combine into one table
4. Every Monday: click Refresh — all steps run automatically on the new files
Manual approach: 3–4 hours. Power Query: 5 minutes after initial setup.
ANALYST NOTE: Every transformation you apply in Power Query is recorded as a step in M language. You can see, edit, or delete any step. This creates a reproducible, auditable data pipeline — a huge advantage over manual cleanup.
Key Power Query transformations for analysts
Home → Transform → Merge Queries → Append QueriesEXAMPLE
Remove top N rows (skip header junk):
Home → Remove Rows → Remove Top Rows → enter number
Split a column by delimiter:
Transform → Split Column → By Delimiter → choose comma/space/etc.
Unpivot columns (wide to long format for pivot tables):
Select the ID columns → Transform → Unpivot Other Columns
→ Converts "Jan|Feb|Mar" columns into "Month | Value" rows
Merge two tables (like VLOOKUP):
Home → Merge Queries → choose matching column → select join type
ANALYST NOTE: Unpivot is one of the most useful Power Query operations — it converts wide data (months as columns) to long data (month as a row), which is required for proper pivot table analysis and Power BI.
Building an Excel Dashboard
Intermediate–AdvancedA dashboard combines charts, pivot tables, slicers, and formatting into one view that a manager or client can use without touching the underlying data. This is a high-value skill in Indian companies — monthly MIS dashboards are still built in Excel at thousands of organisations.
Dashboard design principles
One sheet visible, data hidden on separate sheetsEXAMPLE
Structure your workbook:
• Sheet 1: "Dashboard" — the view sheet, no raw data visible
• Sheet 2: "Data" — raw data table (hidden if needed)
• Sheet 3: "Pivots" — pivot tables the dashboard reads from (hidden)
• Sheet 4: "Reference" — dropdown lists, colour codes (hidden)
Dashboard elements:
→ KPI tiles at the top (Total Revenue, Orders, AOV, Churn)
→ Trend chart (monthly revenue line chart)
→ Category breakdown (bar chart)
→ Top 10 table
→ Slicers for Date, City, Category
Hide gridlines: View → uncheck Gridlines
Hide formula bar and row/column headers for a clean look
ANALYST NOTE: In India, most MIS dashboards are shared as Excel files via email. Build the dashboard so it works even when the viewer does not know Excel — slicers and clearly labelled charts, no formulas visible, no editable cells in the dashboard view.
Conditional formatting — highlight data automatically
Home → Conditional Formatting → New RuleEXAMPLE
Colour a revenue column: green above target, red below:
1. Select the revenue column
2. Home → Conditional Formatting → Color Scales → choose Red-Yellow-Green
Highlight overdue invoices in red:
1. Select the date column
2. New Rule → Format cells where cell value is less than =TODAY()-30
3. Set fill colour to red
Data bars in a performance table:
Conditional Formatting → Data Bars → choose gradient fill
→ Adds a bar inside each cell proportional to its value
ANALYST NOTE: Conditional formatting updates automatically when data changes. It is one of the fastest ways to make a data table visually scannable without building a chart.
Excel Cheat Sheet — Functions Every Analyst Uses Daily
| Function | What It Does | Common Indian Use Case |
|---|
| VLOOKUP / XLOOKUP | Match and fetch data from another table | Add product names to an orders export from an ERP |
| INDEX-MATCH | Flexible lookup — any direction, any version | Match bonus ₹ to employee ID from two sheets |
| SUMIF / SUMIFS | Sum rows matching one or more conditions | Total revenue by city, category, or sales rep |
| COUNTIF / COUNTIFS | Count rows matching conditions | Count overdue invoices, active employees, returned orders |
| IFERROR | Catch errors and replace with fallback | Show "Not Found" instead of #N/A in VLOOKUP |
| IF / IFS | Conditional logic — classify rows | Flag orders above ₹5,000 as "High Value" |
| TRIM / PROPER | Clean text — remove spaces, fix case | Standardise city names before creating a pivot table |
| TEXT | Format dates/numbers as text for display | Convert dates to "August 2026" in a report header |
| DATEDIF | Days, months, years between two dates | Calculate employee tenure for HR appraisal reports |
| NETWORKDAYS | Count working days between two dates | SLA tracking: delivery days excluding Sundays/holidays |
| PIVOT TABLE | Summarise large datasets by group and time | Monthly sales by product category — auto-refreshable |
| POWER QUERY | Import + transform data automatically on refresh | Combine 5 regional sales files into one clean table weekly |
Frequently Asked Questions
Is Excel still important for data analysts in India in 2026?
Yes — Excel remains one of the most widely used tools for data analysis in Indian companies, particularly in finance, FMCG, logistics, HR, and mid-size businesses. Many companies still manage monthly MIS reports, budget tracking, and ad-hoc analysis in Excel. Even companies that use SQL and Power BI extensively still use Excel for one-off analysis, sharing quick reports with non-technical stakeholders, and final formatting. In interviews at Indian companies, Excel is tested in virtually every data analyst role — especially VLOOKUP/XLOOKUP, pivot tables, and SUMIF/COUNTIF. A data analyst who cannot use Excel confidently is at a disadvantage in the Indian job market, regardless of how strong their SQL or Python skills are.
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP (Vertical Lookup) searches for a value in the leftmost column of a range and returns a value from a specified column to the right. Its limitations: (1) it can only look right — the lookup column must always be the first column, (2) it breaks if a column is inserted or deleted (uses column number, not column name), (3) it is slower than INDEX-MATCH on large datasets. XLOOKUP (Excel 365 and later) fixes all these problems: it can look left or right, uses direct range references instead of column numbers so it does not break when columns are added, handles not-found errors natively, and can return an entire row or column. In Indian job interviews, knowing both is expected. VLOOKUP is still more commonly tested because many companies use older Excel versions. XLOOKUP is the modern replacement and is now available in Microsoft 365 subscriptions common in Indian corporates.
What Excel skills do Indian companies test in data analyst interviews?
Based on common assessment patterns at Indian companies, the most frequently tested Excel skills for data analyst roles are: (1) VLOOKUP or XLOOKUP — matching data across two sheets; (2) Pivot Tables — summarising sales/revenue/headcount by category and time period; (3) SUMIF and COUNTIF — conditional aggregations; (4) IF and nested IFs — categorisation and flag logic; (5) Removing duplicates, handling blanks, standardising text — data cleaning; (6) DATEDIF, NETWORKDAYS, date arithmetic — especially in HR and payroll contexts; (7) Dashboard creation — slicers, charts, conditional formatting; (8) Basic Power Query — importing and transforming data. Some companies (especially analytics firms and MNCs) also test INDEX-MATCH, dynamic arrays, and Power Query transformations. FMCG, manufacturing, and logistics companies weigh Excel skills more heavily than tech startups, which tend to prioritise SQL and Python.
When should a data analyst use Excel vs SQL vs Python?
Use Excel when: the dataset is under 100,000 rows, you need to share the analysis with non-technical stakeholders who will open the file directly, you need formatting and charts in one document, or the task is a one-off with no automation needed. Use SQL when: the data is in a database, the dataset has more than 100,000 rows, you need to join multiple tables, or the query will be run repeatedly (daily/weekly). Use Python when: the analysis requires statistical modelling, machine learning, automation of a repetitive task, or processing data that does not fit in SQL or Excel (APIs, JSON, unstructured text). In practice, most Indian data analyst roles require competence in all three. The skill is knowing which tool to reach for — Excel for quick pivots and reporting, SQL for data extraction, Python for complex modelling and automation.
EVIKA ACADEMY · NOIDA SECTOR 51
Build Real Excel Dashboards in Class
Our Excel module covers every skill in this chapter — VLOOKUP, pivot tables, Power Query, and full dashboard projects on Indian sales and HR data. Mock interviews included.
Book Free Demo Class →