1

Hello Friends …………..

Welcome to the
World of Computer.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
2

Microsoft Excel 2010

Excel 2010 Is Spreadsheet Software in
the new Microsoft 2010 office suite.
Excel allows you to store , manipulate
and analyze data in organized
workbooks for home and business
tasks.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
3

How to Open Ms Excel?

• Start
• All Programs
• Ms Office
• Ms excel.
Or
• Run Command - Excel
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
4

L.P. 1 User Interface

• Insert Work Sheet:- Home Tab > Insert

Command > Insert Sheet (Shift+F11).
• Delete Work Sheet:- Home Tab > Delete
Command > Delete Sheet.
• Rename Sheet:- Home Tab > Format
Command > Rename Sheet (Right Click
on Sheet).
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
5

L.P. 1 User Interface

• Resize Multiple Row/Column :- Select

Multiple row/column and Right click >
Row Height/Column width (You can drag
with dragging handle).
• Autofit :- Double click in between two
columns or row.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
6

L.P. 1 User Interface

• Product or Multiplication :-
=product(G27:H27)
=G27*H27
• Dragging Ref. Formula :- Apply formula and
drag it with dragging handle.
• Subtraction :-
=G27-H27
Total=I33(S.total)+I35(SalesTax)-I36(Discount).
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
7

L.P. 1 User Interface

• Totaling :-
=Sum(I27:I31)
=I27+I28+I29+I30+I31
Select all cells > Home tab > Auto Sum
• Division :-
=G27/H27
Sales Tax=I33(Sub total)*I34(Tax Rate)/100
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
8

L.P. 2 IF Formula(Mark sheet)

Continuity (With Dragging Handel):

• Numbers
• Days
• Months

Total Of Marks :
=Sum(C6:H6)
Minimum Marks:
=Min(C6:H6)
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
9

L.P. 2 IF Formula(Mark sheet)

Correct (%):
If(K6(Min Marks)<40,0,I6(Total)/6)
Merge & Center:
It Is Used To Combine Two Or More Cells In to
One Cell. (Home > Merge & Center).

Applying Borders:
It Is Used To Apply Border On Selected Cells.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
10

L.P. 3 IF Formula(Salary Slip)

Basic Salary:
C6= Department
=IF(C6="ACCOUNTS",12000,IF(C6="MANAGEMENT“
,20000, IF(C6="MARKETING", 14500, 9000)))

D.A.(Dearness Allowance):
E6=Basic Salary
=IF(C6="ACCOUNTS", E6*5%, IF(C6="MANAGEMENT",
E6*7%, IF(C6="MARKETING", E6*8%, 0)))
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
11

L.P. 3 IF Formula(Salary Slip)

HRA(House Rent Allowance): F6= D.A.
=(E6+F6)*5%

Help Allowance: D6=Attendance

=IF(C6="ACCOUNTS", 100*D6, IF(C6="PRODUCTION",
150*D6, 0))
Tiffin Allowance :
=IF(C6="MANAGEMENT", 5000, IF(C6="MARKETING",
8000, 0))
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
12

L.P. 3 IF Formula(Salary Slip)

Commission:
=IF(C6="MARKETING",E6*12%, IF(C6="PRODUCTION",
E6*8%, 0))

Bonus:
IF(C6="ACCOUNTS",50000/12,IF(C6="MANAGEMENT",
100000/12,IF(C6="MARKETING",35000/12,40000/12)))

Gross Salary:K6=Bonus
=SUM(E6:K6)
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
13

L.P. 3 IF Formula(Salary Slip)

Provident Fund:(P.F.):
=E6*12%.

Net Salary:
=L6(Gross salary) – M6(P.F.) – N6(Advance
Salary).

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
14

L.P. 4 AND/OR Function

C6=Graduate
D6=Computer literate
E6=Experience
• Accountant :
=IF(AND(C6="Yes", D6="Yes", E6>=1), "Accept",
"Reject")

• Office Assistance :
=IF(OR(C6="Yes", D6="Yes", E6>=1), "Accept",
"Reject")
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
15

L.P. 5 Filter, sorting & Freeze Panes

• Filter :
Data Tab > Filter (Or)
Home Tab > Sort And Filter
• Sorting:
Data Tab > Sort (or)
Home Tab > Sort And Filter
• Freeze Panes:
View Tab > Freeze Panes
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
16

L.P. 6 Cell Formatting & Protection

