Practical Assignment

SEC Paper:-Data Analysis Using Excel

PaperCode:-SEC-51P-103

BCA/BBA/[Link]/B.A/[Link]./BVA

Prepared by:-
Virendra Tank Sir, Assistant Professor
Department of Computer Science
Shri Mahaveer College

Guidelines
 Handwritten(UseBlueand BlackPen)
 UseA4sizefilePages(OneSideLine)
 MentionPageNumberatcentrebottomofthepage.
 UsePunchingFile.
 PrepareCoverPageofthe File.

Question1: Create a Student Data Sheet in MS Excel with the following fields:

Student ID Name Gender Course Year City Phone No Email

Perform the following tasks:

1. Enter data of 10 students.

2. Apply Table formatting.

Solution:Step-by-StepSolution

Step 1: Enter Data

1. Open MS Excel.
2. Write the headings in Row 1:
o A1 → Student ID
o B1 → Name
o C1 → Gender
o D1 → Course
 oE1 → Year
oF1 → City
oG1 → Phone No
oH1 → Email
3. Enter the 10 student records below the headings.

Step 2: Convert Data into Table

1. Select the entire data range (A1:H11).

2. Go to Home Tab.
3. Click Format as Table.
4. Choose any table style.
5. Tick My table has headers.
6. Click OK.

Student ID Name Gender Course Year City Phone No Email

S101 Rahul Male BCA 1 Jaipur 9876543210 rahul@[Link]
S102 Neha Female BCA 2 Ajmer 9876543211 neha@[Link]
S103 Aman Male BBA 1 Kota 9876543212 aman@[Link]
S104 Riya Female BCA 3 Udaipur 9876543213 riya@[Link]
S105 Karan Male BCom 2 Alwar 9876543214 karan@[Link]
S106 Pooja Female BCA 1 Jaipur 9876543215 pooja@[Link]
S107 Mohit Male BBA 3 Ajmer 9876543216 mohit@[Link]
S108 Simran Female BCA 2 Kota 9876543217 simran@[Link]
S109 Arjun Male BCom 1 Jaipur 9876543218 arjun@[Link]
S110 Kavya Female BCA 3 Alwar 9876543219 kavya@[Link]

Question2:CreateaStudentMark sheetinMSExcel Include

columns for:
 StudentName
 Marksin5subjects
 TotalMarks
 Percentage
 Grade(Aif≥75%, Bif≥60%, Cif≥45%,elseF)
 Result (Pass/Fail)

Solution:Step-by-StepSolution

Step1:OpenMSExcelandcreateaworksheetnamed“BBAMarksheet”. Create
Headings:-In Row 1, type the following headings:

A1 B1 C1 D1 E1 F1 G1 H1 I1
StudentN Subject 1 Subject 2 Subject 3 Subject 4 Subject 5 Total % Grade
ame
 Step2—EnterData
ExampleData(Rows2–5):

Student
Subject 1 Subject 2 Subject 3 Subject 4 Subject 5
Name
Anjali 78 85 82 80 88
Raj 65 70 60 68 72
Priya 55 58 60 50 62
Arjun 90 88 92 85 91

Step3—Apply Formulas

Column Formula(Example:Row2)
TotalMarks (G2) =B2+C2+D2+E2+F2
Percentage (H2) =G2/500*100

Grade(I2) =IF(H2>=75,"A",IF(H2>=60,"B",IF(H2>=45,"C","F")))

Step4—CopyFormulas Down
Dragdownthe formulasinG2,H2,andI2forallstudents.

Step5—FormatSheet
 Boldtheheaderrow
 UseBordersforneatness
 Usetwodecimalplacesforpercentage
 Optionallycolor-codegrades(ConditionalFormatting)

FinalOutput

Student
Subject 1 Subject 2 Subject 3 Subject 4 Subject 5 Total % Grade
Name
Anjali 78 85 82 80 88 413 82.6 A
Raj 65 70 60 68 72 335 67.0 B
Priya 55 58 60 50 62 285 57.0 C
Arjun 90 88 92 85 91 446 89.2 A
Question3:[Link] below

