1.Graph-Plotting facilities in MS Excel.(Display student results using excel charts.

)
Enter the following details for 10 Students Register Number, Name, Subject1 Marks,Subject2 Marks, Subject3
Marks, Subject4 Marks, Total Marks and Percentage.
Procedure

1. Enter the details for 10 students such as Regno, name, subl marks, sub2 marks, sub3 marks, sub4 marks.
2. Resize the worksheet's rows and columns.

• Select row --> HOME --> Cell --> Format Auto fit Row Height.

• Select column --> HOME --> Cells --> Format --> Auto fit column Width.

3. To center the spreadsheet heading.

• Select A1:I1 --> HOME --> Merge and Center.

4. Create the formula to compute total marks, percentage, highest marks and lowest marks in the respective
cells
5. Draw a bar graph for Register number against total marks

• Select the cells G3:G12

• Insert --> 2 D Bar

• Choose design-->select data

Select Horizontal axis label --> Edit --> select the cells A3:A12 --> ok Select series l --> Edit --> type series
name as total marks
• Layout --> Chart title --> Above the chart --> type "Total marks of a class with register
• number"

•
Layout --> Axis title --> Primary vertical axis --> vertical title --> type Register number
•
Layout --> Axis title --> Primary horizontal axis --> title below axis --> type Total marks
6. Draw a pie chart for one student showing his marks in different subjects from total score
• Select the cells C3: F3

• Insert --> pie --> 2-D pie

• Layout --> Chart title --> Above the chart --> type the respective student name

• Right Click on graph --> select data -->horizontal axis label --> edit --> select c2 to f2 headings -->
ok
2.Writing conditional expressions (using IF) and Using logical functions (AND, OR, NOT) Using
lookup and reference functions (Arithmetic functions, logical functions,text functions).
Procedure
Click on to start program Microsoft office Microsoft excel
Step 1. Type the formula/function as given in the following tables and observe the result. Formula must start
with equal (=) sign.
Arithmetic functions:
=sum() it find the sum of values of a given range
=mod() it find the remainder from the division
=power() it calculates the x power y
=product() it calculates the product
=sqrt() it find the square root of a number
Date Functions:
=date() it returns the serial number of a date. It counts number of days since 1900
=today() it returns today's date.
=day() it returns day from the given date.
=month() it returns month from the given date.
=year() it returns year from the given date.
Time Functions:
=now() it returns the today’s date and time
=time() it returns serial number of time
=hour()it extract the hours form the time
=minute() it extract the minute form the time
=second() it extract the seconds form the time
Logical Functions
=AND()it returns True, if expressions are True, otherwise return FALSE
=OR() it returns TRUE, if expressions are TRUE, otherwise return FALSE
=NOT() it returns TRUE, if expressions are TRUE, otherwise return FALSE
=IF()return first value/statement, if condition is TRUE, otherwise returns second value/statement. i.e. result is
depending on condition status.
=SUMIF() it finds the sum of values in a range which are satisfied by the condition.
Test Functions:
=CHAR() it returns a character for a given code
=CODE() it returns a ASCII value of a character
=LEN() it counts the number of characters in the input text
=UPPER() it converts lower case text into upper case
=LOWER() it converts upper case text into lower case.
 3.Formatting features of MS-Word.-it includes paper-size, margins, header and footer, page no. and
creation of table options.(Time Table).

Procedure
1. Open MS Office-MS Word – File – New

2. Go to Layout- Select any Margin option you like and– Click Ok.

3. Go to Layout-select paper-size from SIZE.

4. Go to Insert-add header, footer and pageno.

5. Go to Table-Insert-Table- chose Number of Columns and Rows as per the data to be entered.(Both
education qualification and language known)
6. Enter the details, format it (bold and increase the font size via standard tool Bar).

7. When ever you want to increase the number of column in the existing row, Select that row and go to
Table-click Split Cells- enter number of columns- click Ok.
8. In order to decrease the existing column numbers, select that columns and Go to Tables- click Merge
cells.
4.Create a Visiting Card of your college using page size as follows
i)Page width=”3.2” ii)Page height=”2.2” And use different font styles,sizes, alignments.

Procedure:
Step 1: Open MS-Word by click on START button; go to All Programs, then select Microsoft Office Word
2007.
Step 2: To open a new document, Click on Office Button then select New - > Blank Document then click on
create option.
Step 3: Now click on “Page Layout” from the Menu bar. Then click on Margins then click on Custom
Margins option. Then the “Page Setup” dialog box appears. In this you find three tabs namely “Margins”,
”Paper”, ”Layout”. Then in the ‘Margins’ tab, make all the parameters like Top, Bottom, Left, Right, and
Gutter to zero and make Gutter Position to Left. Then in the Page tab, change the width and height options to
3.2 and 2.2 respectively. Then in the Layout tab, make the Header and Footer to zero. Now this page is set to
the visiting card as follows. Step 4: In this step we have to enter the telephone number and Fax number on
the top part of the paper. It can be done as follows:
• First go to Insert menu, then select Symbol option.
• Then change Font to “Windings”.
• Then select the appropriate to your need i.e., to the telephone option select , and to
• The Fax option select .
Step 5: Now write your institution name and make it to the center alignment button .
Step 6: Now write all the details you want to put in your visiting card as your needs. And select the text and
make it to center .
Step 7: Now change the background color by selecting Page color option from Page Layout menu.
Step 8: This is the final step in creating Visiting Card. In this step, we have to save the letter as
“Visiting Card.docx” by selecting “Save” option from Office button. Then a prompt window will ask you to
write a file name. Now you have to give the file name and press the save button.
5.Use of Statical functions in MS-Excel

