📘 DATA ANALYTICS SERIES · CHAPTER 62

Advanced Excel for Data Analysts — India 2026

Power Query ETL, Power Pivot data modelling, DAX measures in Excel, dynamic array functions, XLOOKUP, dashboard design principles, and VBA automation basics — the advanced Excel skills that make Indian analysts 5× faster and pass the toughest MIS interview tests.

⏱ 20 min read📅 September 2026📍 India

Beyond Basic Excel — What Advanced Actually Means

Most Excel users in India describe themselves as "proficient in Excel" — which means VLOOKUP, SUM, basic Pivot Tables, and some conditional formatting. That is the baseline, not advanced. Advanced Excel for analysts means building systems that update automatically, handle thousands of rows reliably, and produce outputs that need zero manual intervention after the initial build.

Skill levelWhat you can doIndia job market value
BasicSUM, AVERAGE, IF, VLOOKUP, basic Pivot Tables, simple chartsMIS Executive — ₹3–5 LPA
IntermediateSUMIFS, INDEX-MATCH, Pivot with slicers, conditional formatting, Power Query basicsMIS Analyst / Jr Data Analyst — ₹5–9 LPA
AdvancedPower Query + Power Pivot + DAX, dynamic arrays, XLOOKUP, multi-table data models, automated dashboardsData Analyst / Sr MIS — ₹9–18 LPA
ExpertComplex VBA macros, Power BI + Excel hybrid, BigQuery/SQL feeding Excel, enterprise reporting automationAnalytics Lead / BI Developer — ₹15–28 LPA

Power Query — Automate Your Data Cleaning Forever

Power Query records every data transformation as a step. When source data updates, one click rebuilds everything. Never manually clean a CSV again.

Remove duplicates
Home → Remove Rows → Remove Duplicates
De-duplicate on a key column (order_id, customer_id) rather than all columns to avoid accidentally removing valid records.
Split column by delimiter
Transform → Split Column → By Delimiter
Split "First Last" into two columns, or split "2026-09-15" into year/month/day for granular date filtering.
Unpivot columns
Select month columns → Transform → Unpivot Columns
Convert wide format (Jan, Feb, Mar as columns) into long format (Month, Value as two columns) — essential for Pivot Table and Power BI compatibility.
Merge queries (SQL JOIN equivalent)
Home → Merge Queries → choose join type
Left join an orders table to a products table on product_id. Works exactly like SQL LEFT JOIN — all join types available.
Add custom column
Add Column → Custom Column → write M formula
Create calculated columns using Power Query's M language. Example: [Revenue] - [Cost] for profit, or Text.Upper([City]) for consistent capitalisation.
Change data types in bulk
Select columns → Transform → Data Type
Always set date columns to Date (not Text) and numeric columns to Decimal/Integer before loading. Wrong data types break every downstream calculation.

Power Pivot — Multi-Table Data Models Without SQL

Power Pivot adds a relational data model to Excel — you can join multiple tables by relationships and build DAX measures that work across all related tables. This is how you analyse millions of rows in Excel without VLOOKUP performance problems.

How to set up a Power Pivot data model
1. Load each table via Power Query (do NOT check "Load to worksheet" — load to data model only)
2. Open Power Pivot: Data → Manage Data Model
3. Switch to Diagram View — drag to create relationships between tables (e.g., orders.product_id → products.product_id)
4. Ensure relationship direction: fact table (orders) connected to dimension tables (products, customers, dates)
5. Create a Date dimension table with CALENDARAUTO() or import a pre-built one
6. Write DAX measures in the measure grid — they now work across all related tables in any Pivot Table

DAX Measures in Excel — The 8 You Actually Use