Answerthefollowingquestion
1. SettheTextalignment,Columnswidthandhighappropriately.
2. UseAutoFilltoputtheSeriesNumbersintocellsA5:A7.
3. FormatcellsC3:G7,C8:E11,C13:E13toincludedollarsignwithtwodecimal places.
4. FindtheAverageSalesandMaximumSalesforeachCity.
5. FindtheTotalSalesforeachMonth.
6. CalculatetheProfitforeachmonth,whereprofit=TotalSales–Cost
7. Calculatethe10%Bonus,whichis10%oftheProfit.
8. FindtheTotalSalesforeachMonth;onlyforsalesgreaterthan30,000.
9. FindtheNoofSalesforeachMonth;onlyforsalesgreaterthan30,000.
10. CreatethefollowingCharts:

Solution:Step-by-StepSolution

Step1:SetTextAlignment,ColumnWidth, andRowHeight

1. Selectallcells(Ctrl+A).
 2. OntheHome tab:
o ClickCentertocenter-aligntexthorizontally.
o ClickMiddleAligntocentervertically.
3. Adjustcolumnwidths:
o Double-clicktheborderbetweeneachcolumnheader(e.g.,between B and C) for
AutoFit.
o Ormanuallyset:
 ColumnB(City):width≈15
 ColumnsC–E(Months):width≈12
 ColumnsF–G(Average&Maximum):width≈10
4. Adjustrowheight:
o Selectallrows,right-click→RowHeight→setto20forneat formatting.
Step2:UseAutoFilltoFillSeriesNumbers(A5:A7)

1. InA3:A4,youalreadyhaveC001andC002.
2. Selectbothcells(A3:A4).
3. Dragthe fillhandle(bottom-rightcornerofA4)downtoA7.
4. ExcelwillautomaticallyfillC003,C004,C005.

Step3:FormatCellstoIncludeDollarSignandTwoDecimalPlaces

1. Selecttheseranges:
oC3:G7(salesdata)
oC8:E11(total,cost,profit,bonus)
oC13:E13(totalsforsales>30,000)
2. GotoHome→Numbergroup→Accountingformat($).
3. Ensure2decimalplacesaredisplayed.

Step4:FindtheAverageandMaximumSalesforEachCity
Foreachrow(NewYork→Munich):

 Average(ColumnF):
=AVERAGE(C3:E3)
 Maximum(ColumnG):
=MAX(C3:E3)

Copytheformulasdownforallcities(F3:G7).

Step5:FindTotalSalesforEachMonth
InRow8:

 January(C8):=SUM(C3:C7)
 February(D8):=SUM(D3:D7)
 March(E8):=SUM(E3:E7)

Step6:CalculateProfitforEachMonth
Profit=TotalSales–Cost In
Row 10:
  January(C10):=C8-C9
 February(D10):=D8-D9
 March(E10):=E8-E9

Step7:Calculate10%Bonus
Bonus=10%of Profit
InRow11:

 January(C11):=C10*10%
 February(D11):=D10*10%
 March(E11):=E10*10%

Step8:FindTotalSales>30,000forEachMonth
UsetheSUMIFfunction. In
Row 13:

 January(C13):=SUMIF(C3:C7,">30000",C3:C7)
 February(D13):=SUMIF(D3:D7,">30000",D3:D7)
 March(E13):=SUMIF(E3:E7,">30000",E3:E7)

Step9:FindNumberofSales> 30,000
InRow14:

 January(C14):=COUNTIF(C3:C7,">30000")
 February(D14):=COUNTIF(D3:D7,">30000")
 March(E14):=COUNTIF(E3:E7,">30000")

Final Output:
Question5:CreateaAnnualReportinMSExcelgivenbelow
AnswerthefollowingQuestion

