Saturday

MS-EXCEL 2003

Microsoft Excel is the popular Windows spreadsheet program that provides worksheets, charts, database and list operations, and application programming all in one software environment. It is used for managing, analyzing and presenting data in a graphical manner. It is MS-Office Package  developed by Microsoft Corp. USA.
                                          
Spreadsheet:- Spreadsheet is worksheet  that organizes data into rows and columns, and  one work sheet contains the 256 columns and 65536 rows. Worksheet allows the user to perform calculations on it and express the results in reports. The spreadsheet has rows and columns. Each intersection of a row and  a column forms a cell, in which you store data. A cell accepts four types of data: Characters, Numeric values, Formula and Functions. They are used for tracking expenses, budgeting, Inventory Management, Accounting, Production Planning etc. Spreadsheets may also be referred to as worksheets. Examples of Popular spreadsheet packages are Microsoft Excel, Lotus-123 etc.
Work Book :- A work book is a file like a document in MS-Word when you save your file by giving file name then it will renamed with your file name (e.g. Book1 to Anant.xls). A work book contains 256 worksheets. By default 3 worksheets appears. Other you can insert form insert menu.
Features of Excel:
  1. Windows Based on an Application : - Like all windows application it has tool bars, short menus, Auto Correct, Online help and wizard.
  2. Work Book : - Work book contain or holds one or more work sheets.
Starting MS-Excel
Steps:
1.          Click on Start menu
2.          Click on Run…
3.          Type the Excel and click on Ok button
Or, Click on StartàProgramàMS-OfficeàMS-Excel
Then excel window will appear
Moving  Cell Pointer on the Work Sheets.
Right Arrow Key: -                   To move the cell point one cell right.
Left Arrow:-                            To move the cell pointer one cell left.
UP Arrow: -                            To move the cell pointer one cell up.
Down Arrow :-                         To move the cell pointer one cell down.
Page UP  :-                               To move the cell pointer one screen up.
Page Down:-                            To move the cell pointer one screen Down.
Tab key  :-                               To move the cell pointer one cell right.
Shift Tab : -                              To move the cell pointer one cell leftEnter key
                                                To move the cell pointer next row.
Ctrl + Right Arrow: -                To move the cell pointer end of the column.
Ctrl + Left Arrow : -                 To move the cell pointer first of column.
Ctrl + Down Arrow:-               To move the cell pointer end of  the rows.
Ctrl + Up Arrow : -                  To move the cell pointer first of the row.
Ctrl + Page Down  : -               To open or switch to next or second sheet.
Ctrl + Page UP : -                     To open or switch to previous sheet.

Selecting Cell or cells

By Mouse

                                Drag and drop method.

By Keyboard

                                Pressing Shift key + Arrow keys (Up, down, righ, left)

Deleting data from the cell or cells

Steps:
1.        Select the required cell or cells.
2.        Press Delete key from the keyboard.

Renaming Sheets

Steps:
1.        Right button click  at required sheet.
2.        Click on Rename
3.        Type the sheet name and press enter key.
Or,
Double click on Sheet Tab and type the sheet name and press Enter key

Deleting Work Sheets

