SIRSA (HARYANA)

BCA - 118

Practical file on
MS Word and MS excel

Submmited To : Submmited By :

Somveer Sir Amarjeet Verma

19470019

BCA ( 1ST Sem. )

INDEX
Sr Practical’s Name Date Signature
No.
1. Explain the component of ms word. 2/8/19
2. Explain the steps to import a file 6/8/19
using suitable example.
3. What is template write the steps to 19/8/19
create a template.
4. How can you insert drop cap in ms 28/8/19
document.
5. What do you mean by theme in ms 3/9/19
document.
6. Explain various steps to draw a flo9w 11/9/19
chart in document.
7. What do you mean by macros how 17/9/19
can you use and create it.
8. Explain the component of ms excel 19/9/19
9. Explain various steps to split data into 3/10/19
multiple coloums in excel
10. Explain [if] function by using by 9/10/19
example.
11. What is data table, explain the steps 18/10/19
to create one variables and two
variables data table by using giving
suitable example.
12. Explain various strategic functions. 24/10/19
Practical No. 1
Q.Expiain the component of word docoument (window)

The various components of a word document are –

• Menu Bar – it has file, edit, view, insert, format, tools, table,
window and help menus
• Standard Toolbar – It has shortcut icons to the menu
commands
• Formatting Toolbar – It has various menus for style, font
and font-size, bullets and number, border icons etc.
• Ruler – to set tabs, paragraph alignment, other formats.
And many other components.
Practical No. 2
Q.Explain the steps to import a file using suitable
example
To import an MS Word file to your project, select the
corresponding import option in the Select import type list,
locate the necessary file on your computer and click
the OK button as shown in the screenshot below:

• After you click the OK button, it will display a file format

conversion window:

• This means that it will first try to convert your original

Microsoft Word document (either in .doc or .docx format)
into an HTML document. If the conversion process is
successful, then it will display the Document styles matching
dialog. If you have problems during this step, cancel import,
open the document in Microsoft Word, save it as RTF
and import the RTF file instead.

• Then, configure document matching styles and click

the OK button to continue.

• It will start processing the converted HTML file. It will show

the progress and display the results in a separate window

• When the import is finished, click the OK button to close this

window and start working on imported data in It.
Practical No. 3
Q.What is template and write the stewps to create a
template
Microsoft Word allows you to create documents of many
different types with a great deal of customization. However,
there are occasions where you may need to repeatedly create
the same type of document. Word makes this task easier by
allowing you to create a template of your documents that
requires only a little editing with each use. Find out how to
create a template in Microsoft Word 2007 after the jump.

Launch Microsoft Word 2007.

• You can double-click on a desktop shortcut or locate it in
the list of installed programs by clicking the Start button on
your Windows desktop.
• Mac users can find Word 2007 in the dock at the bottom of
the desktop screen.
Open a document that will function as the base for your
template.
• Click on the Office button and select Open from the menu.
Double-click on the name of the file you need.
• If you want to create a template from a blank document,
click the Office button, choose "New" and double-click the
blank document icon.

Choose "Word Template" from the slide-out menu.

• A window will launch allowing you to name your
document template, choose where it will be saved and
change the document type.
• Click "Templates" under the "Favorite Links" listing in the
left pane of this pop-up window.

Name your document template.

• Make sure that the "Save as type" option is set to "Word
Template (*.dotx)" below the file name.
• You can also maintain compatibility with earlier versions
of Word and save a thumbnail to represent the file by
checking the appropriate boxes.

Save the document template by clicking the "Save"

button.
The "Save As" window will close.
Use your template when creating future documents.
• Click the Office button, choose "Templates" in the left
pane of the pop-up window and select your template from
the files available.
• Save the template as a regular Word 2007 document in the
appropriate place and with a unique file name.
Practical No. 4
Q.How can you insert drop cap in the insert.
Drop caps are a decorative touch, done through typographical
means, that you can apply to your document. Drop caps are
traditionally done with the first letter of a chapter or some
other major section of a document. To create drop caps, do the
following:

1. Type your paragraph as you normally would.

2. Make sure the insertion point is somewhere within the
paragraph.
3. Display the Insert tab of the ribbon.
4. In the Text group, click Drop Cap. Word displays some
common options for drop caps.
5. Choose Drop Cap Options. Word displays the Drop Cap
dialog box.
6. Chose the position for your drop cap, according to your
preference.
7. Change any other options to format your drop cap.
8. Click on OK.
Practical No. 5
Q.What do you mean by theme explain.

Introduction

