MS Excel Formulas and Functions Notes for Competitive Exams

Microsoft Excel is a spreadsheet program, and the questions on its formulas and functions are very common in computer awareness sections of SSC, banking, railways, State exams and also in computer proficiency tests. A student who knows the 30 most important functions and the rules of cell referencing can answer almost every question. This note explains Excel formulas and functions in simple language, with tables, examples, errors and practice questions. If you want to revise the basics of the Office package first, read our notes on MS Office basics.

Basic Terms of Excel

• Workbook: The Excel file, which can have many sheets. The default extension is .xlsx (older versions use .xls).
• Worksheet: A single sheet made of rows and columns. The sheets are named Sheet1, Sheet2 and so on.
• Cell: The box where a row and a column meet, such as A1. Columns have letters and rows have numbers.
• Range: A group of cells, such as A1:A10.
• Active cell: The cell that is currently selected.
• Formula bar: The bar above the sheet that shows the content of the active cell.
• Fill handle: The small square at the corner of the active cell, used to copy a formula or a series.

A modern worksheet has 1,048,576 rows and 16,384 columns (A to XFD).

What Is a Formula and What Is a Function?

A formula is an expression that does a calculation and always starts with the equal sign (=). For example, =A1+B1 adds the two cells. A function is a ready-made formula with a name, for example =SUM(A1:A5). Each function has a name, the brackets and the arguments inside them. The operators used in formulas are + (add), - (subtract), * (multiply), / (divide) and ^ (power). Excel follows the BODMAS order, so the multiplication is done before the addition, and the brackets are solved first.

Mathematical and Statistical Functions

Logical and Conditional Functions

The IF function has three parts: the test, the value if true and the value if false. The IF functions can be placed inside each other, which is called nesting, to check many conditions.

Lookup Functions

In VLOOKUP, the last argument FALSE means exact match, and TRUE means approximate match. The "V" in VLOOKUP stands for vertical, and the "H" in HLOOKUP stands for horizontal.

Text Functions

Date and Time Functions

Cell References: Relative, Absolute and Mixed

This is one of the most asked topics. A relative reference (A1) changes when you copy the formula to another cell. An absolute reference ($A$1) does not change, whichever cell you copy it to. A mixed reference ($A1 or A$1) locks only the column or only the row. The F4 key is used to change the type of reference while editing a formula. For example, if C1 has =A1*B1 and you copy it to C2, it becomes =A2*B2. But if it is =A1*$B$1, then the part $B$1 remains the same.

Common Error Messages

• #DIV/0! appears when a number is divided by zero.
• #NAME? appears when Excel does not recognise a function name or text in the formula.
• #VALUE! appears when the wrong type of value is used, such as text in an addition.
• #REF! appears when the cell reference is not valid, for example when a referred cell has been deleted.
• #N/A appears when a value is not available, such as a VLOOKUP that cannot find a match.
• #NUM! appears when there is a problem with a number in the formula.
• ##### appears when the column is too narrow to show the number.

Useful Features Asked in Exams

Besides functions, you should know a few features. Sort arranges the data in ascending or descending order. Filter shows only the rows that meet a condition. Conditional formatting changes the colour of cells that meet a rule. Pivot table summarises a large amount of data quickly. Charts such as column, line, pie and bar show data in a picture form. Freeze panes keeps the top rows or the first columns visible while scrolling. Data validation restricts the type of value that can be entered in a cell. Flash fill fills data by guessing the pattern. Many shortcuts are also asked, and you can revise them from our notes on keyboard shortcuts and terminology.

Worked Examples

Example 1: Pass or fail. Suppose the marks of a student are in cell B2, and the passing marks are 40. The formula =IF(B2>=40,"Pass","Fail") shows Pass for 45 and Fail for 32. If you want a grade, you can nest the function: =IF(B2>=75,"A",IF(B2>=50,"B","C")).

