Diwali Offer!Flat 20% OFF
Enroll Now
PrakalpanaLive online tech training
Data Analytics⏱️ 11 min readπŸ“… Nov 22

Excel for Data Analysis: The Complete 2026 Guide with a Worked Example

MS
Meenakshi Sundaramβ€’Senior Business Analyst
πŸ“‘ Contents (26 sections)

πŸ“Œ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

  • TRIM removes extra spaces, a common cause of failed lookups.
  • PROPER, UPPER and LOWER fix inconsistent capitalisation.
  • TEXTSPLIT, TEXTBEFORE and TEXTAFTER split values such as "Pune, Maharashtra" into city and state.
  • Find and Replace (Ctrl+H) fixes repeated typos at once.
  • 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:

  • Rows: what you group by (region, product).
  • Columns: a second grouping (month).
  • Values: what you measure (sum of sales, count of orders).
  • Filters and slicers: interactive filtering.
  • 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:

  • UNIQUE lists distinct values: =UNIQUE(Sales[Region]).
  • SORT and SORTBY order results.
  • FILTER returns only the rows that meet a condition:
  • =FILTER(Sales, (Sales[Region]="South") * (Sales[Amount]>50000), "No rows")

  • SEQUENCE generates number series, useful for calendars and ranks.
  • 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:

  • Combine all files in a folder into one table.
  • Unpivot wide month columns into a tidy long table.
  • Merge tables like a SQL join.
  • Change types, split columns and remove errors.
  • 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:

  • Trend over time: line chart.
  • Comparing categories: bar chart, sorted.
  • Part of a whole with few parts: stacked bar, rarely a pie.
  • Relationship between two numbers: scatter plot.
  • 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.

  • 1Load and clean. Import it with Power Query, trim text, set Date as a date and Quantity and Unit Price as numbers, and remove duplicate Order IDs.
  • 2Enrich. Add Amount as Quantity times Unit Price. Bring in Category with XLOOKUP from a product master table.
  • 3Summarise. Build a pivot table with Region in rows, Month in columns and Sum of Amount in values. Add Show Values As, Difference From previous month.
  • 4Answer questions. Use SUMIFS for returned order value by region, and FILTER to list orders above a threshold for review.
  • 5Present. Add KPI cards for total sales, orders, average order value and return rate, one trend chart and one category bar chart, with slicers for region and category.
  • 6Refresh. Next month, replace the source file and click Refresh All.
  • 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:

  • AVERAGE, MEDIAN and MODE.SNGL for central values. Report the median for skewed data such as order values or salaries.
  • STDEV.S and VAR.S for spread in a sample.
  • PERCENTILE.INC and QUARTILE.INC for distributions and outlier checks with the IQR rule.
  • CORREL for the strength of a linear relationship between two columns.
  • FORECAST.LINEAR and the Forecast Sheet for simple trend projections.
  • The Analysis ToolPak (File, Options, Add-ins) adds descriptive statistics, histograms, regression and t-tests in a few clicks.
  • These are enough to answer most "is this difference real" and "what is typical" questions before you reach for Python.

    πŸ“ŒMistakes That Break Analysis

  • Mixing data and summaries on one sheet. Keep raw data, calculations and outputs on separate sheets.
  • Hard-coded numbers inside formulas. Put assumptions such as tax rate or target in labelled cells and reference them.
  • Merged cells in data ranges. They break sorting, filtering and pivot tables. Use Center Across Selection instead.
  • Relative references that shift when copied. Use F4 to lock ranges with dollar signs where needed.
  • No checks. Add a control total that compares your summary with the raw data so errors show up immediately.
  • Manual monthly steps. If you repeat the same cleaning every month, move it into Power Query.
  • πŸ“ŒShortcuts That Save Time

  • Ctrl+T: create a Table.
  • Ctrl+Shift+L: toggle filters.
  • Ctrl+Arrow keys: jump to the edge of data.
  • Ctrl+Shift+Arrow: select to the edge.
  • Alt+=: AutoSum.
  • F4: toggle absolute references while editing a formula.
  • Ctrl+1: format cells.
  • Alt, N, V: insert a pivot table.
  • Ctrl+; : insert today's date.
  • πŸ“Œ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.

    MS

    Written by

    Meenakshi Sundaram

    Senior Business Analyst

    πŸš€ Master Data Analytics

    Live online + 1-on-1 Data Analytics training Β· Join 5000+ developers

    Not sure what to do? Book career counselling (β‚Ή200 / 30 min)

    Not sure what to learn next?

    A 1-on-1 session with an industry mentor and a roadmap built for you. How it works

    1. 1Discuss your goals
    2. 2Get a personal plan
    3. 3Start learning
    Book for β‚Ή200/ 30 min