Styles and themes are powerful tools in Word that can help
you create professional-looking documents easily. A style is a
predefined combination of font style, color, and size of text
that can be applied to selected text. A theme is a set of
formatting choices that can be applied to an entire
document and includes theme colors, fonts, and effects.
In this lesson, you will learn how to apply, modify, and create
a style; use style sets; apply a document theme; and create a
custom theme.Using styles and themes
To select a style:
• Select the text to format. In this example, the title is
selected.
• In the Style group on the Home tab, hover over each
style to see a live preview in the document. Click
the More drop-down arrow to see additional styles.
• Left-click a style to select it. Now the selected text
appears formatted in the style.

You can also use styles to create a table of contents for your
document. To learn how, review our article on How to Create
a Table of Contents in Microsoft Word.

To modify a style:
• Select the text in the style you want to change. In this
example, we are changing AdWorks Agency, which
has the Title style applied.
• Locate the style in the Styles group.
• Right-click the style, and a menu appears.
• Left-click Modify, and the Modify Style dialog box
appears.
• Change any of the formatting.
• Click OK to apply the modifications to the style.
Practical No. 6
Q. Explain various steps to draw a flow chart in ms word.
• Flowcharts are a very good way to get a new idea explained in
a presentation. Office 2007 has some great new tools for
creating a visual appealing flowchart which can be used from
Excel, Word or PowerPoint. Here we will take a look at
creating a simple flowchart in Word 2007.

• For the sake of this article I am going to start at the top of the
document on the right side under the title. Then
click Insert then Shapes on the Ribbon to select a shape
from the Flowchart section. You can then draw an outline for
the size of this object on the document. You then will see
how it will look right away.
• To link the shapes on your flow chart there is a multitude of
options. Here I am going to use an arrow. To efficiently
create the flow chart, it is best to get the basic design first
before worrying about the graphic effects. You can always
add the effects later.

• I choose a quick format for the first shapes, drawn the arrow,
and am ready to set up more sections. I just copy and paste
the first box and drag it to the appropriate position.After
getting accustomed to selecting shapes and adjusting their
positions it’s probably a good idea to start adding text. To
add text to the chart simply Right Click on a box and
select Add Text from the menu and begin typing away.
• The mini formatting toolbar is still available with any text you
enter into the chart which comes in handy if you are use to it.
If you are not a fan and want to stop it, you can make the
mini formatting toolbar stop popping up.

• Once you have the basic outline of the flowchart complete,

it’s time to add some “pizzazz” to everything. This is done
using the Format tab on the Ribbon. Here are a few shots of
what you can do. This is the different colors you can make
the shapes.

• Here are Shadow and 3D effects. The cool thing with Office
2007 is you can just hover the mouse over the different effects
and get a preview of how it will look in the document. This
saves a lot of time rather than relying on a trial and error
approach.

• Another feature is inserting Smart Art. This allows you to

insert nice pre-designed graphic layouts. You do not need to
be graphic design expert to insert professional looking
content.

• A handy window opens up where you can insert text into the
chart which in this case is Radial Cycle. You will see the text
as it would appear in the smart art at the same time it’s
entered.

• A good way to help the graphics in your chart stand out better
is to change the background color of the document. This is
done from Page Layout and Page Background sections on
the Ribbon.
• This is a simple flowchart made with the steps and different
options shown above.
Practical No. 7
Q.What do you mean by macros how can you create
and use it.
To create a macro to perform these steps, first highlight some
text in your document, then click on the View tab on the main
ribbon, then click on the tiny down arrow under
the Macros icon to get the following drop-down menu..
• Click where it says Record Macro and you should get a
screen like this:
Type in a name for your macro, then click on the Button icon
• Click on the new macro name listed on the left side of the
screen (under where it says <separator>) to highlight it, then
click the Add button to add the macro to your Quick Access
Toolbar , then click the OK button.

• The macro setup window will go away and you will be taken
back to your document and your cursor will have been turned
into a tiny picture of an old fashioned tape recorder. This
means your macro is recording, so now it’s time to do the
things you want recorded.
1. Click on the Current Font box and select Ariel.
2. Click on the Font Size box and select 16.
3. Click on the Bold and Italics icons.

• That’s all you want your macro to record, so to make it stop

recording, click on the View tab again from the main ribbon,
then click on Macros, and then Stop recording.
• Your macro should now be ready for use. To test it, highlight
some text, then look at the Quick Access Toolbar; on it there
should be a new Macro icon:
• To test your macro, highlight some text, then click on the new
Macro icon on your Quick Access Toolbar. Your macro
should run and perform all of the formatting tasks you
assigned it to do.
• There are of course more options and ways to record macros
in Word, but this is by far the easiest and simplest. Enjoy!
Practical No.8
Q.Explain the component of workbook in ms excel

