Lab-5

LAB NO.
05
Introduction to MS Excel
Get familiar with MS Excel.
Items Description
Course Title ICT
Lab Title basic Excel formulas
Duration 3 Hours
Operating System /Tool/Language Microsoft Office/MS Excel
Objective Learn and practice basic Excel formulas
Lab Activity: Exploring Basic Excel Formulas

Objective:
 Learn and practice basic Excel formulas to manipulate data and perform calculations.
Tools Required:
 Microsoft Excel (any version).
Instructions:
1. Open Microsoft Excel.

2. Create a new workbook.
3. Follow the steps below and use the sample data provided to practice basic formulas.
Task 1: Setting Up Data

1. Input the following data into your worksheet:
Name Math Science Englis Total Marks Average Status

h Marks
Alice 85 90 88
Bob 78 83 85
Charli 92 87 95
e
Diana 88 91 84
Task 2: Using Basic Formulas

2.1 SUM Formula:
 Calculate the Total Marks for each student:

1. Click on the first cell in the "Total Marks" column (E2).
2. Enter the formula:
=SUM(B2:D2)
3. Press Enter.
4. Drag the formula down to calculate for all rows.
2.2 AVERAGE Formula:
 Calculate the Average Marks for each student:

1. Click on the first cell in the "Average Marks" column (F2).
=AVERAGE(B2:D2)
3. Press Enter.
2.3 IF Formula:
 Determine the Status of each student (Pass/Fail):

1. Assume a passing average is 80.
2. Click on the first cell in the "Status" column (G2).
=IF(F2>=80, "Pass", "Fail")
4. Press Enter.
Task 3: Additional Calculations

3.1 MAX Formula:
 Find the highest marks in the "Math" column:
1. Below the data table, click on an empty cell.
=MAX(B2:B5)
3.2 MIN Formula:
 Find the lowest marks in the "Science" column:

=MIN(C2:C5)
3.3 COUNT Formula:
 Count the number of students:

=COUNT(A2:A5)
Task 4: Formatting the Worksheet

1. Apply borders to the table.
2. Format the marks columns with bold text.
3. Use conditional formatting to highlight cells in the "Status" column that contain "Fail"
in red.
Here’s how you can integrate the CONCAT formula into a practical lab example, similar to the
style above.
Task 5: Using the CONCAT Formula

Objective:
Combine first and last names into a single "Full Name" column.
5.1 Add the Following Data to Your Worksheet:
1. Extend your current worksheet or use a new sheet.

2. Enter the following data:
First Name Last Full Name

Name
Alice Johnson
Bob Smith
Charlie Brown
Diana Miller
5.2 Using the CONCAT Formula:
 Combine the First Name and Last Name into the Full Name column.
1. Click on the first cell in the "Full Name" column (C2).

=CONCAT(A2, " ", B2)
oThis formula combines the First Name in A2 and the Last Name in B2, separated
by a space (" ").
3. Press Enter.
4. Drag the formula down to apply it to all rows.
Expected Result:
After applying the formula, the "Full Name" column should display:
First Last Name Full Name

Name
Alice Johnson Alice Johnson
Bob Smith Bob Smith
Charlie Brown Charlie Brown
Diana Miller Diana Miller
5.3 Extra Challenge:
 Add a new column called Email and generate email addresses for each student based on
their Full Name.
Example formula:
=CONCAT(A2, ".", B2, "@example.com")

Expected email for Alice Johnson: alice.johnson@example.com
Task 6: Using the NOW and TODAY Formulas

Objective:
Understand and apply the NOW and TODAY formulas to display the current date and time
dynamically.
1. Extend your worksheet or use a new sheet.

2. Create a table like this:
Description Value
Current Date
Current
DateTime
6.2 Using the TODAY Formula:
 Display only the current date.
1. Click on the cell next to Current Date (B2).

=TODAY()
3. Press Enter.
o This will display the current date (e.g., 2024-11-18).
6.3 Using the NOW Formula:
 Display both the current date and time.

1. Click on the cell next to Current DateTime (B3).
=NOW()
3. Press Enter.
o This will display the current date and time (e.g., 2024-11-18 10:30 AM).
6.4 Format the Results:
1. Change Date Format:

o Select the cell with the Current Date.
o Go to the Home tab → Number group → Select Short Date or Long Date
format.
2. Change Time Format:
o Select the cell with the Current DateTime.
o Go to the Home tab → Number group → Select Custom Format and use:
dd/mm/yyyy hh:mm AM/PM
Expected Result:
Your table should look like this:
Description Value
Current Date 2024-11-18
Current 2024-11-18 10:30 AM
DateTime
Task 7: Exploring Excel Auto Fill
Objective:
Learn how to use Auto Fill in Excel to quickly populate cells with patterns, sequences, or
repeated data.
1. Create a new worksheet or extend the existing one.

2. Set up the table below:
Number Weekdays Months Sequence

1 Monday January
2
7.2 Using Auto Fill for Sequences:
 Populate the Number and Sequence columns automatically.
1. Number Column:
o Select the cells A2
oDrag the fill handle (small square at the bottom-right corner of the selection)
down to extend the series (e.g., 1, 2, 3, 4, ...).
2. Sequence Column:
o In D2, type 1.
o In D3, type 3.
o Select both cells and drag the fill handle downward.
o Excel will create a pattern (e.g., 1, 3, 5, 7, ...).
7.3 Using Auto Fill for Text:
 Populate the Weekdays and Months columns automatically.
