TAIYARI24HOUR.

COM

Microsoft
Excel

Prepared By:
Pankaj sir.

Notes By- Pankaj sir. Visit site – taiyari24hour.com

 TAIYARI24HOUR.COM

MS EXCEL
Ms excel is a spread sheet based application. it is developed by Dan Bricklin and
Bob Frankston. The previous name of ms excel spread sheet was VisiCalc it
was developed in 1979. It is used for creating ledger invoices and analysis report, it
shows the file in the form of work book, the work book contains many work sheets
each work sheet has 1048576 rows and 16384 column, the column is given name
a, b, c like that and the rows are numbered 1, 2, 3. The page is divided into different
cells & each cell can hold one set of value and the value is refered.
A1
By the address; there are different type of addressing in ms excel.
The extension of excel file is .xls

Notes By- Pankaj sir. Visit site – taiyari24hour.com

 TAIYARI24HOUR.COM

CCC Note
1-Relative addressing: - this addressing is used when u requires copying the
formula in different cell and need the address to be adjust according to the
corresponding cell and column.
For ex:-

A B C D E F G H

1 Div.
Rollno Name Hindi Eng. Math Total Per
2 1001 reshu 67 78 76 =c2+d2+e2 =F2/3
3 1002 ishita 56 67 90
4 1003 kanak 76 65 84
5 1004 tripti 87 76 65
6 1005 sriyeshi 67 78 98

DIV.=if(g2>=60,”first”, if(g2>=45,”second”, if(g2>=33,”third”,”fail”)))

2- Absolute addressing: - this method of addressing is used with ($) dollar symbol
with column name and row number this method of addressing does not change if u
copy the formula in other cell. It means $ makes the row and column constat
(unchanged).
For ex:-
Roll no name Hindi Eng. Math total per
1001 reshu 67 78 76 =$c$2+$d$2+$e$2 =F2/3
1002 ishita 56 67 90
1003 kanak 76 65 84
1004 tripti 87 76 65
1005 sriyeshi 67 78 98
1006 ramesh 56 74 67

Mixed addressing: - relative and absolute addressing methods are mixed

together in the form of mixed addressing.
for ex:- 7% 18% 12%
name Post salary Ta da hra Total
reshu doctor 12000 =C3*$D$1 =C3*$E$1 =C3*$F$1 =C3+D3+E3+F3

By-Pankaj
Notessir
By- Pankaj sir. visit site-taiyari24hour.com
Page 3
CCC Note

ishita accountant 14000

kanak clerk 30000
tripti manager 45000
sriyeshi engineer 24000

1. There are some menu and tool bars are available in ms excel.

Office button
New: - this option is used for create a new work book
Steps:-click on office button
1. Select new option
2. Click on blank workbook and
3. click on create button
4. Press ctrl+ n key from the key board.
Open:-it is used for open your workbook which is saved in the computer’s
memory.
Steps: - click on office button
1. select open option
2. a dialog box will be displayed
3. select your file
4. click on open button
5. Or press ctrl+ O key from the keyboard.
Save workbook: - it is used for save your file in the current directory.
Steps: - click on office button
1. select save option
2. A dialog box will be displayed
3. Type your file name
4. Click on save button
5. Or press ctrl+ s from the keyboard.
Save as: - it is used for save your file with different name.
Steps: - click on office button
1. Select saves as option
2. A dialog box will be displayed
3. Type your file name (new name)
4. Click on save button.
1. Or press F12 function key from the key board.
Page setup: - it is used to set the margin, paper source, page size etc… of your
worksheet.
Steps: - click on office button
Select page setup option
Set the required option
Click on ok button.

Notes By- Pankaj sir. Page 4

CCC Note

Print: - It is using for print your file on the paper with the help of printing device
(printer).
Prepare Properties: - It is used for show the status of your file like file name,
version, statistic etc....
Close: - This option is used to exit you from the ms word.

