Ms office notes | Excel notes exam khatir | ms excel notes pd full theory by r kumar excel |

 MS – Excel

Ms-Excel is spreadsheet package available under Ms-Office. It number of fuction for any type of calculation this is used to maintain the records of college, university, bank, organisation etc. if user calculate ant type of calculation on manual paper and change the value in dependent call than again change in precedent cell but in Ms-Excel  automatically recalculate.

Workbook :The file of Ms-Excel program is called a workbook. A workbook is collection of sheets. By default a new workbook has three sheets1, sheet2 sheet3. A new work-book load with book1. The extension of workbook is. Xlsx

Sheet :A sheet is collection of columns and rows.

Column :A vertical group of cells is called a column. A column header is indicated by A, B, C, …………………… XFD I e 16384 columns in a particular sheet.

Row :A horizontal group of cells is called a row. A row header is indicated by 1, 2, 3, 1048576 in a particular sheet.

Cell :Intersection between column and row is called a cell. A cell holds 32000 characters. 

Process to open “Ms-Excel”

1. start – All programs- Microsoft Office Excel 2007/2010………………

Open Run” (press Windws button +R)

Ø Type “Excel”

Ø Press “Enter key” click on “ok”

 File Button Ribbon Tabs Title Bar Minimize Button Maximize Button Close Button

Name Box : it displays current cell address.

Formula Bar : it displays current cell data and function.

Sheet Tab : this is used to activate particular sheet.

Mathematical and Statiscal Function 

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SL NO	ROLL NO	NAME	PHY	CHE	BIO	HND	MATH	TOTAL	AVERAGE	RESULT	MAX	MIN
2	1	1	ALIX	89	76	87	89	89
3	2	2	NEHA	67	89	56	48	90
4	3	3	BABITA	89	76	87	89	89
5	4	4	MADHU	89	76	87	89	89
6	5	5	ROHIT	67	89	56	48	90
7	6	6	ARTI	89	76	87	89	89
8	7	7	ARUN	89	76	87	89	89

 

Sum() : This is a mathematical function, which is used to add(sum) numeric value according to specified cell-range or argument. Syntax: =Sum (Cell-Range)

Example:

=sum (D2:H2) 

Average() :This is used to return the average of numeric value according to specified cell- range or argument.

Syntax:

          =average (Cell-Range)

          Example:

          =average (D2:H2) 

Max() :It reurns the maximum value from specified cell-range argument.

Syntax:

=max (Cell-Range)

Example:

=max (D1:H8) 

Min() :It reurns the minimum value from specified cell-range or argument.

Syntax:

          =min (Cell-Range)

          Example:

          =min (H1:H8) 

Count() :It counts the cells that hold numeric value from specified cell-range.

Syntax.

          =count (cell-Range)

          Example:

          =count (a1: h6)

Counta()  :It counts the cells that hold any type of data from specified cell-range.

Syntax:

          =counta (cell-Range)

          Example:

          =counta (a1: h6)

Countblank() :It counts the cells that is is empty from specified cell-range

Syntax:

          =countbank (Cell-Range)

Example:

=countblank (a1: h6)

 

          Text Function

Left() :It returns number of characters from specified string (word) according to specified position return. If position is not specified then return first character from let side.

Syntax:

          =Left(“String” ,n)

                   Where n=1,2,3………..is position to return

          Example:

          =left(“computer”,3)

          Com

          =left(computer”)

          C

Right()       :It returns number of characters from specified string (word) according to specified position return. If position is not specified then return first character from right side.

Syntax:

          =Right (“String”, n)

                   Where n=1,2,3…………..is position to return

          Example:

          =right (“computer”,5)

            Computer

          =right (computer”)

                   R

Mid() :It returns number of characters from specified string (word) according to specified position to start and return from left side.

Syntax:

          =Right (“String” ,m,n)

                   Where m=1.2.3……………is position to start

                   Where n=1,2,3…………….is position to return

          Example:

          =mid (“computer”)

                   Put

