SECTION– B
SPREADSHEET
PROGRAM 1:
Eight salesmen sell three products for a week. Using a spreadsheet create a sales report.
The report should include the name of the salesman, Amount of sales for each productand the
salesman’s total sales in the format given below.
Sales for the Month
Total Amt. for Total Amt. for Total Amt. for
Name Total sales
product1 product2 product3
a) Type in all text and numbers in the spreadsheet.
b) Format all numbers as a currency.
c) Center the spreadsheet headings across the spreadsheet.
d) Format all text.
e) Create formulas to display a total for each sales rep.
f) Create formulas to display a total for each product.
g) Create a formula to calculate the total sales for all sales rep's for the month.
Solution: Click on Start Programs Microsoft Office Microsoft Office Excel 2007.
This will open a new excel workbook displaying a blank Excel worksheet.
a) To Type in all text and numbers in the spreadsheet.
Now enter data of eight salesmen in the worksheet.
b) To Format all numbers as a currency.
i. Select the range B3:D10
ii. Home cells Format Format Cells or Right click on the range, select Format cells
from the short-cut menu, a Format cells window appears on the screen.
iii. Select the Number tab and choose proper Currency from the category list box.
iv. Click on OK.
c) To Center the spreadsheet headings across the spreadsheet.
Select the range A1:E1 then choose Home Alignment Merge & Center.
d) To Format all text.
i. Select the range.
ii. Home cells Format Format Cells or Right click on the range, select Format cells
from the short-cut menu, a Format cells window appears on the screen.
iii. Select the Alignment tab and choose Center from the Horizontal list box. Select Center
from the vertical list box.
iv. Click on OK.
e) Create formulas to display a total for each sales rep.
i. Enter the formula into cell E3.
= B3 + C3+ D3 or = SUM (B3:D3)
ii. Copy the formula (=B3 + C3+ D3) to cells from E4 to E10.
f) Create formulas to display a total for each product.
i. Enter the formula into cell B12.
= SUM (B3:B10)
ii. Copy the formula (=B3 + C3+ D3) to cells from C10 to D10.
g) Create a formula to calculate the total sales for all sales rep's for the month.
i. Enter the formula into cell C13.
= SUM (B3:D10)
OUTPUT:
35 | P a g e
PROGRAM 2:
Enter the following details for 10 employees Employee Code, Employee name, Basic
salary, DA, HRA, Loans, Total salary and Tax.
Salary for the Month
Employee Employee Basic Total Annual
DA HRA Loan Tax
Code Name Salary Salary Salary
a) Type the Employee Code, Employee Name, Basic Salary and Loan amount data for 10
employees in the spreadsheet.
b) Format all numbers as a currency.
c) Center the spreadsheet headings across the spreadsheet.
d) Format all text.
e) Create a formula to compute DA as 50% of the Basic salary and copy this to all the cells.
f) Create a formula to compute HRA as 12% of the Basic salary and copy this to all the cells.
g) Create a formula to compute Total salary and copy this to all the cells.
h) If Total salary is greater than 500000, compute Tax as 20% of Total salary otherwise 10% of
the Total salary using a formula.
Solution: Click on Start Programs Microsoft Office Microsoft Office Excel 2007.
This will open a new excel workbook displaying a blank Excel worksheet.
a) To Type the Employee Code, Employee Name, Basic Salary and Loan amount data
for 10 employees in the spreadsheet.
Now enter 10 employees’ data in the worksheet.
b) To Format all numbers as a currency.
i. Select the range C3:112
ii. Home cells Format Format Cells or Right click on the range, select Format cells
from the short-cut menu, a Format cells window appears on the screen.
iii. Select the Number tab and choose proper Currency from the category list box.
iv. Click on OK.
c) To Center the spreadsheet headings across the spreadsheet.
Select the range A1:I1 then choose Home Alignment Merge & Center.
d) To Format all text.
i. Select the range.
ii. Home cells Format Format Cells or Right click on the range, select Format cells
from the short-cut menu, a Format cells window appears on the screen.
36 | P a g e
iii. Select the Alignment tab and choose Center from the Horizontal list box. Select Center
from the vertical list box.
iv. Click on OK.
e) Create a formula to compute DA as 50% of the Basic salary and copy this to all the cells.
i. Enter the formula into cell D3.
= C3 * 50%
ii. Copy the formula (== C3 * 50%) to cells from D4 to D12.
f) Create a formula to compute HRA as 12% of the Basic salary and copy this to all the
cells.
i. Enter the formula into cell E3.
= C3 * 12%
ii. Copy the formula (= C3 * 12%) to cells from E4 TO E12.
g) Create a formula to compute Total salary and copy this to all the cells.
i. Enter the formula into cell G3.
= C3 + D3 + E3 – F3
ii. Copy the formula (= C3 + D3 + E3 – F3) to cells from G4 to G12.
i) If Total salary is greater than 500000, compute Tax as 20% of Total salary otherwise
10% of the Total salary using a formula.
i. Enter the formula into cell H3.
= G3 * 12
ii. Copy the formula (= G3 * 12) to cells from H4 to H12.
iii. Enter the formula into cell I3.
iv. =IF (H3>=500000, H3*20%, H3*10%)
v. Copy the formula (=IF (H3>=500000, H3*20%, H3*10%)) to cells from I4 to I12.
37 | P a g e
PROGRAM 3:
Enter the following details for 10 Students Register Number, Name, Subject1 Marks,
Subject2 Marks, Subject3 Marks, Subject4 Marks, Total Marks and Percentage.
Test Marks data of a Class
Register Subject1 Subject2 Subject3 Subject4 Total
Name Percentage
Number Marks Marks Marks Marks Marks
a) Type the Register Number, Name and marks of four subjects for 10students in the spreadsheet.
b) Format all text and numeric data appropriately.
c) Center the spreadsheet headings across the spreadsheet.
d) Create a formula to compute the Total marks and copy this to all the cells.
e) Create a formula to compute Percentage and copy this to all the cells.
f) Create a formula to compute the highest and lowest score using a library function.
g) Draw a bar graph for Register Number against total marks.
h) Draw Pie chart for one student showing his marks in different subject from total score.
Solution: Click on Start Programs Microsoft Office Microsoft Office Excel 2007.
This will open a new excel workbook displaying a blank Excel worksheet.
a) To Type the Register Number, Name, Marks of four subjects for 10 students in the
spreadsheet.
Now enter 10 students’ data in the worksheet.
b) To Format all text and numeric data appropriately as a currency.
i. Select the range B3:J12
ii. Home cells Format Format Cells or Right click on the range, select Format cells
from the short-cut menu, a Format cells window appears on the screen.
iii. Select the Alignment tab and choose proper alignment from the category list box.
iv. Click on OK.
c) To Center the spreadsheet headings across the spreadsheet.
Select the range A1:j1 then choose Home Alignment Merge & Center.
d) Create a formula to compute the Total marks and copy this to all the cells.
i. Enter the formula into cell G3.
= SUM (C3:F3)
ii. Copy the formula (=SUM (C3:F3)) to cells from G4 to G12.
38 | P a g e
e) Create a formula to compute Percentage and copy this to all the cells.
i. Enter the formula into cell H3.
= AVERAGE (C3:F3)
ii. Copy the formula (= AVERAGE (C3:F3)) to cells from H4 to H12.
f) Create a formula to compute the highest and lowest score using a library function.
i. Enter the formula into cell I3.
= MAX (C3:F3)
ii. Copy the formula (= MAX (C3:F3)) to cells from I4 TO I12.
iii. Enter the formula into cell J3.
= MIN (C3:F3)
iv. Copy the formula (= MIN (C3:F3)) to cells from J4 TO J12.
g) To draw a bar graph for Register Number against total marks.
i. Select the range of cells containing the data that you want to display in a chart. G3:G12
ii. Select the chart type form the Insert Charts Bar.
iii. Adding Chart Title: Layout Labels Chart Title.
iv. Adding Axes Title: Layout Labels Axes Title.
a. X-Axis Title: Register Number
b. Y-Axis Title: Total Marks
v. Change Legend Series Name:
a. To add series name, choose Design Select Data. Select the series name and click
the edit button. In Edit Series Dialog box, type the series name as Total Marks.
b. To add register numbers to the chart choose Design Select Data. To Select Axis
label range, click on the cell A1 and drag the cell pointer to A12.
39 | P a g e
Total Marks
110
109
108
Register Number
107
106
105
104 Total Marks
103
102
101
100 200 300 400 500
Total Marks
j) To Draw Pie chart for one student showing his marks in different subject from total
score.
i. Select the range of cells containing the data that you want to display in a chart. C3:E3
ii. Select the chart type form the Insert Charts Pie.
iii. Select the appropriate chart title and other chart components.
Shruthi
91 89
Subject2 Marks
Subject3 Marks
Subject4 Marks
88 98
40 | P a g e
PROGRAM 4:
A housewife maintains the budget expenditure in a spreadsheet under the headings
Income and Expenses. Income includes husband’s and Wife’s income separately under
different headings. Expenses include Rent, Bills, Household expenses and medical expenses.
Budget for the Month
Income Expenses Total
Savings
Husband Wife Rent Bills Household Medical Expenditure
a) Type the Income and Expenses data for the entire month in the spreadsheet.
b) Format all numbers as currency.
c) Center the spreadsheet headings across the spreadsheet.
d) Create a formula to compute the Total expenditure and copy this to all the cells.
e) Create a formula to compute the savings and copy this to all the cells.
f) Draw a bar graph to show expenditure under each heading.
g) Draw Pie chart to show the distribution of salary.
Solution: Click on Start Programs Microsoft Office Microsoft Office Excel 2007.
This will open a new excel workbook displaying a blank Excel worksheet.
a) To Type the Day Number, Husband and wife salaries, expenses for 7 days in the
spreadsheet.
Now enter 7 days income the expenses data in the worksheet.
b) To Format all numbers as a currency.
i. Select the range B4:111
ii. Home cells Format Format Cells or Right click on the range, select Format cells
from the short-cut menu, a Format cells window appears on the screen.
iii. Select the Number tab and choose proper Currency from the category list box.
iv. Click on OK.
c) To Center the spreadsheet headings across the spreadsheet.
Select the range A1:I1 then choose Home Alignment Merge & Center.
d) Create a formula to compute the Total expenditure and copy this to all the cells.
i. Enter the formula into cell H3.
=SUM (D4:G4)
This formula gives you the expenses of the Day 1.
41 | P a g e
ii. Copy the formula (=SUM (D4:G4)) to cells from H5 to H10.
iii. Enter the formula into cell B11
=SUM (B4:B10)
iv. Copy the formula (=SUM (B4:B10)) to cells from C11 to H11.
e) Create a formula to compute the savings and copy this to all the cells.
i. Enter the formula into cell I11.
= B11 +C11-H11
ii. Copy the formula (= C3 * 12%) to cells from E4 TO E12.
f) Create a formula to compute Total salary and copy this to all the cells.
i. Enter the formula into cell G3.
= C3 + D3 + E3 – F3
g) To draw a column graph to show expenditure under each heading.
i. Select the range of cells containing the data that you want to display in a chart. D4:G10
ii. Select the chart type form the Insert Charts Column.
Rs. 7,000.00
Rs. 6,000.00
Rs. 5,000.00
Rs. 4,000.00 Rent
Bills
Rs. 3,000.00
Household
Rs. 2,000.00 Medical
Rs. 1,000.00
Rs. 0.00
Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7
42 | P a g e
h) Draw Pie chart to show the distribution of salary.
i. Select the range of cells containing the data that you want to display in a chart.
Range 1 is D11:G11 and to select Range 2 hold the Ctrl Key and click on the cell I11
ii. Select the chart type form the Insert Charts Pie.
iii. Select the appropriate chart title and other chart components.
DISTRIBUTION OF MONTHLY SALARY
Rent Bills Household Medical Savings
Bills
10%
Rent
10% Household
7%
Savings
70% Medical
3%
43 | P a g e
44 | P a g e