Microsoft Excel

Microsoft- Excel

Excel is a application program. Excel is used for mathematical, statistical, scientific, graphics, tabulation, database and accounting purpose. It consists row and column. It has 256 columns and 65536 rows. Excel provides the ability to perform calculation, format reports, create charts (graphics), and even provides a simple database facility.

START MICROSOFT-EXCEL

To start Excel follow the following steps...

#     Click start button.

#      Point to program ,a sub menu display

#     Click excel/enter.                                                                                                                                                                                            

TITLE BAR

Title bar is horizontal area located at the very top of the screen.

MENU BAR

The menu bar is directly below the title bar and it displays the menu. A menu displays a list of commands such as File, View, Insert, Format, Tools, Window, and Help.

FORMULA BAR

Formula bar is display below the toolbar and above the sheet column name. In view mode the shape of formula bar is above in edit mode the shape of formula bar is below

WORKSHEET/SPREADSHEET

Worksheets is use to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations. Capable of every thing from simple to complicated calculation. Each worksheet is divided in row, column, and cell separated by grid lines. The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. The name of the active sheet is bold.

COLUMN

column are vertical divisions of worksheet. The column name are letter. The first column is column A, and the last is called IV. Each worksheet has 256 columns (A through IV).

ROW rows are horizontal division worksheet. Each row is separated by horizontal grid lines. Rows are numbered. The first row is 1 and the last is 65536. Each worksheet has 65536 rows (1 through 65536).

CELL

the intersection of row and column is a rectangle area called cell. Cell is the basic unit of spreadsheet. Worksheet consists of columns A,B,C,D, ......... and rows 1,2,3, …...... Column and row when combined make a cell, such as A12, B3, F11, M921 …..... A cell can contain a value, a formula, or a text entry.

CELL ADDRESS

cell address (reference) are the combination of column letter and row number. Each cell has unique address. For example, the upper left cell of a worksheet is A1.

GRIDLINES

The lines on the worksheet dividing it into rows and columns. These lines are normally non-printable and we easily examine the data, its rows and columns. These grid lines cut each other throughout the worksheet to make the boundaries for each cell.

MENUS

Menu is a list of command. You can use menus to give Excel instructions about what you want to do. Menu display a basic command. Most menus are located on the menu bar at the top of the excel window. There are total nine menus on menu bar, which are File, Edit, View, Insert, Format, Tools, Table, Windows, and Help. Every menu on bar contains list of commands.

FILE MENU

COMMAND

     1. NEW...  

CTRL+N

         This command is used to open a new spreadsheet file follow the following steps.

#      Click on the file menu

#      click new

#      to create a new, blank spreadsheet, click the general tab, and then double-click the spreadsheet icon.

     2. OPEN... CTRL+O:

          This command is used to open an existing spreadsheet file. Follow the following steps.

#      Click on the file menu

#      click open

#      An open menu dialog box appears on screen. In look in field enter drive name and folder name where document exist which you want to open.

#      Enter the name of the file in File name field and then click open button.

3. SAVE... CTRL+S

This procedure is used to save the active file with its current name or to save file first time.

#      Click on File in menu bar.

#      Click on Save

     4. SAVE AS…    (F12)

       This command is used to save the active file with a different file name, or folder or drive

#      Click on File in menu bar

#      Click Save As

#      Enter the new name of the file in save as dialog box

#      Click  Save button

  

5. CLOSING A SPREADSHEET (CTRL+F4 OR CTRL+W):

This command is used to close an opened spreadsheet. Follow the following steps.

#     Click on the file menu

#     Click close

To close all open spreadsheet without exiting the program, hold down shift, click on the file menu and then click close all.

EDIT MENU COMMAND

1. UNDO (CTRL+Z)

     This procedure is used to undo any changes you made. To                undo any changers follow the following steps.

                  Click undo in edit menu on menu bar

                  2. REDO (CTRL+Y):

This procedure is used to reverse the action of the undo command. To use redo follow the following steps.

Ø  Click redo in edit menu on menu bar

3. FILL (Ctrl+ D or Ctrl + R)

This command is used to copies the contents & format of the cell or selected into the cells, down, up, right, and left any one. Follow the following steps.

#     Select the range which you want to fill

#     Point to fill in edit menu

#      Select down, up, right , or left any one

4. Auto fill

#    select the cell that contains the data you want to copy

#  Drag the fill handle across the cells you want fill, and then release the mouse button.

5. DELETE  (DEL KEY)

This procedure is used to delete cells, rows or column

#    select cells, row or column you want to delete

