“Advanced Excel” often appears in Indian analyst job listings, but the listing rarely says what that means. In practice, employers want you to clean data quickly, summarise it accurately and build something others can reuse. This guide breaks advanced Excel into the skills that actually get tested, and covers the AI and Python features that changed in 2026.
Facts in this article were checked against Microsoft Support documentation and Microsoft’s official blogs in October 2026.
The core skills, in order of importance
| Skill | Why it matters | What “good” looks like |
|---|---|---|
| Clean, structured tables | Everything else depends on tidy data | Data stored as an Excel Table with one header row and no merged cells |
| Lookups | Combining data from different sheets is a daily task | Confident with XLOOKUP, and able to read older VLOOKUP and INDEX/MATCH formulas |
| PivotTables | The fastest way to summarise data | Can group dates, add calculated fields and use slicers |
| Logical and text functions | Cleaning and categorising data | IF, IFS, AND, OR, TEXT, LEFT/RIGHT/MID, TRIM, TEXTSPLIT |
| Dynamic arrays | Modern, readable formulas | FILTER, SORT, UNIQUE, SEQUENCE |
| Power Query | Repeatable cleaning and combining of files | Can import a folder of monthly files and refresh in one click |
| Charts and simple dashboards | Communicating results | Clean charts with clear titles, no chart junk |
Lookups: learn XLOOKUP, but read VLOOKUP
Microsoft’s documentation shows XLOOKUP is available in Excel for Microsoft 365, Excel 2024 and Excel 2021, but not in Excel 2016 or Excel 2019. That matters in India, where some companies and clients still run older versions. Use XLOOKUP where you can, because it handles left lookups and missing values more cleanly, but be able to read and fix VLOOKUP and INDEX/MATCH formulas in inherited workbooks.
PivotTables: still the workhorse
If you can only master one feature, make it PivotTables. Interview tasks often ask you to “summarise sales by region and month” or “find the top five customers”. Practise:
- Grouping dates by month, quarter and year.
- Showing values as a percentage of the row or column total.
- Adding slicers and timelines.
- Building a PivotTable from the data model when you have more than one table.
Power Query: the skill that saves the most time
Power Query lets you record cleaning steps once and repeat them with a refresh. Typical uses include combining monthly files from a folder, removing blank rows, splitting columns, unpivoting cross-tab data and merging tables. The same Power Query engine sits inside Power BI, so time spent here also prepares you for BI work.
What changed in Excel in 2026
Copilot in Excel became agentic
On 22 April 2026, Microsoft announced that Copilot’s agentic capabilities in Word, Excel and PowerPoint were generally available. In Excel, Copilot can explore data, build and explain analysis and make changes directly in the workbook. Microsoft said this is available to Microsoft 365 Copilot and Microsoft 365 Premium subscribers, and to Personal and Family plans.
The COPILOT worksheet function was withdrawn
Microsoft’s support page says that from 14 September 2026, the COPILOT function is no longer available in Excel and cells using it return #NAME?. It had only been a preview feature. Microsoft now points users to the Copilot pane instead.
Python in Excel is more widely available
According to Microsoft Support, Python in Excel is available to Enterprise and Business users on Windows (Current Channel, Version 2408 and later), on Mac (Version 16.96 and later) and on Excel for the web. Standard compute is included, with limited monthly access to faster premium compute. A paid add-on gives more premium compute and extra recalculation options. It is not supported on the iPad, iPhone or Android apps.
Lists and arrays inside a cell
On 24 September 2026, the Excel team announced that Excel now supports multiple values in a single cell, through lists, arrays in cells and nested arrays. Check how such cells behave when exported or loaded into other tools before relying on them in shared reports.
How to use Copilot and Python in Excel sensibly
- Use Copilot for first drafts, such as a formula you cannot remember or a quick summary, then check the result yourself.
- Never accept a number you cannot verify. Reconcile AI-generated totals with a PivotTable or a SUM.
- Use Python in Excel for things formulas do badly: statistical summaries, more complex reshaping or charts using Python libraries.
- Know your company’s policy before sending confidential data to any AI feature.
- Remember who will open the file. Python cells and Copilot features depend on the recipient’s version and licence.
A four-week practice checklist
- Week 1: Convert raw data into Tables; practise XLOOKUP, IF/IFS, TEXT functions and data validation.
- Week 2: PivotTables with date grouping, percentage views, slicers and a simple dashboard sheet.
- Week 3: Power Query: combine a folder of files, clean them and load the result to a PivotTable.
- Week 4: Dynamic arrays (FILTER, UNIQUE, SORT), one Python in Excel experiment if available to you, and a timed mock task: answer five business questions from a dataset in 30 minutes.
How Excel fits into the bigger analyst toolkit
Excel is the starting point, not the finish line. Most analyst roles also expect SQL and a BI tool such as Power BI or Tableau. A good rule: use Excel for quick, small analyses and for sharing with business users; move to SQL when data lives in a database; and move to Power BI or Tableau when the same report needs to be refreshed and shared regularly.
Frequently asked questions
Is VLOOKUP still worth learning?
Learn to read and fix it, because many existing workbooks use it and some organisations still run Excel 2016 or 2019, where XLOOKUP is not available. For new work in Microsoft 365, XLOOKUP is the better choice.
Is Python in Excel free?
Microsoft Support says Python in Excel with standard compute is included for eligible Enterprise and Business users, with a limited monthly allowance of faster premium compute. A paid add-on provides more premium compute.
Should I get a MOS Excel certification?
It can help freshers and career-switchers prove their skills. Experienced analysts are usually judged on interview tasks instead. See our guide to the MO-210 and MO-211 exams.
Official sources
- Microsoft Support: XLOOKUP function
- Microsoft Support: Python in Excel availability
- Microsoft Support: COPILOT function (retirement notice)
- Microsoft Copilot Blog: Agentic capabilities in Word, Excel and PowerPoint (22 April 2026)
- Microsoft Excel Blog: Excel now supports multiple values in a single cell

Leave a Reply