Office Automation Lab Manual

Part A

Ex.No .1 Leave Letter using MS-Word

Aim:

To write a leave letter to the principal by using different alignments using MS-Word.

Procedure:

1. Open Microsoft Word and create a new document.

2. Set Up the Document Layout:
o Set margins and choose a suitable font (e.g., Times New Roman, size 12).
o Insert a header or footer if necessary.
3. Begin Writing the Letter:
o Sender’s Address (Right-Aligned):
 Highlight the address and use the Align Right button on the Home tab.

From
Name
[Your Address]
[City, State, Zip Code]

o Principal’s Address (Left-Aligned):

 Align this section to the left.

To
The Principal
[College Name]
[College Address]

o Subject Line (Center-Aligned):

 Center-align the subject line to make it stand out.

Subject: Request for Leave of Absence

o Greeting (Left-Aligned):

Respected Sir/Madam,

o Body of the Letter (Justified):

 Write the body text and justify the alignment for a clean look.

I am [Your Name], a student of class [Your Class and Section]. I am writing

to inform you that I am unable to attend college from [Start Date] to [End
Date] due to [Reason for Leave, e.g., a medical issue, a family function,
etc.].

I kindly request you to grant me leave for [Number of Days] days. I assure
you that I will cover the missed lessons and assignments during my absence.
 I have enclosed a medical certificate from my doctor (if applicable) for
your reference.

I would be grateful if you consider my request and grant me the leave of

absence.

o Closing (Left-Aligned):

Thank you for your understanding and support.

Yours sincerely,

[Your Name]
[Class and Section]

Formatting Tips in MS Word

 Adjust Alignment by selecting the text and using the alignment options in the Home tab
(Left, Center, Right, Justify).
 Use Bold or Italics for emphasis on headings or important parts of the text, such as the
subject line.
 Spacing: Ensure consistent spacing between paragraphs. Use single or 1.15 line spacing for a
neat appearance.

2) Create a bio-data using different alignments and use the page border using MS-Word.

Step-by-Step Guide to Creating a Bio-Data in MS Word

1. Open MS Word:

Launch Microsoft Word on your computer.

2. Set Up the Document:

Margins: Go to the “Layout” tab, click “Margins,” and choose your preferred margin settings or set custom
margins.

Font and Size: Select a readable font (like Times New Roman) and size (usually 12 pt) from the “Home” tab.

3. Apply Page Border:

Go to the “Design” tab.

Click on “Page Borders” in the “Page Background” group.

In the “Borders and Shading” dialog box:

Select the “Page Border” tab.

Choose the style, color, and width of the border.

Decide if you want a border around the whole page or just specific parts.

Click “OK” to apply the border.

4. Create the Bio-Data Content:

5. Review and Save:

Proofread your bio-data for any errors or formatting issues.

Save your document by clicking “File” > “Save As,” and choose a location and file name.

6. Print or Share:

For printing, go to “File” > “Print” and select your print settings.

To share electronically, save as a PDF by choosing “File” > “Save As” and selecting PDF from the file type
options.

By following these steps, you can create a well-organized and visually appealing bio-data document.

3. Create a time table of your class using MS-Word.

1. Open MS Word:

Launch Microsoft Word on your computer.

 2. Set Up the Document:

Margins: Go to the “Layout” tab, click “Margins,” and select your preferred margin settings.

Font and Size: Choose a readable font (like Arial or Times New Roman) and size (usually 12 pt) from the
“Home” tab.

3. Insert a Table for the Timetable:

1. Create the Table:

Go to the “Insert” tab.

Click on “Table” and drag to select the number of rows and columns you need. For a weekly timetable, you
might start with a table that has 6 columns (one for time slots and one for each weekday) and 7 rows (one
for the header and one for each day of the week).

2. Set Up the Table Structure:

After inserting the table, you can adjust its size and formatting by dragging the borders or using the “Table
Design” and “Layout” tabs.

3. Populate the Table:

Fill in the headers for the columns. For a weekly timetable, you might use:

09:00- 09:50- 10.40 -

