Microsoft Excel Lab Quiz

Course Information

• Course name: Applications of ICT
• Class: ME-17
• Lab number: 5
• Lab topic: Microsoft Excel (Basics)
• Date: (missing)
• Time: (missing)
• Lab Engineer: (missing)

Lab 5: Microsoft Excel Introduction

• Excel is a powerful spreadsheet application
• Widely used for data organization, calculation, analysis, and visualization.
• Useful in various fields like finance, engineering, business, and data science.
•  The lab covers basic Excel features
•  Students will learn to manage data, use formulas and create charts & graphs.
• Students will gain a solid understanding of Excel capabilities
• This lab will make students proficient in Excel for future academic and professional use.

Lab 5 Objectives

• Introduce students to the Excel interface and basic functions.
•  Enable students to input, organize and format data in Excel.
• Provide students with the ability to use basic Excel formulas (e.g., SUM, AVERAGE).
• Teach students to sort and filter Excel data.
• Introduce charts and graphs for visualizing data.
• Teach students about conditional formatting and basic logical formulas (IF statements).
•  Provide practical exercises to reinforce theoretical knowledge.

Lab 5 Description

• The lab consists of several practical sessions.
• Each session focuses on different Excel features.
•  Session 1: Familiarizes students with the interface, including data entry, cell formatting, saving worksheets
• Session 2: Introduces basic formulas (SUM, AVERAGE), demonstrating simplification of mathematical tasks.
• Session 3: Teaches sorting and filtering for efficient data management and analysis.
• Session 4: Focuses on creating charts and graphs for data visualization
• Session 5: Introduces advanced formulas (e.g., IF statements) and conditional formatting for enhanced data presentation and decision-making.

Activity 1: Introduction to Excel & Data Entry

• Objective: Familiarize students with the Excel interface and teach basic data entry, formatting, and navigation.
• Steps:
  • Open Excel
  • Familiarize students with the Excel interface (Ribbon, cells, rows, columns)
  • Data Entry: Enter data for Name, Age, Salary in provided example or create own, into the worksheet.
  • Formatting Cells: Make the header row bold using the Bold button; adjust column width
  • Apply Currency formatting to the Salary column.
  • Save the Workbook: Save the file with a meaningful name (e.g., A1_StudentID)

Activity 2: Basic Formulas (SUM, AVERAGE)

• Objective: Introduce students to basic Excel formulas (SUM and AVERAGE).
• Steps:
  • Use the file created in Lab 1.
  • Using SUM Formula: Calculate total salary in a cell by using formula =SUM(C2:C9).
  • Using AVERAGE Formula: Find average salary by using formula =AVERAGE(C2:C9).
  • Save the Workbook: Save the new file as A2_StudentID

Activity 3: Sorting and Filtering Data

• Objective: Teach students how to sort and filter data in Excel.
• Steps: - Add new data rows to the data
  • Sort Data: Sort the data by Age (smallest to largest) using the Sort feature.
  • Filter Data: Use filtering to display only entries with salaries greater than 50,000.
  • Save the Workbook: Save as A3_StudentID

Activity 4: Creating Basic Charts

• Objective: Introduce students to creating simple charts in Excel.
• Steps:
  • Enter monthly sales data (e.g., January, February, March, April).
  • Create a column chart to visualize the sales data.
  • Customize chart with title, axis labels, and styles.
  • Save the Workbook: Save as A4_Charts

Activity 5: Advanced Formulas & Conditional Formatting

• Objective: Introduce more advanced formulas and conditional formatting.
• Steps:
  • Use the IF formula to determine bonus eligibility based on salary.
  • Apply conditional formatting to highlight cells containing salaries from a certain range (e.g., >50,000) with different color, using different Conditional Formatting options.
  • Save the Workbook: Save as A5_StudentID

Activity 6: Conditional Formatting & Data Validation

• Objective: Teach students how to apply conditional formatting rules and use data validation to control input.
• Steps
  • Apply conditional formatting: Highlighting cells based on predefined rules like scores greater than 80 in green and scores below 60 in red.
  • Data Validation: Set data validation rules for a column to allow only specific values. For example, allowing A, B, C, D, or F in the grade column. Use appropriate dropdown lists.
  • Save the file as A6_StudentID

Test Activities

• Test Activity 1: Basic Excel Functions and Formulas (data manipulation)
• Test Activity 2: Conditional Formatting (specific criteria)
• Test Activity 3: Data Validation & Conditional Formatting (applying data validation techniques and conditional formatting)