• Active Cell
In an Excel 2007 worksheet, you click on a cell to make it
the active cell. It displays a black outline.

• Office Button
Clicking on the Office Button displays a drop-down menu
containing a number of options, such as Open, Save, and
Print. The options in the Office Button menu are similar to
those found under the File menu in previous versions of
Excel.

• Ribbon
The Ribbon is the strip of buttons and icons located above the
work area in Excel 2007. The Ribbon replaces the menus and
toolbars found in earlier versions of Excel.

• Column Letter
Columns run vertically on a worksheet and each one is
identified by a letter in the column header.

• Row Numbers
Rows run horizontally in a worksheet and are identified by a
number in the row header.
Together a column letter and a row number create a cell
reference. Each cell in the worksheet can be identified by this
combination of letters and numbers such as A1, F456, or
AA34.

• Formula Bar
The Formula bar is located above the worksheet. This area
displays the contents of the active cell. It can also be used for
entering or editing data and formulas.

• Name Box
Located next to the formula bar, the Name Box displays the
cell reference or the name of the active cell.
• Sheet Tabs
By default, there are three worksheets in an Excel 2007 file.
There can be more. The tab at the bottom of a worksheet tells
you the name of the worksheet, such as Sheet1 or Sheet2. You
switch between worksheets by clicking on the tab of the sheet
you want to access.Renaming a worksheet or changing the tab
color can make it easier to keep track of data in large
spreadsheet files.

• Quick Access Toolbar

This customizable toolbar allows you to add frequently used
commands. Click on the down arrow at the end of the toolbar
to display the available options
Practical No. 9
Q.Explain various steps to split data into multiple
coloum in excel
Use the Convert Text to Columns Wizard in Excel 2007 when
you need to split combined data into separate columns, such
as a first name and last name; or city, state, and zip code. This
type of combined data often results when you open or import
files created in another application.

If necessary, insert blank columns to the right of the cells you

want to convert into multiple columns.
If you want your data in three columns, you must have two
blank columns.
• Select the cells you want to convert.
You can’t split empty cells, and you can’t split merged cells.
You must first unmerge the cells.
• Click the Text to Columns button on the Data tab.
The Convert Text to Columns Wizard appears.
• Select the Original Data type that best suits your existing
data.
For example, if you’re separating text that is variable in length
such as a first name and last name, select Delimited.
• Click Next.
The option you see next depends on which data type you
selected in the previous step.
• If you selected Delimited, enter the character you use to
separate your text.
If you selected Fixed Width, click the ruler bar where you
want the data to split.
• Click Finish.
Excel separates the selected cells into multiple columns.
Practical No. 10
Q.Explain (if) function using by example.
IF function is one of the most popular functions in Excel, and
it allows you to make logical comparisons between a value
and what you expect.
So an IF statement can have two results. The first result is if
your comparison is True, the second if your comparison is
False.

• For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then

return a 1, otherwise return a 2).
Simple IF examples
=IF(C2=”Yes”,1,2)
In the above example, cell D2 says: IF(C2 = Yes, then return a
1, otherwise return a 2)
=IF(C2=1,”Yes”,”No”)

• In this example, the formula in cell D2 says: IF(C2 = 1, then

return Yes, otherwise return No)As you see, the IF function
can be used to evaluate both text and values. It can also be
used to evaluate errors. You are not limited to only checking
if one thing is equal to another and returning a single result,
you can also use mathematical operators and perform
additional calculations depending on your criteria. You can
also nest multiple IF functions together in order to perform
multiple comparisons
• =IF(C2>B2,”Over Budget”,”Within Budget”)In the above
example, the IF function in D2 is saying IF(C2 Is Greater
Than B2, then return “Over Budget”, otherwise return
“Within Budget”)
• =IF(C2>B2,C2-B2,0)
In the above illustration, instead of returning a text result, we
are going to return a mathematical calculation. So the formula
in E2 is saying IF(Actual is Greater than Budgeted, then
Subtract the Budgeted amount from the Actual amount,
otherwise return nothing).
=IF(E7=”Yes”,F5*0.0825,0)
In this example, the formula in F7 is saying IF(E7 = “Yes”,
then calculate the Total Amount in F5 * 8.25%, otherwise no
Sales Tax is due so return 0)
Practical No. 11
Q.What is the data table and explain the steps to create
one variable and two variable data table by giving
suitable example.