1. Openanewworkbookandcreatetheabove worksheet.
2. Makesurethatyourworksheetlookslikethepicture(Alignment,Shedding, Borders,
Wrap text, Orientation …).
3. FindtheentirecustomerIDs.
4. FormatColumE&DtoCurrencywithdollarsignandtwodecimalplaces.
5. FindtheTotalAnnualPurchasesforeachCity.
6. FindtheAverageAnnualPurchasesforeachEducation.
7. Findthetotalnumberofcustomersfromeachgender.
8. Findthetotalannualsalaryforeachgenderineachcity.
CreatethefollowingChart:
Solution:Step-by-StepSolution

1. Createworkbook→A1:USAAnnualPurchasesReport2011→selectA1:F1
→Merge&Center,bold,largerfont.
2. Row2headers(A2:F2):CustomerID,Gender,City,Education,Annual Purchases,
Annual Salary. Enter data in A3:F12.
3. FillCustomer IDs:putC11andC12inA3:A4 →selectboth→dragfill handle down
to A12.
4. Formatmoney:selectE3:E12andF3:F12→Home→Number→Currency ($), 2
decimals.
5. Tableformatting:selectA2:F12→Borders→ [Link] row.
Adjust column widths, wrap text, center headers.
6. Citytotalpurchases(e.g.,B16forNewYork):
=SUMIF(C$3:C$12,"NewYork",E$3:E$12)—copy/modifyforChicago, Seattle.
Format as Currency.
7. Educationaveragepurchases(e.g.,B21forUniversity):
=AVERAGEIF(D$3:D$12,"University",E$3:E$12)—repeatforHigh School,
None.
8. Gendercounts:
=COUNTIF(B$3:B$12,"M")and=COUNTIF(B$3:B$12,"F").
9. City×Gendertotalsalary(exampleNewYorkMale):
=SUMIFS(F$3:F$12,C$3:C$12,"NewYork",B$3:B$12,"M")—repeatfor each
city/gender.
10. Place summary boxes (City totals, Education averages, Gender counts,
City×Gendersalaries)andformatwithshadingandborderstomatchthe picture.
11. Createchart:selectCitytotalsrange→Insert→ColumnChart(Clustered)
→addtitleTotalAnnualPurchasesbyCityanddatalabels.
12. Finalcheck:changeanydatacell(EorF) —totals,averages,countsandchart should update
automatically.
Final Output:
Question6:Createamacrotoautomatethe generationofabasicsalesperformance report for a
business.

1. Calculatesthepercentageofsalestargetachieved foreachsalesperson.
2. Appliesconditionalformattingtohighlightsalesperformance.

Solution:Step-by-StepInstructions

Step 1: Set Up the Sales Data

1. OpenanewExcelworkbook.
2. Createatablewiththefollowingcolumns:
o Salesperson:Nameofthesalesperson.
o Region:Regioninwhichtheyoperate.
o SalesTarget:Thesalestargetfortheperiod.
o SalesAchieved:Theactualsalesachievedbythesalesperson.
o PercentageofTargetAchieved:Thepercentageofthetargetachieved by the
salesperson.

SampleData:

Salesperson Region Sales Sales PercentageofTarget

Target Achieved Achieved
John North 100,000 90,000
Alice South 120,000 110,000
Bob East 150,000 140,000
Charlie West 80,000 75,000
Dana North 90,000 85,000

Step2:EnabletheDeveloper Tab
Tocreatethemacro,youneedtheDeveloperTabvisible.

1. GotoFile>Options>CustomizeRibbon.
2. UnderMainTabs,checktheboxfor Developer.
3. [Link].

Step3:RecordtheMacrotoAutomateReport Generation Starting the

Macro Recording:

1. GototheDeveloperTabandclickonRecordMacro.
2. IntheRecordMacrodialogbox:
o MacroName:EnterSalesReportMacro.
o ShortcutKey(optional):Assignashortcutkeyifyouwant(e.g.,
Ctrl+Shift+R).
o StoreMacroIn:ChooseThisWorkbook.
o Description:[Link]"Macroto generate
sales performance report."
3. ClickOKtostartrecording.