#    on edit menu click delete

#    select any radio button from delete dialog box press OK button

     6. CUT (move) cell entries (ctrl + X & ctrl + V)

To cut a cell or range of cells content to another location. Follow the following steps:

#    Select the cell or range of cell you want to cut/ move.

#   Click on the edit menu

#    Click CUT

#    Move to the cell or select the cell where you want to move or paste

#       Click on the Edit menu

#    Click paste.

7.  COPYING CELL ENTRIES (CTRL+C & CTRL+V):

To copy a cell or range of cells contents to another location. follow the following steps.

*     Select the cell or range of cell you want to copy

*      Click on the edit menu  *      Click copy

*      Move to the cell or select the cell where you want to copy

*      Click on the edit menu   *       Click paste

      8. PASTE (CTRL+V):

To paste procedure is used to insert the contents of the clipboard at the insertion point. This command is only available if you have cut or copied an object, text, or contents of a cell or range of cells. Follow the following steps.

*   Click on paste in edit menu. Or press ctrl+v

9. CLEAR:

This rocedure is used to erase or remove the contents of a cell or a range of cell

* select the cells you want to clear

* click on the edit menu

* point to clear

* click any one from (All, contents, formats, or comments.)

10. FIND TEXT (CTRL+F):

This procedure is used to find/search selected cells or sheet for the character you specify follow following steps

* click on edit in menu bar

* click on find, a find dialog box appear

* Type the character to search in the find what field

* click on find next to find one by one

11. REPLACE TEXT (CTRL+H):

this procedure is used to replace character or word with another character or word in the current worksheet. To replace a particular word or piece of character follow the following steps

# click on edit on menu bar

# click replace, a replace dialog box appear

# type the character to search in the find field

# type the character or word to replace in replace with field

# click replace to replace one by one or click replace all to replace all words

VIEW MENU COMMANDS

1. Normal

This command is used to Switches to normal view, which is the default document view for most spreadsheet tasks. We use the following steps

#  Click on the View Menu bar

#  Click on the normal in view menu

2. Toolbars

This command is used to Displays or hides toolbars. For display or hide a toolbar we use the following steps

#  To display a toolbar, select the check box next to the toolbar name

#  To hide a toolbar, clear the check box.

3. Full Screen

This command is use to Hide most screen elements so that we can view more of your document. This command is use to show the full screen of our document. For this we use following steps

#  Click on the View Menu bar

#  Click on the Full Screen to view full document in the View menu bar

#  To switch back to your previous view

#  Click Full Screen or press ESC.

4. Zoom

This command is use to Controls how large or small the current file appears on the screen

*  Click on the View Menu bar

*  Click on the zoom

      INSERT MENU COMMAND

     1. INSERT CELLS / INSERT BLANK CELL

This command is used to insert a cell or range of cells in a work sheet. Inserts cells starting at the insertion point. You can choose to shift other cells in the table to the right or down

*   select the number of cells as you want to insert .

*   click on the insert menu .

*   A dialog box appear on screen

*   Click shift cells right or shift cells down radio button

2. INSERT ROWS

This procedure is used to insert a row in a worksheet. Follow the following steps

*   Click a cell in a row where you want insert a row.

*   click on insert menu

*   click on rows

To insert multiple rows

               This procedure is used to insert more than one                

                row in a works. Follow the following steps.

*  select the cell in the column where you want to insert rows.

*  click on insert menu

*  click on rows

3. INSERT COLUMN

Insert a column (single column)

This command is used to insert a column in a work sheet. Follow the following steps.

*    Click a cell in the column where you want to insert a column.

*    click on insert menu

      Click on column

      INSERTION OF MULTIPLE COLUMNS

This command is used to insert range of columns in a worksheet.

Follow the following steps.

@    Select the range of cells in the row where you want to insert the columns.

@    click on insert menu

@    Click on column.

4. WORKSHEET

Insert a new worksheet

This procedure is to used to add a single new worksheet to the left of selected sheet tab.

#    click on insert menu

#    click on worksheet

        Insert multiple worksheets

     This procedure is used to add new multiple worksheets to the left of selection tab.

#    Hold down shift

#   Click the number of worksheet tabs you want to add in  the open workbook.

#   Click on worksheet in insert menu.

5.   FUNTION WIZARD

About using function to calculate values

Funtions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. For example, the SUM function adds values or ranges of cells.

Arguments arguments can be numbers, text, logical values such as TRUE or FALSE, The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other funcations. For more information about using a function as an argument for another function, also known as nesting functions, click