Home button
Cut: - it is used for cut the selected text.
Steps:-select the text or sentence
1. Click on home button
2. Click on cut button
Press ctrl+x from the keyboard.
Copy: - it is used for copy the selected text or cell contents.
Steps:-select the cell area in your work sheet
1. Click on Clipboard box
2. Select copy option or
3. Press ctrl+c from the keyboard.
Paste:-it is used for paste the cut/copied text or cell contents. Or it is used to paste
the content of the clip board.
Steps:-cut/copy the text or cell contents.
1. Click on home button
2. Select paste option or
3. Press ctrl+v from the keyboard
Format painter: - it is used to copy the formatting and paste it into another text.
Font
Font name:- this command is used to set or change the formatting of your
document that will also be appeared when this document is printed on the paper.
Steps: - select the text which you wants to format
1. Click on home button
2. Click on font box
3. Select the formatting style (font face/name).
Font size:- it is use for change the size of the selected text, you can also make the
font bold, italic and underline.
Strike through:- the selected text appear with an straight line that strikes on it .for
ex:- computer
Sub scripts: - the selected character appears below from the normal base line. The
shortcut key of subscript is ctrl+=
For ex:- a2
Super scripts: - the selected character appears above from the normal base line.
The shortcut key of superscript is ctrl+shift+= For ex: - a2
Clear formatting: - it is used for clear the formatting from the text or sentences
which you have selected.
Text highlight color:-it makes the text highlighted as it is marked by the
highlighter pen.
Font color: - this is used for color the selected text or sentences.
Notes By- Pankaj sir. Page 5
CCC Note

Grow font / shrink font:-it is used to increase or decrease the size of the selected
text or sentence.
Fill color:-it makes the text highlighted as it is marked by the highlighter pen or set
the background color into selected cell.
Font color: - this is used for color the selected cell, row, and column.
Top align:-it is used for set the top alignment of the selected cell.
Middle align:-it is used for set the middle alignment of the selected cell.
Bottom align:-it is used for set the bottom alignment of the selected cell.
Cell alignment: - you can format the cell value its alignment font border and
pattern. To work with this you have to select particular cell or cell range and choose
the formatting style from the format cell box.
Cells: - you can format the cell value its alignment font border and pattern. To work
with this you have to select particular cell or cell range and choose the formatting
style from the format cell box.
Row formatting:-it formats the row for row size set the height of the row hide and
unhide it.
Column formatting:-to set the column width and auto fit selection of the column
we used column format.
Sheet format:-you can rename hide and unhide the sheet with the help of this
option.
Auto format:-table designs are saved in excel template u can set the style and
pattern of the table using auto format
Auto sum:- it is use to display the sum directly to the selected cells.
Fill: - it fills the numbers to the selected cells. You can generate the series also the
series can be from linear and growth for number. In the same way you can fill the
series of dates.
Linear: - it fills the series by adding the step value to the number.
Growth: - it fills the series by multiplying the value with the step value.
Clear:-
All: - it removes all cell contents and formatting including comments and hyper
links from the selected cells.
Formats: - it removes only the formatting from your selection the content and
comment are unchanged.
Content: - it removes the cell content (data and formula) from the selected cell.
Comments: - it removes only the comment from the selected cell.
Sort:-it arranges the record on the basis of any field or more than one field.
Steps:-select the table
 Click on home menu
1. Select sort option
2. Choose the sort by column option
3. Select ascending or descending order
4. Click on ok button.
Filter:-it is used to short out the records from the table and show the records
selectively.
Insert menu
Notes By- Pankaj sir. Page 6
CCC Note

Picture: - it inserts the clipart, chart, auto shapes, picture etc... At the insertion
point.

Chart: - the graphical representation of data in the form of graph is called chart.
There are different type of charts are available in ms excel but on the basis of data
sets there are two category of the data series from the chart.

1- single data series: - in this series of data set there is a set of category and one set
of value in the form of series. In this method pie chart will be used.

For ex:-
Party Seats
Bsp 45
Bjp 145
Sp 98
cong. 23
oth. 32
2- multiple data series: - this type of data series consist one set of category and
many set of values

For ex:-
seat seat Seat
Party 2002 2007 2012
BSP 120 202 88
BJP 34 45 45
SP 123 98 225
CONG 67 23 26

Page layout
Margins: - it is used for select the margin sign for the entire workbook or the
current section
Orientation: - it is used for switch the page between the portrait and landscape.
Size: - you can choose the paper size for the current section
Print area:-
1-Set print area:-it define the selected range as a print area which is only portion
of your file that will be printed out
Steps:-select the area which you want to print
1. Click on file menu
2. Select print area  set print area
Clear print area: - it is used for clear the selected print area from your active work
sheet

Notes By- Pankaj sir. Page 7

CCC Note

Page breaks:-it specifies where a new page with breaks in printed copy. Page
breaks are inserted above and to the left of the section
Background:-it is used for choose an image to display as the background of the
sheet.
Print titles:-it specifies row and columns to repeat on each printed page.
Height:-it formats the row for row size set the height of the row.
Width:-to set the column width and auto fit selection of the column we used
column format.
Scale:-it is used for stretch or shrinks the printed output to a percentage of its
actual size. The maximum width and height must be set to automatic to use this
feature.
Gridlines:-
View:-It is use for show the lines between row & columns in the sheet to make
editing and reading easier.
Print:-it is used for print row and columns heading

