Excel is still the first tool on almost every data analyst job description in India, and the tool you will be tested on in the first interview round. The features that matter for analysis are a short list: the grid itself, formulas, PivotTables, charts, filters, conditional formatting and Power Query. Every formula that matters is written out below with its exact syntax and a working example.
Excel tutorials have a habit of showing a screenshot of a formula instead of the formula. A screenshot cannot be copied or read on a phone, so everything here is plain text.
What Excel is, and how big the grid actually is
Excel is a spreadsheet program in Microsoft Office. Data sits in cells arranged in rows and columns, and you write formulas that reference those cells rather than retyping the numbers.
One worksheet holds 1,048,576 rows and 16,384 columns, and the last column is labelled XFD. That figure gets misquoted as 16,383 surprisingly often, including in older versions of this article. Columns are numbered from 1, so column 16,384 exists and is the last one.
Those limits sound generous and are not. A single month of transaction data from a mid-sized Indian e-commerce seller will pass a million rows, and Excel slows down long before it hits the ceiling. That boundary is the honest reason analysts learn SQL next.
The features of MS Excel that matter for data analysis
You do not need to know every menu. These are the features an analyst opens on a normal working day.
| Feature | What it does | Where to find it | Why analysts use it |
|---|---|---|---|
| Formula bar | The strip marked fx where you type formulas | Above the column headers | Every calculation on this page is typed here |
| Tables | Turns a range into a named object with headers that stay put | Insert menu, then Table | Formulas keep working when new rows arrive |
| Sort and Filter | Orders rows and hides the ones you do not want | Data menu | First move on any messy sheet |
| Remove Duplicates | Deletes repeated rows based on chosen columns | Data menu | Duplicate order IDs are the most common data fault |
| Text to Columns | Splits one column into several using a separator | Data menu | Splitting a full name or a comma-separated address |
| Conditional formatting | Colours cells that meet a rule | Home menu | Spotting outliers without writing a formula |
| Data validation | Restricts what can be typed into a cell | Data menu | Stops a colleague typing Bengaluru three different ways |
| PivotTable | Summarises thousands of rows into a small table by category | Insert menu | The single highest-value feature in Excel |
| Charts | Bar, line, pie, scatter and combination visuals | Insert menu | Turning a result into something a manager will read |
| Power Query | Loads, cleans and reshapes data before it reaches the sheet | Data menu, Get and Transform | Repeating a monthly clean-up in one click |
PivotTables deserve the emphasis. Most of what beginners write as a long SUMIFS chain can be done by dragging two fields into a PivotTable in about fifteen seconds.
How to write a formula
Every formula starts with an equals sign. After that you name a function and give it arguments in brackets, separated by commas. A single cell is written as C2. A range is written with a colon, so C2:C50 covers everything between those two cells.
The one piece of syntax that trips up beginners is the dollar sign. Write C2 and Excel adjusts the reference when you copy the formula down a column. Write $C$2 and it stays locked on that exact cell. Write C$2 and only the row is locked. If a formula works in the first row and produces nonsense in the second, a missing dollar sign is almost always the cause.
Maths and aggregation formulas
These are the ones you will use most. The Syntax column shows the general form and the Example column shows a version you can type straight into a sheet of sales data.
| Formula | What it does | Syntax | Example |
|---|---|---|---|
| SUM | Adds every number in a range | SUM(range) | =SUM(C2:C4) returns 2125 for values 180, 320 and 1625 |
| AVERAGE | Returns the mean of the numbers in a range | AVERAGE(range) | =AVERAGE(C2:C4) |
| COUNT | Counts only the cells that hold numbers | COUNT(range) | =COUNT(C1:C4) returns 2 when C1 holds text and C4 is blank |
| COUNTA | Counts every cell that is not empty, text included | COUNTA(range) | =COUNTA(C1:C4) |
| MIN | Returns the smallest number in a range | MIN(range) | =MIN(C2:C500) |
| MAX | Returns the largest number in a range | MAX(range) | =MAX(C2:C500) |
| SUBTOTAL | Aggregates a range using a function number and ignores hidden rows | SUBTOTAL(function_num, range) | =SUBTOTAL(1, A2:A4) returns the average of A2 to A4 |
| ROUND | Rounds a number to a chosen number of decimals | ROUND(number, digits) | =ROUND(C2, 2) |
| MOD | Returns the remainder after one number is divided by another | MOD(number, divisor) | =MOD(A2, 5) |
| POWER | Raises a number to a power | POWER(number, power) | =POWER(A2, 3) |
| CEILING | Rounds up to the nearest multiple of a value | CEILING(number, significance) | =CEILING(A2, 10) turns 143 into 150 |
| FLOOR | Rounds down to the nearest multiple of a value | FLOOR(number, significance) | =FLOOR(A2, 10) turns 143 into 140 |
| SUMIF | Adds the values that meet one condition | SUMIF(range, criteria, sum_range) | =SUMIF(D2:D500, “Bengaluru”, C2:C500) |
| SUMIFS | Adds the values that meet several conditions | SUMIFS(sum_range, range1, criteria1, range2, criteria2) | =SUMIFS(C2:C500, D2:D500, “Bengaluru”, E2:E500, “Delivered”) |
| COUNTIF | Counts the rows that meet one condition | COUNTIF(range, criteria) | =COUNTIF(D2:D500, “Pune”) |
| COUNTIFS | Counts the rows that meet several conditions | COUNTIFS(range1, criteria1, range2, criteria2) | =COUNTIFS(D2:D500, “Pune”, E2:E500, “Returned”) |
| AVERAGEIF | Returns the mean of values that meet a condition | AVERAGEIF(range, criteria, average_range) | =AVERAGEIF(D2:D500, “Chennai”, C2:C500) |
Two notes. SUBTOTAL takes a function number first, where 1 means average and 9 means sum, which is why =SUBTOTAL(1, A2:A4) returns an average rather than a total. And MOD works on one number at a time, not a whole range, so write it in the first row and copy it down.
Text formulas
Real data arrives dirty. Names have trailing spaces, cities are capitalised inconsistently, and order codes bundle two facts into one string. These formulas fix that.
| Formula | What it does | Syntax | Example |
|---|---|---|---|
| LEFT | Returns the first characters of a text value | LEFT(text, num_chars) | =LEFT(A2, 3) turns BLR-4471 into BLR |
| RIGHT | Returns the last characters of a text value | RIGHT(text, num_chars) | =RIGHT(A2, 4) turns BLR-4471 into 4471 |
| MID | Returns characters from a chosen starting position | MID(text, start_num, num_chars) | =MID(A2, 5, 4) |
| LEN | Counts the characters in a value, spaces included | LEN(text) | =LEN(A2) |
| TRIM | Removes leading, trailing and repeated spaces | TRIM(text) | =TRIM(A2) |
| UPPER | Converts text to capitals | UPPER(text) | =UPPER(A2) |
| LOWER | Converts text to lower case | LOWER(text) | =LOWER(A2) |
| PROPER | Capitalises the first letter of every word | PROPER(text) | =PROPER(A2) turns bengaluru south into Bengaluru South |
| CONCAT | Joins several text values into one | CONCAT(text1, text2) | =CONCAT(A2, ” “, B2) |
| TEXTJOIN | Joins a whole range using a separator | TEXTJOIN(delimiter, ignore_empty, range) | =TEXTJOIN(“, “, TRUE, A2:A10) |
| SUBSTITUTE | Replaces one piece of text with another | SUBSTITUTE(text, old_text, new_text) | =SUBSTITUTE(A2, “Rs.”, “”) |
| FIND | Returns the position of one string inside another | FIND(find_text, within_text) | =FIND(“-“, A2) |
| TEXT | Formats a number as text in a chosen pattern | TEXT(value, format_text) | =TEXT(C2, “0.00”) |
LEFT, MID and RIGHT are frequently described wrongly, so to be exact: LEFT returns characters counted from the left, RIGHT returns characters counted from the right, and neither of them tells you how long the string is. LEN does that.
Date formulas
Dates in Excel are numbers wearing a costume, which is why you can subtract one date from another and get a count of days.
| Formula | What it does | Syntax | Example |
|---|---|---|---|
| TODAY | Returns the current date | TODAY() | =TODAY() |
| NOW | Returns the current date and time | NOW() | =NOW() |
| DAY | Returns the day of the month, 1 to 31 | DAY(date) | =DAY(A2) |
| MONTH | Returns the month number, 1 to 12 | MONTH(date) | =MONTH(A2) |
| YEAR | Returns the year from a date | YEAR(date) | =YEAR(A2) |
| WEEKDAY | Returns the day of the week as a number | WEEKDAY(date, return_type) | =WEEKDAY(A2, 2) |
| EOMONTH | Returns the last day of a month | EOMONTH(start_date, months) | =EOMONTH(A2, 0) |
| DATEDIF | Returns whole units between two dates | DATEDIF(start, end, unit) | =DATEDIF(A2, B2, “d”) |
| TIME | Builds a time value from hours, minutes and seconds | TIME(hour, minute, second) | =TIME(14, 30, 0) |
Lookup and logic formulas
This is the group that separates someone who can use Excel from someone who says they can. Interviewers ask about VLOOKUP almost every time.
| Formula | What it does | Syntax | Example |
|---|---|---|---|
| VLOOKUP | Finds a value in the first column of a table and returns a value from another column | VLOOKUP(lookup_value, table_array, col_index_num, FALSE) | =VLOOKUP(A2, Products!A:D, 3, FALSE) |
| XLOOKUP | Does the same job in any direction with no column counting | XLOOKUP(lookup_value, lookup_array, return_array) | =XLOOKUP(A2, Products!A:A, Products!C:C) |
| INDEX | Returns the value sitting at a given position in a range | INDEX(array, row_num) | =INDEX(C2:C500, 12) |
| MATCH | Returns the position of a value inside a range | MATCH(lookup_value, lookup_array, 0) | =MATCH(A2, Products!A:A, 0) |
| IF | Returns one value when a test passes and another when it fails | IF(test, value_if_true, value_if_false) | =IF(C2>10000, “Large”, “Small”) |
| IFS | Checks several conditions in order and returns the first match | IFS(test1, value1, test2, value2) | =IFS(C2>50000, “A”, C2>10000, “B”, TRUE, “C”) |
| AND | Returns TRUE only when every condition is true | AND(test1, test2) | =AND(C2>1000, D2=”Bengaluru”) |
| OR | Returns TRUE when at least one condition is true | OR(test1, test2) | =OR(D2=”Pune”, D2=”Mumbai”) |
| IFERROR | Replaces an error result with something readable | IFERROR(value, value_if_error) | =IFERROR(VLOOKUP(A2, Products!A:D, 3, FALSE), “Not found”) |
Always pass FALSE as the last argument to VLOOKUP. Leave it out and Excel does an approximate match, which returns a confidently wrong answer on unsorted data.
A worked example: one month of orders
Say you have a sheet from a Bengaluru electronics retailer. Column A holds order IDs like BLR-4471, column B holds the order date, column C holds the amount in rupees, column D holds the delivery city and column E holds the status.
Start by running TRIM on the city column, because “Bengaluru ” and “Bengaluru” are different values to Excel and will split your totals. Then use Remove Duplicates on the order ID column, since duplicated rows from a bad export are the usual reason a revenue figure comes out too high.
Now the actual questions, and the formula that answers each one.
| Question | What it does | Syntax | Example |
|---|---|---|---|
| Total revenue for the month | Adds the whole amount column | SUM(range) | =SUM(C2:C500) |
| Revenue from one city | Adds only the rows matching that city | SUMIF(range, criteria, sum_range) | =SUMIF(D2:D500, “Bengaluru”, C2:C500) |
| Delivered revenue from one city | Adds rows matching two conditions at once | SUMIFS(sum_range, range1, criteria1, range2, criteria2) | =SUMIFS(C2:C500, D2:D500, “Bengaluru”, E2:E500, “Delivered”) |
| Average order value | Mean of the amount column | AVERAGE(range) | =AVERAGE(C2:C500) |
| Typical order value | Middle value, unmoved by one huge order | MEDIAN(range) | =MEDIAN(C2:C500) |
| Month of each order | Extracts the month number for grouping | MONTH(date) | =MONTH(B2) |
Report the median alongside the mean, because a single ₹2,40,000 order drags an average somewhere unhelpful. For the monthly trend, copy the MONTH column down and build a PivotTable with it in rows and the amount in values. That takes less time than writing the SUMIFS chain, and it updates when the data does.
Mistakes that quietly break Excel analysis
Leaving out FALSE in VLOOKUP. Covered above, and worth repeating because the answer looks plausible.
Numbers stored as text. Imported CSVs often bring numbers in as text, and SUM will happily ignore them and return a smaller total with no warning. Left-aligned numbers in a column are the tell.
Merged cells. They break sorting, filtering and PivotTables. Use centre-across-selection instead.
Hard-coding a value inside a formula. Typing the GST rate straight into a hundred formulas means hunting down every one of them when the rate changes. Put the rate in a single cell and reference it absolutely.
Not checking row counts. Run COUNTA on your key column before and after any clean-up step. If the count moves unexpectedly, stop and find out why.
Where Excel stops
Being straight about this: Excel is not a good place to do everything. Past a few hundred thousand rows it becomes slow and fragile, and it has no version control, so two people editing the same monthly report produce two files that disagree.
That is why the standard Indian analyst stack is Excel plus SQL plus a visualisation tool such as Power BI or Tableau, with Python added when the work gets repetitive. Excel is where you start, not where you finish. Our guide to data analyst qualifications sets out the full sequence, and data analytics courses for beginners covers the no-coding entry paths.
Frequently asked questions
How many rows and columns does Excel have?
A single Excel worksheet holds 1,048,576 rows and 16,384 columns, with the last column labelled XFD. These limits apply to the .xlsx format used by Excel 2007 and every version since. Older .xls files were capped much lower, at 65,536 rows and 256 columns.
What are the most important Excel formulas for a data analyst?
SUM, AVERAGE, COUNT and COUNTA for basic aggregation, SUMIFS and COUNTIFS for conditional aggregation, VLOOKUP or XLOOKUP for joining two sheets, IF and IFERROR for logic, and TRIM plus LEFT, RIGHT and MID for cleaning text. If you know those well and can build a PivotTable, you can pass most first-round Excel screens.
What is the difference between COUNT and COUNTA in Excel?
COUNT only counts cells that contain numbers, so it skips text and blanks. COUNTA counts every cell that is not empty, including text, dates and error values. Analysts use COUNT to check how many numeric readings exist and COUNTA to check how many rows have any value at all.
Is Excel enough to get a data analyst job in India?
Rarely on its own. Most data analyst listings in India ask for Excel plus SQL, and increasingly a visualisation tool such as Power BI or Tableau. Excel gets you through the first screening round and is genuinely used every day on the job, but candidates who only have Excel tend to lose out to those who can also query a database.
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches only the first column of a range and returns a value from a column you specify by number, which breaks if someone inserts a column. XLOOKUP takes the lookup range and the return range separately, so it can look leftwards and does not depend on column counting. XLOOKUP is available in Microsoft 365 and Excel 2021 onwards, so learn VLOOKUP too, because plenty of Indian offices still run older versions.
Should I learn Excel or Python first for data analysis?
Excel first, in almost every case. It gives you a feel for what a dataset looks like, with instant visual feedback and no setup. Move to Python once your files get too big for Excel or you find yourself repeating the same clean-up every month.
Learn Excel to the level employers test for
Most people who say they know Excel know about a fifth of it. The gap shows up quickly in an interview when someone is asked to build a PivotTable on the spot or explain why their VLOOKUP returned the wrong row.
SkilloVilla’s Excel: Beginner to Advanced course takes it from the grid to PivotTables, lookups and dashboards with live teaching, at ₹33,110. If you want Excel as the opening module of a full analyst path rather than on its own, the Data Analytics with Python track at ₹71,999, currently ₹58,999 puts it alongside SQL, statistics, Python and placement support.
Fees and ratings last checked August 2026; confirm current numbers with the provider before enrolling.
