Assignment 01 Class 1: Introduction to MS Excel

Rashmi Mishra
personRashmi Mishra
September 23, 2024

Assignment 01 

Class 1: Introduction to MS Excel


Assignment 1: Workbook Creation and Basic Data Entry

Objective: Practice creating a new workbook, entering data, and saving your work.

Instructions:

  1. Create a New Workbook:
    • Open MS Excel and create a new blank workbook.
    • Save the workbook with a meaningful name (e.g., Student_Data.xlsx).
  2. Enter Data:
    • In the first sheet, enter the following data:
      • A1: Name
      • B1: Age
      • C1: Grade
      • A2 to A6: Enter names of five students.
      • B2 to B6: Enter corresponding ages for each student.
      • C2 to C6: Enter the grades for each student.
  3. Format the Data:
    • Bold the headers in Row 1.
    • Adjust the column widths to fit the data.
    • Apply a border around the range A1

.

  1. Save and Close:
    • Save your changes and close the workbook.

Assignment 2: Basic Formatting and Cell Styles

Objective: Apply basic formatting to enhance the appearance of your data.

Instructions:

  1. Open an Existing Workbook:
    • Open the workbook you created in Assignment 1.
  2. Apply Formatting:
    • Change the font style and size for the headers in Row 1.
    • Apply a background color to the header row.
    • Format the "Age" column (Column B) to display numbers with a specific number format (e.g., no decimal places).
    • Format the "Grade" column (Column C) with a different background color to distinguish it from other columns.
  3. Save and Review:
    • Save your changes.
    • Review your formatting to ensure it is consistent and visually appealing.

Assignment 3: Data Navigation and Cell Selection

Objective: Practice navigating between cells and selecting ranges.

Instructions:

  1. Create a New Worksheet:
    • In the same workbook, create a new worksheet named Navigation_Practice.
  2. Enter Data:
    • In Sheet1, enter a list of items in Column A (e.g., Item1, Item2, Item3).
    • Enter quantities in Column B and prices in Column C.
  3. Practice Navigation:
    • Use the arrow keys to move between cells.
    • Select a range of cells from A1 to C5.
    • Select the entire Column B and Row 2.
  4. Use Cell References:
    • In cell D1, enter a formula to calculate the total quantity of items by summing up Column B.
    • In cell D2, enter a formula to calculate the average price from Column C.
  5. Save and Close:
    • Save your workbook and close it.

Assignment 4: Entering and Formatting Dates

Objective: Practice entering and formatting dates in Excel.

Instructions:

  1. Open a New Workbook:
    • Open a new workbook or use an existing one.
  2. Enter Dates:
    • In Column A, enter a list of important dates (e.g., project deadlines, events) in various formats (e.g., 08/28/2024, August 28, 2024).
  3. Format Dates:
    • Change the date format of the entire column to a different style (e.g., short date or long date format).
    • Apply a date format to display the day of the week along with the date.
  4. Save and Review:
    • Save your changes.
    • Review the date formatting to ensure consistency.

Assignment 5: Creating and Saving Multiple Worksheets

Objective: Learn to manage and save multiple worksheets within a single workbook.

Instructions:

  1. Open or Create a Workbook:
    • Open an existing workbook or create a new one.
  2. Add New Worksheets:
    • Add two new worksheets to the workbook.
    • Rename the worksheets to Sales_Data and Inventory.
  3. Enter Sample Data:
    • In the Sales_Data sheet, enter a list of sales transactions with columns for Date, Product, and Amount.
    • In the Inventory sheet, enter data for Product, Quantity, and Reorder Level.
  4. Save and Close:
    • Save your workbook with a descriptive name (e.g., Company_Data.xlsx).
    • Close the workbook.

SOlUTIONS

Assignment 1: Workbook Creation and Basic Data Entry