Formula Menu
Function:-functions are pre-defined set of formula that executes the result
according the procedure. There are different types of functions some of them are
used for micro programming and some of them are used for spread sheet functions.
1-Text function:-this type of function takes the input and generates the out put in
the form of text.
Char: - it is used for return the character from the code
=char (65)
Code:-it returns code no of the character
=code (“A”)
Concatenate: - it is used for joins the two or more strings
=concatenate (“hello”, “world”)
Dollar:-it precedes the dollar symbol with any number
=dollar (1500)
Left: - it is used for show the strings from the left
=left (“hello world”,7)
Rept: - it is used for repeat the strings
=rept (“R”, 5)
Right:-it show the string from the right
=right (“hello boys”, 7)
Trim: - it is used for remove the extra spaces
=trim (“hello India”)

Abs: - it returns the absolute value

=abs (-50)
Fact: - it returns the factorial of any number
=fact (5)
Int:-it returns integer number
=int (55.4)
Notes By- Pankaj sir. Page 8
CCC Note

Mod:-it show the reminder from the division

=mod (13, 2)
Product:-it multiply all numbers
=product (12, 3)
Sum:-it show the result by adding all numbers
Name: - it creates the name for cell range, or computes the value that u can use to
refer to the cell range or value
1- define:-it is used for creating cell name for set of value in to the individual cell
For ex:-
jansale febsale marchsale
Steps:- sale 2000 4000 3000
 Select cell expense 230 321 409 b2
 Click on profit formula menu
define name
 Type the field name (jansale)
 Click add
 Select cell c2
 Click on formula menu define name
 Type the field name (febsale)
 Click ok
 Now type the formula (=jansale+febsale) it will calculate the value from
b2 and c2.
Auditing: - this option traces the value collectively with formula and dependency
of formula or cell to different formula
1. Trace precedent:-if the formula is given in any cell it draw the tracer arrow
from the cell to the formula.
2. Trace dependent:-the value collected with the formula is traced by trace
dependent a tracer arrow draw from the table to formula.
3. Remove arrows :-it is use for remove all arrows from the table.

Data menu
Get external data: - you can select and create the data table and query from
external data base source like ms access for importing you have to choose the file
name from import select source box
Sort:-it arranges the record on the basis of any field or more than one field.
Steps:-select the table
 Click on data menu
 Select sort option
 Choose the sort by column option
 Select ascending or descending order
 Click on ok button.
Filter:-it is used to short out the records from the table and show the records
selectively. In filter there are 2 types of filter are available –
1- Auto filter:-it filters the record and shows the list of records selectively from the
table it is easiest way to list the record from the table it filters one field at a time.
Notes By- Pankaj sir. Page 9
CCC Note

Steps:-select the table

1. Click on data menu
1. Select filter option
2. Click on auto filter option.
2- Advance filter:-in advance filter you can filter the record form the table
according to given criteria so that it may meet the criteria range which you wants to
display the table area is called table range and the items which are written besides
the table is called criteria.

Name Post Salary

Ishita accountant 14000
Kanak Clerk 30000
Rahul Clerk 19000
Reshu Doctor 12000
Sriyeshi engineer 45000
Tripti engineer 30000
Criteria
post salary
clerk >=19000
Steps:-select the table
 Datafilter advance filter
 In criteria range box click and select the criteria
 Click ok
Text to column:-it converts the selected text into a column.

Review menu
Comment: - it is used for giving any notation about any cell, cell range or value.
PROTECTION:-
Protect sheet:-it prevent the changes to cell contents work sheet’s item in the chart
, graphics object on a work sheet or chart sheet.
Allow user to edit range:-it allows you to designate user computers or groups who
may makes changes to the specified cell without entering a password.
Protect workbook:-it is used to protect a workbook’s structure and window u can
prevent changes to the structure of a work book so that there can not be delete,
moved, hidden, unhidden or renamed and new sheet can not be inserted.
View menu
Normal:-it switches to normal view which is the default view for most tasks of ms
excel such as entering the data, filtering the record, charting etc…
Notes By- Pankaj sir. Page 10
CCC Note

Page break preview: - it switches to page break preview it is used for display your
work sheet as it will be printed out.
Custom view: - it creates deferent views of your work sheet. An view provide an
easy way see your data with deferent display option.
Full screen: - it hides most screen elements so that u can view more of your file to
switch back on your original view press esc key from the keyboard.
Zoom:-
Window—
New window: - it opens a new window with the same content as the active window
so u can view deferent part of your file at the same time.
Arrange:-it display all open files in separate windows on the screen. The arrange
command make it easier to drag between the files.
Split: - it split the active window into panes or removes the split from the active
window.

The End

Notes By- Pankaj sir. Page 11