Trainer Sumer Singh email id - ssmcca27@gmail.
com
Excel Basics & Advanced
Sl.no Topic Min Day 2
1 Introduction 15
2 Cell Formats 30
3 Parts of Screen 15
4 Features 60
Filling in data - Fill & Simple formula
Find / Replace
Goto
Edit text
Tea Break 10
5 Paste special 30
Formulas
1 Basics 30
2 Referencing 50
Relative, absolute and mixed referencing
3 Sum 30 Day 3
4 If 60
5 Sumif 30
Tea Break 10
Point No Particulars
1 Part of Microsoft office Suite, by far most popular spreadsheet
2 Few Uses of Excel
Number Crunching
Creating charts
Organizing lists and data
Accessing other data
Creating Graphics and diagrams (flow charts)
3 Workbooks, Worksheets, Cells and Range
Workbooks
Add New - File-New-WorkBook or Righ Click in Explorer Mode -New -Microsoft Excel
Worksheet
WorkSheets
How Many in a WorkBook ?
How to Set the Default Number
Insert,Delete,Move,Copy(New/Exsiting),Rename,Group,SelectAll,Activate,Hide,UnHide
,Background
Cells
How Many in a sheet ?
Cell Names
Cell Formats
Number
Alignment
Font
Borders
Fill
Protection
Rows,Colums
Height,Width,Insert,Delete
Using Keyboard
Up arrow
Down Arrow
Left Arrow
Right Arrow
Pgup
Pgdn
Ctrl + Pgup
Ctrl + Pgdn
Move active cell up
Move active cell Down
Move active cell left
Move active cell Right
Move active cell Up one screen
Move active cell down one screen
Move up one worksheet
Move down one worksheet
Group of Cells is called a Range
Cell Formats Format-->Cells or Rigth Click --->Format Cells
Default Format General
Number
Decimal Comm Sep
452435.525 452435.53 452,435.53
-452435.525 -452435.53 452435.53 -452435.53
Currency Same as Above but with a Currency Symbol
£452,435.53 € 452,435.53 -$4,588.00
Accounting £ 452,435.53 £ 452,435.53 -£ 4,588.00
Date Default <--Serial Value Based on 1900 Year
To Chage Tools-->Options-->Calculation - 1904
01/01/05 January 1, 2005
1/Jan April 21, 2007
1/Jan/05 4/21/07 6:56
1-Jan-05 J
Jan-05 J/05
January/05 1/Jan/2005
Time 6:56 6:56:37 6:56:37 AM
6:56 AM 6:56:37 AM
Percentage 0.745 75%
0.745 74.50%
Fraction 0.2 1/5
0.27 17/63
0.278 139/500
0.4 1/2
0.7 3/4
0.7 7/10
0.7 70/100
Scientific 432534523 4.33E+08
-487878788 -4.88E+08
4.00E-18 4E-18
Text 4536 4536 4555
345 345 488
4881 0
Special 442099542 (44) 209-9542 Phone
442099542 442-09-9542 Social Security
Custom 1. You can specify up to four sections of format codes. The sections,
separated by semicolons, define the formats for positive numbers,
negative numbers, zero values, and text, in that order. If you specify
only two sections, the first is used for positive numbers and zeros, and
the second is used for negative numbers. If you specify one section, all
numbers use that format. If you skip a section, include the ending
semicolon for that section.
[Positve Nos];[Negative Nos];[Zero];[Text]
000 1 001
2 002
Using text
#,##0 1500 1,589
#,##0 "US Dollars" 1500 1,500 US Dollars
"Amount: "General 1500 Amount: 1500
"The amount is "#,##0" dollars" 1500 The Amt is 1,500 Dollars
Scaling large numbers
#,##0, 3456789 3,457
Data Validation
0.00;"Positive numbers only!" 189 189.00
-789 Positive Numbers Only
Zero with Dashes
#,##0_);(#,##0);-0-_) 0 -0-
12.2 12
-12 (12)
Date Formats
mmmm-yy 12/31/2004 December-04
mmmm d, yyyy 12/31/2004 December 31, 2004
dddd 12/31/2004 Friday
mmmm d, yyyy (dddd) 12/31/2004 December 31, 2004 (Friday)
"It's" dddd 12/31/2004 Thank God It's a Friday
General;General;General;[Red]General Only text is red Only text is red
General;General;General;[Red]General 234 234
Special symbols
©General 1994 ©1994
General;General;General;General® Registered Registered®
Descriptive
"Positive";"Negative";"Zero";"Text" 12 Positive
-12 Negative
0 Zero
abc Text
Conditional Formatting and Colur
[red], [blue], [cyan], [magenta], [white], [black], [blue], and [green], you can use [color1] thru [color56].
[Blue][>200]General;[Red][>100]General;[Gree 458 300
150 150
100 100
[Red][<1]0.0%;[Blue][>=1]#,##0;General 0.5 50.0%
1 1
1555 1,555
-1 -100.0%
[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0 4,569,785
Magic Fill
@*. Sam Sam........................................
@*- Sam Sam---------------------------------
Display number in Thousand
#,###0, 1345689 1,346
Display number in Million 13595890 14
#,###0,, 135956890 135.96
-$4,588.00
-£ 4,588.00
(Alt 0169)
(Alt 0174)
] thru [color56].
Quick Access Tool bar
Ribbon Tabs
Button Groups
Formula Bar
Name Box
Work Sheets
Status Bar
Customize Status Bar
Practicals
Fill Months usind Auto fill
Sales data starting from 50,000 with rs sign and increase by 2.5 % each month
Then Sum the total of Projected sales
Month Projected Sales
Note:
Types of data in excel
Numerical data
Text
Formulas
Text that begins with a number is considered as Text, hence the cell cannot be used for numeric calculations
Eg: 12apples
Edit text
To edit text or value in a cell press F2 and then edit/ delete or click in formula bar and edit
Double click in cell and edit
Word "Edit" appears on the left side of the "Status Bar"
In edit if you click on Home option it will take to the beginning of the cell
Tip : When a range of cells is selected, excel automatically moves the cell pointer to the next cell in the range when you pr
Use Ctrl+enter to place information into multiple cells simultaneously
Find and Replace
Find and select on Home tab - Short cut Ctrl + F
Goto
Find & Select on Home tab - short cut - Ctrl + G
Special - helps you select cells with special features
n the range when you press enter key
Paste Special
Function Action
1 All Copies everything - Value, text and formatting
2 Formulas Copies only the formulas - No formatting
3 Values Copies only the Value - formulas will get erased
4 Formats Copies only the formats
5 Comments Copies only the comments of the cell
6 Validation Copies only Validation criteria
7 All Except Borders Copies everything other than borders
8 Column Width Copies Column width information
Operations
1 None
2 Add
3 Subract
4 Multiply
5 Divide
6 Skip Blanks
7 Transpose
Formula Basics
Mathematical Operations
Cell references
Values or text
Worksheet functions such as SUM or AVERAGE
Operator Name
+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponentiation
& Concatenation
= Logical comparison (equal to)
> Logical comparison (Greater than)
< Logical Comparison (less than)
>= Logical comparison (Greater than or Equal to)
<= Logical Comparison (less than or equal to)
<> Logical Comparison (not equal to)
Parenthesis
2
3
5
Formula -1 4
Formulas must begin with = sign
either enter or use pointing by clicking on fx on formula bar
Particulars
Relative reference row and column reference can change
Absolute reference row and column reference does not change
Mixed reference either the row or coloumn reference is relative
Relative Referencing
Item Quantity Price Total
Chair 4 ₹ 125.00
Desk 4 ₹ 695.00
Lamp 3 ₹ 39.95
Absolute referencing
Item Quantity Price Total Sales Tax
Chair 4 ₹ 125.00 ₹ 500.00
Desk 4 ₹ 695.00 ₹ 2,780.00
Lamp 3 ₹ 39.95 ₹ 119.85
Sales tax 7.50%
Mixed Referencing
Width
Length 1 2 3 4
1
1.5
2
1.5
3
Calculate Total
Calculate Sales tax
5
SUM
Horizontal
100 200 300 600 =SUM(C4:E4)
Vertical
100
200
300
600 =SUM(C7:C9)
Single Cells
100 300 600 =SUM(C13,D14,E13)
200
Multiple Ranges
100 400
200 500
3000 600
4800 =SUM(C17:C19,E17:E19)
Functions
100 400
200 500
300 600
900 =SUM(MAX(E23:E25),MAX(C23:C25))
What Does It Do ?
This function creates a total from a list of numbers.
It can be used either horizontally or vertically.
The numbers can be in single cells, ranges are from other functions.
Syntax
=SUM(Range1,Range2,Range3... through to Range30).
Formatting
No special formatting is needed.
Note
Many people use the =SUM() function incorrectly.
This example shows how the SUM has been combined with plus + symbols.
The formula is actually doing more work than needed.
It should have been entered as either =C48+C49+C50 or =SUM(C48:C50).
100
200
300
600 =SUM(C48+C49+C50) Wrong!
=SUM(C48:C50) Correct
=C48+C49+C50 Correct
IF
What Does It Do?
This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.
Syntax
=IF(Condition,ActionIfTrue,ActionIfFalse)
The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.
Formatting
No special formatting is required.
Example 1
The following table shows the Sales figures and Targets for sales reps.
Each has their own target which they must reach.
The =IF() function is used to compare the Sales with the Target.
If the Sales are greater than or equal to the Target the result of Achieved is shown.
If the Sales do not reach the target the result of Not Achieved is shown.
Note that the text used in the =IF() function needs to be placed in double quotes "Achieved".
Name Sales Target Result
Aditya 1000 5000 Not Achieved =IF(C31>=D31,"Achieved","Not Achieved")
Bhaskar 6000 5000 Achieved =IF(C32>=D32,"Achieved","Not Achieved")
Chetan 2000 4000 Not Achieved =IF(C33>=D33,"Achieved","Not Achieved")
Example 2
The following table is similar to that in Example 1.
This time the Commission to be paid to the sales rep is calculated.
If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.
If the Sales do not reach Target, there is no Commission
Name Sales Target Commission
Aditya 1000 5000 0 =IF(C43>=D43,C43*10%,C43*0%)
Bhaskar 6000 5000 600 =IF(C44>=D44,C44*10%,C44*0%)
Chetan 2000 4000 0 =IF(C45>=D45,C45*10%,C45*0%)
Nested IF'S
Lets consider a three tire comission
Eg Sales > 0 0
Sales > 1000 10%
Sales > 2000 20%
As Single IF cannot achive the above.You will have to use a IF within an IF.
Name Sales Commission
Aditya 950 0 IF(C53>2000,20%*C53,IF(C53>1000,10%*C53,0))
Bhaskar 1050 105
Chetan 2000 200 IF True If False then another IF
Main IF
You can nest upto seven ifs
You can refer the sales slabs and commission to cells as below, so if the slabs or the
rate of commission changes then same could be changed in the cells rather than in the formulas
Name Sales Commission
Aditya 1245 124.5
Example 3
This example uses the =AND() within the =IF() function.
Lets say there is a 10% discount on certain product categories
The discount is only given on products which are on Special Offer, when the Order Value
is 1000 or above.
The =AND() function is used with the =IF() to check that the product is on offer and that
the value of the order is above £1000.
Special Order
Product Offer Value Discount Total
Windows Yes 950 0 950
MAC No 2000 0 2000
UNIX Yes 500 0 500
Linux Yes 3000 300 2700
=IF(AND(C61="Yes",D61>=1000),D61*10%,0)
ot Achieved")
ot Achieved")
ot Achieved")
n the formulas
