MS Excel & MS Access Lab Manual
MS Excel Questions
Q1: Student Data Filters
a) Filter the student details where Score > 79.
b) Filter the student details where Score is between 50 and 70.
Step-by-Step Procedure:
1. Open Microsoft Excel and enter the student data with columns: FirstName, LastName,
Score.
2. Select the table and go to Data → Filter.
3. Apply filter condition for Score > 79.
4. Reapply filter condition for Score between 50 and 70.
Figure: Filtered student details in Excel (Score > 79 and Score between 50–70).
Q2: Sales Data Pivot Table
Generate a Pivot Table to calculate total sales and average sales region-wise.
Step-by-Step Procedure:
1. Enter sales data with columns: Date, Salesman, Region, Sales(Rs).
2. Select the table and go to Insert → PivotTable.
3. Drag Region to Rows.
4. Drag Sales to Values (Sum and Average).
Figure: Pivot Table showing Region-wise Total and Average Sales.
Q3: Month vs Sales Chart
Plot sales data using a Line Chart.
Step-by-Step Procedure:
1. Enter columns: Month, Sales(Rs).
2. Select data → Insert → Line Chart.
3. Format chart with title and gridlines.
Figure: Month vs Sales Line Chart.
Q4: Student Marks Bar Chart
Represent total marks obtained by students using a Bar Chart.
Step-by-Step Procedure:
1. Enter columns: S.No, Student Name, Total Marks.
2. Select data → Insert → Bar Chart.
Figure: Student Marks Bar Chart.
Additional Excel Questions
Q5: Employee Salary Analysis
Step-by-Step Procedure:
1. Enter columns: EmpID, Name, Basic, HRA, DA.
2. Add Gross = Basic+HRA+DA.
3. Apply Conditional Formatting → Highlight Gross > 50,000.
Figure: Employee Salary Table with Conditional Formatting.
Q6: Attendance Report
Step-by-Step Procedure:
1. Enter columns: Student Name, Total Classes, Classes Attended.
2. Calculate Attendance %.
3. Insert → Pie Chart.
Figure: Attendance Report Pie Chart.
Q7: Product Sales Trend
Step-by-Step Procedure:
1. Enter columns: Month, Product A, Product B, Product C.
2. Insert → Line Chart.
3. Add Trendline for Product A.
Figure: Product Sales Trend with Trendline.
MS Access Questions
Q1: Student Table
Create a Student table with fields RollNumber, StudentName, Class, Subject marks, Total,
Average, Result.
Step-by-Step Procedure:
1. Open MS Access → Create → Table Design.
2. Enter fields as shown below.
3. Create query to calculate Total, Average, and Result.
Figures: StudentMarks Table Design and Query Result.
Q2: Personal Information Table
Create a PersonalInfo table with fields FirstName, MiddleName, LastName, Nationality,
Gender, TemporaryAddress, PermanentAddress.
Step-by-Step Procedure:
1. Open MS Access → Create → Table Design.
2. Create Form for data entry.
3. Create Report grouped by Nationality.
Figure: PersonalInfo Table Design.
Q3: Retail Store SalesTransactions
Create a SalesTransactions table to record retail sales.
Step-by-Step Procedure:
1. Open MS Access → Create → Table Design.
2. Create fields as shown.
3. Create Query to calculate TotalAmount.
4. Generate Report grouped by PaymentMode.
Figures: SalesTransactions Table Design and Query Result.
Q4: Inventory Table
Create an Inventory table with fields ProductID, ProductName, Quantity, UnitPrice.
Step-by-Step Procedure:
1. Create table in Design View.
2. Add query to calculate StockValue = Quantity × UnitPrice.
Figures: Inventory Table Design and Query Result.
Q5: Library Management
Create a Books table for library management.
Step-by-Step Procedure:
1. Create table in Design View with BookID, Title, Author, Publisher, Year, CopiesAvailable.
2. Create Query to list books published after 2015.
3. Create Report for books with fewer than 5 copies.
Figure: Books Table Design.
Q6: Orders Database
Create an Orders table with fields OrderID, CustomerName, Product, Quantity, PricePerUnit,
OrderDate.
Step-by-Step Procedure:
1. Create Orders table in Design View.
2. Create Query to calculate TotalBill = Quantity × PricePerUnit.
3. Generate Report for January orders.
Figure: Orders Table Design.
