Talk to our expert

+91 98211 15117

Sign In
Join for Free
Edit Content
Sign In
Join for Free
Open App

Recent Posts

Share this Post

Complete Excel Formulas Guide for Students

Basic Mathematical Operations

Formula Syntax Description Example Result
SUM =SUM(range) Adds all numbers in a range =SUM(A1:A5) Sum of cells A1 through A5
SUBTRACT =A1-B1 Subtracts one number from another =10-5 5
MULTIPLY =A1*B1 Multiplies two or more numbers =5*3 15
DIVIDE =A1/B1 Divides one number by another =10/2 5
PERCENTAGE =(Part/Total)*100 Calculates percentage =(25/100)*100 25%
POWER =POWER(number,power) Raises number to specified power =POWER(2,3) 8
SQRT =SQRT(number) Returns square root =SQRT(16) 4
ABS =ABS(number) Returns absolute value =ABS(-5) 5
ROUND =ROUND(number,digits) Rounds number to specified decimal places =ROUND(3.14159,2) 3.14
MOD =MOD(number,divisor) Returns remainder after division =MOD(10,3) 1
NEET 2026 crash course for Class 12 students with NCERT focused preparation
NEET 2026 crash course designed for focused NCERT-based revision and exam readiness.

Statistical Functions

Formula Syntax Description Example Use Case
AVERAGE =AVERAGE(range) Calculates arithmetic mean =AVERAGE(A1:A10) Grade point calculations
MEDIAN =MEDIAN(range) Finds middle value in dataset =MEDIAN(A1:A10) Central tendency analysis
MODE =MODE.SNGL(range) Most frequently occurring value =MODE.SNGL(A1:A10) Survey data analysis
COUNT =COUNT(range) Counts cells containing numbers =COUNT(A1:A10) Data validation
COUNTA =COUNTA(range) Counts non-empty cells =COUNTA(A1:A10) Attendance tracking
COUNTIF =COUNTIF(range,criteria) Counts cells meeting criteria =COUNTIF(A1:A10,">50") Grade analysis
MAX =MAX(range) Returns largest value =MAX(A1:A10) Highest score identification
MIN =MIN(range) Returns smallest value =MIN(A1:A10) Lowest score identification
STDEV =STDEV.S(range) Standard deviation (sample) =STDEV.S(A1:A10) Data spread analysis
VAR =VAR.S(range) Variance (sample) =VAR.S(A1:A10) Variability measurement

Lookup and Reference Functions

Formula Syntax Description Example Application
VLOOKUP =VLOOKUP(lookup_value,table_array,col_index,FALSE) Vertical lookup in table =VLOOKUP("John",A1:C10,2,FALSE) Student grade lookup
HLOOKUP =HLOOKUP(lookup_value,table_array,row_index,FALSE) Horizontal lookup in table =HLOOKUP("Math",A1:E3,2,FALSE) Subject score lookup
INDEX =INDEX(array,row,column) Returns value at specific position =INDEX(A1:C10,5,2) Data retrieval
MATCH =MATCH(lookup_value,array,0) Finds position of value =MATCH("John",A1:A10,0) Position finding
XLOOKUP =XLOOKUP(lookup_value,lookup_array,return_array) Enhanced lookup function =XLOOKUP("John",A:A,B:B) Modern data lookup
CHOOSE =CHOOSE(index,value1,value2,...) Returns value from list =CHOOSE(2,"A","B","C") Option selection

Text and String Functions

Formula Syntax Description Example Purpose
CONCATENATE =CONCATENATE(text1,text2,...) Joins text strings =CONCATENATE("Hello"," ","World") Text combination
CONCAT =CONCAT(text1,text2,...) Modern text joining =CONCAT(A1," ",B1) Name formatting
LEFT =LEFT(text,num_chars) Extracts leftmost characters =LEFT("Excel",2) “Ex”
RIGHT =RIGHT(text,num_chars) Extracts rightmost characters =RIGHT("Excel",3) “cel”
MID =MID(text,start,length) Extracts middle characters =MID("Excel",2,3) “xce”
LEN =LEN(text) Returns text length =LEN("Excel") 5
UPPER =UPPER(text) Converts to uppercase =UPPER("excel") “EXCEL”
LOWER =LOWER(text) Converts to lowercase =LOWER("EXCEL") “excel”
PROPER =PROPER(text) Capitalizes first letters =PROPER("john smith") “John Smith”
TRIM =TRIM(text) Removes extra spaces =TRIM(" Excel ") “Excel”