• What is a data table in Excel?

• In Microsoft Excel, a data table is one of the What-If Analysis
tools that allows you to try out different input values for formulas
and see how changes in those values affect the formulas output.
• Data tables are especially useful when a formula depends on
several values, and you'd like to experiment with different
combinations of inputs and compare the results.
• Currently, there exist one variable data table and two variable
data table. Although limited to a maximum of two different input
cells, a data table enables you to test as many variable values as
you want.
• Note. A data table isn't the same thing as an Excel table, which is
purposed for managing a group of related data. If you are looking
to learn about many possible ways to create, clear and format a
regular Excel table, not data table, please check out this
tutorial: How to make and use a table in Excel.

• How to create a one variable data table in Excel

• One variable data table in Excel allows testing a series of values
for a single input cell and shows how those values influence the
result of a related formula.
• To help you better understand this feature, we are going to follow
a specific example rather than describing generic steps.
• Suppose you are considering depositing your savings in a bank,
which pays 5% interest that compounds monthly. To check
different options, you've built the following compound interest
calculator where:
• B8 contains the FV formula that calculates the closing balance.
• B2 is the variable you want to test (initial investment).

And now, let's do a simple What-If analysis to see what your

savings will be in 5 years depending on the amount of your initial
investment, ranging from $1,000 to $6,000.

• Here are the steps to make a one-variable data table:

1. Enter the variable values either in one column or across one row.
In this example, we are going to create a column-oriented data
table, so we type our variable values in a column (D3:D8) and
leave at least one blank column to the right for the outcomes.
2. Type your formula in the cell one row above and one cell to the
right of the variable values (E2 in our case). Or, link this cell to
the formula in your original dataset (if you decide to change the
formula in the future, you would need to update only one cell).
We choose the latter option, and enter this simple formula in E2

Tip. If you want to examine the impact of the variable values on

other formulas that refer to the same input cell, enter the
additional formula(s) to the right of the first formula, as shown
in this example.
3. Select the data table range, including your formula, variable
values cells, and empty cells for the results (D2:E8).
4. Go to the Data tab > Data Tools group, click the What-If
Analysis button, and then click Data Table…

5. In the Data Table dialog window, click in the Column Input

cell box (because our Investment values are in a column), and
select the variable cell referenced in your formula. In this
example, we select B3 that contains the initial investment value.

6. Click OK, and Excel will immediately populate the empty cells
with outcomes corresponding to the variable value in the same
row.
7. Apply the desired number format to the results (Currency in our
case), and you are good to go!
Now, you can take a quick look at your one-variable data table,
examine the possible balances and choose the optimal deposit
size:
Row-oriented data table
• The above example shows how to set up a vertical, or column-
oriented, data table in Excel. If you prefer a horizontal layout,
here's what you need to do:
1. Type the variable values in a row, leaving at least one empty
column to the left (for the formula) and one empty row below (for
the results). For this example, we enter the variable values in cells
F3:J3.
2. Enter the formula in the cell that is one column to the left of your
first variable value and one cell below (E4 in our case).
3. Make a data table as discussed above, but enter the input value
(B3) in the Row input cell box:

4. Click OK, and you will have the following result:

• How to make a two variable data table in Excel

A two-variable data table shows how various combinations of 2

sets of variable values affect the formula result. In other words, it
shows how changing two input values of the same
formula changes the output.
The steps to create a two-variable data table in Excel are basically
the same as in the above example, except that you enter two
ranges of possible input values, one in a row and another in a
column.
To see how it works, let's use the same compound interest
calculator and examine the effects of the size of the initial
investment and the number of years on the balance. To have it
done, set up your data table in this way:
1. Enter your formula in a blank cell or link that cell to your original
formula. Make sure you have enough empty columns to the right
and empty rows below to accommodate your variable values. As
before, we link the cell E2 to the original FV formula that
calculates the balance: =B8
2. Type one set of input values below the formula, in the same
column (investment values in E3:E8).
3. Enter the other set of variable values to the right of the formula, in
the same row (number of years in F2:H2).
At this point, your two variable data table should look similar to
this:

4. Select the entire data table range including the formula, the row
and column of the variable values, and the cells in which the
calculated values will appear. We select the range E2:H8.
5. Create a data table in the already familiar way: Data tab > What-
If Analysis button > Data Table…
6. In the Row input cell box, enter the reference to the input cell for
the variable values in the row (in this example, it's B6 containing
the Years value).
7. In the Column input cell box, enter the reference to the input cell
for the variable values in the column (B3 containing the Initial
Investment value).
8. Click OK.