Structure The structure of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the funtion name.

There are numbers of functions but the main functions are

a. date and time function

date

This function is used to return a particular date.

Syntax

DATE (year, month, day)

year: the year argument can be one to four digits. Excel interprets the year argument according to the date system you are using. Time

This function is used to return a particular time according to a specified format

syntax

= Time(hour, minute, second)

hour is a number from 0(zero) to 23 representing the hour.

Minute is a number from 0 to 59 representing the minute.

Second is a number from 0 to 59 representing the second.

Examples

Time (12,0,0) equals 12:00:00 P.M.

Time(16,48,10) equals 4:48:10 P.M.

b. SUM

This function is used to sum two or more number.

For example

=sum (2,4) To add 2, 4 the result is 6

=sum (A1:A10) To add number from cell A1 to cell A10 etc

c.     IF

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to coduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical expression if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

d. MAX

Return the largest number in a set of values

=MAX(number1,number2, …)

Number1, number2,…. are 1 to 30 numbers for which you want to find the maximum value.

Examples

If A1=10, A2=7, A3=9, A4=27 and A5=2 then:

=MAX (A1:A5) equals 27

=MAX (A1:A5, 30) equals 30

e. MIN

returns the smallest number in a set of values

syntax

=MIN(number1,number2,…….)

Number1, number2,…. are 1 to 30 numbers for which you want to find the minimum value.

Examples

If A1=10, A2=7, A3=9, A4=27 and A5=2 then:

=MIN(A1:A5) equal 2

=MIN(A1:A5, 0) equal 0

f. AVERAGE

this function returns the average (arithmetic mean) of the arguments.

Syntax

=AVERAGE(number1,number2,….)

Number1 , number2,…. Are 1 to 30 numeric arguments for which you want the average.

Examples

IF A1=10, A2=7, A3=9, A4=27 and A5=2 then:

=AVERAGE(A1:A5) equals 11

=AVERAGE(A1:A5) equals 10

=AVERAGE(A1:A5, 5) equals

=SUM(A1:A5)/COUNT(A1:A5) equals 11

g. COUNT

this function is used to counts the number of numeric cells within a range.

Syntax

=COUNT(range)

For example in figure

=COUNT (A1:A6) equals 4 because there are 4 numeric between A1 to A6.

h. MONTH

returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December)

syntax

MONTH (“year/month/day”)

Examples

MONTH (“6-may”) equals 5

MONTH (“2004/04/01”) equals 4

i.    DAY

Returns the day of a date represented by a serial number. The day is given as an integer, ranging from 1 to 31.

Syntax

DAY(“year/month/day”)

=DAY(“4-jan”) equals 4

=DAY(“15-apr-1998”) equals 15

=DAY(“8/11/1998”) equals 11

=day(“2001/10/10”) equals 10

j. YEAR

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.

Syntax

YEAR(“year/month/day”)

Examples

=YEAR(“7/5/1998”) equals 1998

=YEAR(“2006/05/01”) equals 2006

6. CREAT CHART

The procedure is used to create chart. Follow the following steps.

#   Select the cells that contain the dat that you want to appear in the chart. if you want the column and row labels to appear in the chart, include the cells that contain them in the selection.

v     Click chart wizard or click on chart in insert menu

v     Select chart type

v     Follow the instructions in the chart wizard

v     At the last click finish

Format menu command

1. FORMAT CELL AND TEXT

This procedure is used to change the layout (format) of a cell that is font, border, alignment  etc .

      Select a cell or range of cell you want to format.

      Click on the format menu.

      click cells

      Select different option from format cell dialog box.

      click ok

     TO INSERT DECIMAL PLACES

o       Select a cell or range of cell you want to format.

o       click on the format menu

o       click cells

o       format cell dialog box appears

o       select from category list box

o       different format number appear, select any format from them

o       enter number of decimal you want in decimal places  box

o       press ok box

TO CONVERT SELECT CELLS INTO TEXT

 This procedure is used to convert selected cells into text even if numerical will be treated as text and no calculation is performed on text.

#      select a cells or range of cell you want to format .

#     click on format menu

#      click cells

#     format cell dialog box appear

#      select text from category list box

#     press ok button

   

  2. BORDER AND SHADING

 This procedure is used to select border for selected cells

v     select the cells you want to add border to

v     click on the format menu

v     click cells

v     format cell dialog box appear

v     click on border tab, different border option appear

v     select different option according to your requirement

v     press ok button

SHADING

This procedure is used to select fill color for selected cells.

v     select the cells you want to add shad to.

