πWhy Excel Still Matters for Analysts
SQL, Python and Power BI get the headlines, but Excel is still the first tool most Indian companies hand a data analyst. Finance, sales, operations and HR teams send data in spreadsheets and expect answers back in them. Interviewers test Excel for that reason, and strong Excel skills make every other tool easier, because the ideas (filtering, grouping, lookups, joining) are the same.
This guide covers the Excel skills that matter for analysis, in the order you will use them, and ends with a worked example. For the full skill path, see the data analyst roadmap.
πStep 1: Get the Data Into Shape
Turn the range into a Table
Select your data and press Ctrl+T. Excel Tables expand automatically when new rows arrive, give columns readable names in formulas (such as Sales[Amount]), and make pivot tables and charts update cleanly.Clean text
Fix numbers and dates
Numbers stored as text will not add up. Use VALUE or Data, Text to Columns to convert them. For dates in mixed formats, standardise them before any analysis, because a month total built on wrong dates is silently wrong.Remove duplicates and blanks
Use Data, Remove Duplicates, choosing the columns that define a duplicate. Find blanks with Go To Special (F5, Special, Blanks) and decide whether to fill, remove or flag them.πStep 2: Lookups and Logic
XLOOKUP
XLOOKUP is the modern lookup. It searches any column and returns from any column:=XLOOKUP(A2, Products[Code], Products[Category], "Not found")It defaults to exact match and lets you set a value for missing keys. Older workbooks use VLOOKUP or INDEX with MATCH; know how to read them, but write new work with XLOOKUP.
IF and IFS
IF handles one condition, IFS handles several in order:=IFS(D2>=100000, "High", D2>=25000, "Medium", TRUE, "Low")Combine with AND and OR for compound conditions. For long mapping tables, a lookup table with XLOOKUP is cleaner than deeply nested IFs.
IFERROR
Wrap a formula to replace errors with something readable, but use it carefully: hiding errors can hide real data problems.πStep 3: Conditional Aggregation
SUMIFS, COUNTIFS and AVERAGEIFS answer most business questions directly:
=SUMIFS(Sales[Amount], Sales[Region], "South", Sales[Month], "Mar")=COUNTIFS(Sales[Status], "Returned", Sales[Region], G2)Remember the sum range comes first in SUMIFS. Point criteria at cells instead of typing values, so one formula copies across a whole summary grid.
πStep 4: Pivot Tables
Pivot tables are the fastest way to explore data. Insert, PivotTable, then drag fields:
Useful features interviewers ask about: Show Values As (percentage of total, difference from previous month), grouping dates into months and quarters, calculated fields, and refreshing after the source Table grows.
πStep 5: Dynamic Arrays
Modern Excel formulas return whole ranges that spill into neighbouring cells:
=FILTER(Sales, (Sales[Region]="South") * (Sales[Amount]>50000), "No rows")Together they build live, formula-driven reports, for example a top 10 customer list that updates as data changes: =TAKE(SORTBY(Customers, Customers[Revenue], -1), 10).
πStep 6: Power Query
Power Query (Data, Get Data) records each cleaning step and replays it on refresh. Use it when you receive the same messy file every week or month. It can:
Once a query is built, next month's report takes one click on Refresh All. Power Query in Excel is the same engine used in Power BI, so the skill transfers directly.
πStep 7: Charts and Dashboards
Choose the chart by the question:
A simple Excel dashboard has KPI cards at the top (formulas pointing at summary cells), a trend chart, a category breakdown and slicers connected to the pivot tables. Keep colours consistent and remove chart clutter like gridlines and 3D effects.
πWorked Example: Monthly Sales Review
Imagine you receive a monthly export of orders with columns Order ID, Date, Region, Product Code, Quantity, Unit Price and Status.
That workflow covers what most analyst roles expect from Excel, and it is a good portfolio piece.
πStatistics Functions Analysts Use
Excel covers the everyday statistics an analyst needs without any add-in:
These are enough to answer most "is this difference real" and "what is typical" questions before you reach for Python.
πMistakes That Break Analysis
πShortcuts That Save Time
πWhen to Move Beyond Excel
Excel slows down with very large data and makes repeatable, auditable analysis harder. That is the point to add SQL for pulling data, Power BI or Tableau for shared dashboards, and Python with pandas for heavy cleaning, see our pandas tutorial. Analysts who combine all four are the ones who move up fastest.
πFAQ
Is Excel enough to get a data analyst job?
Rarely on its own. Excel plus SQL and one BI tool is the realistic minimum for most data analyst roles in India in 2026.How long before I am comfortable with Excel for analysis?
With daily practice on real files, most learners are confident with cleaning, lookups, SUMIFS and pivot tables within a few weeks. Power Query and dashboards take longer, so build them into a project rather than learning them in isolation.Which Excel version should I learn?
Microsoft 365 Excel, because it has XLOOKUP, dynamic arrays and the latest Power Query. Many companies use it, and interviews assume these functions.What Excel questions come up in interviews?
Lookups, SUMIFS, pivot tables, handling duplicates and blanks, and a small hands-on task. See our data analyst interview questions.πLearn It Live
Prakalpana's Data Analytics course teaches Excel, SQL, Power BI, Tableau and Python live online, with an optional 1-on-1 track and real business projects. Explore the data analytics learning path, then WhatsApp or call +91 9180377502 for a free demo.