Anil’s First Day With Excel, Anil had just been promoted to Junior MIS Analyst at a manufacturing company in Surat. On his first morning, his manager dropped a file on his desk – 500 rows of sales data – and said: “I need the total sales, average order value, highest single order, how many orders came from Delhi, and a Pass/Fail column for targets – by noon.”
Anil stared at the screen. He knew Excel had formulas. He had heard of SUM. But the rest? He had no idea. He spent four hours doing it manually with a calculator. He got the numbers wrong twice. He missed the noon deadline. That evening, a colleague sat with him for 45 minutes and taught him 10 formulas. The next time the same report came, Anil finished it in 8 minutes. This guide teaches you exactly what that colleague taught Anil – and 10 more formulas on top.
Excel has over 400 built-in functions. You do not need to know all of them. In fact, 80% of real office work is done with just 20 to 25 formulas. This guide teaches you the most important ones – in plain language, with real examples from actual workplace situations, explained the way a helpful colleague would explain them.
No jargon. No textbook language. Just clear, honest explanations and formulas you can use starting today.
Chapter 1: What Is an Excel Formula? – The Basics First
Before we jump into the list, let us make sure you understand what a formula actually is – because this one concept prevents most beginner confusion.
An Excel formula is an instruction you write inside a cell that tells Excel to calculate something and show you the answer. Every formula in Excel starts with an equals sign (=). The moment you type = in a cell and press Enter, Excel knows you are not typing regular text – you are giving it a calculation to perform.
| If you type this in a cell… | Excel shows this… | Why? |
| 100 | 100 | No = sign – Excel treats it as plain text or number |
| =100 | 100 | Has = sign – Excel evaluates it (but 100 = 100, so same result) |
| =50+30 | 80 | Excel adds 50 and 30 and shows the answer |
| =SUM(A1:A5) | Total of A1 to A5 | Excel runs the SUM function on that range |
| =IF(A1>50,”Pass”,”Fail”) | Pass or Fail | Excel checks the condition and shows the matching result |
The Golden Rule of Excel Formulas, Every single formula must start with = (equals sign). Without it, Excel treats your formula as plain text and nothing gets calculated. This is the number one reason beginners get confused when their formula “does not work” – they forgot the = sign.
How to Type a Formula – Step by Step
- Click on the cell where you want the result to appear.
- Type = (the equals sign). You will see it appear in the cell and in the formula bar above.
- Type the formula name and opening bracket – for example: =SUM(
- Select the cells you want to include, or type them manually: A2:A10
- Close the bracket: =SUM(A2:A10)
- Press Enter. The answer appears in the cell.
Use the Formula Bar to Edit If your formula has an error or you want to change it, click on the cell and look at the formula bar at the top of Excel. You can click directly in the formula bar and edit the formula there – much easier than retyping it in the cell.
Understanding Cell References
Most formulas work by referencing cells – like A2 or B5:B100 – instead of typing the actual numbers. This is what makes Excel powerful. When the data in A2 changes, your formula result updates automatically.
| Reference Type | Example | What It Means | When to Use |
| Single cell | A2 | Just cell A2 | Reference one specific value |
| Range | A2:A100 | All cells from A2 to A100 | Sum, Count, or Average a column |
| Row range | A2:F2 | All cells from A2 to F2 in one row | Work across a horizontal range |
| Full column | A:A | Every cell in column A | When data length is unknown |
| Cross-sheet | Sheet2!A2 | Cell A2 on Sheet2 | Reference data from another sheet |
Chapter 2: Math & Statistics Formulas – The Foundation
Back to Anil, The first thing Anil’s colleague showed him was the math formulas – because they are the fastest win. “Stop using your calculator for totals,” she said. “Type this one formula and let Excel handle a thousand rows in one second.”
Formula 1 – SUM: Add a Range of Numbers
SUM is the most used formula in all of Excel. It adds up all numbers in a range you specify. Whether you have 5 rows or 50,000 rows – the formula is identical.
Syntax:
=SUM(number1, [number2], ...)
=SUM(A2:A100) ← most common form| Cell | Data / Context | Formula | Result |
| B12 | Total monthly sales | =SUM(B2:B11) | ₹4,85,200 |
| E5 | Sum of multiple ranges | =SUM(A2:A10, C2:C10) | ₹2,31,800 |
| D3 | Add specific values | =SUM(100, 250, 75) | 425 |
AutoSum Shortcut, Click on an empty cell below your column of numbers. Press Alt + = (Alt and Equals at the same time). Excel automatically writes =SUM(…) and selects the range above. Press Enter. This is the fastest way to sum a column.
Formula 2 – AVERAGE: Find the Mean Value
AVERAGE adds all the numbers in a range and divides by the count. Perfect for sales averages, attendance percentages, test score averages, or any situation where you need the typical value.
Syntax:
=AVERAGE(number1, [number2], ...)
=AVERAGE(B2:B30)| Cell | Data / Context | Formula | Result |
| C14 | Average test score of class | =AVERAGE(C2:C13) | 72.4 |
| D20 | Avg monthly sales for year | =AVERAGE(D2:D13) | ₹38,750 |
Formula 3 – MIN: Find the Lowest Value
MIN returns the smallest number in a range. Use it to find the lowest salary, the minimum order, the worst test score, or the lowest stock level.
Syntax:
=MIN(number1, [number2], ...)
=MIN(B2:B100)| Cell | Data / Context | Formula | Result |
| F2 | Lowest salary in department | =MIN(C2:C50) | ₹18,500 |
| G2 | Minimum stock level | =MIN(D2:D30) | 12 units |
Formula 4 – MAX: Find the Highest Value
MAX is the opposite of MIN – it returns the largest number. Use it for highest sales, top score, maximum order value, or peak attendance.
Syntax:
=MAX(number1, [number2], ...)
=MAX(B2:B100)| Cell | Data / Context | Formula | Result |
| F3 | Top sales this month | =MAX(C2:C50) | ₹1,25,000 |
| H2 | Highest marks in exam | =MAX(D2:D60) | 98 |
Formula 5 – ROUND: Control Decimal Places
ROUND trims a number to a specific number of decimal places. Essential for financial reports where you need clean, rounded figures instead of long decimals.
Syntax:
=ROUND(number, num_digits)
num_digits = 2 means round to 2 decimal places
num_digits = 0 means round to whole number
num_digits = -2 means round to nearest 100| Cell | Data / Context | Formula | Result |
| C2 | 3.14159265 | =ROUND(C2, 2) | 3.14 |
| D5 | ₹4,522.87 | =ROUND(D5, 0) | ₹4,523 |
| E8 | 7834 | =ROUND(E8, -2) | 7800 |
ROUNDUP and ROUNDDOWN, ROUND rounds to the nearest value. ROUNDUP always rounds up (=ROUNDUP(2.1, 0) gives 3). ROUNDDOWN always rounds down (=ROUNDDOWN(2.9, 0) gives 2). Use these when direction of rounding matters – like always rounding up invoice amounts.
Formula 6 – SUMIF: Add Numbers That Meet a Condition
SUMIF is a smarter SUM. Instead of adding everything, it only adds numbers where a condition is met. This is one of the most powerful formulas for sales reports, department summaries, and financial analysis.
Syntax:
=SUMIF(range, criteria, [sum_range])
range = the column to CHECK the condition in
criteria = the condition to look for
sum_range = the column to ADD (if different from range)| Cell | Data / Context | Formula | Result |
| E2 | Sum sales only from ‘North’ region | =SUMIF(C2:C100,”North”,D2:D100) | ₹2,45,800 |
| F2 | Sum all orders above ₹5,000 | =SUMIF(B2:B50,”>5000″) | ₹87,500 |
| G3 | Sum sales for rep ‘Ravi’ | =SUMIF(A2:A100,”Ravi”,D2:D100) | ₹1,18,200 |
Chapter 3: Counting Formulas – Know Your Data
Priya’s Attendance Sheet, Priya – our HR executive – came back with a new problem. She needed to know: how many employees had submitted their forms, how many had marked attendance, and how many came from the Delhi office. Three different counting needs. Three different formulas. She did not know any of them. Here they are.
Formula 7 – COUNT: Count Cells With Numbers
COUNT counts how many cells in a range contain numbers. It ignores empty cells and cells with text. Use it to count how many numeric entries exist in a column.
Syntax:
=COUNT(value1, [value2], ...)
=COUNT(B2:B100)| Cell | Data / Context | Formula | Result |
| D2 | How many rows have a sales number | =COUNT(B2:B50) | 42 |
| E2 | Count of numeric test scores | =COUNT(C2:C30) | 28 |
Formula 8 – COUNTA: Count All Non-Empty Cells
COUNTA counts every cell that has any content – numbers, text, dates, anything. Empty cells are not counted. Use this when you want to know how many rows have been filled in.
Syntax:
=COUNTA(value1, [value2], ...)
=COUNTA(A2:A100)| Cell | Data / Context | Formula | Result |
| F2 | How many employees submitted forms | =COUNTA(A2:A200) | 163 |
| G2 | How many rows have any data | =COUNTA(B2:B500) | 487 |
COUNT vs COUNTA: COUNT only counts numbers. COUNTA counts everything (numbers, text, dates). If your column has names (text), COUNT gives 0 but COUNTA gives the correct count. Use COUNT for numeric columns like scores or amounts. Use COUNTA for name columns or any mixed data.
Formula 9 – COUNTIF: Count Cells That Meet One Condition
COUNTIF is the counting version of SUMIF. It counts how many cells in a range meet a condition you specify. This is incredibly useful for quick data analysis without pivot tables.
Syntax:
=COUNTIF(range, criteria)
range = the column to check
criteria = the condition to count| Cell | Data / Context | Formula | Result |
| E2 | How many employees from Delhi | =COUNTIF(C2:C100,”Delhi”) | 34 |
| F2 | How many sales above ₹10,000 | =COUNTIF(D2:D200,”>10000″) | 67 |
| G2 | How many rows say Pass | =COUNTIF(E2:E60,”Pass”) | 48 |
| H2 | How many cells are empty | =COUNTIF(A2:A100,””) | 12 |
Formula 10 – COUNTIFS: Count With Multiple Conditions
COUNTIFS is COUNTIF with more power – it lets you apply two or more conditions at the same time. Only rows that meet ALL conditions are counted.
Syntax:
=COUNTIFS(range1, criteria1, range2, criteria2, ...)| Cell | Data / Context | Formula | Result |
| J2 | Employees from Delhi who are Active | =COUNTIFS(C2:C100,”Delhi”,D2:D100,”Active”) | 21 |
| K2 | Sales above 10k from North region | =COUNTIFS(B2:B100,”>10000″,E2:E100,”North”) | 15 |
Chapter 4: Condition Formulas – Teach Excel to Think
These formulas give Excel the ability to make decisions. Instead of you reading every row and deciding what to write, these formulas do it automatically for every row at once.
Formula 11 – IF: Make a Decision Based on a Condition
IF checks a condition. If the condition is TRUE it shows one result, if FALSE it shows another. This is covered in full detail in our separate IF Formula guide – here is a quick recap.
Syntax:
=IF(logical_test, value_if_true, value_if_false)| Cell | Data / Context | Formula | Result |
| C2 | Score in B2 = 78 | =IF(B2>=50,”Pass”,”Fail”) | Pass |
| D2 | Sales in C2 = ₹95,000 | =IF(C2>100000,”Bonus”,”No Bonus”) | No Bonus |
| E2 | Stock in D2 = 30 units | =IF(D2<50,”Reorder”,”OK”) | Reorder |
Formula 12 – IFS: Multiple Conditions, Cleaner Code
IFS lets you check multiple conditions without nesting IF inside IF. Available in Excel 2019 and Microsoft 365 only.
Syntax:
=IFS(condition1, result1, condition2, result2, ...| Cell | Data / Context | Formula | Result |
| C2 | Marks = 88 | =IFS(B2>=85,”A”,B2>=70,”B”,B2>=55,”C”,TRUE,”F”) | A |
| D3 | Attendance = 72% | =IFS(C3>90,”Bonus”,C3>=75,”Normal”,TRUE,”Warning”) | Warning |
Formula 13 – AND: All Conditions Must Be True
AND returns TRUE only when every condition inside it is true. Usually used inside an IF to combine two or more conditions.
Syntax:
=AND(condition1, condition2, ...)
=IF(AND(B2>50, C2>80), "Eligible", "Not Eligible")| Cell | Data / Context | Formula | Result |
| D2 | Score=72, Attendance=88% | =IF(AND(B2>50,C2>80),”Eligible”,”Not Eligible”) | Eligible |
| E3 | Score=45, Attendance=90% | =IF(AND(B3>50,C3>80),”Eligible”,”Not Eligible”) | Not Eligible |
Formula 14 – OR: At Least One Condition Must Be True
OR returns TRUE if any ONE condition is true, even if the others are false. Use it when meeting just one requirement is enough.
Syntax:
=OR(condition1, condition2, ...)
=IF(OR(B2="VIP",C2>50000),"Priority","Regular")| Cell | Data / Context | Formula | Result |
| E2 | Type=Regular, Order=₹65,000 | =IF(OR(B2=”VIP”,C2>50000),”Priority”,”Regular”) | Priority |
| F3 | Type=Regular, Order=₹8,000 | =IF(OR(B3=”VIP”,C3>50000),”Priority”,”Regular”) | Regular |
Chapter 5: Lookup Formulas – Search and Find Data Instantly
Ankit’s Customer Problem, Ankit works in the accounts department. Every morning he receives a list of 50 invoice numbers and needs to find the customer name and amount for each one from a master sheet of 3,000 rows. Without a lookup formula, this means scrolling, searching, and copying manually – which takes 40 minutes. With VLOOKUP, it takes 2 minutes. This chapter is Ankit’s favourite.
Formula 15 – VLOOKUP: Search a Column and Return a Value
VLOOKUP stands for Vertical Lookup. You give it a search value, it finds it in the first column of a table, and returns a value from any other column in that same row. It is the most famous lookup formula in Excel.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value = what you are searching for
table_array = the full data table to search in
col_index_num = which column number to return (1=first, 2=second...)
range_lookup = FALSE for exact match (always use FALSE for beginners)| Cell | Data / Context | Formula | Result |
| D2 | Find customer name for Invoice 1042 | =VLOOKUP(C2,A2:E200,2,FALSE) | Ravi Sharma |
| E2 | Find amount for Employee ID 2055 | =VLOOKUP(D2,A2:F500,4,FALSE) | ₹45,200 |
| F2 | Find department for staff code | =VLOOKUP(B2,StaffTable,3,FALSE) | Finance |
Always Use FALSE for Exact Match, VLOOKUP’s fourth argument controls match type. Always type FALSE (or 0) unless you specifically need approximate matching. TRUE/1 is the default but it can return wrong results on unsorted data – a very common beginner error that causes incorrect reports.
Formula 16 – XLOOKUP: The Modern Replacement for VLOOKUP
XLOOKUP is available in Excel 2021 and Microsoft 365. It is smarter than VLOOKUP – it can search in any direction, return multiple columns, handle errors natively, and does not break when you add or delete columns.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])| Cell | Data / Context | Formula | Result |
| D2 | Find dept for Emp ID in A2 | =XLOOKUP(A2,B2:B500,D2:D500,”Not Found”) | Operations |
| E3 | Search LEFT – name from ID | =XLOOKUP(C3,D2:D100,A2:A100,”Not Found”) | Priya Mehta |
VLOOKUP vs XLOOKUP – Which Should You Learn?
If you are on Microsoft 365 or Excel 2021, learn XLOOKUP – it is easier and more powerful. If you are on Excel 2016 or 2019, learn VLOOKUP. Both solve the same problem – finding data in a table. See our dedicated XLOOKUP guide for the full deep-dive.
Chapter 6: Text Formulas – Work With Words and Names
Neha’s Data Cleaning Problem, Neha received a customer database where full names were all in one column. Her CRM needed first name and last name in separate columns. She also had phone numbers mixed with text, email addresses with extra spaces, and product codes buried inside longer strings. Her manager told her to clean it manually – 800 rows. Neha found the text formulas and cleaned the entire file in 15 minutes.
Formula 17 – LEFT, RIGHT, MID: Extract Parts of Text
These three formulas extract a specific number of characters from text – from the left side, right side, or any position in the middle.
Syntax:
=LEFT(text, num_chars) ← extract from the LEFT
=RIGHT(text, num_chars) ← extract from the RIGHT
=MID(text, start_num, num_chars) ← extract from the MIDDLE| Cell | Data / Context | Formula | Result |
| B2 | A2 = “Ravi Sharma” | =LEFT(A2, 4) | Ravi |
| C2 | A2 = “PROD-2024-MUM” | =RIGHT(A2, 3) | MUM |
| D2 | A2 = “INV-2024-0891” | =MID(A2, 5, 4) | 2024 |
| E2 | A2 = “9876543210” | =LEFT(A2, 5) | 98765 |
Formula 18 – LEN and TRIM: Measure and Clean Text
LEN counts the number of characters in a cell – including spaces. TRIM removes all extra spaces from text – leading spaces, trailing spaces, and double spaces between words, leaving only single spaces between words.
Syntax:
=LEN(text) ← count total characters
=TRIM(text) ← remove extra spaces
| Cell | Data / Context | Formula | Result |
| B2 | A2 = “Hello World” | =LEN(A2) | 11 |
| C3 | A3 = ” Ravi Sharma “ | =TRIM(A3) | Ravi Sharma |
| D4 | A4 = ” invoice “ | =LEN(TRIM(A4)) | 7 |
Formula 19 – CONCATENATE and & : Join Text Together
CONCATENATE joins two or more pieces of text into one. The simpler modern way is to use the & symbol, which does the same thing and is faster to type.
Syntax:
=CONCATENATE(text1, text2, ...) ← older method
=A2&" "&B2 ← modern method using & symbol
=CONCAT(text1, text2, ...) ← newest method (Excel 2019+)| Cell | Data / Context | Formula | Result |
| C2 | A2=Ravi, B2=Sharma | =A2&” “&B2 | Ravi Sharma |
| D3 | A3=INV, B3=2024 | =A3&”-“&B3 | INV-2024 |
| E4 | A4=+91, B4=9876543210 | =A4&B4 | +919876543210 |
TEXTJOIN for Multiple Cells, Need to join 10 names from A2:A10 with commas? Use =TEXTJOIN(“, “, TRUE, A2:A10). Much faster than &-ing every cell individually. Available in Excel 2019 and Microsoft 365.
Chapter 7: Date Formulas – Work Smarter With Time
Dates are everywhere in business data – joining dates, order dates, deadlines, expiry dates, payment due dates. These formulas make date calculations effortless.
Formula 20 – TODAY and NOW: Get the Current Date and Time
TODAY returns today’s date. NOW returns today’s date and the current time. Both update automatically every time the file is opened or recalculated. No arguments needed – just type the function name with empty brackets.
Syntax:
=TODAY() ← returns today's date (e.g., 20/03/2026)
=NOW() ← returns today's date and time (e.g., 20/03/2026 14:35)| Cell | Data / Context | Formula | Result |
| B2 | Any cell | =TODAY() | 20/03/2026 |
| C2 | Days since joining (join=01/01/2024) | =TODAY()-B2 | 445 days |
| D2 | Is today past the deadline in A2? | =IF(TODAY()>A2,”Expired”,”Valid”) | Valid / Expired |
Formula 21 – DATEDIF: Calculate the Difference Between Two Dates
DATEDIF calculates the difference between two dates in years, months, or days. It is the hidden gem of date formulas – extremely useful for HR teams calculating employee tenure and age.
Syntax:
=DATEDIF(start_date, end_date, unit)
unit = "Y" → complete years
unit = "M" → complete months
unit = "D" → total days| Cell | Data / Context | Formula | Result |
| D2 | Joining=01/01/2021, Today=20/03/2026 | =DATEDIF(B2,TODAY(),”Y”) | 5 years |
| E2 | Order date to delivery date | =DATEDIF(B2,C2,”D”) | 14 days |
| F2 | Contract start to end | =DATEDIF(A2,B2,”M”) | 18 months |
Formula 22 – IFERROR: Handle Errors Gracefully
IFERROR catches formula errors – like #N/A, #DIV/0!, #VALUE! – and replaces them with a clean, friendly message instead of showing a red error to your manager or client.
Syntax:
=IFERROR(value, value_if_error)
value = your formula that might produce an error
value_if_error = what to show instead of the error| Cell | Data / Context | Formula | Result |
| C2 | VLOOKUP for ID not in table | =IFERROR(VLOOKUP(A2,B:D,2,FALSE),”Not Found”) | Not Found |
| D3 | Dividing by zero | =IFERROR(A3/B3, 0) | 0 |
| E4 | Nested with XLOOKUP | =IFERROR(XLOOKUP(A4,C:C,D:D),”Missing”) | Missing |
Always Wrap Lookup Formulas in IFERROR, Any formula that searches for data – VLOOKUP, XLOOKUP, MATCH – can return #N/A if the value is not found. Always wrap them: =IFERROR(VLOOKUP(…), “Not Found”). This makes your reports look professional instead of full of red error marks.
Chapter 8: The Master Reference Table – All 20+ Formulas at a Glance
Here is your complete quick-reference table. Bookmark this page, screenshot it, or print it and keep it on your desk. This one table replaces hours of Googling.
| #No. | Formula Name | Syntax | What It Does | Example | Result |
|---|---|---|---|---|---|
| #01 | SUM | =SUM(range) | Add all numbers | =SUM(A2:A50) | ₹1,85,000 |
| #02 | AVERAGE | =AVERAGE(range) | Find the mean | =AVERAGE(B2:B12) | 72.4 |
| #03 | MIN | =MIN(range) | Lowest value | =MIN(C2:C100) | ₹18,500 |
| #04 | MAX | =MAX(range) | Highest value | =MAX(C2:C100) | ₹1,25,000 |
| #05 | ROUND | =ROUND(num,digits) | Round to N decimals | =ROUND(3.14159,2) | 3.14 |
| #06 | SUMIF | =SUMIF(rng,crit,sum_rng) | Sum if condition met | =SUMIF(C:C,”North”,D:D) | ₹2,45,800 |
| #07 | COUNT | =COUNT(range) | Count numeric cells | =COUNT(B2:B50) | 42 |
| #08 | COUNTA | =COUNTA(range) | Count non-empty cells | =COUNTA(A2:A200) | 163 |
| #09 | COUNTIF | =COUNTIF(range,criteria) | Count if condition met | =COUNTIF(C:C,”Delhi”) | 34 |
| #10 | COUNTIFS | =COUNTIFS(r1,c1,r2,c2) | Count – multiple conditions | =COUNTIFS(C:C,”Delhi”,D:D,”Active”) | 21 |
| #11 | IF | =IF(test,true,false) | Decision – 2 outcomes | =IF(B2>=50,”Pass”,”Fail”) | Pass |
| #12 | IFS | =IFS(c1,r1,c2,r2,…) | Decision – many outcomes | =IFS(B2>=85,”A”,TRUE,”B”) | A |
| #13 | AND | =AND(cond1,cond2) | All conditions must match | =IF(AND(B2>50,C2>80),”Yes”,”No”) | Yes |
| #14 | OR | =OR(cond1,cond2) | Any condition must match | =IF(OR(B2=”VIP”,C2>50000),”P”,”R”) | P |
| #15 | VLOOKUP | =VLOOKUP(val,tbl,col,0) | Search column → return value | =VLOOKUP(A2,B:E,2,FALSE) | Ravi Sharma |
| #16 | XLOOKUP | =XLOOKUP(val,lkp,ret) | Modern lookup, any direction | =XLOOKUP(A2,B:B,D:D,”None”) | Finance |
| #17 | LEFT/RIGHT/MID | =LEFT(text,n) | Extract part of text | =LEFT(“Ravi Sharma”,4) | Ravi |
| #18 | LEN / TRIM | =LEN(text) / =TRIM(text) | Count chars / clean spaces | =TRIM(” Hello “) | Hello |
| #19 | CONCATENATE / & | =A2&” “&B2 | Join text together | =”INV”&”-“&”2024” | INV-2024 |
| #20 | TODAY / NOW | =TODAY() / =NOW() | Current date / date+time | =TODAY() | 20/03/2026 |
| #21 | DATEDIF | =DATEDIF(start,end,”Y”) | Difference between dates | =DATEDIF(B2,TODAY(),”Y”) | 5 years |
| #22 | IFERROR | =IFERROR(formula,”msg”) | Handle formula errors | =IFERROR(VLOOKUP(…),’N/A’) | Not Found |
Chapter 9: Common Mistakes Beginners Make – and How to Fix Them
Every beginner makes these mistakes. Knowing them in advance saves hours of frustration and avoids the embarrassment of submitting wrong reports.
| Mistake | What It Looks Like | Problem | How to Fix It |
| Forgetting the = sign | SUM(A2:A10) | Excel shows it as text, no calculation | Always start: =SUM(A2:A10) |
| Text without quotes | =IF(A2>50,Pass,Fail) | Excel cannot find ‘Pass’ or ‘Fail’ | Use quotes: “Pass”, “Fail” |
| Wrong VLOOKUP col number | =VLOOKUP(A2,B:F,6,0) | If table only has 5 columns → #REF error | Count columns carefully; col 1 = first column of your table_array |
| VLOOKUP without FALSE | =VLOOKUP(A2,B:D,2) | Approximate match – returns wrong value on unsorted data | Always add ,FALSE or ,0 as 4th argument |
| Spaces inside cell values | COUNTIF finding 0 results | Cell has ” Delhi” (space before) vs “Delhi” | Use TRIM to clean data first, then apply formula |
| Circular reference | =SUM(A1:A5) typed in A3 | Formula refers to its own cell | Move formula to a different cell (e.g., A6) |
| Hardcoding values | =SUM(100,200,300) | Formula must be retyped if data changes | Reference cells: =SUM(A1:A3) |
| Range not locked when copying | =VLOOKUP(A2,B2:D20,2,0) | Range B2:D20 shifts when copied down | Lock range: =VLOOKUP(A2,$B$2:$D$20,2,0) |
The $ Sign – Absolute vs Relative References, When you copy a formula down, cell references shift automatically (A2 becomes A3, A4, etc.). Sometimes you want a reference to stay fixed – like a lookup table. Add $ signs to lock it: $B$2:$D$100 means that range never moves no matter where you copy the formula. Press F4 while your cursor is on a reference to add $ signs automatically.
Chapter 10: 10 Pro Tips for Writing Better Excel Formulas
- Start simple, then add complexity. Write =SUM(A2:A10) first. Confirm it works. Then build =SUMIF(B2:B10,”North”,A2:A10). Small steps prevent large errors.
- Name your ranges. Instead of =SUM(D2:D100), name the range ‘MonthlySales’ in Formulas > Name Manager. Then write =SUM(MonthlySales). Readable, maintainable, and self-documenting.
- Use Ctrl + ` to show all formulas. Press Ctrl and the backtick key (`) to toggle between showing formula results and showing the actual formula text. Perfect for reviewing your work or debugging.
- Press F2 to enter edit mode. Click a cell with a formula and press F2. Excel highlights all the cell references in different colours – each colour corresponds to a different range. Makes debugging visual.
- Use structured table references. Convert your data to an Excel Table (Ctrl+T). Then reference Table1[Sales] instead of D2:D100. These references expand automatically as you add rows.
- Evaluate formulas step by step. Go to Formulas > Evaluate Formula. Excel walks through your formula one step at a time, showing intermediate results. This is the most powerful debugging tool for complex formulas.
- Test on 5 rows before applying to 5,000. Always test any new formula on a small, manual-checkable sample. Confirm every result is correct before copying it down to the full dataset.
- Combine IFERROR with every lookup. Every VLOOKUP, XLOOKUP, or MATCH can fail if a value is not found. Always wrap: =IFERROR(yourformula, “Not Found”). Makes reports professional and error-free.
- Learn keyboard shortcuts for formulas. Alt+= inserts SUM instantly. Ctrl+Shift+Enter for array formulas. Tab to autocomplete a formula name. F4 to lock references. These save dozens of clicks per day.
- Read the formula bar out loud. Before pressing Enter, read your formula aloud: “IF cell B2 is greater than 50, show Pass, otherwise show Fail.” If the sentence makes sense, the formula is probably correct.
Frequently Asked Questions
Start with SUM, AVERAGE, COUNT, and IF – in that order. These four formulas cover 60% of what most beginners need for daily work. Once you are comfortable with these, add VLOOKUP (or XLOOKUP), SUMIF, and COUNTIF.
In casual usage, people use these words interchangeably. Technically, a function is a built-in operation like SUM or IF. A formula is the complete expression you type in a cell – like =SUM(A2:A10). Every function becomes part of a formula when you type it in a cell with an = sign.
Basic formulas (SUM, AVERAGE, IF, VLOOKUP, etc.) work in all Excel versions including Excel 2010 onwards. Some newer formulas have restrictions: IFS, CONCAT, and TEXTJOIN need Excel 2019 or later. XLOOKUP and dynamic array formulas need Excel 2021 or Microsoft 365.
Two possible reasons: (1) You forgot the = sign – without it, Excel treats the text as plain content, not a formula. (2) The cell is formatted as ‘Text’. Fix this by selecting the cell, going to Home > Number Format > General, then re-enter the formula.
Absolutely – and this is where Excel becomes truly powerful. You can nest formulas: =IFERROR(VLOOKUP(A2,Sheet2!A:C,2,FALSE),”Not Found”). You can use formula results inside other formulas: =IF(SUM(B2:B10)>100000,”Target Met”,”Below Target”). Combining formulas is how experts build complex reports.
Knowing 15 to 20 formulas well is enough to be genuinely useful in any office role. The 22 formulas in this guide cover virtually everything an MIS executive, HR professional, accountant, or sales analyst needs for daily work. Depth of understanding matters more than breadth – it is better to know 15 formulas deeply than to have a vague memory of 50.
Microsoft offers Excel for the web completely free at office.com – no installation needed. Google Sheets supports most Excel formulas and is free. You can also download sample datasets from Kaggle or data.gov.in and practice on real data.
Summary – Anil’s Report, Done in 8 Minutes
Anil, Six Months Later, Do you remember Anil from the beginning – the Junior MIS Analyst who spent four hours doing manually what could have been done in 8 minutes? Six months later, Anil is the person his entire office comes to for Excel help.
When a new colleague struggled with a report, Anil sat with them for 45 minutes and taught them the same 22 formulas you just learned. ‘The formulas are simple,’ Anil told them. ‘The only thing that makes it hard is not knowing where to start. Now you know exactly where to start.’
| Category | Formulas Learned | What They Help You Do |
| Math & Statistics | SUM, AVERAGE, MIN, MAX, ROUND, SUMIF | Calculate totals, averages, and conditional sums |
| Counting | COUNT, COUNTA, COUNTIF, COUNTIFS | Analyse data frequency and meet-condition counts |
| Decision Making | IF, IFS, AND, OR | Automate classifications, flags, and status labels |
| Lookup & Search | VLOOKUP, XLOOKUP | Find and retrieve data from large tables instantly |
| Text Handling | LEFT, RIGHT, MID, LEN, TRIM, CONCAT | Clean, extract, and join text and names |
| Dates & Time | TODAY, NOW, DATEDIF | Calculate tenure, age, deadlines, and due dates |
| Error Handling | IFERROR | Make all reports clean and professional |
Every formula in this guide is one you will use in real work – not just in practice exercises. Save this guide, refer back to the master table whenever you need a quick reminder, and most importantly, open Excel right now and try one formula on your own data.
The best way to learn Excel formulas is not to read about them. It is to type them yourself, make a mistake, fix the mistake, and understand why it works. That process – type, test, fix, understand – is how every Excel expert actually learned. You now have the starting point they all wished they had.
Free Excel Tools at ibusinessmotivation.com Need to automate Excel tasks without writing formulas? Visit ibusinessmotivation.com for free browser-based tools: Multiple Excel File Merger, Excel Data Cleaner, Excel Worksheet Split Tool, and 20+ more. No installation, no technical knowledge, no signup required.