Total Revenue
Total Revenue := SUM(Orders[revenue])
📌 Base measure — always create this before any calculated variants. All other revenue measures reference this.
YoY Revenue Growth %
YoY Growth % :=
DIVIDE(
    [Total Revenue]
    - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date])),
    CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
) * 100
📌 Requires a proper Date table marked as Date table. SAMEPERIODLASTYEAR shifts the filter context to the same period in the prior year.
Running Total (MTD)
MTD Revenue :=
CALCULATE(
    [Total Revenue],
    DATESMTD(Dates[Date])
)
📌 DATESMTD returns all dates from the start of the current month up to the current date in context.
Average Order Value
Avg Order Value :=
DIVIDE([Total Revenue], DISTINCTCOUNT(Orders[order_id]))
📌 Use DIVIDE instead of / to avoid division-by-zero errors when a filter returns no orders.
Customer Count
Unique Customers :=
DISTINCTCOUNT(Orders[customer_id])
📌 DISTINCTCOUNT counts unique values — essential when one customer can have multiple orders.
Conversion Rate
Conversion Rate % :=
DIVIDE(
    CALCULATE(DISTINCTCOUNT(Events[user_id]),
              Events[event_type] = "purchase"),
    DISTINCTCOUNT(Events[user_id])
) * 100
📌 Counts unique purchasers as a % of all unique visitors in the current filter context.
RANKX — Product Ranking
Product Revenue Rank :=
RANKX(
    ALL(Products[product_name]),
    [Total Revenue],
    ,
    DESC,
    Dense
)
📌 ALL() removes the current filter on products so every product is ranked against all products, not just the filtered set.
Previous Month Revenue
Prev Month Revenue :=
CALCULATE(
    [Total Revenue],
    PREVIOUSMONTH(Dates[Date])
)
📌 Use alongside [Total Revenue] in the same Pivot Table to create an implicit MoM comparison column.

Dynamic Array Functions — Excel 365 Superpowers

FILTER
=FILTER(array, include, [if_empty])
Live-filtered table: =FILTER(A2:D100, C2:C100="North") returns only North region rows, updating automatically when data changes.
SORT
=SORT(array, [sort_index], [sort_order])
Sort any range without touching the source: =SORT(A2:B50, 2, -1) sorts by column 2 descending. Feeds dynamic dashboards.
UNIQUE
=UNIQUE(array, [by_col], [exactly_once])
Live dropdown source: put =UNIQUE(B2:B500) in a helper column, reference it as Data Validation source for a self-updating dropdown list.
XLOOKUP
=XLOOKUP(lookup, search, return, [if_not_found])
=XLOOKUP(A2, Products[ProductID], Products[Price], "Not found") — replaces VLOOKUP entirely. Works left, right, vertical, horizontal.
SEQUENCE
=SEQUENCE(rows, [cols], [start], [step])
Generate calendar tables, running number lists, or test data: =SEQUENCE(12,1,1,1) creates the list 1 through 12 for a month selector.
TEXTSPLIT
=TEXTSPLIT(text, col_delim, [row_delim])
Split "Delhi,Mumbai,Noida" into three cells: =TEXTSPLIT(A2,","). Works for splitting city lists, tags, or multi-value fields.

VBA Automation — 3 Macros Every Analyst Should Know

You do not need to become a VBA developer. Three macros cover 80% of analyst automation needs:

1. Refresh all Power Query connections and Pivot Tables
Sub RefreshAll()
    ' Refresh every query and pivot in the workbook
    ThisWorkbook.RefreshAll
    Application.Calculate
    MsgBox "Refresh complete — " & Now()
End Sub
💡 Assign to a button on the dashboard sheet. One click updates everything from source data.
2. Export the active sheet as a PDF
Sub ExportAsPDF()
    Dim path As String
    path = ThisWorkbook.path & "\" & _
           ActiveSheet.Name & "_" & Format(Now, "YYYY-MM-DD") & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path
    MsgBox "Saved to: " & path
End Sub
💡 Saves a date-stamped PDF of the current sheet to the same folder as the workbook. Faster than File → Export every time.
3. Loop through sheets and format all tables consistently
Sub FormatAllTables()
    Dim ws As Worksheet
    Dim tbl As ListObject
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            tbl.TableStyle = "TableStyleMedium2"
            tbl.HeaderRowRange.Font.Bold = True
        Next tbl
    Next ws
    MsgBox "All tables formatted."
