microsoft excel

 Microsoft Excel: The Basics

  Micro soft excel is one of the windows family application. Ms excel is a powerful spreadsheet program. 

  Starting  the excel  program  :-

ü Click the start button on the windows taskbar

ü  open program

ü open Microsoft office

ü  click Microsoft office excel 2003

     Exit ms – excel

Ø Click file menu

Ø Click exit

Ø Click on yes to save or not to exit with out saving

Work book in excel is defined as an excel file containing multiple spreadsheets within it. The spread sheets have unique names in the works book. The main elements of the excel screen are:

Ø Formula bar - formula bar which shows the cell address and it’s containing.

Ø Row headers – rows which represented by numbers. Starting 1 up to 65536.

Ø Column headers – column which represented by alphabets. Starting from A, B, C, D …Z, AA, AB – AZ BA, BB, BC, IV total 256 columns.

Ø Active cell – cells where the user store his or her data. The intersection of columns and rows.

Ø Scroll bars – to see a different area of the sheet, use the scroll bars.

-      One row up or down click the arrows in the vertical scroll bar.

-      One column left or right clicks the arrows in the horizontal scroll bar.

Ø Work sheet – sheet where you store block parts of a work book.

Ø In a book there are three default sheets (sheet 1, sheet 2, and sheet 3) but you can increase the number of sheets, as you want.

    Cell references

    On work sheet, the rectangular area where a row 

       And column intersect is known as cell. Each cell has a reference identified by its column and row headings. For example   

       A1 represents the cell in column a, on row 1

        F10 represents the cell in column F, on row 10

    Moving around work sheets.

ü Ctrl + home = first document (file).

ü Ctrl + end = last document.

ü Pg up = vertically first file in screen.

ü Pg down = vertically back screen.

ü Home = screen front and back right and left.

ü Alt + pg down = horizontally screen back (last).

ü Alt + pg up = horizontally screen first.

ü The go to (F5) = if you went find your file.

Ø Click edit

Ø Click go to

Ø Write your file in reference   

Ø Ok

           OR

Ø Press F5 keys

Ø Write your file in reference   

Ø Ok

    Selection system in ms – excel work books by mouse , press shift ,press ctrl

Ø Text in cell – select the cell, double click

Ø A single cell–click the cell, or press the arrow keys to move to the cell.

Ø For entire row – click on the row head.

Ø For enter column – click on the column head.

Ø A Range of cells – click the first cell of the range, & then drag to the last cell.

Ø A large range cells – click the first cell in the range, & then hold down shift and click the last cell in the range.

With the ms excel program you can create:

ü Spread sheets   

ü Charts

ü Calculation fields

ü   Data bases and so on

    Creating a new sheet and save it.

 Add a single work sheet

Ø Click insert menu

Ø Click worksheet

Add multiple worksheets

Ø Hold down shift, and then select the same number of existing work sheet tabs

Ø Click insert menu

Ø Click work sheet

Insert a new sheet that’s based on a custom template.

Ø Right click a sheet tab

Ø Click insert menu

Ø Double click the template for the type of sheet, or select work sheet and then ok.

Save a file

Ø Click file menu

Ø Click save

Ø Choose the target of save in

Ø Type file name

Ø Click save

    Open an existing files

Ø Click file menu

Ø Click open

Ø In the look in box, click the drive, folder that contains your workbook.

Ø In the folder list, dabble-click folders until you open the folder that contains the work book you want.

Ø Finally dabble click the workbook you want to open.

    Delete sheet from a work Book.

Ø Select the sheet you want to delete.

Ø Click edit menu

Ø Click delete sheet

    Rename a sheets

Ø Right click up to sheets

Ø Rename

Ø Write in file name

OR

Ø Select the sheet you want to  rename

Ø Click format menu

Ø Open sheet

Ø Click rename

Ø Type the new name

OR

Ø Dabble click on the sheet you want to rename

Ø Type the new name

    Color – coding a sheet tab

- Sheet tabs can be color – coded (a colored line is drawn under the sheet name).

·        Right click on the name sheet tab.

·        Click  tab color

·        Choose on one of the colors offered (green, for example).

·        Click ok

OR 

·        Select sheet

·        click format menu

·        open sheet

·        click tab color

    Background in sheet

·        Select sheet

·        Click format

·        Open sheet

·        Click background

    Delete Background

·        Select sheet

·        Click format

·        Open sheet

·        Click delete background

    Hide a sheet

Ø Select the sheet you want to hide

Ø Click format menu

Ø Open sheet

Ø Click hide

    displaying a hidden  sheet

Ø click format menu

Ø open sheet

Ø click unhide

Ø In the unhide sheet box, Dabble click the name of hidden sheet OR select the name of hidden sheet & then click ok.

    Hide a work book

Ø Open the work book you want to hide

Ø Click window menu

Ø Click hide

    displaying a hidden work book

Ø Click window menu

Ø Click unhide

Ø In the unhide work book box, Dabble click the name of hidden sheet  OR select the name of hidden sheet & then click ok

    Entering the data