1. Weekdays:
o In B2, type Monday.
o Drag the fill handle downward to auto-fill the remaining days of the week.
2. Months:
o In C2, type January.
o Drag the fill handle downward to auto-fill the remaining months of the year.
Expected Results:
After applying Auto Fill, the table should look like this:
Number Weekdays Months Sequence
1 Monday January 1
2 Tuesday Februar 3
y
3 Wednesday March 5
4 Thursday April 7
5 Friday May 9
7.4 Auto Fill for Custom Lists:
 Create a custom list (e.g., project names or task names):

1. In a new column, type Task A in the first cell.
2. Drag the fill handle downward.
3. Excel will repeat the text automatically.
Task 8: Exploring Data Formats in Excel

Objective:
Understand and apply different data formats in Excel to visualize and process data correctly.
1. Create a new worksheet or extend the existing one.

2. Set up the table below:
Data Type Example Value Desired Format Formatted Output

General 12345.678 General
Number 12345.678 Number (2
decimals)
Currency 12345.678 $ Currency
Accountin 12345.678 Accounting
g
Date 44561 Short Date
Time 0.5 Time
Percentage 0.123 Percentage
Fraction 0.75 Fraction
Scientific 123456789 Scientific
Text 00123 Text
8.2 Applying Data Formats:
1. General Format:
o Select the cell under Formatted Output for "General".
o Ensure the cell is in the default General format.
2. Number Format:
o Select the cell under Formatted Output for "Number".
o Go to Home → Number group → Select Number.
o Set 2 decimal places.
3. Currency Format:
o Select the cell under Formatted Output for "Currency".
o Go to Home → Number group → Select Currency.
o Choose $ as the currency symbol.
4. Accounting Format:
o Select the cell under Formatted Output for "Accounting".
o Go to Home → Number group → Select Accounting.
5. Date Format:
o Select the cell under Formatted Output for "Date".
o Go to Home → Number group → Select Short Date.
6. Time Format:
o Select the cell under Formatted Output for "Time".
o Go to Home → Number group → Select Time.
7. Percentage Format:
o Select the cell under Formatted Output for "Percentage".
o Go to Home → Number group → Select Percentage.
8. Fraction Format:
o Select the cell under Formatted Output for "Fraction".
o Go to Home → Number group → Select Fraction.
9. Scientific Format:
o Select the cell under Formatted Output for "Scientific".
o Go to Home → Number group → Select Scientific.
10. Text Format:
o Select the cell under Formatted Output for "Text".
o Go to Home → Number group → Select Text.
Expected Results:
After applying the formats, the table should look like this:
Data Type Example Value Desired Format Formatted Output

General 12345.678 General 12345.678
Number 12345.678 Number (2 12,345.68
decimals)
Currency 12345.678 $ Currency $12,345.68
Accountin 12345.678 Accounting $ 12,345.68
g
Date 44561 Short Date 01/01/2022
Time 0.5 Time 12:00 PM
Percentage 0.123 Percentage 12.3%
Fraction 0.75 Fraction 3/4
Scientific 123456789 Scientific 1.23E+08
Text 00123 Text 00123

Lab-5

Uploaded by

Copyright:

Available Formats

Lab-5

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab-5

Uploaded by

Copyright:

Available Formats

LAB NO.

Get familiar with MS Excel.

Lab Activity: Exploring Basic Excel Formulas

 Microsoft Excel (any version).

1. Open Microsoft Excel.

Task 1: Setting Up Data

Name Math Science Englis Total Marks Average Status

Task 2: Using Basic Formulas

 Calculate the Total Marks for each student:

2.2 AVERAGE Formula:

 Calculate the Average Marks for each student:

 Determine the Status of each student (Pass/Fail):

=IF(F2>=80, "Pass", "Fail")

Task 3: Additional Calculations

3.2 MIN Formula:

 Find the lowest marks in the "Science" column:

3.3 COUNT Formula:

 Count the number of students:

Task 4: Formatting the Worksheet

Task 5: Using the CONCAT Formula

5.1 Add the Following Data to Your Worksheet:

1. Extend your current worksheet or use a new sheet.

First Name Last Full Name

5.2 Using the CONCAT Formula:

1. Click on the first cell in the "Full Name" column (C2).

=CONCAT(A2, " ", B2)

First Last Name Full Name

5.3 Extra Challenge:

=CONCAT(A2, ".", B2, "@example.com")

Task 6: Using the NOW and TODAY Formulas

6.1 Add the Following Data to Your Worksheet:

1. Extend your worksheet or use a new sheet.

6.2 Using the TODAY Formula:

 Display only the current date.

1. Click on the cell next to Current Date (B2).

6.3 Using the NOW Formula:

 Display both the current date and time.

6.4 Format the Results:

1. Change Date Format:

dd/mm/yyyy hh:mm AM/PM

Your table should look like this:

Task 7: Exploring Excel Auto Fill

1. Create a new worksheet or extend the existing one.

Number Weekdays Months Sequence

7.2 Using Auto Fill for Sequences:

 Populate the Number and Sequence columns automatically.

7.3 Using Auto Fill for Text:

 Populate the Weekdays and Months columns automatically.

7.4 Auto Fill for Custom Lists:

 Create a custom list (e.g., project names or task names):

Task 8: Exploring Data Formats in Excel

8.1 Add the Following Data to Your Worksheet:

1. Create a new worksheet or extend the existing one.

Data Type Example Value Desired Format Formatted Output

8.2 Applying Data Formats:

Data Type Example Value Desired Format Formatted Output

You might also like