Use of “Insert Function” in Microsoft Excel

Use of “Insert Function” from Formula Tab:

Using “Insert Function” button in Formula tab we can use all the available functions in Excel. The following box will appear on the screen. It contains all formulas and they are categories wise divided. We can avoid any syntax error using formula from this option (Insert function)

As picture shows, dialog box is appeared for sum function. We can type the numbers or range in the text boxes. More importantly we can select range using mouse, for this we can click red-arrow button, by this sum dialog box will reduce its size and then after selecting range we can re-click on this red-arrow button.

If Function in Microsoft Excel

If Function:

One of the important logical function is if. If function is used to check a particular condition and value of function if the condition is true or false.

Syntax :

=if(Condition, value_if_condition is true, Value_if_condition is false)

e.g.

=if(F2>100, “Yes”,”No”)

For example, in the above function we are checking cell F2,If the value in F2 is greater than 100 then function will return Yes and if the value in F2 is not greater than 100 (mean false condition) then function will return No in active cell.

Autosum Feature and Relative Cell Reference

Autosum Feature and Relative Cell Reference:

Excel provides Autosum button in Home Tab (Editing group). This includes 5 commonly used functions sum, average, count, max and min. Using this feature we can avoid typing these five functions. But caution is required while auto-selecting range.

In the given example, autosum is used in cell F2. Now we can place the mouse handle in cell F2 and can copy formula using fill handle(dragging downward), Excel will copy formula to all the below cells. While copying formula cell addresses automatically get changed, this feature is known as relative cell reference.

 

Use of Formulas in Microsoft Excel: (How to perform calculations in Excel)

Use of Formulas in Microsoft Excel: (How to perform calculations in Excel)

One of the important features of Excel is its calculation abilities. Excel provides large number of formulas for calculations. Built-in formulas are known as Functions. Before going to use Functions, we must learn use of formulas; it gives us ability to use formulas in any customized calculations.

Every calculation, formula starts with equal to sign (=)

E.g. if we want to add number 4 and 5 then we must type =4+5 (now press Enter), excel will perform addition and gives us answer i.e. 9. Active cell will display result of 4+5 but formula bar is displaying =4+5

In this manner we can perform any type of calculations like:

=90-12+45*304-17/3-3 (Now just press Enter to get result)

For this kind of calculations Excel uses BODMAS rule.

In the same manner we can use cell addresses in calculation, for example to multiply A1 and A2, we must type =a1*a2, excel is going to display result in active cell.

Use of built-n functions:

There are large number of built-in formulas in excel, known as Functions. These functions are in different categories, like statistical, logical, financial, date, text, mathematical and engineering formulas.

Starting with simplest and common formula that is Sum, sum is used to add number. Either we can use comma separated numbers or a range of cells. Syntax is :

=sum(6,7,8,9) {press Enter}

Or we can use =sum(a1:a10)

A1:A10 is known as range

A1: starting cell address

A10: last cell address

(:) colon sign is used in between to form a range of adjacent cells.

Comma(,) can be used to sum non-adjacent cells.

=SUM(a1, c23, a12,e12)

Some more commonly used formulas are:

=AVERAGE(num01,num02,num03) this will return average of numbers or range

=PRODUCT(num01,num02) this will return multiplication of numbers

=POWER(number-x, power-y)

It will result into x^y

=SQRT(576) this will result into square root of number.

=MIN(A1:A10) it will find minimum(smallest) number among the range.

=MAX(A1:A10) it will find maximum(largest) number among the range.

=COUNT(11,23,34,42,51,67) it will result count of numbers, not sum or addition. So result is 6.

 

Formatting sheet using Row height and Column Width:

Formatting sheet using Row height and Column Width:

By default width of a column in Excel sheet is 8.43 and height of row is 15. But we can change row height and column width according to our requirement. For this we need to use Home Tab---Cell group---Format option.

Manually we can change row height and column width using mouse pointer. Keeping mouse handle at splitter between each column and rows, by dragging mouse we can resize rows and columns. By double clicking on Splitter between rows/Columns, Auto Fit Row height and column width feature can be used. 

Cell Editing in Microsoft Excel:

Cell Editing in Microsoft Excel:

Cell editing in Microsoft Excel means “Making changes in any cell.”  If you have entered word Rakesh in any cell, and you want to make it Rakesh Kumar, as we start typing Kumar…Excel removes word Rakesh from cell. To complete this task we need cell editing, in simple words instead of mouse handle we need cursor in cell. Following three methods can be used to edit cell:

First: we can insert cursor in cell by clicking mouse on cell.

Second: We can edit cell from Formula bar (Just below the Ribbon)

Third: Directly press F2(Function Key 2), this will provide cursor in current cell.

How to Delete Rows, Column and Work Sheet in Microsoft Excel:

How to Delete Rows, Column and Work Sheet:

If we want to delete some rows, columns from worksheet/database, we can use Delete Option from Cell group of Home Tab. It will delete current row or column where Mouse Handle is positioned.

Same option can be used to delete the entire work sheet. After clicking delete sheet option, excel displays a warning that whole data is going to be delete.  More importantly delete sheet action cannot be undo, so confirm it before clicking delete sheet. Undo can be used after deleting rows and column.

Delete sheet option can be used from right click on work sheet tab. Remember that undo can not be used after deleting sheet.

How to Insert Rows, Column and new work sheet in Microsoft Excel

How to Insert Rows, Column and new work sheet in Microsoft Excel:

As we know there are 16384 columns and 1048576 rows available in MS excel 2007/2010. But if we have prepared a list or database and after that we need row/column in between then there are options to insert rows and columns also.

Therefor we have to use Insert Option in Home Tab (Cells Group), don’t confuse with Insert Tab. Its Insert option in Home Tab. Using this option we can insert row, column, cells and even new worksheets also.

By default there are three sheets in workbook. (That default value can be changed from Excel Options)

Insert Row option provides an additional row above the mouse handle position. It does not ask for row above or below (like MS word table menus). Therefore Mouse handle position is important for this option.

Same case with Insert Column option, Excel provide additional column towards left side to Mouse handle.

Note:

1.       Adding new column is just adjustment into total available column (16384 columns). If all the 16384 columns are already filled, then excel will not insert new column. More importantly if anything entered in last column (XFD) then also excel does not insert new column.

2.       Adding new Row is just adjustment into total available row (1048576 rows). If all the 1048576 rows are already filled, then excel will not insert new row. More importantly if anything entered in last row (1048576) then also excel does not insert new row.

3.       There is no particular limit of maximum number of work sheet, it depends on memory of computer.

 

 

 

Use of Sub Total in Microsoft Excel 2007/10

Use of Sub Total in Microsoft Excel 2007/10:

Let’s consider an example to understand sub-total in Excel. Suppose we have different product in our shop /business, and we maintain record of each sell. With the help of sub-total we can calculate, analyze product wise sell and money earned by each product category sell.

Suppose we have different products on Electronics store like Tv, Washing Machine, Microwave, Refrigerator, LCD, LED. Total we sell these 6 products with different specification (like different size, capacity).

First, we just need to type product name and price.(Preparing list)

Second, we need to sort list by Product name(Item wise sorting)

Third, use of sub-total in Data menu to analyze income from different products.

Data after sub total

Multiple Sheet Referencing in Microsoft Excel:

We can use (refer) cell of one sheet in any calculation on another sheet. For this we just have to use following syntax:

Sheet_name!cell_name

If we have changed the sheet name (sheet rename) then we must use that name. The picture is showing total of A1 cell of sheet1(value:10), Sheet2(value:20), Sheet3(value:30) on 4th Sheet.