Maximum (MAX)
Select the cell where you want the maximum value to appear.
Type =MAX( and then select the range of cells or type the cell references you want to find the maximum
from.
Close the parentheses and press Enter.
Minimum (MIN):
Select the cell where you want the minimum value to appear.
Type =MIN( and then select the range of cells or type the cell references you want to find the minimum from.
Close the parentheses and press Enter.
Mean (AVERAGE):
Select the cell where you want the mean (average) value to appear.
Type =AVERAGE( and then select the range of cells or type the cell references you want to find the mean of.
Close the parentheses and press Enter.
Median (MEDIAN):
Select the cell where you want the median value to appear.
Type =MEDIAN( and then select the range of cells or type the cell references you want to find the median of.
Close the parentheses and press Enter.
Standard Deviation (STDEV):
Select the cell where you want the standard deviation value to appear.
Type =STDEV( and then select the range of cells or type the cell references you want to find the standard
deviation of.
Close the parentheses and press Enter.
Count (COUNT):
Select the cell where you want the count value to appear.
Type =COUNT( and then select the range of cells or type the cell references you want to count.
Close the parentheses and press Enter.
 6.Data Validations :Specifying a valid range of values for a cell, Specifying a list of valid values for a
cell, Specifying custom validations based on formula for a cell, Sorting and Filtering Data facility in
MS Excel.(Employees of a company)

Filtering:

1. Select your data: Click on any cell within your dataset. If your data has headers, make sure to include
them in your selection.
2. Navigate to the Data tab: Click on the "Data" tab in the Excel ribbon at the top of the window.
3. Click on Filter: In the "Sort & Filter" group, click on the "Filter" button. This will add filter arrows to
the headers of your selected data.
4. Use filter arrows: Click on the filter arrow icon next to the column header you want to filter. A
dropdown menu will appear with various options depending on the type of data in that column.
5. Select filtering options: Choose the filtering options you want to apply. You can filter by specific
values, sort alphabetically or numerically, apply text filters, date filters, and more.
6. Apply multiple filters: You can apply filters to multiple columns simultaneously by clicking on the
filter arrows of different columns and selecting your desired filtering options.
7. Clear filters: To remove filters, click on the filter arrow of the filtered column and select "Clear Filter
From [Column Name]" or click on the "Filter" button in the Data tab to remove all filters.

Sorting:

1. Select your data: Click on any cell within your dataset. If your data has headers, include them in your
selection.
2. Navigate to the Data tab: Click on the "Data" tab in the Excel ribbon.
3. Choose a sorting method: In the "Sort & Filter" group, you have several options:
o Sort A to Z: Sorts selected data in ascending order.
o Sort Z to A: Sorts selected data in descending order.
o Custom Sort: Allows you to specify sorting criteria for multiple columns.
4. Sort A to Z or Z to A: Click on either "Sort A to Z" or "Sort Z to A" depending on your preference.
This will sort the selected data based on the column that's currently active.
5. Custom Sort:
o Click on "Sort" to open the Sort dialog box.
o In the Sort dialog box, specify the sorting options for each column.
o You can add additional sorting levels and choose sorting order (ascending or descending) for
each level.
o Click "OK" to apply the custom sorting.

1. Whole Number Validation:

▪ Select the cell or range of cells where you want to apply the validation.
▪ Go to the "Data" tab in the Excel ribbon.
▪ Click on "Data Validation" in the "Data Tools" group.
▪ In the Data Validation dialog box, select "Whole number" from the Allow dropdown
menu.
▪ Optionally, set the minimum and maximum values allowed for the whole numbers.
▪ Click "OK" to apply the validation.

2. Text Length Validation:

▪ Select the cell or range of cells where you want to apply the validation.
▪ Go to the "Data" tab in the Excel ribbon.
▪ Click on "Data Validation" in the "Data Tools" group.
▪ In the Data Validation dialog box, select "Text length" from the Allow dropdown menu.
▪ Specify the minimum and/or maximum text length allowed.
▪ Click "OK" to apply the validation.
3. Date Validation:
▪ Select the cell or range of cells where you want to apply the validation.
▪ Go to the "Data" tab in the Excel ribbon.
▪ Click on "Data Validation" in the "Data Tools" group.
▪ In the Data Validation dialog box, select "Date" from the Allow dropdown menu.
▪ Specify the start and end dates if needed.
▪ Optionally, choose a date format from the Data dropdown menu.
▪ Click "OK" to apply the validation.

4. List Validation:
▪ Create a list of valid entries somewhere in your worksheet.
▪ Select the cell or range of cells where you want to apply the validation.
▪ Go to the "Data" tab in the Excel ribbon.
▪ Click on "Data Validation" in the "Data Tools" group.
▪ In the Data Validation dialog box, select "List" from the Allow dropdown menu.
▪ Click in the "Source" box and either type the range of cells containing your list or select
them using your mouse.
▪ Optionally, check the "In-cell dropdown" box to allow users to select from a dropdown
list.
▪ Click "OK" to apply the validation.