Step4:PerformtheActionstoRecord

Action1:CalculatethePercentage ofTargetAchieved
1. InthePercentage ofTargetAchievedcolumn(ColumnE),selectthefirst row under
the header.
2. Enterthefollowingformulatocalculatethepercentage:
3.=D2/C2

ThisformuladividestheSalesAchieved(ColumnD)bytheSalesTarget
(ColumnC)togetthepercentage.

4. PressEnter.
5. Copytheformuladownthecolumntofilltherestoftherows.

Action2:ApplyConditionalFormatting

1. SelectthePercentage ofTargetAchievedcolumn(ColumnE)whereyou want to

apply conditional formatting.
2. GototheHomeTab>ConditionalFormatting>NewRule.
3. ChooseFormatcellsthatcontain:
o CellValue:greaterthanorequalto1(thisrepresents100%target achieved).
o Settheformattogreenfillwith darkgreentext(indicatingtarget achieved).
4. ClickOK.
5. Repeattheabovestepstoapplyanotherrule:
o CellValue:lessthan1(indicatingtargetnotachieved).
o Settheformattoredfillwithwhitetext(indicatingtargetmissed).

Action3:Auto-ResizeColumns
 1. Selecttheentiredataset(clickonthetop-leftcornerofthesheettoselect everything).
2. Double-clickanycolumnbordertoauto-resizethecolumnssothatthetext fits
properly.
Step5:StopRecordingtheMacro

Onceyou’vecompletedtheaboveactions,go backtotheDeveloperTabandclick
StopRecording.

Step6:RunningtheMacro

Toapplythemacrotoanynewdata:

1. Opentheworkbookwiththesalesdata.
2. GototheDeveloperTab>Macros.
3. Selectthemacroyourecorded(SalesReportMacro)andclickRun.
4. Themacrowill:
o Automaticallycalculatethepercentageofthetargetachieved.
o Applyconditionalformattingtohighlightsalesperformance.
o Generatethesalesperformancechart.
o Auto-resizethecolumns.

Final Output:

Question7: Create an Employee Attendance System in MS Excel using Formulas, Sorting, and
Filtering.
 Create a worksheet with the following columns: Employee ID, Employee Name, Department,
Designation, Basic Salary, Working Days, Days Present, Attendance Percentage, and Status.
 Enter data for 12 employees.
 Calculate Attendance Percentage = (Days Present / Working Days) × 100.
 Set Status condition: Attendance ≥ 80 = Present, Attendance < 80% = Defaulter.
 Format Basic Salary as Currency.
 Sort records by Attendance Percentage (Highest to Lowest).
 Apply filters to show only Present employees and employees from a specific department.
 Save the file as Employee_Attendance.xlsm.
Solution
1. Create Table and Enter Data
Employee Employee Department Designation Basic Working Days Attendance Status
ID Name Salary Days Present Percentage
101 Rahul HR Manager
102 Neha IT Developer
103 Aman Sales Executive
104 Pooja IT Analyst
105 Rohit HR Assistant
106 Simran Finance Accountant
2. Attendance Percentage Formula:-Formula used in Excel:
=(Days Present/Working Days)*100
3. Status Formula
=IF(H2>=75,"Present","Defaulter")
4. Format Salary
Select the Basic Salary column → Home Tab → Currency Format.
6. Sorting
Data Tab → Sort → Attendance Percentage → Largest to Smallest.
7. Filtering
Data Tab → Filter → Select 'Present' in Status column or choose a specific Department.
8. Save File
File → Save As → Excel Macro Enabled Workbook (*.xlsm)
File Name: Employee_Attendance.xlsm

Final Output:
Employee Employee Department Designation Basic Working Days Attendance Status
ID Name Salary Days Present Percentage
101 Rahul HR Manager 45000 26 24 92.31 Present
76.92
102 Neha IT Developer 50000 26 20 Defaulter
84.62
103 Aman Sales Executive 35000 26 22 Present
96.15
104 Pooja IT Analyst 48000 26 25 Present
69.23
105 Rohit HR Assistant 30000 26 18 Defaulter