10:50-11:40 11.40 – 12.30 12:30- 01:00-01:50
09:50 10:40 10.50
DAY 01.00
1 2 3 4 5
MONDAY DM SE R DAA L CS
SA DS KK SF KK
Rm No 1 Rm No 1 Rm No 1 Rm No 1 Rm No 1
TUESDAY R Lab Placement DAA U CC
KK (Tech) SF SA
Short DS Rm No 1 Rm No 1
Break Rm No 1
WEDNESDAY R Lab CS CC N DAA
KK KK SA SF
Rm No 3 Rm No 4 Rm No 1
THURSDAY DAA Lab R SE C CC
SF KK DS SA
Rm No 1 Rm No 1 Rm No 1
FRIDAY DAA Lab SE R H CC
SF DS KK SA
Rm No 1 Rm No 1 Rm No 1
SATURDAY DM R CC SE 12.20-
SA KK SA DS 01.00
Rm No 1 Lab Lab Rm No 1 DAA
SF
Rm No 1

4. Format the Table:

1. Adjust Column Width:

Drag the borders of the columns to fit the content properly.

2. Apply Styles:

Use the “Table Design” tab to apply styles like shading, borders, and font colors to make your timetable
more visually appealing.

3. Merge Cells (Optional):

If you want to merge cells for specific time slots or days, select the cells, right-click, and choose “Merge
Cells.”

4. Add Borders:

To make the table look neat, ensure borders are applied to all cells. Go to the “Table Design” tab, and in the
“Borders” group, select “All Borders.”

5. Enter Your Schedule:

Fill in each cell with the relevant class or activity for that time slot and day. For example

6. Review and Save:

Proofread your timetable to ensure accuracy.

Save your document by clicking “File” > “Save As,” and choose a location and file name.

7. Print or Share:

To print the timetable, go to “File” > “Print” and adjust your print settings.

To share electronically, you may want to save it as a PDF by choosing “File” > “Save As” and selecting PDF
from the file type options.

4. Create documents of your own and write the steps using MS-Word:

a) Insert Pictures

b) Insert Shapes

a) Insert Pictures

1. Open MS Word:

Launch Microsoft Word on your computer and open the document where you want to insert the picture.

2. Position the Cursor:

Place your cursor where you want the picture to appear in your document.

3. Insert the Picture:

Go to the “Insert” tab in the Ribbon.

Click on “Pictures” in the “Illustrations” group.

Choose “This Device” if the picture is saved on your computer. Alternatively, you can select “Stock Images”
or “Online Pictures” to find images from Word’s library or the web.

Browse to the location of the picture on your computer, select it, and click “Insert.”

4. Format the Picture:

After the picture is inserted, you can format it by clicking on it to bring up the “Picture Format” tab.

Use options such as “Crop,” “Resize,” “Wrap Text,” and “Picture Effects” to adjust the appearance and
positioning of your picture.
5. Save Your Document:

Click “File” > “Save” to save the changes to your document.

b) Insert Shapes

1. Open MS Word:

Launch Microsoft Word and open the document where you want to insert the shape.

2. Position the Cursor:

Place your cursor where you want the shape to appear or ensure that the cursor is not positioned in a
specific place if you want the shape to be freely placed.

3. Insert the Shape:

Go to the “Insert” tab in the Ribbon.

Click on “Shapes” in the “Illustrations” group.

A dropdown menu will appear showing different types of shapes (e.g., rectangles, circles, arrows, lines, etc.).

Select the shape you want to insert by clicking on it. The cursor will change to a crosshair.

4. Draw the Shape:

Click and drag on the document where you want the shape to appear. Release the mouse button to create
the shape.

You can adjust the size of the shape by dragging its corners or edges.

5. Format the Shape:

Click on the shape to bring up the “Shape Format” tab.

Use options like “Shape Fill,” “Shape Outline,” and “Shape Effects” to customize the appearance of your
shape.

You can also add text inside the shape by right-clicking the shape and selecting “Add Text,” then typing your
text.
6. Save Your Document:

Click “File” > “Save” to save the changes to your document.

5. Create a documents using MS-Word.

a) The word “MS-Word” as the watermark of the document.

b) Set the background color of the document.

c) Choose the indent tab.

d) Change the space between paragraphs by adding space.

a) Add the Word "MS-Word" as a Watermark

Open MS Word:

Launch Microsoft Word and open a new or existing document.

Insert the Watermark:

Go to the “Design” tab in the Ribbon.

Click on “Watermark” in the “Page Background” group.

Choose “Custom Watermark” at the bottom of the dropdown menu.

Set the Watermark Text:

In the “Printed Watermark” dialog box, select “Text watermark.”

In the “Text” field, type “MS-Word.”

You can customize the font, size, color, and layout of the watermark as desired.

Click “Apply” and then “OK.”

Review Your Document:

The watermark “MS-Word” will now appear behind the text on each page of your document.

b) Set the Background Color of the Document

Open MS Word:

Make sure your document is open.

Set the Background Color:

Go to the “Design” tab in the Ribbon.

Click on “Page Color” in the “Page Background” group.

Choose a color from the palette, or click “More Colors” to select a custom color.

Apply and Save:

The selected background color will apply to the entire document.

Click “File” > “Save” to save your changes.

c) Choose the Indent Tab

Open MS Word:

Open the document where you want to adjust the indent settings.

Access the Indent Settings:

Place the cursor in the paragraph you want to adjust, or select multiple paragraphs.

Go to the “Home” tab in the Ribbon.

Click on the small arrow in the bottom-right corner of the “Paragraph” group to open the “Paragraph” dialog
box.

Set the Indentation:

In the “Paragraph” dialog box, go to the “Indents and Spacing” tab.

Under “Indentation,” you can set values for “Left,” “Right,” and “Special” (such as “First line” or “Hanging”)
indents.

Adjust the measurements as needed and click “OK” to apply.

d) Change the Space Between Paragraphs by Adding Space

Open MS Word:

Open your document.

Adjust Paragraph Spacing:

Select the paragraphs where you want to adjust the spacing.

Go to the “Home” tab in the Ribbon.

Click on the small arrow in the bottom-right corner of the “Paragraph” group to open the “Paragraph” dialog
box.

Under the “Spacing” section:

Use the “Before” and “After” fields to add space before or after the selected paragraphs. For example,
setting “After” to 12 pt will add extra space after each paragraph.

Click “OK” to apply the changes.

6. To prepare students mark sheet with the fields of Name, Register_Number, Mark1, Mark2,

Mark3, Total, Average, Result and Class using MS-Excel.

Steps to Prepare a Student Mark Sheet in MS Excel

1. Open MS Excel:

 Launch Microsoft Excel and open a new blank worksheet.

2. Set Up Column Headers:

 In the first row of the worksheet, enter the column headers for your mark sheet. For example:

A1: Name
B1: Register_Number
C1: Mark1
D1: Mark2
E1: Mark3
F1: Total
 G1: Average
H1: Result
I1: Class

3. Enter Student Data:

 Enter the student data under each respective column. For example:

yaml
Copy code
A2: John Doe
B2: 1001
C2: 85
D2: 90
E2: 78

4. Calculate Total Marks:

 In cell F2, enter the formula to calculate the total marks:

=C2+D2+E2

 Press Enter. Drag the fill handle (a small square at the bottom-right corner of the cell) down
to apply this formula to other rows.

5. Calculate Average Marks:

 In cell G2, enter the formula to calculate the average marks:

=F2/3

 Press Enter. Drag the fill handle down to apply this formula to other rows.

6. Determine Result:

 In cell H2, enter the formula to determine the result based on a passing mark (e.g., 40):

=IF(AND(C2>=40, D2>=40, E2>=40), "Pass", "Fail")

 Press Enter. Drag the fill handle down to apply this formula to other rows.

7. Determine Class:

 In cell I2, enter the formula to assign a class based on the average marks:

=IF(G2>=90, "A", IF(G2>=75, "B", IF(G2>=60, "C", "D")))

 Press Enter. Drag the fill handle down to apply this formula to other rows.

8. Format the Data:

 Headers: Bold the column headers and consider using background color for better visibility.
  Numbers: Format cells in columns F, G, and other numeric columns to show a suitable
number of decimal places if needed. You can do this by selecting the cells, right-clicking, and
choosing “Format Cells” > “Number.”
 Alignment: Adjust text alignment for better readability. You can center-align headers and
right-align numeric data if preferred.

9. Save Your Work:

 Click on “File” > “Save As” and choose a location to save your workbook. Enter a name for
your file and click “Save.”

Sample Mark Sheet Layout

Name Register_Number Mark1 Mark2 Mark3 Total Average Result Class

John Doe 1001 85 90 78 253 84.33 Pass B
Jane Smith 1002 92 88 95 275 91.67 Pass A

Formulas in Cells

 Total (F2): =C2+D2+E2

 Average (G2): =F2/3
 Result (H2): =IF(AND(C2>=40, D2>=40, E2>=40), "Pass", "Fail")
 Class (I2): =IF(G2>=90, "A", IF(G2>=75, "B", IF(G2>=60, "C", "D")))

7. To prepare employees payroll data with the fields of Sl.No. Name, Basic_pay, HRA, DA,

PF, Gross_salary and Net_salary.

a) Calculate HRA (10 % of Basic Pay), DA (25% of Basic Pay), DA (12% of Basic

Pay).