Date and Time Functions

Formula Syntax Description Example Application
TODAY =TODAY() Current date =TODAY() Current date stamp
NOW =NOW() Current date and time =NOW() Timestamp
DATE =DATE(year,month,day) Creates date =DATE(2024,12,25) Specific date creation
YEAR =YEAR(date) Extracts year =YEAR(TODAY()) Age calculations
MONTH =MONTH(date) Extracts month =MONTH(TODAY()) Monthly analysis
DAY =DAY(date) Extracts day =DAY(TODAY()) Daily tracking
WEEKDAY =WEEKDAY(date) Day of week number =WEEKDAY(TODAY()) Schedule planning
DATEDIF =DATEDIF(start_date,end_date,"Y") Date difference =DATEDIF(A1,B1,"Y") Age calculation
WORKDAY =WORKDAY(start_date,days) Business days calculation =WORKDAY(TODAY(),10) Project timelines

Logical Functions

Formula Syntax Description Example Use Case
IF =IF(condition,true_value,false_value) Conditional logic =IF(A1>50,"Pass","Fail") Grade evaluation
AND =AND(condition1,condition2,...) All conditions true =AND(A1>0,B1<100) Multiple criteria
OR =OR(condition1,condition2,...) Any condition true =OR(A1>90,B1>90) Alternative criteria
NOT =NOT(condition) Opposite of condition =NOT(A1="") Non-empty check
IFS =IFS(condition1,value1,condition2,value2,...) Multiple conditions =IFS(A1>=90,"A",A1>=80,"B") Grade scaling
IFERROR =IFERROR(value,value_if_error) Error handling =IFERROR(A1/B1,"Division Error") Error management
ISBLANK =ISBLANK(cell) Checks if cell is empty =ISBLANK(A1) Data validation
ISNUMBER =ISNUMBER(cell) Checks if cell contains number =ISNUMBER(A1) Data type validation

Financial Functions

Formula Syntax Description Example Application
PMT =PMT(rate,nper,pv) Loan payment calculation =PMT(0.05/12,60,-10000) Monthly loan payment
PV =PV(rate,nper,pmt) Present value =PV(0.08,10,-1000) Investment valuation
FV =FV(rate,nper,pmt,pv) Future value =FV(0.06,10,-500) Savings projection
NPV =NPV(rate,value1,value2,...) Net present value =NPV(0.1,A1:A5) Investment analysis
IRR =IRR(values) Internal rate of return =IRR(A1:A5) Return calculation
RATE =RATE(nper,pmt,pv) Interest rate =RATE(48,-200,8000) Loan rate finding

Data Analysis Functions

Formula Syntax Description Example Purpose
SUMIF =SUMIF(range,criteria,sum_range) Conditional sum =SUMIF(A:A,"John",B:B) Filtered totals
SUMIFS =SUMIFS(sum_range,criteria_range1,criteria1,...) Multiple criteria sum =SUMIFS(C:C,A:A,"John",B:B,">50") Complex filtering
AVERAGEIF =AVERAGEIF(range,criteria,average_range) Conditional average =AVERAGEIF(A:A,"Math",B:B) Subject averages
COUNTIFS =COUNTIFS(criteria_range1,criteria1,...) Multiple criteria count =COUNTIFS(A:A,"John",B:B,">80") Complex counting
SUBTOTAL =SUBTOTAL(function_num,range) Filtered data calculations =SUBTOTAL(109,A1:A10) Filtered sums
RANK =RANK(number,array,order) Ranking values =RANK(A1,A:A,0) Performance ranking

Essential Keyboard Shortcuts

