Microsoft Excel (MS Excel) Notes

 Microsoft Excel

Introduction:

Application fine name: excel.exe

Extension name: .XLS

Microsoft Excel is a spreadsheet software program produced by Microsoft Corporation, USA. It allows user to organize, format and calculate data with formulas using a spreadsheet system. This software as a part of the Microsoft office suite (group) and is compatible with other applications in the office suite.

MS-Excel is also known as commercial spreadsheet application produced and distributed by Microsoft Corporation for Microsoft Windows and Mac OS X operating system. It features the ability to perform basic calculations, use graphing tools, and create pivot tables and chart.

Excel has the same basic features as every spreadsheet, which use a collection of cells arranged into rows and columns to organize data manipulation. There are 256 columns and 65536 rows in a single worksheet (for office 2003) or 16384 columns and 1048576 rows in a single worksheet (for office 2007). We can move the cell pointer around the worksheet by pressing arrow key or by clicking left button of mouse into the particular cell.

#. How to start MS-Excel program?

Steps:

1.   Click on Start button

2.   Click on Programs or All programs

3.   Click on Microsoft Office  group

4.   Click on Microsoft Excel

OR

i) Click on Start button

ii)Click on Run or press winlogo+R. It displays run dialog box.

iii)Into the open box, type the application file name of MS-Excel program (excel.exe)

iv) Click on ok. Then we will get following Microsoft Excel program screen.

1. Title bar

2.   Menu bar

3.   Options bar

4.   Formula bar

5.   A B C columns name (column headings)

6.   1 2 3 row number (row headings)

7.   Cell pointer

8.   Left arrow

9.   Right arrow

10. Down arrow

11.Up arrow

12. Close

13.Restore/Maximize

14. Minimize

#. How  to Save the worksheet data? (Ctrl+S/F12)

Steps:

1.   Click on Office button at the top- left side of the workbook screen.

2.   Click on Save option. It displays Save As  dialog box.

3.   Choose the required drive and directory name.

4.   Type the file name into file name box.

5.   Click on Save Button

#. How to Close the worksheet data? (Ctrl+W)

Steps:

1.   Click on Office button

2.   Click on Close option.

#. How to open the new blank workbook? (Ctrl+N)

Steps:

1.   Click on Office button

2.   Click on New option

3.   Click on Blank Workbook

4.   Click on Create button

#. To open worksheet data: (Ctrl+O/Crtl+F12)

Open option i s used to open the workbook data, which is already saved for editing or printing  or reading purpose.

Steps:

1.   Click on Office button

2.   Click on Open option. It displays open dialog box.

3.   Choose the required drive and directory name, where the files are kept.

4.   Choose the file name

5.   Click on Open button

#. How to exit from MS-Excel Program (Alt+F4)

Steps:

1. Click on Office button

2.  Click on Exit Excel button

Basic Terms of MS-Excel Program:

#. What is workbook?

Microsoft Excel is a workbook. It is the collection of worksheet. Default; there are 3 worksheets in a workbook. They are Sheet1, Sheet2, Sheet3.

#. What is worksheet?

Worksheet is the group of cells (row/column). There are 16384 columns and 1048576 rows in a single worksheet.

#. What is row?

Horizontal line of the cells on the worksheet is called row.

#. What is column?

Vertical line of the cells on the worksheet is called column.

#. What is cell?

Small rectangular block on the worksheet is called cell. It is also known as meet point of rows and columns.

#. What is cell address?

Each cell has its own address, which is denoted by column name and row number.

Example:

A₁, B₁₀, D₂₀, etc.

#. What is cell range?

Cell range is the grouped or highlighted rectangular block on the worksheet, which is separated by double dot or colon.

Example:

A₁ . . D₅        B₂ .. B₈           A5 ..G5

A₁ : D₅          B₂ : B₈               A5:G5

#. Operators:

Operators are the single or group of arithmetical or logical indicators (sign). These are the elements of the formula, which are used to solve the numerical problem. There are two types of operators, they are;

A.  Arithmetical (Numerical) operators

B.Logical operators

#. Numerical operators:

Numerical operators are those operators, which perform some arithmetical operation like: addition, subtraction, multiplication, division, percentage and exponents

For examples:

i) + (plus) = for addition

ii) – (minus)  = for subtraction

iii) * (Asterisk) = for multiplication

iv) / (Slash) = for division

v) % (percentage) = to calculate percentage

vi) ^ Caret (Exponent)  = to calculate square, cube, 4^th, 5^th, .. nth times