Len() :It counts character from specified word. Space counts character.

Syntax:

          =Len (“string”)

          Example:

          8

          =len (“computer world”)

                   14

Upper()  :It returns each character of specified string in          upper case (capital letter).

Syntax:

         =upper (“String”)

           Example:

           =upper (“computer”)

                 COMPUER

                         =len (“COMPUTER”)

                            COMPUTER

                          =len (“computer world”)

                             COMPUTER WORLD

Lower() :It returns each character of specified string in lower (small letter).

Syntax.

               =lower (“String”)

                 Example:

               =lower (“COMPUTER”)

                    Compuer

               =proper (“computer”)

                         Computer

               =len (“computer word”)

                         Computer world

Rept() :It repeats the specified string according to specified term.

Syntax:

          =rept (“String”)

          Example:

           =rept (“RAM”)

             RAM RAM RAM RAM

                     

                         Date and Time Function

Ms-Excel support date from 1-1 1900 t0 31-12-9999.

Current date : Ctrl+:

Current time: Ctrl+:

Now() :It returns current system date and time.

Syntax:

        =now()

Day() :It returns day (dd) from specified date.

Syntax:

              =month (“date”)

              Example:

             =day (“8/15/1947”)

                         15

Month() :It returns month from specified date.

Syntax:

             =month (“date”)

                 Example:

            =month (“8/15/147”)

                    8

Year() :It returns year from specified date.

Syntax:

     =year (“date”)

     Example:

     =year (“8/15/1947”)

           1947

 

Weekday() :It returns an integer from1to 7 to indicate Sunday to Saturday from specified date i. e know week name.

Syntax:

        =weekday (:date”)

          Example:

          =weekday (“8/15/1947”)

             6 (Friday)

Days360() :It returns days from two specified date.

 Syntax.

               =days360 (“old date” , “current date”)

                  Example:

                  =days360 (“8/15/1947” , “2/6/2021)

                     Logical Function

If()      :This is a logical function. It returns true statement according to specified condition is true otherwise false statement.