End Sub
💡 Instantly applies consistent formatting to every Excel table in every sheet — useful after importing new data that breaks existing formatting.

Excel Interview Test — What Indian Employers Actually Ask

📋 Two-table XLOOKUP / INDEX-MATCH
Given an orders sheet and a products sheet, add product name and category to the orders table using the product_id key. Tests lookup depth and handling of missing values.
📋 SUMIFS with multiple criteria
Total sales for "Electronics" category, "North" region, in Q3 2026. Tests SUMIFS syntax and understanding of criteria ranges.
📋 Pivot Table with calculated field
Create a Pivot Table showing revenue by category with a calculated field for profit margin %. Tests Pivot creation and calculated field addition.
📋 Power Query — clean and load
Given a messy CSV with header rows, merged cells, and inconsistent date formats — clean it in Power Query and load to a proper table. Tests practical ETL awareness.
📋 Conditional formatting rule
Highlight all rows where profit margin is below 10%. Tests understanding of formula-based conditional formatting rules using the entire row reference pattern ($A1 vs A1).
📋 Dynamic dashboard with slicers
Build a one-page dashboard with 3 KPI cards and 2 charts, all controlled by a month slicer. Tests ability to connect multiple Pivot Tables to a single slicer.

Frequently Asked Questions

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

Yes — Excel remains critical for Indian data analysts in 2026, particularly in MIS, finance, operations, banking, and consulting roles. Advanced Excel (Power Query, Power Pivot, dynamic arrays) has significantly expanded what is possible without writing code. Many Indian companies still run their entire reporting stack on Excel, and analysts who can build automated, refresh-ready Excel dashboards are highly valued. SQL and Python do not replace Excel — they complement it for different tasks.

What is Power Query in Excel and how is it useful?

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool. It connects to CSV, SQL databases, SharePoint, web pages, and other sources, applies transformations (clean, filter, reshape, merge), and loads the result into a worksheet or data model. The key advantage: transformations are saved as steps — when source data updates, you click Refresh and the entire cleaned table rebuilds automatically. This eliminates the manual cleaning that most Excel users do every time they receive a new file.

What is the difference between VLOOKUP, XLOOKUP, and INDEX-MATCH?

VLOOKUP: looks up a value in the leftmost column, returns a value from a specified column to the right. Breaks when you insert columns. Cannot look left. INDEX-MATCH: more flexible — can look in any direction, does not break on column insertion, slightly faster on large datasets. XLOOKUP (Excel 365): the modern replacement for both — single function, looks in any direction, handles not-found values natively with the [if_not_found] argument, and returns entire ranges. Use XLOOKUP on Excel 365; use INDEX-MATCH on older Excel versions.

What are dynamic array functions in Excel and why do they matter?

Dynamic array functions (introduced in Excel 365) return arrays that automatically spill into adjacent cells without pressing Ctrl+Shift+Enter. Key functions: FILTER (returns rows matching a condition), SORT / SORTBY (sorts a range), UNIQUE (returns distinct values), SEQUENCE (generates a number sequence), XLOOKUP (replaces VLOOKUP), and TEXTSPLIT (splits text into arrays). These enable workflows that previously required VBA macros — like a live-filtered, sorted table that updates when source data changes.

What Excel skills do Indian MIS analyst interviews test?

Common Excel interview tests for Indian MIS and data analyst roles: VLOOKUP or XLOOKUP on a two-table join scenario, SUMIFS with multiple criteria, Pivot Table with custom grouping and calculated field, conditional formatting based on a rule, Power Query basic transformation (remove duplicates, change data type, filter rows), and sometimes a small dashboard task. Advanced roles also test INDEX-MATCH, dynamic arrays, and Power Pivot relationships.

Master Advanced Excel in 45 Days — at Evika Academy

Our Excel + MIS course in Noida Sector 51 covers everything in this chapter — Power Query, Power Pivot, DAX, dynamic arrays, and dashboard design — with live practice on Indian business datasets.

📱 Book Free DemoView Excel Course
🎓 Free Demo Class — Online & Offline · Noida Sector 51