Example 2: Total and average. If the marks of five subjects are in B2 to F2, then =SUM(B2:F2) gives the total and =AVERAGE(B2:F2) gives the average. If the total is 400 and the number of subjects is 5, the average is 80.

Example 3: Counting with a condition. To count how many students scored more than 60 in the range B2:B21, use =COUNTIF(B2:B21,">60"). To add only the marks above 60, use =SUMIF(B2:B21,">60").

Example 4: Joining names. If the first name is in A2 and the last name is in B2, then =A2&" "&B2 or =CONCATENATE(A2," ",B2) joins them with a space in between.

Range Operators and Special Symbols

The colon (:) is the range operator and refers to all the cells between two cells, such as A1:A5. The comma (,) is the union operator, which joins separate references, such as =SUM(A1:A3,C1:C3). The ampersand (&) joins the text. The dollar sign ($) fixes a reference. The percentage sign (%) converts a number to a percentage, and the quotation marks ("") enclose the text in a formula. The sheet name is separated from the cell by the exclamation mark, for example Sheet2!A1. These small points are asked as one-mark questions in many exams.

Data Types in Excel

A cell can hold three main types of data: text (label), numbers (value) and formulas. By default, the text is aligned to the left of the cell and the numbers are aligned to the right. A date is also stored as a number internally, so you can add or subtract the dates. If you type a number with a leading apostrophe, Excel treats it as text. These points explain why a formula sometimes shows #VALUE! when it tries to add text.

Common Mistakes

Students forget the equal sign at the start, and then Excel treats the entry as simple text. Another common mistake is to forget the closing bracket or to use the wrong separator between arguments. In VLOOKUP, many candidates choose a column number that is outside the table, which gives the #REF! error, or they forget to use FALSE for an exact match. For counting, students wrongly use COUNT for text cells, but COUNT ignores the text. In the exam, remember that the absolute reference is made by the dollar sign and not by the hash or the star.

Tips to Learn Excel Functions

1. Practise on a real sheet. Type sample marks and try every function once.
2. Learn the function groups instead of a random list.
3. Always start a formula with the equal sign, and check the brackets.
4. Revise the difference between COUNT, COUNTA and COUNTIF.
5. Solve the previous year questions on cell references and error messages.

Practice Questions With Answers

• Q1. With which symbol does an Excel formula begin? Answer: The equal sign (=).
• Q2. Which function counts only the cells that contain numbers? Answer: COUNT.
• Q3. What is the cell reference $A$1 called? Answer: Absolute reference.
• Q4. Which error appears when a number is divided by zero? Answer: #DIV/0!.
• Q5. Which function is used to look up a value in the first column of a table? Answer: VLOOKUP.

Quick Revision

• A formula starts with =, and a function is a ready-made formula.
• COUNT counts numbers, COUNTA counts non-empty cells, COUNTIF counts by condition.
• $A$1 is absolute, A1 is relative, and F4 changes the type.

Frequently Asked Questions (FAQ)

How does an Excel formula begin?

An Excel formula always begins with the equal sign (=).

What is the difference between a formula and a function?

A formula is an expression written by the user, while a function is a ready-made formula with a name.

What is the difference between COUNT and COUNTA?

COUNT counts only the cells with numbers, while COUNTA counts all the cells that are not empty.

What is VLOOKUP used for?

It searches a value in the first column of a table and returns a value from another column of the same row.

What is an absolute cell reference?

It is a reference like $A$1 that does not change when the formula is copied.

Which key changes the type of cell reference?

The F4 key changes the reference type while editing a formula.

What does the #DIV/0! error mean?

It means that a number is being divided by zero.

What is the extension of an Excel file?

The default extension is .xlsx, and older files use .xls.

How many rows and columns does a worksheet have?

A modern worksheet has 1,048,576 rows and 16,384 columns.

What is the use of the IF function?

It gives one result if a condition is true and another result if it is false.

What is a pivot table?

A pivot table summarises a large amount of data quickly in a new table.

What does the fill handle do?

The fill handle copies a formula or a series to the neighbouring cells.