Microsoft Excel Assignment, M Com.

III, 2024-25
By Dr. Mohammad Shahid
Department of Commerce
Aligarh Muslim University, Aligarh

Note: (i) Attempt any Eight.

(ii) Kindly do the descriptive portion in handwritten. Hands on the practical portion of this
assignment on excel sheets attached for practice.

Q. 1 Write short notes on

(i). Freezing panes
(ii). Fill handle
(iii). Naming range
(iv). Wrap Text
(v). Name Box
Q. 2 What do you mean Excel function? Explain any ten excel functions (accounting, financial, statistical etc.)
with their name, syntax use and examples.
Q. 3 Difference between COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel with suitable
examples.
Q. 4 Difference between SUM, SUMIF and SUMIFS in Ms-Excel with suitable examples.
Q. 5 What do you mean by financial function? Discuss the applications of PV, FV, NPV, DB, CUMIPMT,
etc, with suitable examples.
Q. 6 What is the "What If analysis" in Excel formulas? Explain goals seek or scenario manager using some
assumed data/ conditions.
Q. 7 What filter will you use, if you want more than two conditions or if you want to analyze the list using
database function? Explain the same with assumed data table with some column.
Q. 8 What is sorting in Ms-Excel? Explain the procedure to sort a given multi-column data table according to
a specific column.
Q. 9 What are charts in MS-Excel? Explain any seven excel charts with imaginary data.
Q. 10 What is mail merging? Explain the steps for creating letters to a group of recipients using mail merge
option in MS-word.

Practical Exercises
Note: Attempt any eight

PRACTICAL EXERCISE #1
Using the data given, get the sum of all the figures within the range.
A B C D E F G
1 Mon Tue Wed Thur Fri TOTAL
2 Breakfast 3,560 3,186 2,952 3,395 3,436
3 Lunch 20,16 21,416 19,912 19,681 18,628
3
4 Bar 9,873 12,172 12,642 12,711 18,846
5 Snacks 2,405 3,544 2,694 3,120 3,712
6 TOTALS

PRACTICAL EXERCISE #2
Using the information given in the table below, format the sheet as per instructions and calculate the followings
by using excel functions namely SUM, AVERAGE, COUNTIF, SUMIF, MAX, MIN etc.
A B C D E
1 Date Name Rate Hours Worked Amount
2 26- Kennedy 70 5
Nov
3 26- Kennedy 100 5
Nov
6 26- Mary 100 5
Nov
7 26- Lewis 100 4
Nov
8 30- Judy 100 3
Nov
9 30- Kennedy 70 6
Nov
10 30- Lewis 100 5
Nov
11 30- Kennedy 70 4
Nov
12 30- Judy 100 5
Nov
13 30- Lewis 100 5
Nov
14 02-Dec Judy 70 4
15 Total Amount
(i). Format the worksheet as follows:
Make all the Totals bold, values two decimal places, comma, center the title across columns A-E and make
it size 16, bold and Italic.
(ii). Put a double border round the whole table and a single line border inside the table.
(i). How does one delete an entire row or column and all its cells?
(iii). Insert a column before A and named it S. No. , enter the values from 1 -11 by using fill handle (automatic
filling the numbers) starting with cell A2 (newly).
(iv). Insert a row at top and write “Extra work payment analysis” merge all cells from A-F and center the title
written.
(v). Calculate the amount paid for each entry across the Amount column.
(vi). Calculate the total amount payable by the company to the employees.
(vii). Find total number of employees in the company.
(viii). How many total hours worked are in the table for Judy.
(ix). Find the amount received by each employee from the company.
(x). Find the average of amount paid by company to employees.
(xi). Find out the maximum amount paid to the particular employee.
(xii). Find out the minimum amount paid to the particular employee.
(xiii). Save the worksheet as Payment Analysis.

PRACTICAL EXERCISE #5

Use the worksheet given to answer the questions that follow:

Expenses for the Month of

January vs. Budget

Budget Savings
Salaries and Wages 156675.00
Rent 4300.00
Electricity 1000.00
Telephone 200.00
Advertisements 20000.00
Freight and clearing 15650.00
Security 3800.00

Questions

(i). Insert a new column between Budget and Savings column.

(ii). Enter the title ‘Actual’ in cell C3.
(iii). Enter the following figures in the new column.

Actual
Salaries and Wages 145200
Rent 4300
Electricity 1207
Telephone 142
Advertisements 18550
Freight and clearing 13400
Security 3800

(iv). Calculate the savings in cells D4:D10.