Shortcut Function Description
Ctrl + ; Insert current date Quick date entry
Ctrl + Shift + ; Insert current time Quick time entry
F2 Edit cell Enter edit mode
F4 Repeat last action / Toggle references Efficiency tool
Ctrl + C Copy Standard copy function
Ctrl + V Paste Standard paste function
Ctrl + Z Undo Reverse last action
Ctrl + Y Redo Repeat undone action
Ctrl + A Select all Full selection
Ctrl + S Save File saving
Ctrl + Home Go to cell A1 Quick navigation
Ctrl + End Go to last used cell Navigate to data end
Shift + Space Select entire row Row selection
Ctrl + Space Select entire column Column selection
Alt + = AutoSum Quick sum formula

Formula Writing Best Practices

1. Absolute vs Relative References

  • Relative Reference: A1 (changes when copied)
  • Absolute Reference: $A$1 (stays fixed when copied)
  • Mixed Reference: $A1 or A$1 (partially fixed)

2. Error Prevention Tips

  • Always use parentheses for complex calculations
  • Check data types before applying formulas
  • Use IFERROR for robust formulas
  • Validate input ranges

3. Performance Optimization

  • Avoid volatile functions when possible (NOW, TODAY)
  • Use structured references in tables
  • Minimize array formulas in large datasets
  • Consider XLOOKUP over VLOOKUP for better performance

4. Common Formula Errors

  • #DIV/0!: Division by zero
  • #VALUE!: Wrong data type
  • #REF!: Invalid cell reference
  • #N/A: Value not available
  • #NAME?: Unrecognized formula name

Academic Applications by Subject

Mathematics

  • Statistical analysis using AVERAGE, STDEV, VAR
  • Geometric calculations with POWER, SQRT
  • Probability distributions using statistical functions

Business Studies

  • Financial modeling with PMT, NPV, IRR
  • Sales analysis using SUMIF, COUNTIF
  • Performance tracking with RANK, PERCENTILE

Science

  • Data analysis using statistical functions
  • Experimental result calculations
  • Graph preparation with mathematical formulas

Economics

  • Financial calculations and projections
  • Market analysis using lookup functions
  • Trend analysis with statistical tools

Frequently Asked Questions (FAQs)

Q. How do I use the VLOOKUP formula in Excel?

VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value in the same row from another column.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Step-by-Step Example:

  • Suppose you have student names in column A and their marks in column B
  • To find John’s marks: =VLOOKUP("John", A2:B10, 2, FALSE)
  • The formula searches for “John” in column A and returns the corresponding value from column 2 (column B)
  • Use FALSE for exact match, TRUE for approximate match

Common Tips:

  • The lookup column must always be the leftmost column
  • Use absolute references ($) for table_array when copying formulas
  • Always check for spelling errors in lookup values

Q. What is the formula to calculate percentage in Excel?

Excel calculates percentages using basic division and multiplication formulas.

Basic Percentage Formula: =(Part/Total)*100

Common Percentage Calculations:

Calculation Type Formula Example
Percentage of Total =(A1/B1)*100 =(50/200)*100 = 25%
Percentage Increase =((New-Old)/Old)*100 =((120-100)/100)*100 = 20%
Percentage Decrease =((Old-New)/Old)*100 =((100-80)/100)*100 = 20%
Add Percentage =A1*(1+B1%) =100*(1+10%) = 110
Subtract Percentage =A1*(1-B1%) =100*(1-10%) = 90

Formatting Tip: Select cells and click the “%” button on the Home tab or press Ctrl+Shift+% to format as percentage.

Q. What are the 10 most important Excel formulas for beginners?