9. Optionally, format the outputs the way you need (by applying
the Currency format in our case), and analyze the results:
Practical No. 12
Q. Explain Various strategic function using example

Introduction

• A function is a predefined formula that performs

calculations using specific values in a particular order. While
you may think of formulas as being short mathematical
equations, like 2+2 or F2*C2, they can actually be very
lengthy and involve complex mathematical calculations.

• One of the key benefits of functions is that they can save you
time because you do not have to write the formula yourself.
For example, you could use an Excel function
called Average to quickly find the average of a range of
numbers or the Sum function to find the sum of a cell range.

• In this lesson, you will learn how to use basic functions such
as SUM and AVERAGE, use functions with more than one
argument, and access other Excel 2007 functions.

Basic functions

The parts of a function:

Each function has a specific order, called syntax, which must
be strictly followed for the function to work correctly.

Syntax order:
1. All functions begin with the = sign.
2. After the = sign, define the function name (e.g.,
Sum).
3. Then there will be an argument. An argument is the
cell range or cell references that are enclosed by
parentheses. If there is more than one argument,
separate each by a comma.

An example of a function with one argument that adds a range

of cells, A3 through A9:

An example of a function with more than one argument that

calculates the sum of two cell ranges:

Excel literally has hundreds of different functions to assist

with your calculations. Building formulas can be difficult and
time consuming. Excel's functions can save you a lot of time
and headaches.

Excel's different functions

There are many different functions in Excel 2007. Some of the
more common functions include:

Statistical functions:
• SUM: Adds a range of cells together
• AVERAGE: Calculates the average of a range of
cells
• COUNT: Counts the number of chosen data in a
range of cells
• MAX: Identifies the largest number in a range of
cells
• MIN: Identifies the smallest number in a range of
cells

Financial functions:
• Interest rates
• Loan payments
• Depreciation amounts

Date and time functions:

• DATE: Converts a serial number to a day of the
month
• Day of Week
• DAYS360: Calculates the number of days between
two dates based on a 360-day year
• TIME: Returns the serial number of a particular time
• HOUR: Converts a serial number to an hour
• MINUTE: Converts a serial number to a minute
• TODAY: Returns the serial number of today's date
• MONTH: Converts a serial number to a month
• YEAR: Converts a serial number to a year

You don't have to memorize the functions, but you should

have an idea of what each can do for you.

To calculate the sum of a range of data using AutoSum:

• Select the Formulas tab.
• Locate the Function Library group. From here, you
can access all available functions.
• Select the cell where you want the function to appear.
In this example, select G42.
• Select the drop-down arrow next to
the AutoSum command.
• Select Sum. A formula will appear in the selected
cell, G42.
o This formula, =SUM(G2:G41), is called
a function. The AutoSum command automatically
selects the range of cells from G2 to G41, based on
where you inserted the function. You can alter the
cell range if necessary.
• Press the Enter key or Enter button on the formula bar.
The total will appear.
Excel will not always tell you if your formula contains an
error, so it's up to you to check all of your formulas. To learn
how to do this, read the Double-Check Your
Formulas lesson from our Excel Formulas tutorial.

To edit a function:
• Select the cell where the function is defined.
• Insert the cursor in the formula bar.
• Edit the range by deleting and changing necessary cell
numbers.
• Click the Enter icon.
To calculate the sum of two arguments:
• Select the cell where you want the function to
appear—in this example, G44.
• Click the Insert Function command on the Formulas
tab. A dialog box appears.
• SUM is selected by default.
• Click OK, and the Function Arguments dialog box
appears so you can enter the range of cells for the
function.
• Insert the cursor in the Number 1 field.
• In the spreadsheet, select the first range of cells—in
this example, G21 through G26. The argument appears
in the Number 1 field.
o To select the cells, left-click cell G21 and drag the
cursor to G26, then release the mouse button.
• Insert the cursor in the Number 2 field.

• In the spreadsheet, select the second range of cells—in

this example, G40 through G41. The argument appears
in the Number 2 field.
• Notice that both arguments appear in the function in
cell G44 and the formula bar when G44 is selected.
• Click OK in the dialog box, and the sum of the two
ranges is calculated.

To calculate the average of a range of data:

• Select the cell where you want the function to appear.
• Click the drop-down arrow next to the AutoSum
command.
• Select Average.
• Click on the first cell (in this example, C8) to be
included in the formula.
• Left-click and drag the mouse to define a cell range
(C8 through cell C20, in this example).
• Click the Enter icon to calculate the average.