MS Excel Formulas and Functions Notes for SSC Exams
Rahul Kumar
SSC Exam Expert & Content Editor
In this note
- 1.What Is a Formula and What Is a Function in Excel?
- 2.Operators and the Order of Calculation
- 3.Cell References: Relative, Absolute and Mixed
- 4.Math and Statistical Functions
- 5.Logical Functions
- 6.Text Functions
- 7.Date and Time Functions
- 8.Lookup and Reference Functions
- 9.Financial and Information Functions
- 10.Error Values in Excel
- 11.Important Excel Shortcut Keys for Formulas
- 12.Important Questions Explained
- 13.Quick Tips to Remember
- 14.Common Mistakes to Avoid
- 15.Quick Revision Points
- 16.Practice Questions With Answers
- 17.How to Practise This Topic
- 18.Related Notes and Practice
- 19.Frequently Asked Questions
PDF Notes: MS Excel Formulas and Functions Notes for SSC Exams
Premium PDF · one-time payment of ₹5 · instant access
MS Excel formulas and functions are among the most searched and most repeated topics of SSC Computer Knowledge. In almost every SSC CGL, CHSL, MTS and GD paper, at least one question asks the output of a function, the meaning of an error value or the use of a cell reference. These MS Excel formulas and functions notes for SSC give you the complete list of the important functions, their syntax, simple examples and the exam tips in one place. Read the basic MS Excel Notes notes first, and use the MS Office Notes and Computer Shortcut Keys notes for revision.
What Is a Formula and What Is a Function in Excel?
A formula is an instruction that Excel calculates and shows the result in a cell. Every formula starts with the equal sign (=), for example =A1+B1 or =SUM(A1:A5). A function is a ready-made formula with a name, such as SUM, AVERAGE or IF. A function is written as =FUNCTIONNAME(argument1, argument2, ...), where the arguments are the values, the cell references or the ranges on which the function works.
- A formula can have numbers, cell references, operators and functions, for example =(A1+B1)*10.
- The result of a formula changes automatically when the value of a referenced cell changes.
- Functions are not case-sensitive: =sum(a1:a5) and =SUM(A1:A5) give the same result.
- The arguments are separated by a comma (,) in most computers; some regional settings use a semicolon (;).
- The Insert Function button (fx) near the formula bar, or the shortcut Shift + F3, opens the list of all functions.
- The Formulas tab of the ribbon groups the functions into the categories: AutoSum, Recently Used, Financial, Logical, Text, Date and Time, Lookup and Reference, Math and Trig and More Functions.
Operators and the Order of Calculation
| Type of operator | Symbols | Example and result |
|---|---|---|
| Arithmetic | + - * / ^ % | =2+3*4 gives 14 (multiplication first); =2^3 gives 8; =50% gives 0.5 |
| Comparison | = > < >= <= <> | =A1<>B1 gives TRUE if the two cells are not equal |
| Text (concatenation) | & | ="SSC"&" Exam" gives SSC Exam |
| Reference | Colon (:), comma (,), space | A1:A5 is a range; A1,A5 means two cells; the space gives the common cells of two ranges |
The order of calculation in Excel is: brackets, then percent (%), then exponent (^), then multiplication and division (left to right), then addition and subtraction (left to right), then the text operator (&) and last the comparison operators. This is the same idea as BODMAS, so a formula such as =10+2*3 gives 16 and not 36, while =(10+2)*3 gives 36.
Cell References: Relative, Absolute and Mixed
| Type | Example | What happens when the formula is copied |
|---|---|---|
| Relative reference | A1 | The reference changes with the new position of the formula (copying =A1 one row down gives =A2) |
| Absolute reference | $A$1 | The reference never changes; both the column and the row are locked with the dollar sign |
| Mixed reference | $A1 or A$1 | Only the column ($A1) or only the row (A$1) is locked |
- Press F4 while editing a formula to change the type of reference in the order A1, $A$1, A$1 and $A1.
- A reference to another sheet is written as Sheet2!A1, and to another workbook as [Book2.xlsx]Sheet1!A1.
- A named range gives a name to a cell or a range (for example, Marks) so that the formula =SUM(Marks) is easy to read. It is created from the Name Box or from Formulas, Define Name.
- A 3-D reference such as =SUM(Sheet1:Sheet3!A1) adds the same cell of many sheets.
- A circular reference is a formula that refers to its own cell, and Excel shows a warning.
Math and Statistical Functions
| Function | What it does | Example and result |
|---|---|---|
| SUM | Adds the numbers | =SUM(10,20,30) gives 60; =SUM(A1:A5) adds the five cells |
| AVERAGE | Finds the arithmetic mean | =AVERAGE(10,20,30) gives 20 |
| MAX / MIN | Largest / smallest value | =MAX(5,9,2) gives 9; =MIN(5,9,2) gives 2 |
| COUNT | Counts the cells that contain numbers | =COUNT(A1:A5) counts only the numeric cells |
| COUNTA | Counts the cells that are not empty (numbers and text) | =COUNTA(A1:A5) |
| COUNTBLANK | Counts the empty cells | =COUNTBLANK(A1:A5) |
| COUNTIF | Counts the cells that meet one condition | =COUNTIF(A1:A5,">50") counts the values above 50 |
| SUMIF | Adds the cells that meet one condition | =SUMIF(A1:A5,">50") adds the values above 50 |
| SUMIFS / COUNTIFS | Add or count with many conditions | =COUNTIFS(A1:A5,">50",B1:B5,"Yes") |
| AVERAGEIF | Average of the cells that meet a condition | =AVERAGEIF(A1:A5,">50") |
| PRODUCT | Multiplies the numbers | =PRODUCT(2,3,4) gives 24 |
| SUMPRODUCT | Multiplies the matching cells of the ranges and adds the results | =SUMPRODUCT(A1:A3,B1:B3) |
| LARGE / SMALL | The nth largest / smallest value | =LARGE(A1:A5,2) gives the second largest |
| MEDIAN / MODE | Middle value / most frequent value | =MEDIAN(1,3,9) gives 3 |
| RANK | Position of a number in a list | =RANK(A1,A1:A5) gives 1 for the largest number |
| ROUND | Rounds to the given digits | =ROUND(12.567,2) gives 12.57; =ROUND(1275,-2) gives 1300 |
| ROUNDUP / ROUNDDOWN | Always rounds up / down | =ROUNDUP(12.1,0) gives 13; =ROUNDDOWN(12.9,0) gives 12 |
| INT | Rounds down to the nearest integer | =INT(8.9) gives 8; =INT(-8.9) gives -9 |
| TRUNC | Removes the decimal part | =TRUNC(8.9) gives 8 |
| MOD | Remainder after division | =MOD(10,3) gives 1 |
| QUOTIENT | Whole-number part of a division | =QUOTIENT(10,3) gives 3 |
| ABS | Absolute (positive) value | =ABS(-5) gives 5 |
| POWER / SQRT | Power / square root | =POWER(2,3) gives 8; =SQRT(16) gives 4 |
| RAND / RANDBETWEEN | Random number | =RANDBETWEEN(1,10) gives a whole number from 1 to 10 |
| SUBTOTAL | Total of a filtered list | =SUBTOTAL(9,A1:A10) adds only the visible cells |
Remember: COUNT counts only numbers, COUNTA counts every non-empty cell, and COUNTBLANK counts the empty cells. SSC likes to ask the difference between these three.
Logical Functions
| Function | What it does | Example and result |
|---|---|---|
| IF | Gives one result if the condition is true and another if it is false | =IF(A1>=40,"Pass","Fail") gives Pass when A1 is 40 or more |
| Nested IF | An IF inside another IF for more than two results | =IF(A1>=80,"A",IF(A1>=60,"B","C")) |
| AND | TRUE only if all the conditions are true | =AND(A1>10,B1<5) |
| OR | TRUE if at least one condition is true | =OR(A1>10,B1<5) |
| NOT | Reverses TRUE and FALSE | =NOT(A1>10) |
| IFERROR | Shows a chosen value if the formula gives an error | =IFERROR(A1/B1,"Error") |
| IFS | Checks many conditions in order (Excel 2019 and later) | =IFS(A1>=80,"A",A1>=60,"B",TRUE,"C") |
| TRUE / FALSE | Return the logical values | =TRUE() |
Text Functions
| Function | What it does | Example and result |
|---|---|---|
| LEN | Counts the characters, including spaces | =LEN("SSC Exam") gives 8 |
| LEFT / RIGHT | Takes characters from the left / right | =LEFT("Computer",3) gives Com; =RIGHT("Computer",3) gives ter |
| MID | Takes characters from the middle | =MID("Computer",2,3) gives omp |
| UPPER / LOWER / PROPER | Changes the case | =PROPER("ssc exam") gives Ssc Exam |
| TRIM | Removes the extra spaces | =TRIM(" SSC Exam ") gives SSC Exam |
| CONCATENATE / CONCAT / TEXTJOIN | Joins the text | =CONCATENATE("SSC"," ","CGL") gives SSC CGL |
| FIND / SEARCH | Gives the position of a text (FIND is case-sensitive, SEARCH is not) | =FIND("m","Computer") gives 3 |
| SUBSTITUTE | Replaces the old text with the new text | =SUBSTITUTE("2025","5","6") gives 2026 |
| REPLACE | Replaces the characters at a given position | =REPLACE("ABCDE",2,3,"X") gives AXE |
| REPT | Repeats the text | =REPT("*",3) gives *** |
| TEXT | Shows a number in a given format | =TEXT(0.25,"0%") gives 25% |
| VALUE | Changes the text number into a number | =VALUE("123") |
| EXACT | Checks if two texts are exactly the same | =EXACT("a","A") gives FALSE |
Date and Time Functions
| Function | What it does | Example |
|---|---|---|
| TODAY | Shows the current date; changes every day | =TODAY() |
| NOW | Shows the current date and time | =NOW() |
| DATE | Makes a date from the year, month and day | =DATE(2026,10,12) |
| DAY / MONTH / YEAR | Takes the part of a date | =YEAR(TODAY()) |
| WEEKDAY | Gives the day of the week as a number (1 is Sunday by default) | =WEEKDAY(A1) |
| DATEDIF | Difference between two dates in days, months or years | =DATEDIF(A1,B1,"Y") gives the complete years |
| EDATE / EOMONTH | Date after some months / last day of a month | =EOMONTH(A1,0) |
| NETWORKDAYS | Working days between two dates | =NETWORKDAYS(A1,B1) |
| HOUR / MINUTE / SECOND | Part of a time | =HOUR(NOW()) |
Excel stores a date as a serial number: 1 means 1 January 1900, so a date can be used in a calculation. A time is stored as the fraction of a day (0.5 means 12:00 noon).
Lookup and Reference Functions
| Function | Syntax and use |
|---|---|
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Searches the value in the FIRST COLUMN of the table and returns a value from the given column of the same row. Use FALSE (or 0) for an exact match and TRUE (or 1) for an approximate match. |
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). Searches in the FIRST ROW and returns a value from the given row. |
| XLOOKUP | The new function of Excel 2021 and Microsoft 365: =XLOOKUP(lookup_value, lookup_array, return_array). It can look in any direction and does not need a column number. |
| INDEX | =INDEX(range,row,column) returns the value at the given position of a range. |
| MATCH | =MATCH(value, range, 0) returns the position of a value in a range. INDEX and MATCH are used together. |
| LOOKUP | Finds a value in one row or column and returns the value from the same position of another. |
| ROW / COLUMN | Return the row number / column number of a reference. |
| ROWS / COLUMNS | Count the rows / columns of a range. |
| CHOOSE | Picks a value from a list by its number. |
| OFFSET / INDIRECT | Return a reference that is moved from a cell / made from a text. |
| TRANSPOSE | Changes rows to columns and columns to rows. |
- In VLOOKUP, the lookup value must be in the first column of the table, and the function cannot look to the left.
- V stands for vertical (data arranged in columns) and H stands for horizontal (data arranged in rows).
- The result of a VLOOKUP is #N/A when the value is not found.
Financial and Information Functions
| Function | Use |
|---|---|
| PMT | Monthly payment (EMI) of a loan |
| FV / PV | Future value / present value of an investment |
| NPV / IRR | Net present value / internal rate of return |
| RATE / NPER | Interest rate / number of periods |
| SLN / DB | Depreciation (straight line / declining balance) |
| ISBLANK | TRUE if the cell is empty |
| ISNUMBER / ISTEXT | TRUE if the cell has a number / text |
| ISERROR / ISNA | TRUE if the cell has any error / the #N/A error |
| ISEVEN / ISODD | TRUE if the number is even / odd |
Error Values in Excel
| Error | Meaning and common reason |
|---|---|
| #DIV/0! | A number is divided by zero or by an empty cell |
| #N/A | The value is not available, for example VLOOKUP could not find the value |
| #NAME? | Excel does not know the name; the function name or the range name is wrong |
| #NULL! | The space operator is used for two ranges that do not cross each other |
| #NUM! | A number in the formula is not valid, such as the square root of a negative number |
| #REF! | The cell reference is not valid, for example a referred cell is deleted |
| #VALUE! | The type of the value is wrong, such as adding a number and a text |
| ##### | The column is too narrow to show the number, or a date or time is negative |
Important Excel Shortcut Keys for Formulas
| Shortcut | Action |
|---|---|
| Alt + = | Inserts the AutoSum formula |
| F2 | Edits the active cell |
| F4 | Changes the reference type (relative, absolute, mixed); also repeats the last action |
| Ctrl + ` | Shows all the formulas of the sheet instead of the results |
| Ctrl + Enter | Fills the same formula in all the selected cells |
| Ctrl + Shift + Enter | Enters an array formula (older versions) |
| Ctrl + ; | Inserts the current date as a fixed value |
| Ctrl + Shift + ; | Inserts the current time as a fixed value |
| F9 | Calculates the sheet; Shift + F9 calculates the active sheet |
| Shift + F3 | Opens the Insert Function box |
| Ctrl + D / Ctrl + R | Fill down / fill right |
| Ctrl + Z / Ctrl + Y | Undo / redo |
Important Questions Explained
Question 1: What is the result of =SUM(2,3)*2?
Answer: The result is 10. SUM(2,3) gives 5 and then 5*2 gives 10.
Question 2: Which function counts the cells that contain only numbers?
Answer: COUNT. COUNTA counts all the non-empty cells.
Question 3: What does the formula =IF(A1>=40,"Pass","Fail") show if A1 contains 35?
Answer: It shows Fail, because the condition 35 >= 40 is false.
Question 4: Which function is used to find a value in the first column of a table and return a value from another column?
Answer: VLOOKUP.
Question 5: What will =LEFT("SSC CGL",3) return?
Answer: SSC, the first three characters.
Question 6: What does the error #DIV/0! mean?
Answer: A number is being divided by zero.
Question 7: Which key changes a cell reference from relative to absolute?
Answer: F4. It adds the dollar signs, such as $A$1.
Question 8: What will =MOD(17,5) return?
Answer: 2, because 17 divided by 5 gives the remainder 2.
Quick Tips to Remember
- Always check the first character: a formula must start with the = sign, otherwise Excel treats it as text.
- Learn the three count functions together: COUNT (numbers), COUNTA (non-empty) and COUNTBLANK (empty).
- Remember V for vertical in VLOOKUP and H for horizontal in HLOOKUP.
- For the result of a nested formula, solve the inner bracket first, as in BODMAS.
- Learn the six error values in pairs: #DIV/0! with zero, #REF! with a deleted cell, #NAME? with a wrong name and #N/A with a value not found.
- Use F4 to lock a reference when you copy a formula, such as a fixed tax rate in a cell.
Common Mistakes to Avoid
- Writing a formula without the equal sign.
- Confusing COUNT and COUNTA.
- Using VLOOKUP when the lookup value is not in the first column of the table.
- Forgetting to put the text in double quotation marks inside a function, such as "Pass".
- Mixing up the ROUNDUP and INT functions for negative numbers.
- Thinking that TODAY() gives a fixed date; it changes every day.
Quick Revision Points
- Every formula starts with =; a function is a ready-made formula.
- COUNT = numbers, COUNTA = non-empty, COUNTBLANK = empty cells.
- SUMIF and COUNTIF work with one condition; SUMIFS and COUNTIFS with many.
- VLOOKUP searches the first column; HLOOKUP searches the first row; XLOOKUP is the new function.
- $A$1 is absolute; F4 changes the reference type.
- Errors: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!.
- Alt + = inserts AutoSum; Ctrl + ` shows the formulas.
Practice Questions With Answers
- Which sign must every Excel formula start with?
Answer: = (equal sign) (Formula). - What is the output of =MAX(4,9,7)?
Answer: 9 (Largest value). - What is the output of =LEN("Excel")?
Answer: 5 (Characters). - What is the output of =ROUND(8.567,1)?
Answer: 8.6 (Rounding). - Which function gives the current date and time?
Answer: NOW (TODAY gives only the date). - What is the output of =POWER(3,2)?
Answer: 9 (3 raised to 2). - Which function counts the empty cells in a range?
Answer: COUNTBLANK (Counting). - Which function is used to join two texts?
Answer: CONCATENATE (or CONCAT or the & operator) (Text). - What does #REF! mean?
Answer: The cell reference is not valid (Deleted cell). - Which function looks up a value in the first row of a table?
Answer: HLOOKUP (Horizontal). - What is the result of =INT(-4.2)?
Answer: -5 (INT rounds down). - What is the result of =5+2*3?
Answer: 11 (Order of calculation). - Which shortcut inserts AutoSum?
Answer: Alt + = (Shortcut). - Which function gives the position of a value in a range?
Answer: MATCH (With INDEX). - What is the result of =MID("COMPUTER",4,3)?
Answer: PUT (Text function). - Which symbol locks a cell reference?
Answer: Dollar sign ($) (Absolute reference). - What is the result of =AND(TRUE,FALSE)?
Answer: FALSE (Logical function). - Which function shows a result based on a condition?
Answer: IF (Logical function). - What is the result of =SQRT(81)?
Answer: 9 (Square root). - Which key shows the formulas instead of the results in a sheet (Ctrl + ?)?
Answer: The grave accent key (`) (Ctrl + `).
How to Practise This Topic
- Read the function tables once and learn the purpose of each function in one line.
- Open Excel (or any free spreadsheet) and type every example given above to see the output.
- Revise the list of errors and shortcut keys the next day.
- Attempt the practice questions without looking at the answers.
- Solve the SSC practice papers and the previous year papers to see how SSC asks Excel questions.
Related Notes and Practice
- MS Excel Notes
- MS Office Notes
- Computer Shortcut Keys
- MS Word Notes
- Computer Knowledge Notes for SSC
- SSC practice papers
Frequently Asked Questions
What is the difference between a formula and a function in Excel?
A formula is any calculation that starts with an equal sign, while a function is a ready-made formula with a name, such as SUM, AVERAGE or IF.
Which Excel functions are most important for SSC exams?
SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTIF, SUMIF, IF, VLOOKUP, HLOOKUP, LEN, LEFT, RIGHT, MID, TODAY and NOW are the most important.
What is the difference between COUNT and COUNTA?
COUNT counts only the cells that contain numbers, while COUNTA counts all the cells that are not empty, including text.
What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches the value in the first column of a table and returns a value from another column, while HLOOKUP searches the first row and returns a value from another row.
What is an absolute cell reference?
An absolute reference such as $A$1 does not change when the formula is copied to another cell. Press F4 to make a reference absolute.
What does #NAME? error mean in Excel?
It means that Excel does not recognise a function name or a range name used in the formula, usually because of a spelling mistake.
What is the shortcut key for AutoSum?
Alt + = inserts the AutoSum function in Excel.
How many rows and columns are there in an Excel worksheet?
Excel 2007 and later versions have 1,048,576 rows and 16,384 columns (A to XFD).
What is the use of the IF function?
The IF function gives one result when a condition is true and another result when it is false.
How is a date stored in Excel?
Excel stores a date as a serial number, where 1 is 1 January 1900, so dates can be used in calculations.
How many Excel questions come in SSC exams?
Usually one or two questions come from MS Excel in the Computer Knowledge section of SSC CGL, CHSL and other exams.
How can I practise Excel formulas for the SSC exam?
Type each formula in a spreadsheet, check the output, learn the error values and shortcuts, and solve the MCQs of previous year papers.
Previous chapter
Algorithm and Flowchart Notes for SSC Exams
Next chapter
Artificial Intelligence Notes for SSC Exams: AI, Machine Learning and Generative AI
Rahul Kumar
SSC Exam Expert & Content Editor
Rahul is a senior SSC exam strategist with 8+ years of experience helping aspirants crack CGL, CHSL and MTS exams. He writes in-depth notification breakdowns, exam pattern guides and preparation strategies.