Here are the essential formulas every Excel beginner should master:

  1. SUM: =SUM(A1:A10) – Adds numbers in a range
  2. AVERAGE: =AVERAGE(A1:A10) – Calculates mean value
  3. COUNT: =COUNT(A1:A10) – Counts cells with numbers
  4. IF: =IF(A1>50,"Pass","Fail") – Makes logical decisions
  5. VLOOKUP: =VLOOKUP("John",A:B,2,FALSE) – Searches and retrieves data
  6. SUMIF: =SUMIF(A:A,"Math",B:B) – Conditional summation
  7. CONCATENATE/CONCAT: =CONCAT(A1," ",B1) – Combines text
  8. MAX/MIN: =MAX(A1:A10) – Finds highest/lowest values
  9. COUNTIF: =COUNTIF(A:A,">50") – Counts based on criteria
  10. TODAY: =TODAY() – Inserts current date

Learning Path: Start with SUM and AVERAGE, then progress to IF statements and VLOOKUP as you gain confidence.

Q. How do I write an IF formula with multiple conditions in Excel?

Excel provides several ways to create IF formulas with multiple conditions.

Method 1: Nested IF (for sequential conditions)

=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F"))))

Method 2: IFS Function (cleaner for multiple conditions)

=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", A1>=60,"D", A1<60,"F")

Method 3: IF with AND (all conditions must be true)

=IF(AND(A1>=60, B1>=60, C1>=60),"Pass","Fail")

Method 4: IF with OR (any condition can be true)

=IF(OR(A1>=90, B1>=90, C1>=90),"Excellent","Good")

Best Practices:

  • Use IFS instead of nested IF for better readability
  • AND requires ALL conditions to be true
  • OR requires AT LEAST ONE condition to be true
  • Always include a final “else” value

Q. What is the difference between COUNT, COUNTA, and COUNTIF?

These three functions count cells differently based on specific criteria.

Function What It Counts Syntax Example
COUNT Only cells with numbers =COUNT(range) =COUNT(A1:A10) counts numeric cells
COUNTA All non-empty cells =COUNTA(range) =COUNTA(A1:A10) counts text and numbers
COUNTIF Cells meeting criteria =COUNTIF(range,criteria) =COUNTIF(A1:A10,">50") counts cells >50

Practical Examples:

COUNT:

  • Use for counting test scores, sales figures, or any numerical data
  • Example: =COUNT(B2:B50) counts how many students submitted scores

COUNTA:

  • Use for attendance tracking, survey responses, or any non-empty cells
  • Example: =COUNTA(A2:A50) counts how many students are registered

COUNTIF:

  • Use for conditional counting like pass/fail, above/below threshold
  • Example: =COUNTIF(B2:B50,">=60") counts passing grades
  • Example: =COUNTIF(A2:A50,"John") counts how many times “John” appears

Advanced Tip: Use COUNTIFS for multiple criteria: =COUNTIFS(A:A,"Math",B:B,">80") counts Math scores above 80.

Q. How do I remove or fix #VALUE!, #DIV/0!, and other formula errors in Excel?

Excel displays various error messages when formulas encounter problems. Here’s how to identify and fix them.

Common Excel Errors and Solutions:

Error Meaning Common Cause Solution
#DIV/0! Division by zero Dividing by empty cell or zero Use =IFERROR(A1/B1,"Error") or =IF(B1=0,"",A1/B1)
#VALUE! Wrong data type Text in numeric calculation Check cell contents, use VALUE() function
#REF! Invalid reference Deleted rows/columns in formula Update formula references
#N/A Value not found VLOOKUP can’t find match Verify lookup value exists, check spelling
#NAME? Formula not recognized Misspelled function name Check formula spelling
#NUM! Invalid number Calculation too large/small Check calculation logic
#NULL! Incorrect range Wrong range operator Use colon (:) not comma for ranges

Universal Error Handler:

=IFERROR(your_formula, "friendly_message")

Example:

=IFERROR(VLOOKUP(A1,Table,2,FALSE),"Not Found")

Prevention Tips:

  • Always validate data before applying formulas
  • Use IFERROR wrapper for user-facing spreadsheets
  • Check for empty cells in calculations
  • Ensure consistent data types in ranges

This comprehensive guide covers essential Excel formulas for academic and professional use. Regular practice with these formulas will enhance your data analysis capabilities and academic performance.

Note: Always verify formulas with sample data before using them in important calculations. Excel formula syntax may vary slightly between different versions of Microsoft Excel.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top