Lab 1:Student Information

?.Create a worksheet on student information: Sheet should contain Student

Name,USN,Marks obtained in different subjects,Total
Marks,Average,MAX,MIN,Total Count of students.Use appropriate formulas to
calculate the above scenario.
Steps:

1. Creating a New Spreadsheet

1. Open Excel:
o Launch Microsoft Excel from your Start menu or desktop shortcut.
2. Create a New Workbook:
o Click on File in the top left corner.
o Select New.
o Choose Blank Workbook or any template you prefer.

2. Inserting Rows and Columns

1. Insert a New Row:

o Select the row below where you want the new row to appear by clicking the row
number on the left side of the sheet.
o Right-click on the selected row number.
o Choose Insert from the context menu. This will insert a new row above the selected
row.
2. Insert a New Column:

o Select the column to the right of where you want the new column to appear by clicking
the column letter at the top of the sheet.
o Right-click on the selected column letter.
o Choose Insert from the context menu. This will insert a new column to the left of the
selected column.

3. Dragging and Filling Data

1. Enter Initial Data:

o Click on a cell and type the initial value (e.g., 1).

2. Use the Fill Handle:

o Hover over the small square (fill handle) in the bottom-right corner of the cell with the
data.
o Click and drag the fill handle down (for rows) or to the right (for columns) to copy the
data or fill a series (e.g., numbers or dates).
 3. AutoFill Options:

o After dragging, you can click on the small AutoFill Options icon that appears to select
different fill options (e.g., fill series, fill without formatting).

4. Using Aggregate Functions

1. Sum:

o Click on the cell where you want the total sum to appear.
o Type =SUM( and then select the range of cells you want to sum up. For example,
=SUM(B2:B10).
o Press Enter.
2. Average:

o Click on the cell where you want the average to appear.

o Type =AVERAGE( and then select the range of cells. For example, =AVERAGE(C2:C10).
o Press Enter.
3. Max:

o Click on the cell where you want the maximum value to appear.
o Type =MAX( and then select the range of cells. For example, =MAX(D2:D10).
o Press Enter.
4. Min:

o Click on the cell where you want the minimum value to appear.
o Type =MIN( and then select the range of cells. For example, =MIN(E2:E10).
o Press Enter.
5. Count:

o Click on the cell where you want the count to appear.

o Type =COUNT( and then select the range of cells with numeric data. For example,
=COUNT(F2:F10).
o Press Enter.
6. COUNTA (Counts non-empty cells):

o Click on the cell where you want the count to appear.

o Type =COUNTA( and then select the range of cells. For example, =COUNTA(A2:A10).
o Press Enter.
OUTPUT:
Lab 2: Employee data

?.Create a worksheet on Employee data,sheet should conatain Name of the

Employee,Employee ID,Basic Salary,TA,DA,Net Pay and Date Of Joining.Perform
Sort&Filter functions on this.
Step 1: Create the Worksheet
1. Open Excel or Google Sheets:

o Start a new spreadsheet.

2. Set Up Your Table:

o In the first row, create headers for each column:

▪ A1: Name of Employee
▪ B1: Employee ID
▪ C1: Basic Salary
▪ D1: TA (Travel Allowance)
▪ E1: DA (Dearness Allowance)
▪ F1: Net Pay
▪ G1: Date of Joining
3. Enter Sample Data:

o Fill in the rows below the headers with sample employee data, as shown in the previous
response. Make sure to leave the "Net Pay" column initially empty; we’ll calculate that
with a formula.

Step 2: Calculate Net Pay

1. Enter the Formula for Net Pay:

o In cell F2 (the first row under Net Pay), enter the formula to calculate net pay:

excel
Copy code
=C2+D2+E2

o This formula adds the Basic Salary, TA, and DA for the employee.
2. Copy the Formula Down:

o Click on the bottom right corner of cell F2 (you’ll see a small square, known as the fill
handle) and drag it down to fill the formula in the rest of the cells in the Net Pay column
(F3, F4, etc.).

Step 3: Format the Worksheet

1. Format Currency:
 o Select the Basic Salary, TA, DA, and Net Pay columns.
o Right-click and choose "Format Cells" (Excel) or "Format" > "Number" > "Currency"
(Google Sheets) to display these as currency.
2. Format Dates:

o Select the Date of Joining column (G).

o Right-click and choose "Format Cells" and select "Date" format to ensure the dates are
displayed correctly.

Step 4: Apply Filter

1. Select the Data:

o Click and drag to highlight all the data in the table, including the header row (A1 to G6 in
this case).
2. Add Filter:

o In Excel: Go to the "Data" tab in the toolbar and click on "Filter." Little drop-down
arrows will appear next to each header.
o In Google Sheets: Click on "Data" in the top menu, then select "Create a filter." The
same drop-down arrows will appear.

Step 5: Sort Data

1. Choose a Column to Sort:

o Click the drop-down arrow in the header of the column you want to sort (e.g., "Net Pay"
or "Date of Joining").
2. Sort Options:

o Sort A to Z: This will sort the data in ascending order (e.g., from lowest to highest for
numbers, or earliest to latest for dates).
o Sort Z to A: This will sort the data in descending order (highest to lowest or latest to
earliest).

Step 6: Filter Data

1. Click the Drop-Down Arrow:

o Click on the drop-down arrow next to a column header (e.g., "Date of Joining").
2. Select Filter Criteria:

o You can check or uncheck specific values to show only the rows that meet your criteria
(e.g., show only employees who joined after a certain date).
3. Apply Multiple Filters:

o You can repeat this process for other columns, allowing you to narrow down your data
based on various criteria (e.g., showing only employees with a Net Pay above a certain
amount).

.
OUTPUT:
LAB 3:Working with data
Working with data:Data validation,Pivot table,Pivot chart .
LAB 4: Scenario Summery
LAB 5:
Dealing data with text function.Use of UPPER,LOWER,TRIM,PROPER,CONCATENATE
functions

1. UPPER

● Function: Converts all letters in a text string to uppercase.

● Syntax: =UPPER(text)
● Example:

2. LOWER

● Function: Converts all letters in a text string to lowercase.

● Syntax: =LOWER(text)
● Example:

3. TRIM

● Function: Removes extra spaces from text, leaving only single spaces between words.
● Syntax: =TRIM(text)
● Example:

4. PROPER

● Function: Capitalizes the first letter of each word in a text string.

● Syntax: =PROPER(text)
● Example:

5. CONCATENATE

● Function: Joins two or more text strings into one string. (Note: In newer versions of Excel, you
can also use CONCAT or TEXTJOIN for more flexibility.)
● Syntax: =CONCATENATE(text1, text2, ...) or =CONCAT(text1, text2, ...)
● Example:
OUTPUT: