III B.

COM-SJC June 23, 2019

III.B.Com - Excel Practical

EX:1. Enter the following data in an Excel worksheet and save it salary.xlsx.

Sl.No Employee Name BASIC DA HRA CCA GROSS PF IT DED NET

1 Abinau Bindra 10000 2000 1800 840 14640 1318 1464 2782 11858
2 Madhu Chapra 9500 1900 1710 798 13908 1252 1391 2643 11265
3 Gokul 12500
4 Lindsey 9800
5 Kevin 6820
6 Komal 9850
7 Beena 4520
8 Lakshmanan 6580
9 Gandhi 9850
10 Donald 6550
11 Peiterson 4500
12 Longfellow 9500
13 Kavitha 7500
14 Priya 6500
15 Willson 8500
16 Murugan 7500
17 Ganesan 7750
18 Moorthy 4850
19 Lokmanya 9852
20 Patel 12450
21 Steffi 11650
22 Fiorina 11750
23 Gangadhar 12500
24 Krishnan 6500
25 Mohan 7500
26 Oprah 9500
27 Hemelatha 8500
28 Uma 5000
29 Zeenath 9000
30 Barath 12000

1. Fill the BASIC, DA, HRA, CCA, GROSS, PF, IT, DED, NET column using the following guidelines.
a) DA is equal to 20 % of Basic b) HRA is equal to 15% of Basic+DA
c) CCA is equal to 7 % of Basic+DA d) Gross =Basic+DA+HRA+CCA
e) PF is equal to 9% of Gross f) IT is equal to 10% of Gross
g) DED is equal to PF+IT h) Net = Gross-Ded
2. Copy the entire table to a word document and save it as salary.doc
3. Copy the entire worksheet to another worksheet .
4. Name the first worksheet as salary1 and the second worksheet as salary2
5. Go to salary1 worksheet make changes in the formula as below
a) DA is equal to 15 % of Basic b) HRA is equal to 12% of Basic+DA
c) CCA is equal to 5 % of Basic+DA d) PF is equal to 5% of Gross
e) IT is equal to 12% of Gross

1
 III B.COM-SJC June 23, 2019

EX: 2. Enter the following data in an excel worksheet and name it as cia.xlsx
D.NO MID100 MID(35) END100 END(35) ASN(25) ATTN TOT(100)
05UCO201 A2 B2 C2 21 3
05UCO202 26 42 11 5
05UCO203 11 27 15 4
05UCO204 36 49 12 4
05UCO205 12 28 13 4
05UCO206 28 43 14 2
05UCO207 68 78 21 3
05UCO208 25 31 19 1
05UCO210 37 33 13 2
05UCO212 28 38 12 5
05UCO213 27 13 9 4
05UCO214 45 29 13 4
05UCO215 17 24 8 1
05UCO216 77 57 21 2
05UCO217 10 58 15 4
05UCO218 36 42 10 3
05UCO219 71 67 18 5
05UCO220 77 66 20 2
05UCO221 42 73 12 3
05UCO222 31 42 11 5
05UCO223 59 63 19 5
05UCO224 14 16 6 4
05UCO225 21 46 14 3
05UCO226 50 50 18 3
05UCO227 71 57 6 1
05UCO229 25 46 13 4
05UCO230 17 23 6 1

a) Fill the MID(35) and END(35) column converting the marks into 35 equivalent and find the total.
b) =(a2*.35)
c) Find out how many rows and columns are there in a worksheet using.
d) Practice using arrow keys, Tab, shift +tab, home, end, ctrl+home, ctrl+end, F5, ctrl+G, PgUp and
PgDn keys.
e) Practice different methods of selecting cells using a) Shift+arrow keys b) using F8, c) by entering cell
address, d) using mouse, e)using keyboard
f) Practice: a) select entire row, b) select entire column, c) select entire sheet.
g) Type the following in a word document and name it as course.doc

=IF(H2>=40,"PASS","FAIL")

2
 III B.COM-SJC June 23, 2019

EX:3. Prepare the following table, find total for each product and for each region, also prepare a graph

Product Region1 Region2 Region3

Dolls 350 1275 650
Truks 1325 1370 800
Puzzles 1650 1525 1100

EX:4. Enter the following data in an excel worksheet and name it as cia.xlsx
D.NO MID100 MID(35) END100 END(35) ASN(25) ATTN TOT(100)
05UCO201 18 29 21 3
05UCO202 26 42 11 5
05UCO203 11 27 15 4
05UCO204 36 49 12 4
05UCO205 12 28 13 4
05UCO206 28 43 14 2
05UCO207 68 78 21 3
05UCO208 25 31 19 1
05UCO210 37 33 13 2
=IF(H2>=40,"PASS","FAIL")
1. Fill the MID(35) and END(35) column converting the marks into 35 equivalent and find the total.
2. Find out how many rows and columns are there in a worksheet using.
3. Practice using arrow keys, Tab, shift +tab, home, end, ctrl+home, ctrl+end, F5, ctrl+G, PgUp and PgDn
keys.
4.Practice different methods of selecting cells using a) Shift+arrow keys b) using F8, c) by entering cell
address, d) using mouse, e)using keyboard
5. Practice: a) select entire row, b) select entire column, c) select entire sheet.