• Date Format :- Select Cells > Right Click >

Format Cells > Date.
• Number Format:- Select Cells > Right Click >
Format Cells > Number.
• (%) Format :- Select Cells > Right Click >
Format Cells > Percentage.
• Time Format :- Select Cells > Right Click >
Format Cells > Time.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
17

L.P. 6 Cell Formatting & Protection

• Text Format :- Select Cells > Right Click >

Format Cells > Text.

• Wrap Text :- All Content Visible Within Cell By

Displaying It In Multiple Lines.

• Fill : Color The Background Of Selected

Cells. Select Cell > Right Click > Format Cells >
Fill Option. Or Home Tab > Fill. Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
18

L.P. 6 Cell Formatting & Protection

• Text Alignment :- Select Cells > Right Click >

Format Cells > Alignment > Orientation.
Or Home Tab > Text Orientation.
• Hide / Unhide :- Select Cells , Row , Columns ,
Worksheet > Right Click > Hide / Unhide Or
Home Tab > Format Option.
• Protect/Un Protect :- Select Data > Review
Tab > Protect Sheet > Give Password.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
19
L.P. 7 Conditional Formatting &
Paste Special
Conditional Formatting:-
Select Data > Home Tab > Styles > Conditional
Formatting > Highlight Cell Rules > less Than .

Clear Rules :
Select Data > Home Tab > Styles > Conditional
Formatting > Clear Rules > Clear Rules From
Selected Cell.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
20
L.P. 7 Conditional Formatting &
Paste Special
Data Bars:
Select Data > Home Tab > Styles > Conditional
Formatting > Data Bars.

Icon Sets:
Select Data > Home Tab > Styles > Conditional
Formatting > Icon Sets.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
21
L.P. 7 Conditional Formatting &
Paste Special
Paste Special: Values: Select Data > Copy> Right
Click On Cell> Paste Special> Values.
Transpose :Select Data > Copy> Right Click On
Cell> Paste Special> Transpose.
All Using Source Theme: Select Data > Copy>
Right Click On Cell> Paste Special> All using
source Theme.
Copy Formatting > Paste Special > Formats.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
22

L.P. 8 Charts & Graphs

• Inserting Charts & its Types:

Select Data > Insert Tab > Charts > Select Chart
Type. E.g. Column Chart, Pie Chart.
• Layout - Chart Labels:
Select Chart > layout Tab > Chart Title.
Select Chart > Layout Tab > legend.
Select Chart > Layout Tab > Data Labels.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
23

L.P. 8 Charts & Graphs

• Design – Chart Style:

Select Chart > Design Tab > Chart Style

• Design – Chart Layout:

Select Chart > Design Tab > Quick Layout

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
24
L.P. 9 Data Validation & Removing
Duplicate
Data Validation :
• It Is Used to Define Input Criteria.
• Select Data > Data Tab > Data Tools Group >
Data Validation.
• Define Validation Criteria > Allow date >
Define Range.
• Define Error Alert.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
25
L.P. 9 Data Validation & Removing
Duplicate
Removing Duplicates :

• It is Used to Remove Duplicate Entries.

• Select Data > Data Tab > Data Tools Group >
Remove Duplicates > Select Columns.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
26

L.P. 10 Pivot Tables

Creating Pivot Table:

• Select Data > Insert Tab > Pivot Table.
• Choose Field To add To Report.
• Change Reporting Pattern > Drag & Drop Data To
Field.
• Group/Ungroup: Right Click On Row label >
Group/Ungroup.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
27

L.P. 10 Pivot Tables

• Filed Settings : Right Click On Cell > Value Filed

Settings/Option Tab > Filed Settings > Sum, Average,
Percentage Etc.

Creating Pivot Chart:

• Select Pivot Data > Option Tab > Pivot Chart. OR
• Select Data > Insert Tab > Pivot Chart.
• You Can Filter The Chart Data.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
28

L.P. 11 V & H LookUp

Vertical Lookup:
Formula Tab > Function Library > Lookup & References
> V lookup .
Lookup Value : B3 (Inventory code)
Table _ Array : Select Next Sheet Data
Col_ index_ num: Column No. In Table Array From
Which The Matching Value Should Be Returned.
Range Lookup : FALSE For Exact Match.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
29

L.P. 11 V & H LookUp