1.     Click in cell B2 and type – Charity Barbecue

2.     Press the enter  key ( or one of the arrow keys)

3.     Click in cell B4 and type – Allocation

4.      Press the down – arrow key to move to cell B5

5.     Type – Burger & Bum

6.     Press the down – arrow key and then complete column B as shown above

7.     Enter the data in columns C and D

    Copying and pasting  

The text in cells E5:E7 (in other words E5,E6, and E7 ) is the same as in the range B5:B7 so you can copy that.

1.     Point to cell B5 & hold down the mouse button.

2.     Keeping the button held down, drag the pointer to B7 & then release the mouse button. The range B5:B7 will be highlighted.

3.     Click on the copy button on the tool bar (or select copy from the Edit menu).

4.     Click in E5 (where you want the copy to be placed).

5.     Click the paste button on the toolbar (or select paste from the Edit menu).

    Editing  a cells contain

    for example :  56432

Ø Dabble click over the number

Ø Change number

            Or

Ø F2  press

Ø Change number

    Over  Writing a cells contain updating

Ø 60 = no enter

40

    Filling a range : series of number

Ø Write number (10)

Ø Edit

Ø File

Ø Series

Ø Stop value (80)

Ø Choose Rows or column

Ø Ok

            *** 10, 15, 20, 25, 30, 35, 40… 80,

    Week days :

Monday, Tuesday, Wednesday, Thursday, fray day, Sunday

Ø Write first day

Ø  Monday  ………………..+ drag

    Months

Ø Write first name months

Ø Select rows

Ø Edit menu

Ø Fill

Ø Series

Ø Auto fill

Ø ok 

             Or

Ø first name month

Ø January ………..+ drag

    Dates

Ø Write date

Ø Select rows or columns

Ø Edit

Ø Fill

Ø Series

Ø Choose date unit day, week day, month, year,

Ø Ok

     Or

Ø 12/09/2003……. + drag

    Insert column

Ø Select the columns

Ø Click Insert menu

Ø Click column

    Insert row 

Ø  select the rows

Ø  click insert menu

Ø  click row

     insert blank cells

Ø  select range

Ø  click insert menu

Ø  click cells

Ø  Click shift cells right or shift cells down, as you want.

     Hide a row

Ø  Select the row you want to hide

Ø  Click format menu

Ø  Open row

Ø  Click hide

     Display a hidden row

Ø  Click format menu

Ø  Open row

Ø  Click unhide

     Hide a column

Ø  Select the column you want to hide

Ø  Click format menu

Ø  Open column

Ø  Click hide

     Display a hidden column

Ø  Click format menu

Ø  Open column

Ø  Click unhide

     Resizing columns & rows

       Columns

Ø  Select  text

Ø  Format

Ø  Column

Ø  Width

Ø  Column width type no.

Ø  Ok

    Or

Ø  Drag  +

                       Rows

Ø Select text

Ø Format

Ø Row

Ø Height

Ø Row height type no. 

Ø ok

OR

Ø Drag +

    magnifying a sheets data :

Ø select text

Ø view

Ø zoom

Ø magnifying

Ø choose number : 200%,100%, 75%, 50%, 25%,0%

Ø ok

    sorting a range in ….Ascending & descending

-      Sorts the current selection in ascending order.

Ø write the text & number

Ø data

Ø sort

Ø sort by choose : Ascending & descending

Ø ok

    moving the filtered data to another sheets

Ø example sheet 1 select

Ø right click

Ø choose cut

Ø another sheet paste   

    on creating  calculating formula

Ø formula reference the cells rather than the values

Ø to create on formula spacey  the location

Ø type is equal sign (=)

Ø create : addition (+), subtraction( -), division(/), multiplication(*)

Example :- addition

Ø values  60    59    87   25

                                   31    90   48   14

                                   08    53   72   03

Ø 60  + 59 +87 + 25 location

Ø 60  + 59 +87 + 25    =

Ø 60  + 59 +87 + 25  Enter 231

    Copying a formula

Ø 60 + 59  = 119±

31 + 90 =        .

08 + 53 =        .   

48 + 72 =        .

                    Drag

    Updating a formula

Ø 100 – 50 = 50

         -30 (Enter) =80

         -90 (Enter) = 10

    Calculating number with in bracket

Brackets: example = (75 / 5)*4

                                        15 * 4 Enter

                                             60

    Using the power of 2,3,4  for again number:

          Power: example = 5 ^ 2                = 6^4                  = 3^3   

                                           (5*5)                (6*6*6*6)           (3*3*3)

                                             25                       1296                    27

    Creating sum formula :

           Auto sum a drop down menu of available mathematical Operations to perform.

              A                B               C                D             E (total)         F (Average)

1.          10              20              30               10                70

2.           5               15              25               40                85

TOTAL   15              35              55               50              155

AVE.        7.5            17.5           27.5             25                  