EX:5. Create the following table in Excel and find the result using IF function
=IF(AND(B2>=40,C2>=40,D2>=40,E2>=40,F2>=40),“PASS”,“FAIL”)
For grade
=IF(H2>=75,"Distinction",IF(H2>=60,"First",IF(H2>=50,"Second","Third")))

Name TAMIL ENGLISH MATHS HISTORY SCIENCE TOTAL RESULT Class

Ganesan 40 45 80 78 90
Moorthy 50 52 54 54 40
Lokmanya 82 42 52 25 58
Patel 58 25 60 89 78
Steffi 45 87 47 39 58
Ganesan 87 90 89 45 39
Moorthy 29 45 70 40 48
Lokmanya 45 39 52 59 49
Patel 90 40 69 64 28

3
 III B.COM-SJC June 23, 2019

EX :6. Following are the sales of Maruti Vehicles in Tamilnadu.

Region-wise Sales
Product Chennai Trichy Madurai Kovai
Maruti-800 200 235 250 175
Omni 250 275 350 325
Alto 400 300 350 400
Swift 175 150 150 200
WaganoR 200 225 175 250
Versa 250 200 250 200
a)Prepare a table in Excel and use the following functions. SUM, AVERAGE, MIN, MAX for each
product and for each region and insert a ‘comment’ in the appropriate cell.
b) Also prepare appropriate graph.

EX: 7. Sales of Maruti car in different regions of TN are given below, using Pivot table generate as
many tables as possible.

REGION PLACE NUMBER M800 ALTO A_STAR SWIFT OMNI ECO DZIRE
South Madurai 3005 10 20 20 20 5 20 20
East Kovai 3049 25 10 10 10 8 10 45
West Trichy 3654 21 25 40 41 7 20 12
North Chennai 3055 5 10 50 25 9 30 60
East Kovai 3000 4 20 30 30 8 10 32
South Madurai 3045 5 32 20 10 4 21 10
North Chennai 3048 8 12 10 20 10 3 20
South Madurai 3045 7 2 10 14 12 2 12
South Nellai 3025 10 10 23 12 4 4 14
East ERODE 3021 1 20 20 10 5 12 35
West Trichy 3025 2 30 12 30 8 12 21
North villupuram 3028 8 12 45 20 9 13 40
South Palayamkottai 3030 3 10 12 10 22 15 20
East Ooty 3031 12 20 10 20 10 19 14
West Tanjore 3029 50 30 30 10 16 15 32
West TRICHY 3050 20 30 20 20 20 20 23
North Chennai 3003 10 10 10 15 13 23 24
North Chennai 3008 10 25 16 16 16 15 25
South Madurai 3007 15 15 15 18 45 16 26
East ERODE 3017 17 8 14 20 24 20 30

4
III B.COM-SJC June 23, 2019

EX :8. Enter the following and do as instructed below

Particulars Quarter-1 Quarter-2 Quarter-3 Quarter-4

Opening Bank Balance: 160,000
Expenditures Wages: 33,500 22,500 23,000 23,400
Electricity: 8,500 8,500 9,500 10,600
Overheads: 10,000 12,000 13,900 16,800
Materials: 50,000 65,000 75,000 75,000
Total (expenditures)
Sales Income: 130,000 132,900 133,900 134,100
Profit for this quarter
Closing bank balance:

1. Enter in formulae to calculate 'Total (expenditures) for each quarter. (The sum of all the
individual expenditures)
2. Enter in formulae to calculate the 'Profit' for each quarter. (Sales income less Total
expenditures)
3. Enter in formula to calculate the closing balance for each quarter (The Opening balance plus
the profits)
4. Enter in formulae for the 'Opening balance' for each quarter after the first one

Ex.9:

Name TAMIL ENGLISH MATHS HISTORY SCIENCE TOTAL RESULT GRADE

Ganesan 40 45 80 78 90 333 PASS
Moorthy 50 52 54 54 40 250 PASS
Lokmanya 82 42 52 25 58 259 FAIL
Patel 58 25 60 89 78 310 FAIL
Steffi 45 87 47 39 58 276 FAIL
Ganesan 87 90 89 45 39 350 FAIL
Moorthy 29 45 70 40 48 232 FAIL
Lokmanya 45 39 52 59 49 244 FAIL
Patel 90 40 69 64 28 291 FAIL

=IF(AND(B2>=40,C2>=40,D2>=40,E2>=40,F2>=40),"PASS","FAIL")

5
III B.COM-SJC June 23, 2019

Ex.10:

RESULT AND GRADE USING IF and AND CONDITIONS and Conditional Formating