Horizontal Lookup:
Formula Tab > Function Library > Lookup & References
> H lookup .
Lookup Value : B2 ( Item Column)
Table _ Array : Select Next Sheet Data (Selling Price)
Row_ index_ num: Row No. In Table Array From Which
The Matching Value Should Be Returned.
Range Lookup : FALSE For Exact Match.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
30

L.P. 12 Macros

• Record Macros:
View Tab > Record Macros > Give Macro Name > Start
Recording.
• View & Run Macros:
View Tab > View Macros > Select Macro Name > Click
On Run.
• Go to Special:
Select Cell > Home Tab > Editing Option > Find & Select
> Go to Special.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
31

L.P. 13 Page & Print Setup

Print Preview Option:

• File Tab > Print Option Or
• Quick Access Toolbar > Print Preview And Print.
Borders In Print Preview.
• Select Data > Home Tab > All Borders.
Print Options
• Define Printer Style
• Define No. Of Copies
• Print ( Ctrl+ p)
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
32

L.P. 13 Page & Print Setup

Page Break Preview:

• View a preview of Where Pages Will Break When
Document Is Printed.
• View Tab > Page Break Preview OR
• Status Bar > Page Break preview.
Page orientation:
• Page Layout Tab > Page Orientation >
Portrait(Vertical Format )/ Landscape (Horizontal
Format)
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
33

L.P. 13 Page & Print Setup

Print Titles:
Specify Column And Row To Repeat On Each Printed
Page.
Page layout Tab > Print Titles > Sheet Option > Define
Row/ Column To Repeat.
Print Area :
Make A Specific Area Of The Sheet For Printing.
Select Area or Data > Page layout Tab > Print Area.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
34

L.P. 13 Page & Print Setup

Printing Content :
Select Content > File Tab > Print Option > Settings >
Print Selection.
Print Active Sheet : Print Current Sheet
Print Entire Workbook: Print All Sheets Of Workbook.
Header & Footer :
Insert Tab > Header / Footer.
Status Bar/View Tab > Page Layout.
Page Number: Insert Page No .
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
35

L.P. 14 Tricks & Shortcuts

Drag Formula : Double Click

Formula On Multiple Location: For multiple Selection
Press Ctrl key , Apply Sum Formula And Press Ctrl+
Enter
Repeat Steps (Actions) : F4
Select Entire Row : Shift+ Spacebar
Select Entire Column: Ctrl+ Spacebar
Select Selection : Ctrl+ Shift+ Spacebar
Select Data Till End :Ctrl+ Shift+ Down
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
36

L.P. 14 Tricks & Shortcuts

Drag Formula : Double Click

Formula On Multiple Location: For multiple Selection
Press Ctrl key , Apply Sum Formula And Press Ctrl+
Enter
Repeat Steps (Actions) : F4
Select Entire Row : Shift+ Spacebar
Select Entire Column: Ctrl+ Spacebar
Select Selection : Ctrl+ Shift+ Spacebar
Select Data Till End :Ctrl+ Shift+ Down
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
37

L.P. 14 Tricks & Shortcuts

• Insert Chart : Alt+F1

• Insert Chart In New Sheet : F11
• Print Preview : Ctrl + F2
• Insert Current Date : Ctrl+;
• Insert Current Time : Ctrl+ Shift+;
• Zoom To Selection: View Menu
• TO Zoom in & Zoom Out: Ctrl+ Mouse Scroll

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
38

L.P. 14 Tricks & Shortcuts

Import Custom List(Continuity):

• Select data>File Tab>Options>Advanced>Edit
Custom List>Import >Column
• New Sheet>Type List And Drag…
Number Format : Ctrl+ Shift+!
Date Format: Ctrl+ Shift+#
Time Format: Ctrl + Shift+ @
Insert $ Symbol For Constant Cell Address : F4
Show /Hide Formulas: Ctrl+~(Tilld key)
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
39

L.P. 14 Tricks & Shortcuts

• Two Lines in Same Cell: Alt+ Enter

• Duplicate: Ctrl+ D
• Edit Cell: F2
• Go to Specific Cell: Ctrl+ G
• Insert New Worksheet: Shift+F11
• Open Formula Window: Shift+F3
• Minimize Current Window: Alt+ Space+ N
• Maximize Current Window: Alt+ Space +X

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
40

L.P. 14 Tricks & Shortcuts

Switch Between Workbook:

Ctrl+ Page up, Ctrl+ Page Down
Sum All Of The Above Cell : Alt+ =
Format Number In currency Format:
Ctrl+ Shift+$
Percentage Format: Ctrl+ Shift+%

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
41

L.P. 15 Advance Function

• Product:
=PRODUCT(C3:E3) Return Multiplication
• Average:
=AVERAGE(C5:E5) Calculates Average of given
numbers
• Logarithm (Base 10):
=LOG10(C7)
Gives Log Value for given number
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
42

L.P. 15 Advance Function

• Remainder(MOD):
=MOD(C9,D9)
Gives Remainder after number is divided.
• Power (Raise To):
=POWER(B11,C11)Or=4^3^ (Shift + 6)
• Square Root
=SQRT(C13)Gives Square Root
e.g. square root of 4 is 2
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
43

L.P. 15 Advance Function

• Concatenate:
=CONCATENATE(B15,C12)or=b15&c15
• Round
=ROUND(C17,3)
It round off the number to the given decimals
• Rounddown
=ROUNDDOWN(C19,3)
It makes upward rounding. Ignores adding after 0.5

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
44

L.P. 15 Advance Function

• Roundup
=ROUNDUP(C21,3)It Round downwards.
It considers adding after0.5
• Absolute(ABS)
=ABS(C23)
Gives Absolute Values.
E.g. it converts negative values into positive
values
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
45

L.P. 15 Advance Function

• Maximum:
=MAX(C25:E25)
Gives the Highest Value in selected range.
• Minimum:
=MIN(C27:E27)
Gives the Lowest Value in selected range
• Small:
=SMALL(C29:H29,3)
Returns the nth Smallest Value from the range
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
46

L.P. 15 Advance Function

• Large:
=LARGE(C31:H31,2)
Gives nth Highest value from the range
• Count Days:
=D33-C33
Calculate number of days between two given date
• Count:
=COUNT(C35:E35)
Counts the No. of Cells having numeric value
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
47

L.P. 15 Advance Function

• Counta:
=COUNTA(C37:E37)
Counts no of cells having numeric & text values
• CountBlank:
=COUNTBLANK(C39:E39)
Counts Number of Blank Cells in given range
• Integer:
=INT(C41)Gives Integer value.
Ignores values after decimal.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
48

L.P. 15 Advance Function

• Upper:
=UPPER(C43) Capitalize all letters.
• Lower:
=LOWER(C45) Converts text into lower case.
• Proper:
=PROPER(C47)
Convert text into Sentence Case.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
49

L.P. 15 Advance Function

• Trim:
=TRIM(C49)
Removes Spaces before text. Used to correct
typing mistakes
• Present Value:
=PV(C52,D52,,E52,0)
Gives the Investment Required to make in FD to
get the desired future value at given ROI & Time
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
50

L.P. 15 Advance Function

• Future Value:
=FV(C55/12,D55*12,E55,,0)
Gives the Amount received after certain period
of time at given rate by depositing funds every
month.
E.g. Post or Recurring Account.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
51

L.P. 15 Advance Function

• XIRR
Actual Return:
=(E60+D60+C60)/-C60*100
Actual Profit (355000+205800-500000) = 60800
Returns = 60800/500000
=0.1216 or 12.16%
Annualised Return:
=XIRR(C60:E60,C59:E59)
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
52

L.P. 15 Advance Function

• PMT:
=PMT(C64/12,D64*12,E64,,0)
C64:Rate Of Int.
D64: Years.
E64: Loan Amount.
Gives LOAN Installment (EMI) for given loan
amount at given rate of interest for a particular
period.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
53

L.P. 15 Advance Function

• Goal Seek:
To Find The Result You Want By Adjusting An
input Value.
If you know the result that you want from a
formula, but are not sure what input value the
formula needs to get that result, use the Goal
Seek feature.

Basic, M.S. Office & Internet

_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav
54

L.P. 16 Import External Data

Get External Data.

• Data Tab > Get External Data Group > Data Tab From
Access.(.mdb)
• Data Tab > Get External Data Group > Data Tab >
From Text (.csv(comma Separated value))>Text
Import Wizard > Select Delimited
Option > Select Comma Option > Ok.
• Data Tab > Get External Data Group > From Other
Sources> From Xml Data Import.
Basic, M.S. Office & Internet
_________________________________________________________________
Join InfoSoft – Where Successful Careers Begins Compiled by : - Viral Jadhav