Question8: Assignment on Importing, Cleaning and Formatting Data in MS Excel

Part A – Importing Data

Question: Import a Student Data CSV file into MS Excel.
Student_ID Name Course Marks City
101 Rahul BCA 78 Jaipur
102 Neha BCA 85 Ajmer
103 Aman BCA 67 Kota
104 Pooja BCA 92 Jaipur

Solution:-Steps to Import CSV File:

 Open MS Excel.
 Go to Data Tab.
  Click Get Data → From Text/CSV.
 Select the CSV file.
 Click Load to import the data.

Final Output:

Part B – Data Cleaning

Enter the following data in Excel and perform cleaning operations.
ID Name Course Marks City
201 Amit BCA 75 Jaipur
202 Riya BCA 82 Ajmer
203 Amit BCA 75 Jaipur
204 Karan BCA Kota
205 Simran BCA 90 Delhi

(i) Remove duplicate records using Data → Remove Duplicates.

Solution:-Steps

1. Select the entire table.

2. Go to Data Tab.
3. Click Remove Duplicates.
4. Select all columns.
5. Click OK.

Result After Removing Duplicate

ID Name Course Marks City
201 Amit BCA 75 Jaipur
202 Riya BCA 82 Ajmer
204 Karan BCA (Blank) Kota
205 Simran BCA 90 Delhi

(ii)Handle missing values in the Marks column by filling the average marks or entering 0.
Solution:-Steps

The Marks column has a missing value for Karan.

Method 1: Fill with Average Marks

  Average Marks Calculation:
 (75+82+90)÷3=82.33
 So we fill 82.33 in the blank cell.

 Updated Table
ID Name Course Marks City
201 Amit BCA 75 Jaipur
202 Riya BCA 82 Ajmer
204 Karan BCA 82.33 Kota
205 Simran BCA 90 Delhi

Method 2: Fill with 0

 Alternatively, we can replace the missing value with 0.

ID Name Course Marks City

201 Amit BCA 75 Jaipur
202 Riya BCA 82 Ajmer
204 Karan BCA 0 Kota
205 Simran BCA 90 Delhi

Part C – Text to Columns

Split the Full Name column into First Name and Last Name using Text to Columns with Space
delimiter.

In Microsoft Excel, Text to Columns is used to split data from one column into multiple columns.

Example Data
Full Name
Amit Sharma
Riya Verma
Karan Singh
Simran Kaur

Solution:-Steps to Split Full Name into First Name and Last Name

1. Select the Full Name column.

2. Go to the Data Tab.
3. Click Text to Columns.
4. Select Delimited and click Next.
5. Check the Space delimiter.
6. Click Next.
7. Choose the destination column where you want the split data.
8. Click Finish.

Result After Splitting

First Name Last Name
Amit Sharma
 First Name Last Name
Riya Verma
Karan Singh
Simran Kaur

Part D – Data Splitting

Split Email addresses into Username and Domain using Text to Columns with '@' delimiter.

Using Microsoft Excel, email addresses can be split into Username and Domain using the Text to
Columns feature.

Example Data
Email
amit123@[Link]
[Link]@[Link]
karan_singh@[Link]
simran@[Link]

Solution:-Steps to Split Email Address

1. Select the Email column.

2. Go to the Data tab.
3. Click Text to Columns.
4. Select Delimited and click Next.
5. In Delimiters, type @ in the Other box.
6. Click Next.
7. Choose the destination columns.
8. Click Finish.

Result After Splitting

Username Domain
amit123 [Link]
[Link] [Link]
karan_singh [Link]
simran [Link]

Part E – Data Validation

Apply validation so that Marks must be between 0 and 100 using Data → Data Validation.
Solution:-Steps

1. Select the Marks column (cells where marks will be entered).

2. Go to the Data tab.
3. Click Data Validation.
4. In the Settings tab:
 o Allow: Whole Number