Syntax:

        =if (condition, “true statement” false statement”

 

 

 

 

Percentage =(obtained marks * 100) /total marks

                         G2* 100/400

Division

=if (h2>=60,”first” ,if(h2>=45, “second” ,if (h2>=30,”third”, fail”)))

Sumif() :this is a logical function, which is used to add (sum) numeric value according to specified cell-range and criteria is true.

Syntax

         =sumif (cell-range of criteria,” criteria” cell-range of numeric field)

 

 

 

 

 

=sumif (d1: d7,” doctor” e1:e7)

            ……………………….

=sumif (c1:c7,” chapra,” e1:e7)

Countif()  :This is a logical function, which is used to count the cells that cells that hold specified criteria from specified cell-range.

Syntax

          =countif (cell-range of criteria,” criteria”)

Eample

              =countif (d1 : d7 “doctor” e1: e7

                           ………………….

               =countif (c1c7 “chapra” e1: e7

File Tab (Alt+ F)

New (ctrl+ N)           :This is used to create new workbook (file).

Open (Ctrl+ O)         :This is used to open an existing workbook (file).

Save (Ctrl+ S)      :This is used to save a new workbook with a new name and include modify date in active workbook.

Save (F12 )          :This is used to create duplicate workbook of active workbook and apply password.

Process to Apply Password           

Ø Active as your required workbook

Ø Click on “file button”

Ø Click on “save as”

Ø Click on “tools” drop down

Ø Click on “general option”

Ø Again define password

Ø Click on “ok”

Ø Again retype same password

Ø Click on “save”

Print : It provides some commands such as Quick prin, print preview.

Ø Quck print :This is used to direct print the data of active workbook if printer is ready I. e it don’t provides the dialog box of print.

Ø Print :This is used to print the date of active workbook according to specified i. e it provides the dialog box of print.

Ø Print preview :This is used to display the current workbook sheet’s data how will be print.

Close (ctrl+ w or ctrl+ f4 ) : This is used to close the active workbook.

Exit excel (Alt+ F4)  :This is used to close Ms-excel application

 

                                    Home Tab (Alt+ H)

Cut (Ctrl+ X)       :This is used to remove the sleected data.

Copy (Ctrl+ C)     :This is used to duplicate the selected data.

Paste (Ctrl+ V)       :This is used to insert cut/copy date as insertion point.

Paste Special         :This is used to insert copy data as insertion point according to selected option.

 

Process

Ø Select as your required data

Ø Click on copy”

Ø Again move cursor at required location

Ø Click on “home” tab

Ø Click on “paste” drop down

Ø Click on “paste special” drop down

Ø Again select as your required option

Format painter     :This is used to copy the format of selected data and apply on other data.

Process

Ø Select as your required formatted data

Ø Click on “home” tab

Ø Again double click on “format painter”

Ø Click on other data

Font                    :This is used to change font of selected data.

Font size            :This is used to increase/decrease font size of selected data.

Font color            :This is used to define text color of selected data.

Bold (Ctrl+ B)      :This is used to strong the selected data.

Italic (Ctrl+ I)      :This is used to cursive the selected data.

Underline (Ctrl+ U) :This is used to apply underline of selected data.

Fill Color        :This is used to color that apply as background of selected data.

Top Align       :This is used to change top align (vertical)of selected data.

Middle Align   :This is used to change middle align (vertical) of selected data.

Bottom Align   :This is used to change bottom align (vertical) of selected data.

Left Align          :This is used to change left align (horizontal) of selected data.

Center Align      :This is used to change center align (horizontal) of selected data.

Right Align         :This is used to change right align (horizontal) of selected data.

 

Merge and center     :This is used to combine two and more cells into a single cell.

Process

Ø Select two or more cells

Ø Click on “home” tab

Ø Again click on “merge and center”

Insert   :This is used to insert blank row or rows, column or columns, sheet according to selected and commands.

Ø Selected as your required

Ø Click on “home” tab

Ø Again click on “insert” drop down

Ø Again click on required command

Note:

Ø Blank inserted row or rows adjut above of selected row or rows

Ø Blank inserted column or columns adjut left side of selected column or columns.

Ø Blank inserted cell or cells adjut according to selected option.

Delete  :This is used to delete selected row or rows, column, sheet according to selected and commands.

Process

Ø Active as your required

Ø Click on “home” tab

Ø Again click on “delete” drop down

Rename Sheet   :This is used to change name of active sheet.

Process

Ø Active as your requied sheet

Ø Click on “home” tab

Ø Again click  on “format” drop down

Ø Again click on required sheet”

Protect Sheet    :This is used to apply password on active sheet. In this protection any other user open your wookbook (file)but can’t modify in particular sheet.

Process

Ø Active as your requied sheet

Ø Click on “home” tab

Ø Again click on “format” drop don

Ø Again click on “protect sheet”

Ø Again define password

Hide and unhide  :This is used to hide selected row or rows, column or columns, sheet and also unhide the hidden respectively.

Process

Ø Active as your requied

Ø Click on “home” tab

Ø Again click on “format” drop down

Ø Again click on |”hide and unhide”

Ø Again click on required command

Row height     :This is used to define height of selected row or rows

Process

Ø Active as your requied

Ø Click on “home” tab

Ø Again click on “format” drop down

Ø Again click on “row height”

Ø Again define height

Ø Again click on “ok”

Columan width   :This is used to define width of selected columns.

Process

Ø Active as your requied

Ø Click on “home” tab

Ø Again click on “format” drop down

Ø Again click on “column width”

Ø Again define width

Ø Again click on “ok”

Tab color      :This is used to define active sheet tab color

Process

Ø Active as your requied sheet

Ø Click on “home” tab

Ø Again click on “format” drop down

Ø Again click on “tab color”

Ø Again define color

Sort       :This is used to arrange records in ascending or descending order according to active field and order.

Process

Ø Move cursor on require field

Ø Click on “home” tab

Ø Again click on “soft and filter” drop down

Ø Again click on “tab color”

Ø Again click on required sort order as “A to Z” or “Z to A”

Filter       :This is used to display records to specified condition.

Process

Ø Move the cursor at data-range

Ø Click on “home” tab

Ø Again click on “sort & filter” drop down

Ø Again click on “filter”

Ø Again click on required drop-down field

Ø Again define condition

Conditional formatting   :This is used to define format on selected cell or cell-range according to your specified condition is true.

Process

Ø Select as your required cell or cell-range

Ø Click on “home” tab

Ø Again click on “conditional formatting” drop down

Ø Again define your required condition and format

Clear     :This is used to remove of selected cell or cell-range according to command as clear comments, clear formats, clear contents, clear hyperlinks, all.

Process

Ø Select as your required cell or cell-range

Ø Click on ”home” tab

Ø Again click “clear” drop down

Ø Again click on required command

Autosum      :it displays simple calculation function as sum, average, max, min ect. And unser also use it.

Process

Ø Selected as your required cell-range

Ø Click on “home” tab

Ø Again click on “autosum” drop down

Ø Again click on required function

Format as table   :This is used quickly format a range of selected cell or cell-range to display pre-defined table format data.

Process

Ø Selected as your required cell-range

Ø Click on “home” tab

Ø Again click on “format as table” drop down

Ø Again click on required style

Find (ctrl+ f)    :This is used to search a particular word alphabet from active sheet.

Replace (ctrl+ h)    :This is used to search a particular  word, alphabet from active sheet and convert into other.

Go to(ctrl+ g or f5)  :This is used to move cursor from one location to other location.

Select all(ctrl+ a)    :This is used to select all entire data of active sheet.

Delete (del)            :This is used to permanently remove selected data.

Undo (ctrl+ z)       :This is used to back event step by step.

Redo (ctrl+ y)       :This is used to forward the undo event step by step.

 

 

Insert Tab (Alt+ N)

Picture        :This is used to insert any type of picture in your active sheet.

Process

Ø Click on “insert” tab

Ø Click on “picture”

Ø Again define location of image

Ø Again click on “insert”

Clip art    :This is used to insert picture that automatically install during the installation of Ms-Office

Process

Ø Click on “insert” tab

Ø Click on “clipart”

Ø Again click on “go”

Ø Again click on required image

Shape     :This is used to insert predefine of shape as circle, square, rectangle.

Process

Ø Click on “insert” tab

Ø Click on “shape” drop-down

Ø Again click on required shape

Ø Again draw it

Screenshot  :This is used to insert a picture of any application, user don’t have to minimise that program on taskbar.

Process

Ø Click on “insert” tab

Ø Click on “screenshot” drop down

Ø Again click on required image.

Hyperlink (ctrl+ k)          :This is used to insert link on selected word to active other document.

Process

Ø Select as your required word

Ø Click on “insert” tab

Ø  Click on “hyperlink”

Ø Again select as your required document’s name

Ø Again click on “ok”

Text box     :This is used to draw rectangular box and enter small data. In it.

Process

Ø Click on “insert” tab

Ø Click on “text box” drop-down

Ø Again click on “draw text box”

Ø Again draw it and enter data in it

Header    :This is used to add/modify text, insert page number, data/time, picture etc. that appear on every page according to specified position and alignment.

Process

Ø Enter more records more than one page

Ø Click on “header & footer”

Ø Again define as your required

Word art     :This is used to insert 3-D text.

Process

Ø Click on “insert” tab

Ø Click on “word art”

Ø Again click on required style and enter data

Symbol   :This is used to special character and symbol

Process

Ø Click on “insert” tab

Ø Click on “symbol”

Ø Again define required font and select required symbol/special character

Ø Again click on “insert”

Chart    :Ms-Excel provides the feature of chart to display data in graph format.

There are following type of chart

Column chart    :This is used to display data in vertical bar graph format.  

Process

Ø Selected as your required data

Ø Click on “insert” tab

Ø Click “column” drop down

Ø Again click on required style

Bar chart   :This is used to display data in horizontal bar graph format.

Process

Ø Select as your required data

Ø Click on “insert” tab

Ø Click on “bar” drop down

Ø Again click on required style

Pie chart   :This is used to display data in circular graph format.

Process

Ø Create data as below

 

 

 

 

 

 

 

Ø Select as your required data

Ø Click on “insert” tab

Ø Click on “pie” drop down

Ø Again click on required style

Line chart   :This is used to display data in trend over graph format I. e. compare between two series.

Ø Create data as below

 

 

Ø Selected as your required data

Ø Click on “insert” tab

Ø Click on “line” drop down

Ø Again click on required style

Page Layout Tab (Alt+ P)

Margin   :This is used to define margin of paper i. e. define space to leave gap such as left, right, top, bottom for print on paper.

Process

Ø Click on “page layout” tab

Ø Click on “margin” drop-down

Ø Again click on “custom margins”

Ø Again define as your required

Orientation   :This is used to define orientation of paper to print and display as portrait (vertical) or landscape (horizontal).

Process

Ø Click on “page layout” tab

Ø Click on “orientation” drop-down

Ø Again click on required

Size   :This is used to define size of paper i. e. define height and width of page for print.

Process

Ø Click on “page” layout” tab

Ø Click on “size” drop-down

Ø Again click on size  

Print area  :This is used to set print area to selected cell or cell-range for print and also clear previous set print area.

Process

Ø Selected as your required cell-range

Ø Click on “page layout” tab

Ø Click on “print area” drop-down

Ø Again click on “set print area”

Ø Again click on “print preview”/ctrl+ F2

Break : This is used to insert page break at insertion point ald also remove inserted page break.

Process

Ø Move cursor at required location

Ø Click on “page layout” tab

Ø Click on “break” drop-down

Ø Again click on “insert page break”

Ø Again click on “print preview”/ctrl+ F2

Background      :The is used to insert image that appear as background of active sheet and also remove inserted image.

Process

Ø Click on “page layout” tab

Ø Click on “background”

Ø Again select as your required image

Ø Again click on “insert”

Print title    :This is used define heading of records appear at top of every page for print.

Process

Ø Enter records more than one page

Ø Click on “page layout” tab

Ø Click on “print title”

Ø Again click on “row to repeat at top”

Ø Again click on heading of records

Ø Again click on “print preview”

View Tab (Alt+ W)

 

Formula bar    :This is used to check/uncheck to display/hide to formula bar respectively.

Process

Ø Click on “view” tab

Ø Again check/uncheck to “formula bar”

Gridlines   :This is used to check/uncheck to display/hide to gridline respectively from active sheet.

Process

Ø Click on “view” tab

Ø Again check/uncheck to “gridline”

Headings      :This is used to check/uncheck to display/hide to column and row heading respectively from active sheet.

Process

Ø Click on “view” tab

Ø Again check/uncheck to “headings”

Zoom   :This is used to zoom in (arge) or zoom out (small) data of active sheet.

               Max. Zoom           :400%

               Min. Zoom             :10%

                Actual Zoom          :100%

Arrange all      :This is used to arrange two or more open workbooks in separate window.

Process

Ø Open two or more workbooks

Ø Click on “view” tab

Ø Again click on “arrange all”

Ø Again select as your required option

Ø Again click “ok”

Freeze panes   :This is insert/remove pane at insertion point. In this method the above data of pane don’t scroll

Process

Ø Enter records more than one page and move cursor are required location

Ø Click on “view” tab

Ø Again click on ”freeze panes”

3. Protect Workbook   :This is used to apply password on active workbook. In this protection any other user open your workbook but can’t modify in workbook window i. e rename sheet, delete sheet, move/copy sheet, insert new sheet, tab color, hide/unhide sheet.

Process

Ø Active required workbook

Ø Again click on “review” tab

Ø Again click on “protect workbook”

Ø Again check to “windows” check-box

Ø Again define password

Data Tab (Alt+ A)

 

Sort    :This is used to arrange records In ascending or descending  order according to active field and order.

Process

Ø Move cursor at required field

Ø Again click on “data” tab

Ø Again click on required sort order such as A to Z or to A

Filter   :This is used to display records according to specified condition.

There are two type of filter

1. Autofilter    :This is the quickest way to display records according to condition.

Process

Ø Move cursor at data-range

Ø Again click on “Data” tab

Ø Again click on “filter” tab

Ø Again click on required drop-down field

Ø Again define condition

2. Advance Filter   :This is the display records according to condition in other location.

Process

Ø Firstly define condition

Ø Again click on “data” tab

Ø Again click on “advanced”

Ø Again define data-range in “list range”

Ø Again check to “copy to another location” option

Ø Again define criteria range i. e. condition of cell=range in “criteria range”

Ø Again define destination cell-address i. e. location to display records in “copy to”

Ø Again click on “ok”

Data Validation    :This is used to define condition on selected cell or cell-range. In this method when user enter record and that data satisfied your condition then enter otherwise ignore

Process

Ø Select as your required cell or cell-range

Ø Again click on “data” tab

Ø Click on “data validation” drop-down

Ø Click on “data validation”

Ø Click on “setting” tab

Ø Click select “whole number” from” allow” drop-down

Ø Again define operator such as between, greater than, less than etc. from “data” drop-down

Ø Again define value

Ø Click click on “error alter”

Ø Again define title and message

Ø Click on “ok”

Subtotal     :This is used to apply function as count, average max, min according to control field i. e. group wise in this command the control field must be sorted.

Process

Ø Enter records as below

Ø Again sort the recods as post-wise

Ø Again click on “data” tab

Ø Again click on “subtotal” tab

Ø Select your sorted field from “at each change in”

Ø Again select function such as “sum” from “use function”

Ø Again check to numeric field as “salary”

Ø Again click on “ok”

Formulas Tab (Alt+ M)

Insert function    :This is used to insert any type of function for calculation.

Ø Move cursor at required location i. e. cell

Ø Click on “formulas” tab

Ø Click on “Insrt function” tab

Ø Again select as your required function from category drop-down

Ø Again select as your required function

Ø Again define argument i. e. cell-address or cell-range

Ø Click on “ok”

Autosum        :It displays simple calculation function as sum, average max, min etc. and unser also use it.

Recently used    :It displays all financial function list and also use for calculation

Financial       :It displays all financial function as FV, PMT, PV etc. and user get help and also use for calculation.

Logical      :It displays all logical related function as if, sumif, countif, true not etc. and user gat help and also use for calculation.

Text         :It displays all text related function as left, right, mid, lower, upper, len, proper Rept etc. and user get help and also use that function.

Date & Time   :It displays all data/time related function as now, day, month, year, weekday, days360 etc. and user get help and also use it.

Math & Tri     :It displays all mathematical and trigonometrical related function as sum, average, sin, cos fact, sqrt etc. and user get help and also use it for calcula-tion.

Define name   :This is used to define the name of selected cell or cell-range and also use it with function instead of cell-range/argument.

Process

Ø Select as your required cell or cell-range

Ø Click on “formulas” tab

Ø Click on “define name”

Ø Again define any name

Ø Click on “ok”

Ø Again use the name with function

Trace precedents   :It displays relation between dependent and precedent cell with arrow.

Process

Ø Select as your required precedent cell

Ø Click on “formulas” tab

Ø Again click on “trace precedent”