D.NO MID100 MID(35) END100 END(35) ASN(25) ATTN TOT(100) Result GRADE GRADE
05UCO201 100 35 100 35 25 5 100 PASS Distinction Distinction
05UCO202 78 27.3 42 14.7 11 5 58 PASS Second Second
05UCO203 11 3.85 27 9.45 15 4 32.3 FAIL Third FAIL
05UCO204 23 8.05 10 3.5 12 4 27.55 FAIL Third FAIL
05UCO205 78 27.3 28 9.8 13 4 54.1 PASS Second Second
05UCO206 28 9.8 43 15.05 14 2 40.85 PASS Third Third
05UCO207 68 23.8 78 27.3 21 3 75.1 PASS Distinction Distinction
05UCO208 60 21 31 10.85 19 1 51.85 PASS Second Second
05UCO210 10 3.5 12 4.2 21 2 30.7 FAIL Third FAIL
=IF(H3>=40,"PASS","FAIL")
=IF(H3>=75,"Distinction",IF(H3>=60,"First",IF(H3>=50,"Second","Third")))
=IF(H3>=75,"Distinction",IF(H3>=60,"First",IF(H3>=50,"Second",IF(H3>=40,"Third","FAIL"))))

Functions in Excel

Predefines and built-in formulas are called ‘functions’. A formula or a function always starts with an
“=” symbol.

a) Mathematical functions
b) Statistical functions
c) Date and Time functions
d) Logical functions
e) Text functions

a) M Mathematical functions

=SUM()
=ROUND()
=SQRT()
=ABS()
=TRUNC()

b) Statistical functions

=MAX()
=MIN()
=AVERAGE()
=COUNT()
=COUNTA()
=COUNTBLANK()
=COUNTIF()
=SUMIF()

6
III B.COM-SJC June 23, 2019

c) Logical Functions – are used to make a decision based upon a value within the spreadsheet. It
evaluates the value and makes decision. Upon checking, one value is returned, if the condition is true,
otherwise a different value is returned if the condition is false.
=AND()
=NOT()
=OR()
=IF()
d) Date and time functions – date should be entered in correct format, otherwise it will not be treated
as date. Date is aligned right. The default date format in excel is month/day/year
=NOW()
=DAY()
=MONTH()
=YEAR()
=TODAY()
=WEEKDAY()

e) Text Functions
=CANCATENATE()
This funcition joins several text strings into one text string
Eg. CONCATENATE (“ I AM”, “BASHA”)
=LEN()
This is used to return the number of characters in a text string
Eg: =LEN(“I LOVE MY COUNTRY”)
=LOWER()
This converts all upper case letters in a text string to lower case
Eg: =LOWER(“ WHAT ARE YOU DOING?”)
Eg: =LOWER(A31)
=UPPER()
This converts all lower case letters in a text string into upper case.
Eg: =upper(“what are you doing?”)
=TRIM()
Removes all spaces from a text string except for single spaces between words
Eg: =TRIM(“I AM OK ARE OK?”)
=PROPER()
This capitalizes the first letter in each word of a text
Eg: =PROPER(“ are you going to college or film”?)

Some Examples :

COUNTIF

This function is used to count cells within a range of cells that meet a specified criterion.

a. =COUNTIF(A1:A10,20)
b. =COUNTIF(B8:B28,50000)
c. =COUNTIF(B9:B29,">=50000")
d. =COUNTIF(A10:A30,"LUCAS")

SUMIF

7
III B.COM-SJC June 23, 2019

This function is used to return values from a specified range that meets the condition/criteria.
=SUMIF(B8:B29,50000)
=SUMIF(B9:B30,">50000")
=SUMIF (A10:A30,"LUCAS")

YEAR
=YEAR()
This function returns the year of particular cell
Ig. =YEAR(A3)
MONTH
=MONTH()
This function returns the MONTH of particular cell/date
=MONTH(A3)
Day
=DAY()
This function returns the DAY of particular cell
=DAY (A3)

=TODAY()
This function fetches the current date (of the system)
=NOW
This function returns current date and time
=WEEKDAY()
This returns the weekday, in number

To find out weekday(in words), using IF….

=IF(A6=1,"Sunday",IF(A6=2,"Monday",IF(A6=3,"Tuesday",IF(A6=4,"Wednessday",IF(A6=5,"Thursday",I
F(A6=6,"Friday","Saturday"))))))

=IF(A6=1,"Sunday",IF(A6=2,"Monday",IF(A6=3,"Tuesday",IF(A6=4,"Wednessday",IF(A6=5,"Thursday",I
F(A6=6,"Friday",IF(A6=7,"Saturday","")))))))

Logical Function
They are used to make a decision based upon a value within the spreadsheet/worksheet. It evaluates
the value and makes decision
One value is, if the condition is true, otherwise a different value is returned if the condition is false.
AND() - This returns value true if all the arguments are true and returns false, if one or more argument
is false.
NOT() – This reverses the value of its argument.
OR() – This returns true, if any argument is true and returns false only when all arguments are false.