v     click on arrow next to fill color button on formatting toolbar

v     select any color from palette.

3. POSITIONING CELLS AND TEXT

This procedure is used to change the position of the selected cells. That is horizontal and vertical etc. follow following steps

v     select the cells you want to change the position .

v     click on format menu

v     click cells

v     format cell dialog box appear

v     click on border tab , different border option appear

v     click on alignment tab, different option appear

v     select left  or right or center from alignment text box

v     select top or bottom or center or justify from vertical text box

v     select other option if you want

v     press ok button

4. APPLY AN AUTOFORMAT TO A RANGE

This procedure is used to convert the selected cell data into preset table format.

v     select the range you want to format

v     on the format menu, click autoFormat

v     click the format you want.

TOOLS MENU COMMAND

1.      SPELL CHECK (F7)

This procedure is used to checks spelling in the active workbook. To apply spell check follows the following steps.

#    Click on spell in tools menu

#     Spell check dialog box appear

#     Select any suggestion for very word

#   Click on change button

#   If you don’t select any option then press ignore button

2.AUTO CORRECT

This procedure is used to correct word automatically. As you enter a word computer automatically replace it to its store style.

v     On the tools menu, click autocorrect

v     In the replace box, type a word or phrase that you often want to replace

e.g usu

#    In the with box, type the word that you want replace with e.g usually

#     Click add

3.PROTECTION

This procedure is used to protect a workbook or worksheet. To protect a sheet then you cannot change the content of cells

#    Click on tools menu

#     Point to protection, in drop down menu click protect sheet or protect workbook. Or protect and shared workbook

#     Click different option, enter password in protect sheet dialog box. And click on ok button

DATA MENU COMMAND

1. SORTING

This command is used to rearranges the information in selected rows or cells alphabetically, numerically, or by date in ascending or descending order.

#     select arrange of cell to sort

#      click on the data  menu

#      click on sort a sort dialog box display on screen

#      select column on which you want sort as shown above column A and you may also select other option 

#      click sort ascending or sort descending 

#      click ok

2. FILTER

Filtering is a quick and easy to find and work with a subset of data in list.

Filter list display only the rows that meet the criteria you specify for a column.

Microsoft excel provides two commands for filtering lists

     AUTO FILTER

Which includes filter by selection for simple criteria.

     ADVANCED FILTER

     For more complex criteria unlike sorting, filtering does not rearrange a list . Filtering temporarily hides rows, which you do not want displayed

#      Select range of cells to filter

#      click on data menu

#      click filter

#      select auto filter or advanced filter

#      click arrow in corner of select range and apply different option

Current Bill

Formula:

=IF(C3<=200,C3*1.75,IF(C3<=400,C3*2.5,IF(C3<=500,C3*3.75,C3*4.5)))   write to enter.  

Salary Sheet

Formulas:

Over time:  =IF(C3>8,C3-8,0)   write to enter. 

Payment:  =IF(D3>0,D3*20+8*15)   write to enter. 

Commission Sheet

Formula: =IF(B3<10000,B3*2.5%,IF(AND(B3>=10000,B3<=30000),B3*5%,IF(AND(B3>30000,B3<=50000),B3*7%,IF(B3>50000,B3*10%))))

Salary- Sheet

Formula:

# House Rent formula- =IF(C3>=6000,30%,IF(C3>=4500,40%,IF(C3>=3000,45%,IF(C3>=900,50%))))*C3

# Medical-

=C3*10%    Enter

# Provident Fund-  Provident Fund Basic- 10%

=C3*10%    Enter

# Tax Formula-

     # Income Tax- Basic 900 If you are below 0%

# Income Tax- Basic 900 If the money Above 2%

     # Income Tax- Basic 3000 If the money Above 4%

      #Income Tax- Basic 4500 If the money Above 6%

     # Income Tax- Basic 6000 If the money Above 8%

Formula-  =IF(C3>=6000,8%,IF(C3>=4500,6%,IF(C3>=3000,4%,IF(C3>=900,2%))))*C3 

   # Total  Formula-        =C3+D3+E3-(F3+G3) 

Complete Salary-Sheet

Code	Name	Basic	H-rent	Medical	P-fund	Tax	Total
1	Muaj	5200	2080	520	520	312	6968
2	Usama	4500	1800	450	450	270	6030
3	Nayem	1700	850	170	170	34	2516
4	Moriam	6200	1860	620	620	496	7564
5	Noorani	1500	750	150	150	30	2220

                               End. Abdul Khaleque, Bangladesh.