Steps:
1.        Right button click at required sheet.
2.        Click on Delete
3.        Click on Ok
Or,
1.        Select required sheet.
2.        Click on Edit menu
3.        Click on Delete Sheet
4.        Click on Ok button.
Copy Sheet
Steps:
1.        Right button click at sheet tab
2.        Click on Move or Copy
3.        Tick on at create a copy
4.        Click on Ok button
Move Work Sheet
Steps:
Select the required sheet and drag and drop the required place.
Page Setup
The Page setup command lets you to set the page margin, page Size, paper orientation(tall or wide  and also you can give the Header and footer.
Steps:
  1. Click on File menu
  2. Click on Page Setup
  3. Then a page setup dialog box will appear.
  4. Click on Page and set the size of the paper and set the paper orientation (Portrait or Landscape )
  5. Click on paper margin and set the margin of the page(left, right, top and bottom)
  6. Click on Header and footer  and select custom header and custom footer from the dialog box and specify the header and footer.
  7. At last click on Ok button.
Note : - You can create header and footer from the view menu also.
Print Area
Being a large work area of the sheet, you have to print only required data from the sheet, To do this Select your required data and set the print area.
Steps:
  1. Select your required data to which you want to print.
  2. Click on File menu
  3. Click on Print Area --> Set Print Area
Or, Click on Sheet from the page setup dialog box and range the required sheet.
Clear the Print Area.
  1. Select your print area.
  2. Click on File menu
  3. Click on Print Area --> Clear Print Area

Custom Views

Creates different views of a worksheet. A view provides an easy way to see your data with different display options. You can display, print, and store different views without saving them as separate sheets.
Steps: -
  1. Prepare your data at any place of the work sheet.
  2. Click on View menu
  3. Click on custom views...
  4. Click on Add and type the custom view name
  5. Click on Ok button
  6. To show, that data again click on view menu
  7. Click on Custom View...
  8. Select your custom view name and click on Show.
Inserting Rows and Columns
Steps:
1.                  Select row or column or position the cell pointer at required cell
2.                  Click on Insert menu
3.                  Select Row or column as your need.
Note : - The row will display above of the selected row or cell. and column will display left of the cell or column.
Deleting Row and Column
Steps:
1.                  Select required row or column to which you want to remove.
2.                  Click on Edit menu
3.                  Click on Delete...
4.                  Select or tick on entire row or entire column
5.                  Click on Ok button
Conditional Formatting
The conditional formatting command lets you to format the data according to conditional in the cells. You can specify three conditions from the dialog box.
Steps:
1.                  Select your cells or data
2.                  Click on Format menu
3.                  Click on Conditional Formatting
4.                  Then select the conditions and set the values
5.                  Click on Format...
6.                  Specify the font, font color, Style , border and pattern etc.
7.                  Click on Ok button.
8.                  For next condition Click on ADD and again give the condition and  Click on Format
9.                  repeat steps 6, 7 and 8 and at last click on Ok button.
Increase or decrease the Height of the Row
Steps:
1.                  Select required row or rows
2.                  Click on Format menu
3.                  Click on  Row--> Height
4.                  Type  the value for the height of the row in Pixel.
5.                  Click on Ok button.
Or, Place the mouse pointer at row number and drag and drop.
Increase or decrease the  width of column
Steps:
1.                          Select required column
2.                          Click on Format menu
3.                          Click on Column -->Width
4.                          Type the value for column width
5.                          At last click on Ok button.
or, Drag and drop mouse from column heading
Auto Fit Selection
The autofit selection command lets you to automatically fit the columns as text.
Steps:
1.        Select your  data or text.
2.        Click on Format menu
3.        Click on Auto fit selection
Note : -You can hide or un
hide row and column by using hide column and row.
Formatting Cells
The format cell command lets you to format the cell or data on the cell (e.g Number, Alignment, Font, border, pattern etc)
Steps:
1.                  Select your required cell or cells
2.                  Click on Format menu
3.                  Click on Cells...
Or, Right button click at required selected cell and Click on Format cell
Then a format cell dialog box will appear.
1.                  Click on Number and set the number format.
2.                  Click on Alignment and set the alignment, Wrap, shrink, merge, text direction etc.
3.                  Click on Font and select the required fonts, styles, size, color etc.
4.                  Click on Border and set the border of the cells.
5.                  Click on pattern and set the pattern color for the background of the cells.
6.                  At last click on Ok button.
Fact (Number)
Returns the factorial of a number, equal to 1* 2* 3.......* number.
Syntax:
=Fact(number)<enter>
PI( )
Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
Syntax:
=PI() <Enter>
=3.14
Power( )
Returns the result of a number raised to a power
Syntax:
=power(number, power) <Enter>
Example,
=power(2,3)
=8
Sqrt ( )
Returns the square root of a number.
Syntax:
=Sqrt(number)<enter>
example,
=sqrt(25)
=5
Degrees()
Converts radians to degrees.
Syntax:
=Degrees(angle) <enter>
Radians(angle)
Converts the degrees to radians.
Odd ( )
Rounds a positive number up and negative number down to the nearest odd integer.
Syntax:
Odd(number)<enter>
=Even()
Rounds a positive number up and negative number down to the nearest even integer.
Syntax
=Even(number) <enter>
Example,
=even(5)
=6
2. Statistical Functions
Average ( )
Returns the average (Arithmetic Mean) of its arguments, which can be numbers or names, arrays or references that contains numbers.
Syntax:
=Average(number1,number2, .....) <enter>
Example,
=average(12,4,2,5)
=
Count ( )
Counts the number of cells that contain numbers and numbers within the list of arguments.
=Count(value1,value2, ...............) <enter>
Example,
=count(range of cells)
=
Countblank( )
Counts the number of empty cells in a specified range of cells.
Syntax:
=Countblank(range) <enter>
Example,
=countblank(range of cells)
=
Frequency( )
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.
Syntax:
=Frequency(data_array, bins_arry) <enter>
Max ( )
Returns the largest  value in a set of values. Ignores logical values and text.
Syntax:
=Max(number1,number2,...) <enter>
Example,
=MAX(range of cells)
=
Min( )
Returns the smallest number in a set of values. Ignores logical values and text.
Syntax:
=Min(number1,number2,...) <enter>
=Example,
=Min(range of cells)
=
Median( )
Rerturns the median, or the number in the middle of the set of given numbers.
Syntax:
=Median(number1, number2,......) <enter>
Example,
=Median(range of cells)
Date and Time Functions
Today()
Returns the current data formatted as a date.
Syntax:
=Today( ) <enter>
Now ( )
Returns the current data and time formatted as a date and time.
Syntax:
=Now() <enter>
Days360( )
Returns the number of days between two dates based on a 360 - day year (twelve 30 day months).
Syntax:
=Days("Starting_Date", "Ending_date") <enter>
Example,
=Days("01/01/01",Now())
=
Logical Functions
And( )
Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE
Syntax:
=AND(Logical1,logical2,..) <enter>
If( )
Checks whether a condition  is met, and returns one vaue if TRUE, and Another value if FALSE.
Syntax:
=IF(logical_text,value_if_true, value_if_false) <enter>
Financial Functions : - The financial function lets you to calculate the financial  transactions. In excel there are different types of financial functions which are given below:

PMT ( )

Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax
PMT(rate,nper,pv,fv,type)
Rate   is the interest rate for the loan.
Nper   is the total number of payments for the loan.
Pv   is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv   is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type   is the number 0 (zero) or 1 and indicates when payments are due.
Set type equal to
If payments are due
0 or omitted
At the end of the period
1
At the beginning of the period
Examples
The following formula returns the monthly payment on a $10,000 loan at an annual rate of 8 percent that you must pay off in 10 months:
PMT(8%/12, 10, 10000) equals -$1,037.03
For the same loan, if payments are due at the beginning of the period, the payment is:
PMT(8%/12, 10, 10000, 0, 1) equals -$1,030.16
The following formula returns the amount someone must pay to you each month if you loan that person $5,000 at 12 percent and want to be paid back in five months:
PMT(12%/12, 5, -5000) equals $1,030.20
You can use PMT to determine payments to annuities other than loans. For example, if you want to save $50,000 in 18 years by saving a constant amount each month, you can use PMT to determine how much you must save. If you assume you'll be able to earn 6 percent interest on your savings, you can use PMT to determine how much to save each month.
PMT(6%/12, 18*12, 0, 50000) equals -$129.08
If you pay $129.08 into a 6 percent savings account every month for 18 years, you will have $50,000
PV ( )   : Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Syntax
PV(rate,nper,pmt,fv,type)
FV ( )  : -Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Syntax
FV(rate,nper,pmt,pv,type)
Rate ( ) : - Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
=RATE(nper,pmt,pv,fv,type,guess
Nper ( ) : - Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Syntax
NPER(rate, pmt, pv, fv, type)
Data Table
It has to prepare the table which can  find the required values for the cells according to row input cell and  column input cell.
1. Prepare a data sheet as given below.
2. State or Position the cell pointer at required cell
3. Click on Data menu
4. Click on Table
Then a table dialog box will appear
5. Select row cell from row input cell.
6. Select column cell from column input cell.
7. At last click on Ok button

Scenario
This option lets you to create and save different sets of input values on the cell. It saves the different cell values by calculating according row input cell or column input cells. It is the advanced features of Goal Seek.
Steps:
i. Create a data and type the formula
ii. Select the required cell which contains the formula
iii. Click on Tools menu
iv. Click on Scenario
A scenario dialog box will appear
v. Click on Add and type the scenario name to save the value range the cell to which you want to change.
vi. Click  on Ok and type the value and click on Add
Vii. Repeat steps v, vi again  and again after finish then Click on OK button.
viii. Select scenario name and click on Show.
Goal Seek
The goal seek is a powerful command in excel. It can be used to find required data from the cell by changing another cell value.
Steps:
  1. Position the cell pointer at required cell which contains the formula or result
  2. Click on Tools menu
  3. Click on Goal Seek
Then a goal seek dialog box will appear
  1. Type the required value at to value box
  2. range the required cell at by changing cell box
  3. At last clilck on Ok button and again click on Ok button.
Data Sort
The Sort command lets you to sort the data in ascending (A - Z or 0 -9) or descending (z - a or  9 - 0)  order.
Steps
1. Select your data.
2. Click on Data menu
3. Click on Sort...
Then select the filed name and tick on at Ascending or descending
3. At last click on Ok button
Data Form
Excel has facility that you can make or create a form that is called the data form. You can enter the records into sheet from the data form.
Steps:
1. Prepare the fields in each column and type a records, and give the formula if necessary
2. Click on Data
3. Click on Form ...
5. Click on New button and enter the records.
6. When finish entering the record then click on Close
Filter
The filter command lets you to show or print the records according name, address, or any  fields. It will filters the required data. There are two types of filters.
1. Auto Filter : - Automatically filters the records.
Steps:
1. Select the data
2. Click on Data menu
3. Click on Filter
4. Click on Auto Filter
5. Then select the required column to show or filter the record.
Data Validation
The data validation command lets you to set the required value on the cell. If user inputs the invalid data then it will show the message and don't accept that data.  The data validation command also makes or creates the drop down list.To do this follow the following steps:
1. Select the required cells
2. Click on Data menu
3. Click on Data Validation
Then a data validation dialog box will appear.
4. Click on Settings and set the validation criteria .
5. Click on Input Message and Type the message  and title.
6. Click on Error Alert and select the Style and type the message.
7. At last click on Ok button
Pivot Table
The data Pivot Table command lets you to summarize or analysis the data. It will print the data in row heading and column heading.
Steps:
1. Select required data
2. Click on Data menu
3. Click on Pivot Table
4. Click on Next  --> Next --> Layout
5. Drag and drop the fields  and required row, column headings and data.
6. Click on Ok button
7. Click on Finish
Get External Data
You can import data form the another database program into excel. To import the data from another application use the Get External Data command.
Steps:
1. Click on Data menu
2. Click on Get External Data
3. Click on Import file
4.  Select your database file form the dialog box and Click on Open
5. Click on Ok button
Freeze Pane
The freeze pane command lets you to show the fields in rows or fields in columns or required data always when enter the data in work sheet.
Steps:
1. Position the cursor at required place.
2. Click on Windows menu
3. Click on Freeze Pane
To Remove Freeze Pane
1. Click on windows menu
2. Click on Remove Freeze Pane
Creating a Chart
In Excel the chart command lets you to show the data range in diagrammatic and Graphic Representation form. There are different types of charts in excel. We use these chart according our data.
Steps:
  1. Select your data to which you want to show in chart.
  2. Click on Insert menu
  3. Click on Chart  or click on chart icon from the standard tool bar.
Then a chart dialog box will appear.
  1. Select the chart type and click on Press and Hold to View Sample button ( you can see the sample of the chart)
  2. Click on Next  and again range the data if necessary and tick on row and column radio button.
  3. Then a chart option dialog box will appear.
  4. Click on Title and type the  chart title   and values for the x axis and y axis.
  5. Click on Axes and tick on the required primary axis option.
  6. Click on Gridlines and tick on required option
  7. Click on Legend and  tick on required option to place the legend
  8. Click on Data label and tick on required option
  9. Click on Data Table and tick on required option to show the table
  10. Click on Next and  tick on required option
  11. At last click on Finish button
Formula of MS Excel:-
Total:- sum(c7:r8)@
Result:- iif(or(nepali<32,English<32,math<32,sciece<32,social<32,health<32),"Fail","Pass")@
Percentage:- total/600*100@
Division:- iif(and(percent>=80),"Dist",iif(and(percent<=60),"First",iif(and(percent<=45),"Second",iif(and(percent<32),"Third","Fail"))))
Remarks:- iif(division="Dist","very excellent",iif(division="First","Excellent",iif(division="second","good",iif(division="Third","satisfactory","tryagain"))))@
Rank:- rank(percent,percent:last percent)