(v). Format the sheet title to Arial Black, size 14, and Bold.
(vi). Save the file as Audit 1.
(vii). Format the range B4:D10 to two decimal places.
(viii). Adjust column C such that all the values are displayed.
(ix). Add the title Savings % in cell E3 and calculate the savings as a percentage of the budget.
(x). Format the range E4:E10 as a percentage.
(xi). Enter the row title Total in cell A12 and obtain totals for Budget, Actual, and Savings columns.
(xii). Copy the formula in E10 to E12.
(xiii). Format the new heading to match the existing headings and Align the heading to Right.
(xiv). Save and Close the worksheet.

PRACTICAL EXERCISE #6

From the given table below, compute the following statistical parameters using excel functions.
 S. No. 1 2 3 4 5 6 7
Marks 76 56 78 90 34 67 63

(i) Mean, median, mode for the individual series data set.
(ii) Mean Deviation, standard deviation, Variance

PRACTICAL EXERCISE #7

From the continuous data given in the table below, compute the following statistical parameters using excel
functions in excel spread sheet.
Class 0-10 10-20 20-30 30-40 40-50 50-60 60-70
Interval
7 12 32 54 43 23 10

(i) Mean, median, mode for the individual series data set.
(ii) Mean Deviation, standard deviation, Variance

PRACTICAL EXERCISE #8

The following is a simple payroll:

A B C D E F G H I
1 Name Hours Hourly Basic Gross Tax NSSF Allowances Net
Worked Rate Pay Pay Deductions Contributions Pay
2 John 8 200
3 Peter 12 450
4 Sam 22 300
5 Njogu 30 286
6 Mary 16 220
7 Sally 45 468
8 Jane 15 150
9 Tina 3 280
10
11

Required:

Write formulae using cell names for the following expressions. State where the formula is placed.

(i). Basic Pay = Hours Worked * Hourly Rate.

(ii). Allowances are allocated at 10% of the Basic Pay.
(iii). Gross Pay = Basic Pay + Allowances.
(iv). Tax Deduction is calculated at 20% of the Gross Pay.
(v). Net Pay = Gross Pay – Tax Deductions.

PRACTICAL EXERCISE #9
Assume you are the Accountant of Stationery Supplies Ltd. Below is the current payroll in the workbook OLD
PAYROLL.

OLD PAYROLL.

A B C D E F
 1 Stationery Supplies Ltd.
2
3 Name Basic Allowances Gross Deductions Net Salary
Pay Salary
4 Lewis 15,791 3,137 18928 1,256 17,672
5 Francis 15,537 3,061 18598 776 17,822
6 Edwin 15,506 3,051 18557 999 17,558
7 Bernard 15,417 3,025 18442 1,099 17,343
8 George 15,008 2,902 17910 718 17,192
9 Albert 14,969 2,890 17859 846 17,013
10 Edward 14,651 2,795 17446 760 16,686
11 Cornell 14,618 2,785 17403 663 16,740
12 John 14,553 2,765 17318 558 16,760
13 Carl 14,508 2,752 17260 706 16,554
14
15 Totals 150,558 29,163 179721 8381 171340

The following salary review is given to you in the workbook INCREMENT.

INCREMENT
A B C
1 Name Current Pay % increase
2 Lewis 15,791 19%
3 Francis 15,537 19%
4 Edwin 15,506 22%
5 Bernard 15,417 18%
6 George 15,008 21%
7 Albert 14,969 17%
8 Edward 14,651 15%
9 Cornell 14,618 25%
1 John 14,553 19%
0
1 Carl 14,508 20%
1
1
2
1 Allowances
3
1 20%
4

Required:

Using formulas, you are required to update the payroll with the changes in a blank worksheet. This new blank
sheet is in the workbook NEW PAYROLL.

PRACTICAL EXERCISE #10

From the data given in the table below, do the following as per instructions given below.
A B C D E F
1 ABC Company Sales Performance Report
2
3 Salesman Qtr1 Qtr2 Qtr3 Qtr4 Total
4 Albert 148 156 171 140 615
5 Carl 122 131 153 118 524
6 Cornell 211 243 246 250 950
 7 Edwin 129 150 92 218 589
8 Francis 311 270 247 322 1,150