b) Calculate Gross_salary=Basic_pay+HRA+DA.

c) Calcualte Net_salary=Gross_salary-PF

Steps to Prepare an Employees Payroll Data Sheet in MS Excel

1. Open MS Excel:

 Launch Microsoft Excel and open a new blank worksheet.

2. Set Up Column Headers:

  In the first row of the worksheet, enter the column headers for your payroll data. For
example:

A1: Sl.No.
B1: Name
C1: Basic_pay
D1: HRA
E1: DA
F1: PF
G1: Gross_salary
H1: Net_salary

3. Enter Employee Data:

 Enter the employee data under each respective column. For example:

A2: 1
B2: John Doe
C2: 50000
D2: [Formula]
E2: [Formula]
F2: [Enter PF]
G2: [Formula]
H2: [Formula]

4. Calculate HRA (10% of Basic Pay):

 In cell D2, enter the formula to calculate HRA as 10% of Basic Pay:

=C2*0.10

 Press Enter. Drag the fill handle (a small square at the bottom-right corner of the cell) down
to apply this formula to other rows if you have multiple employees.

5. Calculate DA (25% of Basic Pay):

 In cell E2, enter the formula to calculate DA as 25% of Basic Pay:

=C2*0.25

 Press Enter. Drag the fill handle down to apply this formula to other rows.

6. Enter PF Amount:

 Enter the Provident Fund (PF) amount in cell F2. This value will be subtracted from the Gross
Salary to calculate Net Salary.

7. Calculate Gross Salary:

 In cell G2, enter the formula to calculate Gross Salary as the sum of Basic Pay, HRA, and
DA:

=C2+D2+E2
  Press Enter. Drag the fill handle down to apply this formula to other rows.

8. Calculate Net Salary:

 In cell H2, enter the formula to calculate Net Salary as Gross Salary minus PF:

=G2-F2

 Press Enter. Drag the fill handle down to apply this formula to other rows.

9. Format the Data:

 Headers: Bold the column headers and consider using background color for better visibility.
 Numbers: Format cells in columns C, D, E, F, G, and H to show a suitable number of
decimal places if needed. Select the cells, right-click, and choose “Format Cells” >
“Number.”
 Alignment: Adjust text alignment for better readability. You can center-align headers and
right-align numeric data if preferred.

10. Save Your Work:

 Click on “File” > “Save As” and choose a location to save your workbook. Enter a name for
your file and click “Save.”

Sample Payroll Data Layout

Sl.No. Name Basic_pay HRA DA PF Gross_salary Net_salary

1 John Doe 50000 5000 12500 3000 67500 64500
2 Jane Smith 60000 6000 15000 3500 81000 77500

Formulas in Cells

 HRA (D2): =C2*0.10

 DA (E2): =C2*0.25
 Gross_salary (G2): =C2+D2+E2
 Net_salary (H2): =G2-F2

8. Prepare a bar chart and pie chart for analysis of five year results of your college using
MS-Excel.

1. Organize Your Data

 1. Open MS Excel:
o Launch Microsoft Excel and open a new blank worksheet or an existing worksheet
where you want to create the charts.
2. Enter Your Data:
o Organize your data in a table format. For example, if you want to analyze results over
five years, you might have data on the number of students passing each year. Here’s
an example:

A1: Year | B1: Number of Students

A2: 2019 | B2: 120
A3: 2020 | B3: 150
A4: 2021 | B4: 180
A5: 2022 | B5: 200
A6: 2023 | B6: 220

o Enter your data accordingly. Adjust the column headers and values as per your actual
data.

2. Create a Bar Chart

1. Select Your Data:

o Click and drag to select the range of your data, including headers. For the example
above, select cells A1:B6.
2. Insert the Bar Chart:
o Go to the “Insert” tab in the Ribbon.
o In the “Charts” group, click on “Bar Chart.”
o Choose the type of bar chart you prefer, such as “Clustered Bar” or “Stacked Bar.”
3. Customize the Bar Chart:
o After the chart is inserted, you can customize it:
 Chart Title: Click on the default chart title to edit it. For example, change it to
“Number of Students Passing Each Year.”
 Axis Titles: Add axis titles by clicking on the chart, then go to the “Chart
Design” or “Chart Tools” tab and choose “Add Chart Element” > “Axis
Titles” > “Primary Horizontal” and “Primary Vertical.”
 Format Chart: Use the “Chart Design” and “Format” tabs to adjust colors,