o Data: Between
o Minimum: 0
o Maximum: 100
5. Click OK.

Result:-Now Excel will only allow numbers between 0 and 100 in the Marks column.

Example:

Name Marks
Amit 75
Riya 82
Karan ❌ 120 (Not Allowed)
Simran 90

If a user enters a value less than 0 or greater than 100, Excel will show an error message.

Part F – Conditional Formatting

Apply formatting rules:
Marks ≥ 80 → Green
Marks 50–79 → Yellow
Marks < 50 → Red

Solution:-Steps

1. Select the Marks Column:-Select all the cells in the Marks column.

2. Apply Rule for Marks ≥ 80 (Green)

 Go to Home Tab
 Click Conditional Formatting
 Select Highlight Cells Rules → Greater Than
 Enter 80
 Choose Green Fill
 Click OK

3. Apply Rule for Marks 50–79 (Yellow)

 Select the Marks column again

 Go to Conditional Formatting
 Click Highlight Cells Rules → Between
 Enter 50 and 79
 Select Yellow Fill
 Click OK

4. Apply Rule for Marks < 50 (Red)

  Select the Marks column
 Go to Conditional Formatting
 Click Highlight Cells Rules → Less Than
 Enter 50
 Select Red Fill
 Click OK

Final Output:
Name Marks Color
Amit 85 🟩 Green
Riya 72 🟩 Yellow
Karan 45 🟩 Red

Question9: To create a student database and design a marksheet in Microsoft Excel using formulas
such as VLOOKUP, IFERROR, Total, and Percentage.

Create an Excel Workbook containing two worksheets.

Sheet 1 – Student Database

Create a student database table with the following fields:

Prog. Prog. Web Web

Roll Candidate Father’s DBMS OMT OMT
in C in C Application Application Total % Grade
No Name Name (Th) (Th) (Pr)
(Th) (Pr) (Th) (Pr)
Instructions

1. Enter data of at least 10 students.

2. Calculate Total Marks.
3. Calculate Percentage.
4. Assign Grade using IF formula.

Sheet 2 – Marksheet Layout:-Design a formatted marksheet like a report card.

The marksheet should contain:

 Roll Number (input cell)

 Candidate Name
 Father’s Name
 Subject Marks
 Total Marks
 Percentage
 Grade

When the Roll Number is entered, the following details should automatically appear:

 Candidate Name
 Father’s Name
 Subject Marks
  Total
 Percentage
 Grade

Use VLOOKUP function to fetch the details from Sheet1.

Requirements

1. Use VLOOKUP to fetch student data.

2. Use Absolute Cell Reference ($) for table range.
3. Calculate Total and Percentage in Sheet1 using formulas.
4. Apply proper formatting to the marksheet.
5. Use IFERROR function to avoid #N/A error.

Solution:- Steps
Step 1 – Create Sheet1 (Student Database)

Enter data like this:

Roll No Name Father Name DBMS C Th C Pr OMT Th OMT Pr Web Th Web Pr

101 Rahul Ramesh 75 70 80 65 70 75 80
102 Mohit Suresh 68 72 75 60 65 70 72
Step 2 – Total Marks Formula(Assume subject marks are from D2 to I2)
=SUM(D2:I2)

Step 3 – Percentage Formula(If total marks are in J2)

Maximum marks = 600

=(J2/600)*100

Step 4 – Grade Formula

=IF(K2>=75,"A",IF(K2>=60,"B",IF(K2>=50,"C","F")))

Sheet2 – Marksheet(Create a formatted layout.)

Roll Number Input

Example cell: B2

User will enter Roll Number here.

VLOOKUP Formulas
 Student Name
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,2,FALSE),"")

 Father Name
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,3,FALSE),"")

 DBMS Marks
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,4,FALSE),"")

 Programming in C (Th)
 =IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,5,FALSE),"")

 Programming in C (Pr)
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,6,FALSE),"")

 Total Marks
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,11,FALSE),"")

 Percentage
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,12,FALSE),"")

 Grade
