Lab 5 (Microsoft Excel Basics) PDF

School of Chemical and Material Engineering CS-000: Applications of ICT Class: ME-17 Lab 5: Microsoft Excel (Basics) Date: Time : Lab Engineer : Lab Manual ========== Microsoft Excel =============== **\ ** Lab 5: Microsoft Excel: ======================= Introduction: ============= Micros...

School of Chemical and Material Engineering CS-000: Applications of ICT Class: ME-17 Lab 5: Microsoft Excel (Basics) Date: Time : Lab Engineer : Lab Manual ========== Microsoft Excel =============== **\ ** Lab 5: Microsoft Excel: ======================= Introduction: ============= Microsoft Excel is a powerful spreadsheet application widely used for data organization, calculation, analysis, and visualization. It is a fundamental tool for students and professionals in various fields, such as finance, engineering, business, and data science. This lab will introduce you to the basic and essential features of Excel, providing hands-on experience that will enable you to efficiently manage data, apply formulas, and create visual representations such as charts and graphs. Throughout this lab, you will explore how Excel can be used for basic data manipulation, including entering, formatting, and sorting data, as well as performing basic calculations and visualizing data through charts. By the end of this course, you will have a solid understanding of Excel\'s capabilities and be able to apply these skills in your academic and professional life. Objectives ========== The primary objectives of this lab are to: - Introduce students to the **Microsoft Excel interface** and its basic functions. - Enable students to **enter, organize, and format data** in Excel. - Provide students with the ability to apply **basic formulas** (SUM, AVERAGE, etc.) to perform calculations on data. - Teach students how to **sort and filter data** for easy analysis. - Introduce students to creating **charts and graphs** for data visualization. - Encourage students to explore **advanced functions** such as conditional formatting and basic logical formulas (IF statements). - Provide practical, hands-on exercises that reinforce the theoretical knowledge acquired. Description =========== This lab consists of several practical sessions designed to provide step-by-step instructions on using Microsoft Excel. Each session will focus on different Excel features: - **Session 1:** Focuses on familiarizing students with the **Excel interface**, including data entry, cell formatting, and saving worksheets. - **Session 2:** Introduces the use of **basic formulas** for calculations (SUM, AVERAGE), demonstrating how Excel can simplify mathematical tasks. - **Session 3:** Teaches **sorting and filtering** techniques for efficient data management and analysis. - **Session 4:** Focuses on creating **charts and graphs**, which are vital for visually representing data trends and comparisons. - **Session 5:** Introduces the use of **advanced formulas** like IF statements and **conditional formatting** to enhance data presentation and decision-making. Each lab activity will provide real-life data examples to help students practice, understand, and apply the concepts in a practical context. Activity no 1: **Introduction to Excel and Data Entry** ======================================================= **Objective:**\ Familiarize students with the Excel interface and teach basic data entry, formatting, and navigation. **Steps:** 1. **Open Excel:** - Launch Microsoft Excel from the Start menu or desktop. - Familiarize yourself with the interface: the Ribbon, cells, rows, and columns. 2. **Data Entry:** - Click on cell A1 and enter the word \"Name\". - In cell B1, enter \"Age\". - In cell C1, enter \"Salary\". - Now, in the rows below, enter sample data for five people as given in following example: Name Age Salary (Rupees) --------- ----- ----------------- Fahad 35 100,000 Mustafa 32 85,000 Aimen 24 55,000 Haider 26 78,000 Iqra 28 84,000 3. **Formatting Cells:** - Select the header row (A1) and make the text bold using the **Bold** button from the Home tab. - Adjust column width by hovering between the column headers (A, B, C) and dragging to resize. - Apply **Currency formatting** to the Salary column (C2). 4. **Save the Workbook:** - Click **File** \> **Save As**, and save the file with a meaningful name (e.g., \"A1\_StudentID\"). Activity 2: Basic Formulas (SUM, AVERAGE) ========================================= **Objective:**\ Introduce students to basic Excel formulas, including **SUM** and **AVERAGE**. **Steps:** 1. **Open Excel File:** - Use the file created in Lab 1. 2. **Using the SUM Formula:** - Below the Salary column, in cell C10, enter the following formula: =SUM(C2:C9) and press **Enter**. - The total salary will appear in cell C10. 3. **Using the AVERAGE Formula:** - In cell C11, enter the formula: =AVERAGE(C2:C9) and press **Enter**. - The average salary will be displayed. 4. **Save the Workbook:** - Save the file with a new name as A2\_StudentID. Activity 3: Sorting and Filtering Data ====================================== **Objective:**\ Teach students how to sort and filter data in Excel. **Steps:** 1. **Data Entry:** - Add the following names, ages, and salaries in your worksheet under the existing data: - 7^th^ Row -\> (Daniyal, 32, 45000) - 8^th^ Row-\>(Ayesha, 29, 52000) - 9^th^ Row-\> (Sameer, 35, 38000) 2. **Sorting Data:** - Select the entire data range (A1). - Go to the **Data** tab and click on **Sort**. - Sort the data by **Age** in smallest to largest. 3. **Filtering Data:** - Select the header row (A1). - From the **Data** tab, click on **Filter**. - A small arrow will appear in each column header. Click on the arrow in the Salary column and filter to show only salaries greater than 50,000. 4. **Save the Workbook:** - Save the file as \"A3\_StudentID\". Activity 4: Creating Basic Charts ================================= **Objective:**\ Introduce students to creating simple charts in Excel. **Steps:** **Open a New Excel File** 1. **Data Entry:** - Enter the following data for monthly sales: - (January, 5000) - (February, 7000) - (March, 6000) - (April, 8000) Note Make Two columns (One for Month and Other for Salary) 2. **Creating a Column Chart:** - Select the data range (A1). - Go to the **Insert** tab and choose **Column Chart** from the Chart options. - A chart will appear in your worksheet showing the monthly sales. 3. **Chart Customization:** - Add a chart title by clicking on the chart and selecting the **Chart Title** option. Change the chart style from the **Chart Tools** tab. Give axis label to the chart x-axis as Month and y-axis as Salary 4. **Save the Workbook:** - Save the file as \"A4\_Charts\". Activity no 5: Advanced Formulas and Conditional Formatting: ============================================================ **Objective:**\ Introduce students to more advanced formulas and conditional formatting. **Steps:** - **Open your Activity 3** - **clear the Filter by clicking on Filter Tab in Data:** Now do the following tasks. 1. **Using IF Formula:** - In a new column (D), type \"Bonus Eligibility\". - In cell D2, enter the formula =IF(C2\>50000,\"Eligible\",\"Not Eligible\") and press **Enter**. - Drag the formula down for all rows to check bonus eligibility. 2. **Conditional Formatting:** - Select the Salary column (C2). - From the **Home** tab, click on **Conditional Formatting**. - Apply a new rule to highlight cells greater than 50,000 with a different color. 3. **Save the Workbook:** - Save the file as \"A5\_StudentID\". Activity 6: Using Conditional Formatting and Data Validation ============================================================ **Objective:**\ Teach students how to apply conditional formatting rules and use data validation to control input. **Steps:** 1. **Conditional Formatting:** - Open a new Excel sheet. - Enter a list of test scores for 10 students in column A (e.g., 85, 72, 90, etc.). - Select the range (A1). - Apply **Conditional Formatting** to highlight scores greater than 80 in **green** and scores below 60 in **red**. 2. **Data Validation:** - In column B, enter a new column labeled \"Grade\". - Set up **Data Validation** for the Grade column to allow only text entries like A, B, C, D, F (A for scores above 80, B for 70-79, etc.). - Try entering incorrect values to see how validation works. **Steps to Apply Data Validation:** a. **Select the Cells for Validation:** - Click on the column (in this case, the \"Grade\" column) or range of cells where you want to apply data validation. - For example, if you want to limit the cells in Column B (Grades), select cells B1 to B10. b. **Open Data Validation:** - Go to the **Data** tab in the Excel ribbon. - In the **Data Tools** group, click on **Data Validation**. c. **Set Validation Criteria:** - In the **Data Validation** dialog box, under the **Settings** tab: - In the **Allow** drop-down menu, select **List** (because you want to allow only specific entries like \"A\", \"B\", \"C\", etc.). - In the **Source** field, type the valid options separated by commas. For example: A,B,C,D,F. d. **Optional: Input Message and Error Alert** - **Input Message:** - Go to the **Input Message** tab if you want to show a message when someone selects a cell. Enter a title like \"Enter Grade\" and a message such as \"Please enter A, B, C, D, or F.\" - **Error Alert:** - In the **Error Alert** tab, customize the error message if someone tries to enter an invalid grade. Set the Style to **Stop**, and you can write a message like \"Invalid Grade. Only A, B, C, D, or F are allowed.\" e. **Test the Data Validation:** - Try entering valid values such as \"A\" or \"B\" in one of the cells, and then try entering an invalid value (e.g., \"E\"). - When an invalid value is entered, Excel will show an error message preventing the incorrect input. 3. **Save the Workbook:** - Save the file as \"A6\_StudentID\". Test Activity 1: Basic Excel Functions and Formulas: ==================================================== **Objective:**\ To evaluate students\' ability to use basic Excel functions and formulas for data manipulation. **Instructions:** 1. **Create a Dataset:** - In an Excel worksheet, create a table with the following columns: **Item**, **Quantity**, **Price per Unit**, and **Total Cost**. - Fill in the first three columns with sample data. For example: - Item: Apples, Bananas, Oranges - Quantity: 10, 5, 20 - Price per Unit: 2, 1.5, 1 2. **Calculate Total Cost:** - In the **Total Cost** column, use a formula to calculate the total cost for each item by multiplying the **Quantity** by the **Price per Unit**. - The formula for the first item (e.g., D2) should be =B2\*C2. Drag the fill handle down to apply the formula to other rows. 3. **Sum the Total Cost:** - Below the **Total Cost** column, use the **SUM** function to calculate the total expenditure for all items. 4. **Save and Submit:** - Save the workbook as **Lab1\_BasicFunctions.xlsx** and submit it for evaluation. **Assessment Criteria:** - Correct formulas in the **Total Cost** column. - Accurate use of the **SUM** function. - Proper formatting and organization of the data. Test Activity 2: Conditional Formatting (Lab 5) =============================================== **Objective:**\ To assess students\' ability to apply conditional formatting based on specific criteria. **Instructions:** 1. **Create a Sales Dataset:** - In a new Excel worksheet, enter the following data in a table format: - **Month:** January, February, March, April - **Sales:** 1500, 2000, 1800, 2200 2. **Apply Conditional Formatting:** - Select the **Sales** column and apply conditional formatting to highlight cells that are greater than the average sales value. - To find the average, you can use the **AVERAGE** function in a separate cell (e.g., =AVERAGE(B2:B5)). 3. **Format the Highlighting:** - Choose a fill color (e.g., light green) for the highlighted cells and ensure the formatting is clear and visually appealing. 4. **Save and Submit:** - Save the workbook as **Lab5\_ConditionalFormatting.xlsx** and submit it for evaluation. **Assessment Criteria:** - Successful application of conditional formatting. - Correct identification of cells above the average value. - Clarity and visual appeal of the highlighted cells. Test Activity 3: Data Validation and Conditional Formatting =========================================================== **Objective:**\ To evaluate students\' understanding of data validation techniques and their ability to apply conditional formatting. **Instructions:** 1. **Create a Product Dataset:** - In a new worksheet, enter the following data: - **Product:** A, B, C, D - **Sales:** 500, 700, 300, 900 - **Region:** North, South, East, West 2. **Implement Data Validation:** - In the **Region** column, apply data validation to allow only the values \"North,\" \"South,\" \"East,\" and \"West.\" - Use a dropdown list for ease of entry: - Select the cells in the **Region** column (e.g., C2). - Go to the **Data** tab, select **Data Validation**, choose **List**, and enter the options. 3. **Apply Conditional Formatting:** - Select the **Sales** column and apply conditional formatting to highlight sales amounts that are greater than 600. - Choose a fill color (e.g., orange) for highlighted sales amounts. 4. **Save and Submit:** - Save the workbook as **Lab7\_DataValidation\_ConditionalFormatting.xlsx** and submit it for evaluation. **Assessment Criteria:** - Correct implementation of data validation with dropdown options. - Successful application of conditional formatting. - Proper organization and clarity of the data presented.