styles, and other formatting options.

3. Create a Pie Chart

1. Select Your Data:

o For a pie chart, you might want to analyze the proportion of students passing each
year. Using the same data range, select cells A1:B6.
2. Insert the Pie Chart:
o Go to the “Insert” tab in the Ribbon.
o In the “Charts” group, click on “Pie Chart.”
o Choose the type of pie chart you prefer, such as “2-D Pie” or “3-D Pie.”
3. Customize the Pie Chart:
o After the chart is inserted, you can customize it:
 Chart Title: Click on the default chart title to edit it. For example, change it to
“Proportion of Students Passing Each Year.”
  Data Labels: Add data labels to show percentages or values by clicking on the
chart, then go to “Chart Design” or “Chart Tools” tab and choose “Add Chart
Element” > “Data Labels.”
 Format Chart: Use the “Chart Design” and “Format” tabs to adjust colors,
styles, and other formatting options.

4. Save Your Work

1. Save Your Workbook:

o Click on “File” > “Save As” and choose a location to save your workbook. Enter a
name for your file and click “Save.”

Sample Charts

Here’s how your charts might look:

Bar Chart:

 X-Axis: Years (2019 to 2023)

 Y-Axis: Number of Students
 Bars showing the number of students passing each year.

Pie Chart:

 Pie segments representing each year’s proportion of total students.

 Labels showing either the number or percentage of students for each year.

9. Create a line chart and bar chart using its data series using MS-Excel.

Step 1: Prepare Your Data

Ensure that your data is organized in a table format with headers. For example:

Category Value
A 10
B 20
C 30
D 40

Step 2: Create a Line Chart

1. Select the Data:

o Highlight the data range you want to include in the chart, including headers.
2. Insert Line Chart:
o Go to the Insert tab on the Ribbon.
o Click on the Line or Area Chart button in the Charts group.
 o
Choose the Line chart type (e.g., "Line with Markers").
3. Format the Chart (Optional):
o Customize the chart as needed by adding titles, changing colors, or adjusting the axis
labels.

Step 3: Create a Bar Chart

1. Select the Data:

o Highlight the same data range you used for the line chart.
2. Insert Bar Chart:
o Go to the Insert tab.
o Click on the Bar Chart button in the Charts group.
o Choose the Clustered Bar or Column chart type.
3. Format the Chart (Optional):
o Adjust the appearance, such as adding data labels, changing colors, or resizing the
chart.

Step 4: Arrange the Charts

 Place both charts on the same worksheet for easy comparison.

 Resize and arrange them as needed.

10. To prepare worksheet contains Name and Sales of 10 salesmen. Calculate commission as

per the following:

Sales Commission

First 30,000 5%

Next 40,000 10%

Excess 15%

Step 1: Prepare Your Data

Ensure your data is set up like this in Excel:

Name Sales
Salesman 1 25,000
Salesman 2 45,000
Salesman 3 60,000
 Name Sales
Salesman 4 85,000
Salesman 5 95,000
Salesman 6 120,000
Salesman 7 35,000
Salesman 8 55,000
Salesman 9 70,000
Salesman 10 150,000

Step 2: Calculate Commission

1. Add a Column for Commission:

o In the next column, add a header named "Commission."
2. Enter the Formula for Commission Calculation:
o In the first cell under the "Commission" column (e.g., C2), enter the following
formula:

=IF(B2<=30000, B2*0.05, IF(B2<=70000, (30000*0.05) + ((B2-30000)*0.10),

(30000*0.05) + (40000*0.10) + ((B2-70000)*0.15)))

o Explanation of the Formula:

 If sales are less than or equal to 30,000, the commission is 5% of sales.
 If sales are between 30,001 and 70,000, the commission is calculated as 5% on
the first 30,000 and 10% on the next 40,000.
 If sales exceed 70,000, the commission is calculated as 5% on the first 30,000,
10% on the next 40,000, and 15% on the excess amount over 70,000.
3. Copy the Formula Down:
o Drag the fill handle (small square at the bottom-right corner of the cell) down to fill
the formula for all salesmen.

Example Table with Calculated Commission

Name Sales Commission

Salesman 1 25,000 1,250
Salesman 2 45,000 3,500
Salesman 3 60,000 5,500
Salesman 4 85,000 9,250
Salesman 5 95,000 10,750
Salesman 6 120,000 16,750
Salesman 7 35,000 2,000
Salesman 8 55,000 4,500
Salesman 9 70,000 7,000
Salesman 10 150,000 21,000