=IFERROR(VLOOKUP($B$2,Sheet1!$A$2:$M$11,13,FALSE),"")

Final Output:When you enter Roll Number in Sheet2:

Question10: Create a Payroll System in Microsoft Excel that automatically calculates employee
salary using the HLOOKUP function.

Part 1: Salary Structure (Lookup Table)

Create the following Salary Structure Table in Excel.

Grade A B C D
Basic Salary 30000 25000 20000 15000
HRA 5000 4000 3000 2000
DA 3000 2500 2000 1500
TA 2000 1500 1200 1000

Part 2: Employee Payroll Table

Create the following Employee Payroll Table.

Emp ID Name Grade Basic HRA DA TA Gross Salary
E101 Riya A
E102 Mohan B
E103 Neha C

Requirements

1. Use HLOOKUP function to fetch salary components.

2. Use absolute cell reference for lookup table.
3. Calculate Gross Salary using SUM formula.
4. Apply proper table formatting.

Solution
Step 1: Create Lookup Table

Enter the salary structure in range: A1:E5

Step 2: Enter Employee Data

Create employee table like this:

Emp ID Name Grade

E101 Riya A
E102 Mohan B
E103 Neha C

Step 3: Apply HLOOKUP Formulas

Assume Grade is in C2.

 Basic Salary
=HLOOKUP(C2,$A$1:$E$5,2,FALSE)

 HRA
=HLOOKUP(C2,$A$1:$E$5,3,FALSE)

 DA
=HLOOKUP(C2,$A$1:$E$5,4,FALSE)

 TA
=HLOOKUP(C2,$A$1:$E$5,5,FALSE)

Step 4: Calculate Gross Salary

=SUM(D2:G2)

Final Output:
Emp ID Name Grade Basic HRA DA TA Gross Salary
E101 Riya A 30000 5000 3000 2000 40000
E102 Mohan B 25000 4000 2500 1500 33000
 E103 Neha C 20000 3000 2000 1200 26200

Question11:Assignment on Pivot table

Given data table (EMP NAME, ITEM, QUANTITY, RATE, AMOUNT) in Excel.

Enter the following data

EMP NAME ITEM QUANTITY RATE AMOUNT

Amit Bhat LED 74 9800 725200

Divya Sharma RAM 22 1250 27500

Rohan Shah DVD 12 1450 17400

SiyaSen LCD 10 7800 78000
Rehana Gupta RAM 12 1690 20280

Anuj Singh Key Board 12 520 6240

Mohit Sharma Mouse 14 210 2940

Amrendra Sharma LED 33 11200 369600

Veer Pandit HDD 24 3300 79200

Preetam Pandey LCD 36 12200 439200

Rohit Singh HDD 28 2950 82600

PawanGautam RAM 18 1490 26820

Ranbeer Kumar Cabinet 18 1850 33300

Vidya Gupta UPS 20 1450 29000

Sakram Sharma HDD 13 3340 43420

Shyam Pandey UPS 8 1690 13520

Ganesh Prasad RAM 10 1050 10500

Kajal Prasad Key Board 20 490 9800

Kundan Chauhan LCD 62 4200 260400

Siddharth Pandey UPS 15 2250 33750

Solution:-Step 1: Enter the Data

1. Open MS Excel.
2. Create the following columns:

EMP NAME ITEM QUANTITY RATE AMOUNT

3. Enter all the given records.

Q(i)Create a Pivot Table to find the total quantity of each item sold.

Expected Output Example

ITEM Total Quantity

LED —
RAM —
LCD —
UPS —

Solution:-Steps

1. Select the entire data table.

2. Click Insert → Pivot Table.
3. Choose New Worksheet → OK.
4. In Pivot Table Fields:
o Drag ITEM → Rows
o Drag QUANTITY → Values
5. Ensure Value Field Settings = Sum of Quantity.

Final Output:
ITEM Total Quantity
 LED 107
