cell reference

cell addresses or a range of cells that you want to be included in a calculation.

operators

symbols that specify the operations performed

functions

pre - defined formulas in Excel

advantages of functions

It reduces time and eliminates the chance of writing a wrong formula.

argument in a function

The input values that are required by a function.

value in a function

The values for the operation that are given in parentheses.

rules to enter a valid function

1. It must begin with an equal sign (=). 2. The function must be a valid Excel name. 3. Parentheses must follow the function name. 4. Arguments must be enclosed within parentheses.

ODD

returns a number rounded off to the nearest odd number.

INT

rounds off a number to the nearest integer.

PRODUCT

multiplies the values in a range.

ROUND

rounds off a number to a specified no of digits.

EXP

returns a natural logarithm raised to a specified power.

SQRT

returns the square root of a number

POWER

returns the result of a number raised to some power.

MOD

returns the remainder after a value is divided by a number.

TODAY

displays the current date.

CONCAT (concatenation)

Addition of 2 or more text values using &.

MIN

returns the smallest value in a range of cells.

MAX

returns the largest value in a range of cells.

COUNT

returns the number of values in a given range of cells.

SUM

returns the sum of values in a given range.

AVERAGE

Returns the average of a given range of cells containing numbers or just numbers.

cell range

group of contiguous cells. Written as - A1: B6

How to calculate the sum of multiple non-consecutive cells

Separate the cell addresses with a comma (, ).

how to copy data from the above cell into the one below it

press ctrl + ' and the data will be copied.

how to use range in a formula

= formula (G7: H13)

how to name a range

1. Select the range of cells, then click on the Formulas tab. 2. In the Defined Names group, click on Define Name. 3. In the new name dialogue box, enter a suitable name into the name field. 4. In the scope drop- down list, specify the availability of the range [ ex: whole worksheet, or one specific sheet]. 5. In the refers to field, make sure the required range is specified. 6. Click OK.

shortcut to name cell range

Select the range, click on the name box on the left side of the formula bar, then give a name and press Enter.

relative reference

Reference which is given to the address of a cell based on the position of another cell. When you copy the formula to other cells, the formula changes accordingly.

absolute reference

reference which is used when you do not want to change the address of a cell before copying the address to another cell. Ex: =$A$1+$A$2

mixed reference

reference which is used when you want either the row or the column to be fixed when the formula is copied. ex: =$A1+A$2

how to rename sheet tab

1. Right-click on the sheet tab. 2. Click on the rename option in the shortcut menu. 3. The sheet tab gets highlighted. 4. Type in a suitable name and press Enter.

how to change the color of a sheet tab

1. right click on the sheet tab. 2. select tab color from the shortcut menu. 3. select any tab color

removing a sheet

1. right click on the sheet tab. 2. select delete from the shortcut menu and the sheet is removed.

###### error

occurs when the column does not have space to accommodate the value.

#VALUE error

occurs if the formula contains an invalid argument.

#NAME? error

occurs if the formula is used incorrectly.

#DIV/0 error

occurs if the number is divided by zero or an empty cell.