……..Back to home……..

Cell

A box formed by the intersection of a row and column in a worksheet or a table, in which you enter information.

 

Referencing Cells in Formulas

You can include or reference other cells in a formula. When you do, the result of the formula depends on the values in the referenced cells and changes automatically when the values in the referenced cells change. They are of three types Relative References, Absolute References & Mixed References.

 

Relative References

By default Excel uses Relative Referencing. In relative reference, you actually refer to cell that is above or below or left or right to a number of rows or columns. For example, if you refer to cell D2 from F2, you are actually referring to a column that is two columns to the left of cell D2 and in the same row (the second ‘2’row). When a formula that possesses a relative reference is copied from one cell to another cell, the value in the copied cell also changes. i.e., if you copy a formula =C2+D2 from E2 to the cell F2, the formula in F2 adjusts in such a way that when the cell F2 is dragged downwards by one row, the formula automatically changes to C3+D3 and places the resulting value of C3+D3 in cell F3.

 

Absolute References

In case, if you wish to maintain the original references as they were, then you should make use of the absolute references. If you want to make the above example retain the original reference when it is copied, then you should precede the columns A2 and B2 and the row number by a Dollar ($) symbol. i.e., you should use the formula as =$C$2+$B$2 so that when you copy the formula the original reference is retained. To make the cell appear in absolute format ($) click on the cell you would like to edit and press [F4] and press enter. You will observe the cell A1 turn into =$A$1. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

 

Mixed References

In some situations, you may need to make the cell reference as ‘Mixed’ reference, so that you can lock either a column or a row by preceding it with a Dollar ($) symbol. For example, if you use a formula as =$D4+E4, then you are making the value that is present in cell D4 as absolute and the value in cell E4 as Relative.

 

Quick Reference

Add, Subtract, Multiply & Divide

Type an equal sign (=), use math operators, and then press ENTER.


=10+5 to add

=10-5 to subtract

=10*5 to multiply

=10/5 to divide


Formulas are visible in the formula bar when you select a cell that contains a result. If the formula bar is not visible, on the Tools menu, click Options. Click the View tab, and select the Formula bar check box.

Formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).

Formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.

 

Fill Handle or Autofill

The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.

 

Copy a Formula Using the Fill Handle

1. Select the cell that contains the formula, then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears.

2. Drag the fill handle over the cell or cells to which you want to copy the formula, then release the mouse button.

 

Function

A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.

 

SUM function

The SUM function adds all the numbers that you specify as arguments  Each argument can be a range (range: Two or more cells on a sheet. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. Eg: =SUM(A1:A5) adds the numbers that are contained in cells A1, A2, A3, A4 and A5.

 

PRODUCT Function

The PRODUCT function provides a quick way to multiple numbers in a Excel. The advantage of using this function becomes apparent if you have several numbers to multiply together. It is easier then building a long formula.

eg: =PRODUCT(A1:A5) product of the numbers that are contained in cells A1, A2, A3, A4 and A5.       TIP: Up to 255 numbers can be entered into the function.

 

IF Function

The IF function, one of Excel’s logical functions, tests to see if a certain condition in a spreadsheet is true or false. The IF function returns the first value if the condition specified evaluates TRUE and the second value if it evaluates to FALSE                                              eg: =IF(C2>5000,C2*0.3,C2*0.2)

 

AVERAGE Function

The AVERAGE function, one of Excel's statistical functions, is used to find the average or arithmetic mean of a given list of arguments. eg: = AVERAGE(A1:A5) finds an average of the numbers that are contained in cells A1, A2, A3, A4 and A5.

 

MAX Function

The MAX function, one of Excel's statistical functions, is used to find the largest or maximum number in a given list of values or arguments. eg: =MAX(A1:A5) finds a maximum of the numbers that are contained in cells A1, A2, A3, A4 and A5.

 

MIN Function

The MIN function, one of Excel's statistical functions, is used to find the smallest or minimum value in a list of numbers or arguments. eg: =MIN(A1:A5) finds a minimum of the numbers that are contained in cells A1, A2, A3, A4 and A5.

Up to 30 arguments can be entered.

 

COUNTIF Function

The COUNTIF function, one of Excel's COUNT functions, is used to count up the number of cells in a selected range that meet specified criteria.

The syntax for the COUNTIF function is: =COUNTIF ( Range, Criteria)

Range - the group of cells the function is to search.

Criteria - determines whether the cell is to be counted or not. This can be a number, expression, cell reference, or text string.

eg: =COUNTIF(H2:H8,"=0")

 

SUMIF Function

The SUMIF function is used to add up the values in cells in a selected range that meet certain criteria.

The syntax for the SUMIF function is: = SUMIF (Range, Criteria, Sum Range)

Range - the group of cells the function is to search.

Criteria - determines whether the cell is to be counted or not.

Sum Range - the data range that is summed if the first range meets the specified criteria. If this range is omitted, the first range is summed instead.

eg:=SUMIF(K2:K8,"<15000")

 

Understand Error Values

#####           The column is not wide enough to display the content. Increase column width,      shrink contents to fit the column, or apply a different number format.

#REF!           A cell reference is not valid. Cells may have been deleted or pasted over.

#NAME?       You may have misspelled a function name.

#VALUE!       error if your formula includes cells that contain different data types. A value usedin the formula is of the wrong data type.

#DIV/0!        error when a number is divided either by zero (0) or by a cell that contains novalue.

#NUM!          You may have used an unacceptable argument in a function that requires a numeric argument. You may have entered a formula that produces a number that is too large or too small to be represented in Excel.


 

Pointer Shapes

As with other Microsoft programs, the pointer often changes its shape as you work in Excel. Each pointer shape indicates a different mode of operation. This table shows the various pointer shapes you may see while working in Excel.

Freeze panes to lock specific rows or columns

1.    On the worksheet, do one of the following:

§  To lock rows, select the row below the row or rows that you want to keep visible when you scroll.

§  To lock columns, select the column to the right of the column or columns that you want to keep visible when you scroll.

§  To lock both rows and columns, click the cell below and to the right of the rows and columns that you want to keep visible when you scroll.

2.    On the View tab, in the Window group, click the arrow below Freeze Panes.

3.    Do one of the following:

§  To lock one row only, click Freeze Top Row.

§  To lock one column only, click Freeze First Column.

§  To lock more than one row or column, or to lock both rows and columns at the same time, click Freeze Panes.

 Notes   When you freeze the top row, first column, or panes, the Freeze Panes option changes to Unfreeze Panes so that you can unlock any frozen rows or columns.

* You can freeze rows at the top and columns on the left side of the worksheet only. You cannot freeze rows and columns in the middle of the worksheet.

* The Freeze Panes command is not available when you are in cell editing mode or when a worksheet is protected. To cancel cell editing mode, press ENTER or ESC. For information about how to remove protection from a worksheet

To add a comment

1.  Select the text or item that you want to comment on, or click at the end of the text.

2.  On the Review tab, in the Comments group, click New Comment.

3.  Type the comment text in the comment balloon or in the Reviewing Pane.

 Note    To respond to a comment, click its balloon, and then click New Comment in the Comments group. Type your response in the new comment balloon.

 

 

……..Back to home……..