RAM 62
DVD 12
LCD 108
Key Board 32
Mouse 14
HDD 65
Cabinet 18
UPS 43

Q.(ii) Create a Pivot Table to calculate the total sales amount for each item.

Expected Output

ITEM Total Amount

LED —
RAM —
LCD —
UPS —

Solution:-Steps

1. Insert a Pivot Table again.

2. In Pivot Fields:
o ITEM → Rows
o AMOUNT → Values

Ensure it shows Sum of Amount.

Final Output:
ITEM Total Amount
LED 1,094,800
RAM 85,100
DVD 17,400
LCD 777,600
Key Board 16,040
Mouse 2,940
HDD 205,220
Cabinet 33,300
UPS 76,270

Q. (iii) Create a Pivot Table to show employee-wise total sales amount.

Expected Output

EMP NAME Total Amount

Amit Bhat —
Divya Sharma —
Rohan Shah —
Solution:-Steps

1. Insert Pivot Table.

2. Drag fields:
o EMP NAME → Rows
o AMOUNT → Values

Final Output:
EMP NAME Total Amount
Amit Bhat 725200
Divya Sharma 27500
Rohan Shah 17400
SiyaSen 78000
... ...

Q.(iv)Create a Pivot Table to find the total quantity sold by each employee.

Expected Output

EMP NAME Total Quantity

Amit Bhat —
Divya Sharma —

Solution:-Steps

1. Insert Pivot Table.

2. Drag:
o EMP NAME → Rows
o QUANTITY → Values
3. Ensure it shows Sum of Quantity.

Final Output:
EMP NAME Total Quantity
Amit Bhat 74
Divya Sharma 22
Rohan Shah 12
SiyaSen 10

Q(V) Create a Pivot Chart using Pivot Table to show Item-wise total sales amount.

Chart Type:

 Column Chart
or
 Bar Chart

Solution:-Steps

1. Select the Pivot Table from Question 2.

 2. Go to Insert → Pivot Chart.
3. Choose Column Chart or Bar Chart.
4. Click OK.

The chart will show Item-wise Total Sales Amount.

Q(vi) Create a Pivot Table that shows:

ITEM Total Quantity Total Amount

Steps:

 Rows → ITEM
 Values → QUANTITY (Sum)
 Values → AMOUNT (Sum)

Solution:-Steps

1. Insert Pivot Table.

2. Drag fields:

 ITEM → Rows
 QUANTITY → Values
 AMOUNT → Values

Final Output:
ITEM Total Quantity Total Amount
LED 107 1094800
 RAM 62 85100
DVD 12 17400
LCD 108 777600
Key Board 32 16040
Mouse 14 2940
HDD 65 205220
Cabinet 18 33300
UPS 43 76270

Question12:Create the following table in Microsoft Excel and apply statistical functions.

Student Name Maths Science English

Amit 78 82 74
Divya 90 85 88
Rohan 45 50 40
Siya 67 72 70
Rahul 55 60 58

Using Statistical Functions, calculate the following:

1. Average Marks of Maths using AVERAGE function.

2. Highest Marks in Science using MAX function.
3. Lowest Marks in English using MIN function.
4. Total Students using COUNT function.
5. Number of students scoring above 60 in Maths using COUNTIF function.
6. Median value of Maths marks using MEDIAN function.
7. Mode value of Science marks using MODE function.
8. Standard Deviation of English marks using STDEV function.

Solution (Formulas)
1 Average Marks (Maths)
=AVERAGE(B2:B6)
2 Highest Marks (Science)
=MAX(C2:C6)
3 Lowest Marks (English)
=MIN(D2:D6)
4 Total Students
=COUNT(A2:A6)
5 Students scoring above 60 in Maths
=COUNTIF(B2:B6,">60")
6 Median Marks (Maths)
=MEDIAN(B2:B6)
7 Mode Marks (Science)
=MODE(C2:C6)
8 Standard Deviation (English)
=STDEV(D2:D6)

Final Output