(i). Create a Pie Chart to show the distribution of the total amount amongst the various salesmen.
(ii). You are asked to compare Qtr1 and Qtr4 sales for all salespersons in the above table using a chart:
a. What range of cells do you need to select and how would you select it?
b. What type of graph would you use?
(iii). Create a Line chart to show the distribution of various quarters.
(iv). How can you edit a chart once it is created, lets say, to change the series from columns to rows, to change
the legend, etc?
(v). Select layout having data of each quarter value is embedded as part of chart for better presentation.
(vi). Change the Line chart into Column Chart using change chart type option.
(vii). Show legends at top, not overlapping with the chart area. Edit legends making all uppercase.
(viii). Delete Qtrr1form the chart by using Format the chart option. And add again the same in the
chart.
(ix). Save charts by name as Pie and Column into a folder at desktop with name Graph. And import the same
on word file.
PRACTICAL EXERCISE #11
From the data given in the table, at first enter the data in a new Excel spreadsheet as it is and do the following as
per instruction using Filtering & Sorting options.
(i). Sort the table by class (A-Z), then Star time & cost in ascending order.
(ii).Use advance filtering to show the unique entries in Day column.
(iii). Debbie has asked for Monday evening off. Use filtering to show rows where:
(a). Instructor is Debbie
(b). Day is Monday
(c). Time is after 17:00
(d). Cost is greater than or equal to 5.
(iv). The sports hall floor is being deep cleaned on Wednesday evening at 8pm. Use filtering to find the
classes you need to cancel - that is, where:
(e). The venue is the sports hall; and
(f). The day is Wednesday; and
(g). The time is greater than or equal to 20:00.

Max Start Advance Cost

Class Level Venue
No
Instructor Day
Time Bookings? (Rs)

Advance Wednesda
Pilates d Studio 25 India y 8:00 Y 4.00
Advance Wednesda
Bikram Yoga d Studio 25 Ashley y 10:00 N 4.00
Ashtanga Advance
Yoga d Studio 35 Ashley Tuesday 11:00 Y 4.00
Advance
Squash d Squash Courts 14 Philip Saturday 11:00 N 4.00
Advance
Cross training d Gym 20 Jeff Monday 19:00 Y 4.00
Advance Wednesda
Spin d Studio 12 Ash y 19:00 Y 5.00
Advance Wednesda
Fencing d Sports Hall 25 Miranda y 21:00 Y 7.00
Advance
Gymnastics d Sports Hall 32 Debbie Monday 20:00 Y 7.50
Advance
Jujitsu d Studio 30 Jai Thursday 21:00 N 4.00
Karate Advance Sports Hall 30 Tom Friday 21:00 N 4.00
 d
Yogalates All levels Studio 25 India Thursday 10:00 N 3.50
Trampolining All levels Sports Hall 30 Dom Saturday 11:00 Y 3.50
Stretch All levels Studio 25 Miranda Tuesday 14:00 Y 3.50
Rollerdance All levels Sports Hall 26 Debbie Saturday 15:00 N 5.00
Wednesda
Basketball All levels Sports Hall 30 Paul y 20:00 Y 5.00
Wednesda
Bikram Yoga Beginner Studio 25 Ashley y 9:00 Y 3.50
Pilates Beginner Studio 25 India Monday 9:00 N 3.50
Ashtanga
Yoga Beginner Studio 35 Ashley Tuesday 10:00 N 3.50
Hooptone Beginner Sports Hall 20 Fiona Monday 10:00 Y 3.50
Iyengar Yoga Beginner Studio 30 Debbie Monday 19:00 N 3.50

Q. 5 What are charts in MS-Excel? Explain any five excel charts with their significance. From
the data given in the table below, do the following as per instructions given below.

A B C D E F
1 ABC Company Sales Performance Report
2
3 Salesman Qtr1 Qtr2 Qtr3 Qtr4 Total
4 Albert 148 156 171 140 615
5 Carl 122 131 153 118 524
6 Cornell 211 243 246 250 950
7 Edwin 129 150 92 218 589
8 Francis 311 270 247 322 1,150

(i). Create a Pie Chart to show the distribution of the total amount amongst the various
salesmen.
(ii). Create a Line chart to show the distribution of various quarters.
(iii). How can you edit a chart once it is created, lets say, to change the series
from columns to rows, to change the legend, etc?
(iv). Select layout having data of each quarter value is embedded as part of chart for
better presentation.
(v). Change the Line chart into Column Chart using change chart type option.
(vi). Show legends at top, not overlapping with the chart area. Edit legends making
all uppercase.
(vii). Delete Qtrr1form the chart by using Format the chart option. And add again
the same in the chart.
Q. 7 What is data validation and it’s purpose of using in spreadsheet? A table is given with its
 column headers, you have to explain the data validation procedures in excel spread sheet as
given follows to maintain integrity of entered data:
Name Gender ID Mobile Number Date of joining

(i). Name column should only accept text.

(ii). Apply drop down list to select Gender for their entries.
(iii). ID entered into the respective column should be unique.
(iv). Length of Mobile number 10 is only accepted.
(v). Date of joining should not be weekdays.