#. Logical operators:

These operators are for decision-making. Various logics are used to solve the problems. These operators compare more than two conditions and give the valuable result "true" or "false".

Example:

  i.    = (equal to)

ii.    > (greater than)

iii.    >= (greater than and equal to )

iv.    < (less than)

 v.    <= (less than and equal to )

vi.     < > (not equal to)

#. Logical Functions:

IF, AND, OR are known as logical functions in MS-Excel program.

#. IF Function:

This function is used, if only one condition is given. It may be either true or false.

#. AND function:

This function is used, if the all data range (field) names are given as a condition.

S.N.	Name	Subject					Total	Result
		Eng	Nep	Sci	OM	CM
1	Messi	45	55	40	60	50	250	Pass
2	Ronaldo	85	75	90	85	95	430	Pass
3	John Cena	35	44	36	38	42	195	Pass
4	Salman Khan	65	75	70	65	60	335	Pass
5	Trump	30	45	40	50	55	220	Fail
6	Chetan	45	55	70	85	45	300	Pass

Example:

Result: =if Eng>=35 and Nep>=35 and Sci>=35 and OM>=35 and CM>=35, "Pass", otherwise "Fail"

Solution: =IF(AND(C4>=35,D4>=35,E4>=35,F4>=35,G4>=35),"Pass","Fail")

#. OR Function:

This function is used, if more than one comparative condition is given as a condition. It checks the first condition, if the first condition is wrong it will check the second condition and gives the valuable result "True" or "False".

Sno	Name	Address	Salary	HRA	Total
1	Messi	Dang	12500	0
2	Ronaldo	Pokhara	3500	350
3	John Cena	Surkhet	7500	750
4	Salman Khan	Nepalgunj	5000	500
5	Trump	Butwal	14200	0
6	Chetan	Surkhet	16500	1650

Example:

HRA will give 10% of salary if his/her salary is less than 6000 or address is surkhet

Solution: =IF(OR(D4<6000,C4="Surkhet"),10%*D4,0)

#. Functions(Alt+=):

Functions are used to solve the numerical problems into the resulting cell.

=Sum(Range of cells)=>Returns the sum of selected cells.

=Max(Range of cells) =>Returns the largest value into the selected cells

=Min(Range of cells) => Returns the smallest value for selected cells

=Average(Range of cells) => Returns the average value of the selected cells

=SQRT(Number) >=Returns the square root of a number

=Power(Number,power) => Returns the power of a number.

=Int =>Returns the whole number without decimal place

=Today( ) => Returns the current system date only.

=Now( ) => Returns the current system date and time.

ABC  Cold Store

Kathmandu, Nepal

S.No.	Description	Qty	Rate	Amount
1	Coca-Cola	35	10
2	Fanta	40	15
3	Mirinda	75	12
4	Pepsi	55	14
5	Thums up	150	16
6	Sprit	100	18
7	Ice cream	150	20
8	Mineral water	125	15
9	Reo	180	10

Criteria:

Amount : =Qty x Rate  Enter

Total amount: = Sum(Range of cells) Enter

Maximum amount: =Max(Range of cells) Enter

Minimum amount: =Min(Range of cells) Enter

Average amount: =Average(Range of cells) Enter

Discount amount: =3% of total amount Enter

Vatable amount: =Total amount – discount Enter

13% vat:              = 13% of Vatable amount Enter

Grand Total:        = Vatable amount+13% Vat Enter

#. Adding/Removing border line:

Steps:

1.   Select (highlight) the required cells (rows and columns)

2.   Click on Home menu.

3.   Click on Font drop-down arrow button. It displays a dialog box.

4.   Click on Boarder tab.

5.   Choose the required line style and line color.

6.   Click on outline and Inside button

7.   Click on ok.

#. Font:

Font option is used to change the font name, font style, font size, font color, underline style, superscript and subscript etc. of the selected text.

Steps:

1.   Select (highlight) the required text or records

2.   Click on Home menu.

3.   Click on Font drop-down arrow button. It displays a dialog box.

4.   Click on Font tab.

5.   Choose the required option.

6.   Click on ok.

#. Patterns/Fill:

Pattern or Fill is used to change the background color or patterns style of the selected cells.

Steps:

1.   Select (highlight) the required cells.

2.   Click on Home menu.

3.   Click on Font drop-down arrow button. It displays a dialog box.

4.   Click on Pattern or Fill tab.

5.   Choose the required background color or pattern style.

6.   Click on ok.

#. Alignment:

It is used to change the horizontal/vertical alignment of the record into the cells.

Steps:

1.   Select (highlight) the required cells.

2.   Click on Home menu.

3.   Click on Font drop-down arrow button. It displays a dialog box.

4.   Click on Alignment tab. It displays;

#. Horizontal alignment:

It is used to change the horizontal alignment of the selected t ext into the cells. (left or center or right)

#. Vertical alignment:

It is used to change the vertical alignment of the selected text into the cells. (top or center or bottom)

#.  Wrap text:

It is used to type the multiple lines into a single cell.

#.  Shrink to fit:

It is used to managing the record into the particular cell without changing column width.

#.  Merge Cells:

It is used to join or combine the multiple cells into a single cell.

#.  Orientation:

It is used to change the direction of the record into the cell.

1.   Choose the required options.

2.   Click on ok.

#. Row Height:

Row Height option is used to change (enlarge or reduce) the height of the selected rows.

#.Autofit Row Height:

Autofit Row Height option is used to bring back to the original row height.

#. Column Width:

Column Width option is used to change (enlarge or reduce) the width of the selected columns.

#. Autofit Column Width:

Autofit column width option is used to fix the  column width as per text width.

#. Default Width:

Default Width option is used to display the original column width of the selected column.

#. Hide & Unhide:

Hide & Unhide option is used to hide row, column, sheet and unhide the row, column and sheet on the worksheet.

#. Rename Sheet:

Rename Sheet option is used to change the name of selected sheet.

#. Move or Copy Sheet:

Move  or copy sheet option is used to move or copy the sheet from one workbook to another workbook.

#. Tab Color:

Tab Color option is used to change the sheet tab color of the selected sheet.

#.Protect Sheet:

Protect Sheet option is used to protect the worksheet. When we have to protect the sheet with protection password, nobody can modify the text on the cell. All Cells become locked.

#. Format Cells:

Format Cells option is used to change the number formatting style, alignment, font, boarder line, pattern style etc. of the selected cells.

Steps:

1.   Highlight the required records

2.   Click on Format drop-down arrow button.

3.   Click on Format Cells option. It displays format cells dialog box.

4.   Choose the required options.

5.   Click on ok.

#. Insert:

Insert option is used to insert row, column, worksheet on the active worksheet window screen.

#. How to insert row?

1.   Keep the cell pointer into the required cell.

2.   Click on Home menu.

3.   Click on Insert drop-down button.

4.   Click on Insert Cells and click on Entire Row or Click on Insert Sheet Rows option

#. How to insert Column?

1.   Keep the cell pointer into the required cell.

2.   Click on Home menu.

3.   Click on Insert drop-down button.

4.   Click on Insert Cells and click on Entire Column or Click on Insert Sheet Columns option.

#. How to insert worksheet?

1.   Click on Home menu.

2.   Click on Insert drop-down button.

3.   Click on Insert Sheet  option.

#. Delete:

Delete option is used to delete unnecessary row or column or worksheet on the workbook window screen.

#. How to delete row?

Steps:

1.   Select the row, which we want to delete.

2.   Click on Delete drop-down button

3.   Click on  Delete Cells option and click on Entire Row option or Click on Delete Sheet Rows option.

#. How to delete Column?

Steps:

1.   Select the column, which we want to delete.

2.   Click on Delete drop-down button

3.   Click on  Delete Cells option and click on Entire Column option or Click on Delete Sheet Columns option.

#. How to delete Worksheet?

Steps:

1.   Select the worksheet, which we want to delete.

2.   Click on Delete drop-down button

3.   Click on  Delete Sheet option or press right button of mouse on the sheet and click on Delete option.

#. Format as table:

Format as table option is used to format the selected rows and columns as per need.

Steps:

1.   Select the rows and columns.

2.   Click on Home menu.

3.   Click on Format As table drop-down button

4.   Click on required formatting style.

#. Cell Style:

Cell Style option is used to change the style of the selected cells on the worksheet screen.

Steps:

1.   Select the required cell

2.   Click on Home menu.

3.   Click on Cell Style drop-down button.

4.   Click on required cell style.

#. Conditional Formatting:

Conditional Formatting option is used to format the selected worksheet data for given conditions

Steps:

1.   Highlight the worksheet data.

2.   Click on Home menu.

3.   Click on Conditional Formatting drop-down arrow button

4.   Click on New Rule.

5.   Select the Rule type

6.   Select the required logical operator and type the value and click on Format button.

7.   Choose the required formatting style.