Solution:

  1. Create a New Workbook:
    • Open MS Excel and click on "Blank Workbook."
    • Save the workbook as Student_Data.xlsx by clicking File > Save As, then choose the location and enter the name.
  2. Enter Data:
    • In the first sheet, enter the following data:
      • A1: Name, B1: Age, C1: Grade
      • In A2 to A6, enter student names:
        • A2: John, A3: Lisa, A4: Mark, A5: Sarah, A6: Adam.
      • In B2 to B6, enter corresponding ages:
        • B2: 15, B3: 14, B4: 16, B5: 15, B6: 14.
      • In C2 to C6, enter grades:
        • C2: A, C3: B, C4: A, C5: C, C6: B.
  3. Format the Data:
    • Bold the headers in Row 1 by selecting A1

and clicking the Bold button.

    • Adjust the column widths by double-clicking between the column headings or dragging them to fit the data.
    • Apply a border around the range A1

by selecting the range and using the border option.

  1. Save and Close:
    • Click File > Save to save changes, and then close the workbook.

Assignment 2: Basic Formatting and Cell Styles

Solution:

  1. Open an Existing Workbook:
    • Open the Student_Data.xlsx workbook from Assignment 1.
  2. Apply Formatting:
    • Select the headers in Row 1 (A1

), then change the font style and size (e.g., Arial, 14).

    • Apply a background color to Row 1 by selecting A1

and using the fill color option.

    • Format the Age column (B2

) to display no decimal places by selecting the range, right-clicking, choosing Format Cells, and selecting Number with 0 decimal places.

    • Format the Grade column (C2

) with a different background color (e.g., light blue) by selecting the column and using the fill color option.

  1. Save and Review:
    • Save the workbook, ensuring that all formatting is consistent and visually appealing.

Assignment 3: Data Navigation and Cell Selection

Solution:

  1. Create a New Worksheet:
    • In the Student_Data.xlsx workbook, add a new sheet by clicking the + button at the bottom. Name it Navigation_Practice.
  2. Enter Data:
    • In Sheet1, enter the following:
      • A1: Item1, A2: Item2, A3: Item3.
      • B1: 10, B2: 15, B3: 20.
      • C1: 5.00, C2: 7.50, C3: 10.00.
  3. Practice Navigation:
    • Use the arrow keys to move between cells.
    • Select the range A1

by dragging the mouse or using Shift + arrow keys.

    • Select the entire Column B by clicking on the column header.
    • Select Row 2 by clicking on the row number.
  1. Use Cell References:
    • In D1, enter the formula =SUM(B1:B3) to calculate the total quantity.
    • In D2, enter the formula =AVERAGE(C1:C3) to calculate the average price.
  2. Save and Close:
    • Save the workbook and close it.

Assignment 4: Entering and Formatting Dates

Solution:

  1. Open a New Workbook:
    • Open a new Excel workbook or use an existing one.
  2. Enter Dates:
    • In Column A, enter the following important dates in various formats:
      • A1: 08/28/2024, A2: August 28, 2024, A3: 28-Aug-2024.
  3. Format Dates:
    • Select Column A and change the date format by right-clicking, selecting Format Cells, and choosing Date. You can choose the Short Date or Long Date format.
    • Apply a format to display the day of the week along with the date by selecting Custom and entering dddd, mmmm dd, yyyy.
  4. Save and Review:
    • Save the workbook and review the dates to ensure they are consistently formatted.

Assignment 5: Creating and Saving Multiple Worksheets

Solution:

  1. Open or Create a Workbook:
    • Open an existing workbook or create a new one.
  2. Add New Worksheets:
    • Add two new worksheets by clicking the + button at the bottom.
    • Rename them as Sales_Data and Inventory by double-clicking the tab names.
  3. Enter Sample Data:
    • In the Sales_Data sheet, enter:
      • A1: Date, B1: Product, C1: Amount.
      • A2: 09/01/2024, B2: Laptop, C2: 1500.
      • A3: 09/02/2024, B3: Phone, C3: 800.
    • In the Inventory sheet, enter:
      • A1: Product, B1: Quantity, C1: Reorder Level.
      • A2: Laptop, B2: 20, C2: 5.
      • A3: Phone, B3: 30, C3: 10.
  4. Save and Close:
    • Save the workbook as Company_Data.xlsx and close it.

These assignments will help students practice basic skills in Excel, including workbook management, data entry, formatting, and navigation.


Tags

You Might Like

Show more
Assignment
Share to other apps
Copy Post link