1.
Creating a Student Marksheet
Objective:
To input student marks for five subjects and use formulas to calculate
total, average, percentage, and grade.
Steps to Create the Marksheet:-
Step 1: Set Up the Table Layout.
• Create columns: Roll No, Name, Web Based, DSA, Soft Skills, Web Designing, Maths, Total,
Average, Percentage, Grade.
Step 2: Use Formulas for Total, Average, and Percentage.
• To calculate Total: =SUM(C2:G2)
• To calculate Average: =AVERAGE(C2:G2)
• To calculate Percentage: =(H2 / 500) * 100
Step 3: Use Formula to Assign Grade Based on Percentage.
• Use this formula:
=IF(J2>=90,"A+",IF(J2>=80,"A",IF(J2>=70,"B+",IF(J2>=60,"B",IF(J2>=50,"C+",IF(J2>=40,"C","F"))))))
2. Personal Budget Planner
Objective:
To compare monthly income and expenses, calculate savings, and visualize
the budget using pie charts.
Steps to Create the Budget Planner :-
Step 1: Set Up the Spreadsheet Layout
• Create a table with the following columns:
o Category (e.g., Rent, Food, Transport, Entertainment, Savings, etc.)
o Income/Expense (mention whether it's an income or expense)
o Amount (₹)
Step 2: Enter Monthly Data
• Fill in all your income sources and their amounts.
• Add all your expense categories with estimated or actual amounts.
Step 3: Use Formulas to Calculate Totals
• Total Income:
o Use formula: =SUM(B2:B7, "Income", C2:C7)
• Total Expenses:
o Use formula: =SUM(B2:B7, "Expense", C2:C7)
Step 4: Visualize Using Pie Charts
• Select the data for Category and Amount (only Expenses).
• Go to Insert → Chart → Pie Chart.
• Customize chart title as "Monthly Expenses Breakdown".
3. Sales Record Table
Objective:
To maintain a daily sales record for a week and analyse it using basic
functions to calculate total, average, highest, and lowest sales.
Steps to Create the Sales Record Table:-
Step 1: Set Up the Table Layout
• Create columns with:
o Day (e.g., Monday to Sunday)
o Sales (₹)
Step 2: Enter Daily Sales Data
• Fill in the table with sales figures for each day of the week.
Step 3: Apply Functions to Analyse Data
• Total Sales:
o Formula: =SUM(B2:B8)
• Average Sales:
o Formula: =AVERAGE(B2:B8)
• Highest Sale:
o Formula: =MAX(B2:B8)
• Lowest Sale:
o Formula: =MIN(B2:B8)
4. Attendance Sheet
Objective:
To track daily attendance for a month and highlight absent students
using conditional formatting for easy visibility.
Steps to Create the Attendance Sheet:-
Step 1: Set Up the Table Layout.
Step 2: Use Formula for Total Present.
• Use: =COUNTIF(range, "P") to calculate how many days a student was present.
Step 3: Apply Conditional Formatting to Highlight Absentees.
• Select the attendance cells (not the names or totals).
• Go to: Format → Conditional formatting
• Apply the rule:
o Format cells if... Text is exactly → A
o Set fill colour to red or any bright colour for visibility.
5. Simple Invoice Generator
Steps to Create a Simple Invoice Generator:-
Step 1: Create the table headers
• In the first row, enter:
Serial No., Product Name, Quantity, Price per Unit, Total, Tax (18%), Grand Total
Step 2: Enter product data
• Fill in product names, quantities, and unit prices in the respective columns.
Step 3: Calculate Total (Quantity × Price)
• In the Total column, use the formula:
=Quantity * Price, e.g., =C2*D2
Step 4: Calculate Tax (18%)
• In the Tax column, use the formula:
=E2*18% to calculate GST on the total.
Step 5: Calculate Grand Total (Total + Tax)
• In the Grand Total column, use the formula:
=E2+F2
Step 6: Drag formulas down
• Select and drag each formula cell down to apply to other rows/products.
6. Loan EMI Calculator
Objective:
To calculate the monthly EMI (Equated Monthly Installment) for a loan using
the PMT function in a spreadsheet.
Steps to Create the EMI Calculator:-
Step 1: Set Up the Table Layout.
• Create input fields for:
– Principal Amount (P)
– Annual Interest Rate (R)
– Loan Tenure in Years (T)
Step 2: Convert Values for Calculation.
• Monthly Interest Rate = Annual Rate / 12 / 100
• Number of Months = Tenure in Years × 12
Step 3: Use the PMT Function to Calculate EMI.
• Use the formula:
=PMT(rate, nper, -pv)
– rate = monthly interest rate
– nper = number of months
– pv = principal amount
• Example:
=PMT(0.0083, 60, -500000)
(For ₹5,00,000 at 10% annual interest for 5 years)
Step 4: Format the EMI Output.
• Highlight the EMI cell with bold and currency format.
• Label it clearly as “Monthly EMI”.
7. Employee Database
Objective:
To create a database of employee details and use functions like FILTER, SORT, and
VLOOKUP to manage and retrieve information.
Steps to Create the Employee Database:-
Step 1: Set Up the Table Layout.
• Create the following columns:
– Employee ID
– Name
– Department
– Salary
– Joining Date
Step 2: Enter Sample Employee Data.
• Fill the table with sample data for each employee under the columns mentioned above.
Step 3: Use the SORT Function to Arrange Data.
• To sort employees by Salary in descending order:
=SORT(A2:E10, 4, FALSE)
(Assuming Salary is in column 4)
Step 4: Use the FILTER Function to View Specific Records.
• To filter employees from a specific department (e.g., "HR"):
=FILTER(A2:E10, C2:C10 = "HR")
Step 5: Use the VLOOKUP Function to Search Employee Details.
• To find the name and department of an employee using their ID:
=VLOOKUP("EMP101", A2:E10, 2, FALSE) → (For Name)
=VLOOKUP("EMP101", A2:E10, 3, FALSE) → (For Department)
Step 6: Format the Database for Readability.
• Apply borders, bold headers, and appropriate number/date formats.
• Freeze the top row for easier scrolling.
8. Sales Chart Dashboard
Objective:
To visualize monthly or quarterly sales data using different chart types like bar, column,
line, and pie charts for better business analysis.
Steps to Create the Sales Dashboard:-
Step 1: Prepare the Sales Data Table.
• Create columns:
– Month / Quarter
– Product
– Sales Amount (₹)
Step 2: Insert a Bar Chart.
• Select the data range (e.g., Month vs Sales Amount).
• Go to Insert → Chart → Bar Chart
• Choose clustered bar to compare sales of different products side by side.
Step 3: Insert a Column Chart.
• Use for comparing product-wise or month-wise sales.
• Select your data and go to Insert → Column Chart
• Use stacked column if showing multiple products per month.
Step 4: Insert a Line Chart.
• Best for tracking sales trend over time.
• Select Month and Sales Amount columns.
• Insert → Line Chart → Choose "Smooth Line" or "Markers" for better visual.
Step 5: Insert a Pie Chart.
• Use when showing percentage contribution of products in a specific month or quarter.
• Select data for one month and go to Insert → Pie Chart
• Choose 2D or 3D Pie for visual clarity.
Step 6: Format the Charts.
• Add titles, labels, and data values.
• Use different colors for better clarity.
• Place charts in one sheet to create a proper dashboard layout.
9. Highlight Failing Students
Objective:
To identify and highlight students who have scored less than 35 marks using
conditional formatting.
Steps to Highlight Failing Students:-
Step 1: Prepare the Marks Table.
• Create a table with the following columns:
– Roll No
– Name
– Subject 1
– Subject 2
– Subject 3
– Subject 4
– Subject 5
Step 2: Select the Range of Marks.
• Highlight only the marks cells, not names or roll numbers.
– For example: C2:G10
Step 3: Apply Conditional Formatting.
• Go to: Home → Conditional Formatting → New Rule
• Choose: Format cells that contain → Cell Value less than → 35
Step 4: Set Formatting Style.
• Choose a Red Fill Colour to highlight failing marks.
• Click OK to apply.
Step 5: Review the Sheet.
• All cells with marks less than 35 will now be clearly visible in red.
10. Using Pivot Table for Analysis
Objective:
To analyse product sales based on region and category using a Pivot Table for
better business insights.
Steps to Create Pivot Table for Sales Analysis:-
Step 1: Set Up the Sales Data Table.
Step 2: Insert a Pivot Table.
• Select the entire table (including headers).
• Go to: Insert → Pivot Table
• Choose whether to place the pivot table in a new worksheet or the same sheet.
Step 3: Arrange Pivot Table Fields.
• Rows: Drag Region or Category here.
• Columns: Drag Product (optional) or leave blank.
• Values: Drag Units Sold or Total Sales (₹) here — it will auto-summarize.
• Filters (Optional): Drag Category or Region to filter results interactively.
Step 4: Analyse the Results.
• See total units or sales per region, per category, or both.
• You can also switch to different summarizations (e.g., average, count, etc.)