Sum formula    by column

                            = Sum (A1: D1) Enter = 70

    Sum formula by row

                             = Sum (A1:A2) Enter = 15

    Average formula  by column

                               = Average (A2:D2) Enter= 38.75%

    Average formula by row

                  = Average (A1:A2) Enter = 7.5%

    Color fonts & back ground color (patterns)

- Font color: select a color to apply to a selection of the text.

 - Fill color: select a color to fill the background of a cell with.

             10               20       30

             40               50       60

Ø Select

Ø Format

Ø Cells

Ø Font color

Ø Patterns  color

    Formatting a range of data by

      General formatting:

Ø Format

Ø Cells

Ø Number

Ø General

    Comma formatting:      ,

                   Example - 890456890:   890,456,890    

    Currency  formatting:

                                Example- 1.5 = $1.50

Ø Format

Ø Cells

Ø Number

Ø Currency

    Percentage formatting: 

                              Example: 1.5 = 150 %

Ø Format

Ø Cells

Ø Number

Ø Percentage

    Insert the current date & format it.

              To insert the current date:

Ø = today ( )

Ø Enter

              Or (key board) 

Ø Ctrl + semi colon

    Insert the current time & format it:

               To insert the current date & time:

Ø =  now ( )

Ø Enter

                       To insert the current time: (key board)

Ø Ctrl + shift :

    Aligning data within their cells

Ø Format

Ø Alignment

Ø Choose  Horizontal  & vertically

Ø Top, center, bottom

    Center across columns

-      Merge and center – combine two selected cells in to one new cell that  spans  the width of both and center the contents of this new cell.

Ø Select

Ø Format

Ø Cells

Ø Merge cells

Ø Center

Example

  

A	B	C
TKOTOYS       TKOTOYS
578	794	500
123	256	312
908	89	660

    Setting  borders to a selected  range

Ø Select

Ø Format

Ø Cells

Ø Border

Ø Choose style

Ø Outline &  inside

Ø Ok

                Or

Ø Top, center, bottom, left, center, right

                         Example        456      789                 

                                                905       782

                                                 453       123

    Turning on (off) the grid lines

Ø Tools

Ø Options

Ø Grid lines

Ø Ok

    Removing  the borders

Ø Tools

Ø Options

Ø Grid lines tick

Ø Ok

    Creating a chart & modify it

Ø Select numbers

Ø Insert

Ø Chart

Ø Column

Ø Choose chart sub-type

Ø Next

Ø Choose row or column

Ø finish

Example:           

	A	B	C	D	E	F	G
1	NUMBERS OF TICKETS SOLD FOR SUMMER BARBECUE
2
3			1997	1998	1999	2000
4		ADULTS	120	250	310	277
5		CHILDREN	16	40	60	116
6
7

    Titles Chart

·        Select chart area

·        Chart

·        Chart options

·        Titles

·        Write titles

·        Ok

    Value

·        Select

·        Chart

·        Chart options

·        Data labels

·        Value tic

     chart area color

·        Select  chart area

·        Open Format

·        Click Selected chart area

·        patterns

·        choose border

Ø   click automatic

Ø   choose style

Ø   choose color

Ø   choose weight

Ø   choose shadow or round corner

·        choose area

Ø   click automatic

Ø   choose color

·        ok

     Plot area color

·        Select plot area

·        Open Format

·        Click Selected plot area

·        Patterns

·        Choose border

Ø   Automatic click

Ø   Choose style

Ø   Choose color

Ø   Choose weight

·        Choose area

Ø   Click automatic

Ø   Choose color

·        ok

    Color series

·        Select

·        Open Format

·        Click Selected data series

·        Patterns

·        Choose border

Ø   Click automatic

Ø   Choose style

Ø   Choose color

Ø   Choose weight

Ø   Click shadow

·        Choose area

Ø   Click automatic

Ø   Choose color

·        ok

    Axes

·        Select chart area

·        Chart

·        Click chart options

·        Axes

·        Click category ( x )

·        Click value ( y )

·        Ok

     Gridlines

·        Select chart area

·        Chart

·        Click chart options

·        Gridlines 

·        Choose category ( x )

·        Choose value ( y )

·        Ok

     Legend

·        Select chart area

·        Chart

·        Click chart options

·        Legend

·        Click show legend

·        Choose placement

·        Ok

     Data table

·        Select chart area

·        Chart

·        Click chart options

·        Data table

·        Click Show data table

·        ok

    Resizing and Moving The Chart

·        To Select the Chart, Single Click inside the Chart area.

·        Point to a Blank area of your Chart, Then Click and Drag to Move the Chart to a new Position on the Work Sheet.

·         Point to one of the Chart’s corners and drag it to resize the Chart.

·        In the font size box on the formatting toolbar.

·        Click out side the chart area to de- select the chart.

    Changing a chart’s source data

-      To change a chart’s data source, you must select the chart source data form the menu. Then do the following.

·        Select chart area

·        Click chart menu

·        Click source data

·        Open data range tab

·        Choose data range box

·        Click ok

****************************************