8.   Click on ok.

#. Clear:

Clear option is used to delete the all worksheet data or formatted style or comments or text on the worksheet.

Steps:

1.   Select the worksheet data.

2.   Click on Home menu.

3.   Click on Clear drop-down arrow button. It displays;

Clear All .  (It deletes all worksheet data).

Clear Formats. (It deletes all formatted style).

Clear Contents. ( It deletes all typed records).

Clear Comments. (It deletes all comments).

4.   Click on required option as per need.

#. Fill:

Fill option is used to fill the series data on the worksheet and also justify the record into the cell.

#. How to fill the series data?

Steps:

1.   Keep the cell pointer into the proper cell.

2.   Type the starting value and press enter then select the starting value

3.   Click on Home menu.

4.   Click on Fill drop-down arrow button.

5.   Click on Series option.

6.   Select Row or Column

7.   Type the step value and stop value.

8.   Click on ok.

#. How to justify the record?

Steps:

1.   Select the range of cell.

2.   Click on Home menu

3.   Click on Fill drop-down arrow button

4.   Click on Justify option.

5.   Click on ok.

#. AutoSum:

Autosum option allows to display the sum of the range of cells.

Steps:

1.   Keep the cell pointer into the resulting cell.

2.   Click on Home menu

3.   Click on Autosum drop-down arrow button

4.   Click on Sum.

#. How to sort the records?

Sort option is used to arrange the field records in ascending (A-Z,0-9) and descending (Z-A, 9-0) order into the cells.

Steps:

1.   Select the worksheet records.

2.   Click on Home menu.

3.   Click on Sort & Filter drop-down button

4.   Click on Custom Sort option. It displays a dialog box.

5.   Choose the required column name.

6.   Click on Ascending or Descending order

7.   Click on ok.

#. Find & Select:

Find & select option is used to find or replace the text or number on the worksheet cell.

#. How to find the record?

Steps:

1.   Keep the cell pointer at top of cells.

2.   Click on Home menu.

3.   Click on Find & Select drop-down arrow button.

4.   Click on Find option.

5.   Type the searching records

6.   Click on Find Next button. Then the cell pointer is appeared at that cell where the record is located.

#. How to replace the record?

Replace option is used to find the record into the cell and replace with new record.

Steps:

1.   Keep the cell pointer at top of cells.

2.   Click on Home menu.

3.   Click on Find & Select drop-down arrow button.

4.   Click on Replace option.

5.   Type the searching record and new record (replacement)

6.   Click on Replace or Replace button.

#. Goto(Ctrl +G)/F5:

Goto option is used to move the cell pointer into the particular cell address.

Steps:

1.   Click on Home menu

2.   Click on Find & Select drop-down arrow button

3.   Click on Goto option.

4.   Type the cell address

5.   Click on ok.

#. How to change the margin?

Steps:

1.   Click on Page Layout menu.

2.   Click on Margins drop-down arrow button

3.   Click on Custom Margin option. It displays page set dialog box.

4.   Choose the required margin, paper size and print orientation.

Example

Margin:                               Print Orientation                   Paper size

To margin                           Portrait or Landscape                        A4

Bottom margin

Left margin

Right margin

5.   Click on ok.

#. How to select the print Area?

Set Print Area option is used to select the worksheet data for printing mode.

Steps:

1.   Click on Page Layout menu.

2.   Click on Print Area drop-down button.

3.   Click on Set Print Area option.

# To remove the print area:

Steps:

1.   Click on Page Layout menu.

2.   Click on Print Area drop-down button.

3.   Click on Clear Print Area option.

#. How to change the background of worksheet?

Steps:

1.   Click on Page Layout menu.

2.   Click on Background option.

3.   Choose the required picture.

4.   Click on Insert button.

#. How to delete the background of worksheet?

Steps:

1.   Click on Page Layout menu.

2.   Click on Delete Background option.

#. How to break the page on the worksheet?

Steps:

1.   Keep the cell pointer into the particular cell.

2.   Click on Breaks drop-down arrow button.

3.   Click on Insert Page Break option.

#. To remove the page break code line?

Steps:

1.   Keep the cell pointer into the particular cell.

2.   Click on Page Layout menu.

3.   Click on Breaks drop-down arrow button.

4.   Click on Remove Page Break option.

#. How to the worksheet  data?

Steps:

1.   Click on Office button.

2.   Click on Print option. Or Press Ctrl+P. It displays print dialog box.

3.   Choose the printer name, page range

4.   Click on ok.