MS Excel Formulas – Complete Guide with Table, Important Points, and MCQs
By Home Academy
Introduction to MS Excel Formulas
Microsoft Excel formulas are mathematical expressions used to perform calculations, analyze data, and automate tasks in worksheets. Every formula in Excel begins with an equals sign =.
Example:=A1+B1
This formula adds the values of cell A1 and B1.
Most Important MS Excel Formulas Table
| Formula Name | Syntax | Purpose | Example | Result |
|---|---|---|---|---|
| SUM | =SUM(A1:A5) | Adds numbers | =SUM(10,20,30) | 60 |
| AVERAGE | =AVERAGE(A1:A5) | Finds average | =AVERAGE(10,20,30) | 20 |
| MAX | =MAX(A1:A5) | Finds highest value | =MAX(5,8,2) | 8 |
| MIN | =MIN(A1:A5) | Finds smallest value | =MIN(5,8,2) | 2 |
| COUNT | =COUNT(A1:A5) | Counts numeric cells | =COUNT(1,2,"A") | 2 |
| COUNTA | =COUNTA(A1:A5) | Counts non-empty cells | =COUNTA(1,"A","") | 2 |
| IF | =IF(condition,true,false) | Logical test | =IF(A1>50,"Pass","Fail") | Pass/Fail |
| AND | =AND(condition1,condition2) | Checks all conditions | =AND(5>2,6>3) | TRUE |
| OR | =OR(condition1,condition2) | Checks any condition | =OR(5<2,6>3) | TRUE |
| NOT | =NOT(condition) | Reverses logic | =NOT(5>2) | FALSE |
| LEN | =LEN(text) | Counts characters | =LEN("Excel") | 5 |
| LEFT | =LEFT(text,n) | Extracts left text | =LEFT("Excel",2) | Ex |
| RIGHT | =RIGHT(text,n) | Extracts right text | =RIGHT("Excel",2) | el |
| MID | =MID(text,start,n) | Extracts middle text | =MID("Excel",2,3) | xce |
| CONCATENATE | =CONCATENATE(A1,B1) | Joins text | =CONCATENATE("Home","Academy") | HomeAcademy |
| UPPER | =UPPER(text) | Converts to uppercase | =UPPER("excel") | EXCEL |
| LOWER | =LOWER(text) | Converts to lowercase | =LOWER("EXCEL") | excel |
| TODAY | =TODAY() | Shows current date | =TODAY() | Current Date |
| NOW | =NOW() | Shows date & time | =NOW() | Current Date & Time |
| ROUND | =ROUND(number,digits) | Rounds number | =ROUND(15.678,2) | 15.68 |
| SQRT | =SQRT(number) | Finds square root | =SQRT(81) | 9 |
| POWER | =POWER(number,power) | Calculates power | =POWER(2,3) | 8 |
| VLOOKUP | =VLOOKUP(value,table,col,FALSE) | Searches vertically | =VLOOKUP(101,A1:D10,2,FALSE) | Matching Value |
| HLOOKUP | =HLOOKUP(value,table,row,FALSE) | Searches horizontally | Formula Example | Matching Value |
| COUNTIF | =COUNTIF(range,criteria) | Counts with condition | =COUNTIF(A1:A5,">50") | Count |
| SUMIF | =SUMIF(range,criteria,sum_range) | Conditional sum | =SUMIF(A1:A5,">50") | Sum |
| IFERROR | =IFERROR(value,error_value) | Handles errors | =IFERROR(A1/B1,0) | 0 |
| RANK | =RANK(number,ref) | Finds rank | =RANK(90,A1:A5) | Rank |
| RAND | =RAND() | Generates random number | =RAND() | Random Decimal |
| ABS | =ABS(number) | Absolute value | =ABS(-10) | 10 |
Important Points for Exams
Basic Points
Every formula starts with
=.Functions are predefined formulas in Excel.
Cell references are of three types:
Relative Reference →
A1Absolute Reference →
$A$1Mixed Reference →
A$1or$A1
Important Shortcut Keys
| Shortcut Key | Function |
|---|---|
| Ctrl + C | Copy |
| Ctrl + V | Paste |
| Ctrl + X | Cut |
| Ctrl + Z | Undo |
| Ctrl + Y | Redo |
| Ctrl + S | Save |
| Ctrl + P | |
| F2 | Edit Cell |
| Alt + = | Auto Sum |
| Ctrl + Arrow Key | Move to data edge |
| Ctrl + Shift + L | Apply Filter |
| Ctrl + 1 | Format Cells |
| Ctrl + Home | Go to first cell |
| Ctrl + End | Go to last used cell |
Frequently Asked Exam Questions
| Question | Answer |
|---|---|
| Which symbol starts a formula in Excel? | = |
| Which formula is used for addition? | SUM |
| Which function finds average? | AVERAGE |
| Which function checks conditions? | IF |
| Which function joins text? | CONCATENATE |
| Which formula finds highest value? | MAX |
| Which formula counts numbers only? | COUNT |
| Which key edits a cell? | F2 |
| Which shortcut applies AutoSum? | Alt + = |
| Which formula returns current date? | TODAY() |
MCQ Questions on MS Excel Formulas
1. Which formula is used to add numbers in Excel?
A. ADD
B. TOTAL
C. SUM
D. PLUS
Answer: C. SUM
2. Which function is used to calculate average?
A. AVG
B. AVERAGE
C. MEAN
D. MID
Answer: B. AVERAGE
3. Every formula in Excel starts with:
A. #
B. @
C. =
D. %
Answer: C. =
4. Which formula finds the maximum value?
A. LARGE
B. HIGH
C. MAX
D. TOP
Answer: C. MAX
5. Which function is used for logical testing?
A. IF
B. TEST
C. LOGIC
D. CHECK
Answer: A. IF
6. Which formula counts numeric values only?
A. COUNTA
B. COUNT
C. NUMBER
D. SUM
Answer: B. COUNT
7. Which function combines text?
A. JOIN
B. TEXT
C. CONCATENATE
D. LINK
Answer: C. CONCATENATE
8. Which shortcut key is used for AutoSum?
A. Ctrl + S
B. Alt + =
C. Ctrl + A
D. Shift + A
Answer: B. Alt + =
9. Which formula gives the current date?
A. NOW()
B. DATE()
C. TODAY()
D. DAY()
Answer: C. TODAY()
10. Which function is used to remove errors?
A. ERROR
B. IFERROR
C. FIXERROR
D. REMOVEERROR
Answer: B. IFERROR
Advanced Excel Formulas for Competitive Exams
| Formula | Use |
|---|---|
| INDEX | Returns value from table |
| MATCH | Finds position |
| INDEX + MATCH | Advanced lookup |
| TEXT | Formats numbers/text |
| TRIM | Removes extra spaces |
| SUBSTITUTE | Replaces text |
| FIND | Finds text position |
| SEARCH | Searches text |
| PMT | Loan EMI calculation |
| DATE | Creates date |
Conclusion
Microsoft Excel formulas are extremely important for SSC, Banking, JKSSB, RRB, UGC NET, and other competitive exams. Understanding formulas, shortcut keys, logical functions, and lookup formulas can improve both exam performance and office productivity.