Notes on Microsoft Office Xp for BCA-104

Questions and Answer Based

Windows
1) What is window? What are the components of a Windows?
What do you mean by GUI?
Ans:- When a screen is split into several independent regions each one is called a window. Several
applications can display result simultaneously in different windows. Windows that graphically
display what the computer is doing.

Components of a Window are:

(1) Control menu box:- It is in the upper-left corner of each windows. Clicking on the control-
menu box opens the control menu. The control menu can be used to Move, size and close a
Windows while working with the keyboard.
(2) Title Bar:- It shows the name of the application document or a folder name.
(3) Menu Bar:- It lists the available menus. A menu contains a list of actions or commands.
(4) Scroll bar:- They are used to move through a documents or a list when the entire document or
list does not fit in the windows.
(5) Maximize Button:- Clicking this button using the mouse enlarges the active windows so that it
fills the entire desktop.
(6) Minimize button:- Clicking this button reduces the window to an icon and arranges it on the
desktop minimizing the application windows does not quit the application.
(7) Restore Button:-The maximize button is replaced by the restore button this button returns the
windows to list previous size.
(8) Window border:- It is the outside edge of the window. The windows can be resized by long
tening or shortening the border.
(9) Insertion point:- It is a flashing vertical bar that marks the place where text or graphics are to
appear on typing or drawing.
(10) Mouse pointer:-an arrow used for pointing items. It appears if the mouse is installed on the
system.
(11) Client area:- area inside the windows which is under the application control.

GUI:- GUI are systems that allow certain manipulation of user interface employing windows menus,
icons, dialog boxes, mouse and keyboard. Macintosh toolbar, Microsoft windows and X-
windows are some examples of GUIS.

2) Explain features of windows operating systems?

Page 1 of 48
Ans:-
The features of windows operating system are:-
i) Common Look and Feel:- All windows applications have the same basic look and feel. Once
one know one or two windows applications. It is easy to learn another one.
ii) Device Independence:- Windows present a device independent interface to applications.
Unlike most of today’s Dos application , a windows applications is not bound to the underlying
hardware such as mouse, keyboard or display, windows shields the application deals with the
windows API to manipulate any underlying device.
iii) Multitasking:- Windows provides non-pre-emptive multitasking support, users can have
several applications in progress at the same time. Each application can be active in a separate
window.
iv) Memory Management:- Windows also provides memory management to break the 640k
limitation MS-DOS. An application has the ability to use the extended memory shorted data
segments with other application and swap unwanted segments to disk.
v) Support for existing DOS Applications:-Windows allows most standard Dos application that
does not control the PC’s hard ware, use the PC BIOS or MS-DOS software interrupts can run
in its own window.
vi) Data sharing:- Windows allows data transfer application clipboard. Any data type can be
transferred from one Windows with the clip board. The dynamic data exchange (DDE) protocol
defines how two Applications can share information. Information such as bitmap, metafile,
character strings and other data formats can be shared.
vii) Support for object orientation:- several application can share the same windows
simultaneously. The window handles all Messages such as re-drawing the serene display icons
or pop-up menus and changing the contents of the client area. This facilitates the process of
application develop. An application can choose to display the windows resize the window,
display addition information in the client area and so on.

3) Define purpose of control panel, my computer, and my document

and recycle bin, Accessories and their components (Notepad,
Calculator, MS-Paint etc.)
Ans:-
i) Control Panel:- The most crucial component of windows O.s is the control Panel: the
backstage, the operation room, from where one can configure once computer. Changes colors,
add/remove software, change system settings etc. It offers a host of smart utilities. Some of
them are essential and useful some are informative and entertaining, this is one window
through which one can customize once windows interface.
ii) My computer:- All the computer resources can be accessed from the my computer folder.
To access my computer, double-click on its icons.
One cannot delete the icons in my computer windows as they are default and persistent icons
displayed after loading the OS. My computer icons is a gateway to particular zones of once
system the disk drivers. CD-ROM, printer and the other miscellaneous accessories and features
collected within control panel.

iii) My Documents:- It provides a convenient place to store documents, graphics or other files one
want to access quickly. On the desktop, it is represented by a folder with a sheet of paper in it.
Page 2 of 48
 When one save a file in a program such as word pad or paint the file is automatically saved in
my Documents unless one choose a different location.
iv) Recycle bin:- Windows data store deleted files in Recycle bin, which is located on the desktop.
One can use the recycle bin to retrieve files deleted accidently or empts the recycle bin to
create more space for holding of the deleted files or folders. By default log of disk space is
present for this purpose.
v) Accessories:-Windows comes with several accessories application ranging from very simple
applications such as calculator and paint, to move powerful application such as WordPad (a
word processor) and disk clean up a system tool.
All windows accessories can be accessed from the accessories menu click the start button
paint to programs and then paint to Accessories. The Accessories menu appears, just click on the
shortcut and the application is instantly launched.
Component of Accessories
i) Calculator:- Used to perform calculations on screen as on a desktop calculator.
ii) Note Pad:-A simple text editor that one can create and edit text files.
iii) Paint:-An easy-to-learn graphics application that one can use to create and modify graphics
images.
iv) Word Pad:- A word processing application used to enter, edit and format documents.
v) Game:- Four games-FreeCell, Hearts, Mines weeper and solitaire for ones entertainment.

4) Use of Tastbar, Scrollbar, Slider, Addressbar, etc.

Ans:-
Task bar:- A task bar is located on the button of the desktop. One can click on the task bar and drag
it to other locations. The start button, active program buttons, icons for quick access to program and
the current time are located on the task bar.
Scroll bar:- Sometimes a window is not large enough to display all the information at one page.
When this happens, one use the scroll bar to move or scroll thought the information in the window.
Address bar:- A method of opening files that are on the Internet or one’s computer when one type
an address in the Address bar, once upon the file at that address.

5) What are the naming conversion of files and folder?

Ans-
There are two parts to every file:
1. file name.
2. file extension.
A three letter extension that tells windows what type of file it is file name can be up to 225
Character.

6) How can you create a folder, short cut and a file?

Ans:-

Page 3 of 48
To create a folder:-
i) Click the drive in MY computer where one want to create the new folder.
ii) On the file menu, point to new and then click folder.
iii) Type new folder appears with a temporary name.
iv) Type a name for the new folder and then press enter.
To create a file:-
i) Open the notepad application. Click the start button, point to programs, point to Accessories and
thin click Notepad;
ii) A blank file opens by default inter this text: This is a text file.
iii) Save the file as Hello-text file.
iv) Click Notepad programs close button to close program and the Hello text file. The Notepad
program close.
To create a shortcut:-
i) Right-click on the blank part of the desktop.
ii) On the shortcut menu, select New and then click on shortcut.
iii) Click the Browse button to specify the location, then click the Next> button, name the short cut
and click finish.
7) Write short note on:-
Windows explorer:- It is a comprehensive organizing and management tool for windows files and
folder using this tools, one can view all the file and folders on a selected drive, open the file or folder
move and copy them, rename them, delete, print and use files on other locations on the network.
This windows is divided into two parts on the left side area the drives and folders. The right pane
shows the files and subfolders of the folder or drive one have chosen from the left pane.
Need of accessibility options:- The accessibility wizard makes it easier for people with disabilities
to operate a computer without installing special software Accessibility options such as sticky keys,
shoe sounds and Mouse keys are designed to help users with specific disabilities and made full use
of the computer.
System Tool:- Windows provides system tools for managing disks, protecting data and insuring
good disk performance. To keep once computer in good working order use, these programs on a
regular basic
a) Defragmentation:-The process of re-writing a file to adjust selection of a hard disk. Over
time, parts of the same file can become fragmented that is, spread over different areas on a hard
disk. Fragmentation causes slower disk speed includes the disk defragmenter, which may help
improve disk performance.
b) Character- map:-It works only with windows based programs. This is used to insert special
character into document.
c) Clip board viewer:- It is invisible holding area for copy an cut operation but one can save the
information litter use. It the current information is not save on the clip-board, it is lost the next
time when one copy new information to it. It is mainly used to transfer information between
applications.
d) Media player:- one can use Media player to play audio, video, or animation files, and to
control the settings for multimedia hardware dances. To use media player also one should have
a sound card.

8) Disk Drive utilities supported by windows.

Ans:-

Page 4 of 48
Windows include utilities to format disk and hard drives for errors, defragment diskettes and do other types of
maintenance.
To Access these utilities:-
i) Open the start menu and select programs.
ii) Choose accessories and then system tools.
Disk Defragmenter (Defrag) :- Disk Defrag is used to rearrange files of unused space on the HD so that program run
faster.
i) From the cascading menu of system tools, select disk defragmenter. Select Drive box is opened to select the drive
one want to defrag.
ii) Click the drive one want or defrag, and then click ok.
iii) If one want to change the settings that disk defrag uses, click advanced.
iv) Click start.
While windows defragments the detected disk, the computer can out other tasks, however, the computer will
operate more slowly. To temporarily stop disk defrag so one cam run other programs at full speed, click pause.
Check for disk errors (Scan disk):- It is used to check once hard disk for logical and physical errors & then repair the
damaged areas.
i) Select the option scan disk from the cascading menu.
ii) Click the drive one want to check.
iii) Click the drive. If one want to change the settings scan disk uses when checking the disks surface, click options. If
one wants to change the settings scan disk uses when checking files & folders, click advanced.
iv) Click start.
Format disks:- Formatting a disk means establishing tracks and sectors on the disks when files will be started be aware
that formatting a disk removes all information from the disk one cannot format a disk if there are files open on that disk.
If the disk has been compared, use drive space or their compression software to format the disk.
To format a disk
i) If the disk one want to format is a floppy disk, insert it into its drive. Otherwise go to step2.
ii) Double click my computer icon and then click the icons for the disk one want to format. Be saving not to double
click the disk icon, because one cannot format a disk if it is open in My Computer on Windows explorer.
iii) On the file menu, click format.
The various options for formatting disks are:
i) Capacity:- it should be low density or high density.
ii) Format type:- Quick (erase) formats the disk without checking for errors.
iii) Format type:- Full checks for the disk errors and then reformats it.
iv) Format Type:- Copy system files only copies the system files to the formatted disk to make it bootable.
v) Label:- Type the label name for the disk.
vi) No Label:- This option is selected if one do not require the label name for the disk after formatting.
vii) Display Summary When finished:- To get information about the bad sectors (if any) after the disk is formatted.
Increase Disk space:- It is use to compress both hard & floppy disks to create more free space for files. It is also use to
configure disk drives that one have already compressed by using double space on drive space.

To determine how much space is available on a disk:-

i) Double click the my computer icon & then click the disk one want to check.
ii) On the file menu click properties. A pie chart shows how much free& used space is on the disk.
To create more disk space:-
iii) In the Drives on this computer list, click the drive one want to compress.
iv) On the drive menu, click compress.
v) Click start.
vi) If one have not backed up once files. Click back up files & then follow the instruction on once screen. When one
are done proceed to step 6.
vii) Click compress now.
viii) If windows prompt one to restart once computer, click yes.
ix) If one wants to free up more disk space after once computer restarts, start the disk space troubleshooter again.

Page 5 of 48
MS-WORD
9) What is Word Processor?
Ans:- word Processor is one of the most powerful editor. The basic aim of any word processor is to
generate formatted documents. Word is used to work with documents that contain text, graphics and
other objects. It is excellent word processing software, as it has all the tools to beautify the
documents by applying styles, using original templates, images, etc. as per the user requirements.

10) Features of MS-Words?

Ans:-

i) Quick formatting:- Select the prefect format the first time with the new drop-down. Format
menu, which gives you a preview of font style before you choose them.
ii) Personalized menu:- Commands that we use frequently are prominently featured on the menu.
Menus are easily depended to real all command.
iii) Thesaurus:- Word has a now improved thesaurus. Right-click a word, point to synonyms and
then select a word from the synonyms list.
iv) Spelling and Grammar checks:- By default word automatically checks spelling and grammar
as you type in the document, several manipulations and settings can also be done here.
v) Collect paste Multiple Document:- Gather information from multiple Sources and place up to
12 items on the Microsoft office clipboard at a time.
11) Standard toolbar, formatting toolbar and Drawing toolbar and
Menu bar view icon.
Ans:-

i) Standard toolbar:- It is used to perform found without having an access to menu. User can type
in the new documents or open an existing document by using the standard toolbar. The most
important commands like new, open, save and print are present on the standard toolbar. Just click
on the icon without going through the menus. One can also cut, coy or past with a single click. It
one needs to know the function of a certain icon save the mouse pointer on top of the icon for a
couple of second and a tool tip will appear that provide the name of the tool.
ii) Formatting toolbar:- It offers a variety of attributes shortcuts. It is present on the toolbar are
used to boldface, italic, underline and align the text. It offers drop-down menus for style, font
and font size. One can also control paragraph indents border and text color.
iii) Drawing toolbar:- It is used to add word art, auto shapes, times, arrows and text boxes to a
document and then add color, shadow and background to create images. Word includes many in
built toolbar that can be shown and hidden as and when required.
iv) Menu bar:- It gives the user access to all the software commands and features. It is located just
below the title bar and is used to communicate with the word application. It one click on any of
the option in the menu bar, a list of commands appear as per the choice of menu. The first item in
the menu bar is file, Edit, View, Insert, format Tools, Table, Windows, Help.

Page 6 of 48
v) View icons:- It is used to change the display of the current page.
(a) Normal view:- It displays the section break, fonts and other character attributes, page breaks
and the names of paragraph styles. It shows text formatting but simplifies the layout of the
age so, that one can type and edit quickly. In normal view, page boundaries, headers and
footers, back grounds and drawing objects do not appear.
(b) Web layout view:- It simplifies the tasks and word wraps text to fit the window, displays the
back ground and places graphics on the screen in the same way as they would appear in a
browser in this view.
(c) Print layout view:- It displays the documents precisely as it will be printed, with page
breaks, headers and footers and pictures, arranged correctly.
(d) Outline view:- It displays the routine of a document while one is working on it. This view
can be particularity helpful in rearranging section or promoting or demoting of large
documents.

12) How can you create a file in MS-Word and save the same in
specified folder with password and open the same when needed?
Ans:-

i) At first one click on the start button and then on the MS-office MS-Word.
ii) When the MS-Word is open we click on the file menu.
iii) When we click on the file menu, a list of Option appears. We click on the ‘New’ option.
iv) A dialog box appears we write the document there.
v) Again we click on the file menu and a list of option; we click on the save as option.
vi) Save as dialog box appear. We Write the file name, save as type and if urgent we entered a
password and then we click on the save button then the document is save.

13) How can you insert header/ footer, foot note and end note in your
documents, page number, page break, date, symbols etc.?

Ans:-
i) From the view menu, we choose headers and footer.
ii) When the header and footer are open, we edit the footer inserted of the header; click the switch
between header and footer button on the header and footer.
iii) We type text for the left side of the header or footer, it one want text in the center of the header
or footer, press tab and type text.
To insert page no. or page break. We click on the insert menu and choose page break option or page
no. (Even page or odd page)option.
To insert data and symbols we again click on the insert menu a list of option appear we choose date
and symbols and its dialog box appear we select symbols and adjust date and it appear on the dialog
box.
At the end of the footer there appear footer note and at the end of the header and footer there appear
an end note (usually shown by same design, pictures etc.)

Page 7 of 48
14) What is Word-Wrap? Cut, copy and more selected text?
Ans:- Automatically change of time. After a certain length is Called Word-Wrap.
Cut:- (CTRL+X) cut option is used to move the selected area/ text to the virtual dip broad from where that
can be passed to new location.
Copy:- copying means repeating a portion of text from one location to another location. Whenever a region is
selected and copied, it is temporarily placed on the clop-board (a place on RAM). Copy:- (CTRL+C).
Paste:- (CTRL+V) paste operation is a counter part of both cut and copy operations, it is used to paste the
selected region from one location to another location on the dialog box.

15) Difference between Save and Save As…?

Ans:- The save option normally saves the file and demands for a name and further it this command
is again used, it normally updates the file. It products the file with extension.DOC.
The save As , save an existing file under a new name or different format to be imported in to
another program.
16) Method of selecting:
A Word, a sentence, a paragraph, a time, multiple sentence,
entire documents, etc.?
Ans:-
USING THE MOUSE TO SELECT
A Word:- Double-Click within the word.
A Sentence:- Hold the ctrl key once and Click the mouse anywhere on the sentence.
A Paragraph:-Click three times successively within the paragraph (or move the mouse pointer to the
selection bar. It will turn into) an arrow pointer up and to the right: with the pointer in that state, double click
to select the paragraph.
A Line:- Move the mouse pointer to the selection for until it changes to an arrow and click once. Selection
bar is the invisible columns between the left edge of the document windows and the left margin of the page.
Any text:- Drag the mouse over the block of text one want to select.
Entire Document:- One can use either of the three wars to select the whole document.
 On the Edit menu click select all.
 Move the mouse pointer to the left margin of the document until it changes to a pointer arrow.
Hold down the ctrl key and click once.
 Click three times successively on the left –hand side the screen.
USING THE KEYBOARD TO SELECT
 To select next character to right press shift + Right arrow.
 To select next character to left press shift+ left arrow.
 To select the end of a word press ctrl+ shift + Right arrow.
 To select the beginning of a word press ctrl+ shift+ left arrow.
 To select the end of a paragraph press ctrl+ shift+ Down arrow.
 To select the beginning of a paragraph press ctrl+ shift+ up arrow.
 To select the end of a document press ctrl+ shift+ end.
 To select the beginning of a document press ctrl+ shift+ home.

Page 8 of 48
17) What is Mail Merge? What are the components of Mail Merge?
Discuss stepwise process for Mail Merge?
Ans:- Mail merge is an excellent feature of MS-Word which minimize the completely related to
typing and repetition with the help of mail merge option one can easily produce letter, catalogs, parts
lists etc. It is merging of two separate files to formula separate merged file.
It is a three step process where a user needs to create the main documents and data source.
The third step (Merging of files) is accomplished automatically by MS-Word.
a) Main Document:- Main document is the theme of the object. It is body of a letter, control,
schematic of catalog, body of form letter etc.
b) Data source:- It is basically a file which consist the recipient list. It may be word document an
excel sheet.
c) Merging:- Microsoft word automatically merges main documents. After the merge process is
completed separate documents can either be printed or can be sent via e-mail.
The mail merge process has four main steps:-

1) Opening or creating the main documents.

2) Opening or creating the data source.
3) Editing the main documents.
4) Performing the merge.
1) Opening or creating the main documents:-
i) On the tools menu, click mail merge. This will open the mail merge helper.
ii) In the main document area of the mail merge helper dialog box, click the create button.
iii) Click on the option form letters. A dialog box appears asking whether you want to use the
active windows for the main document becomes the mail merge main document Word returns
to the mail merge helper.
iv) Proceed further with step 2 given below.
2) Opening or creating the Data source:-
i) In the data source area of the mail merge helper dialog box, click on the option get data.
ii) Click on the option create Data source. The create Data source dialog box appears, In this
dialog box, one can specify the field names listed in the Header Row box are often used in
form letters, mailing tables and envelopes. One can remove the fields that one do not want to
selecting, them once at time and then clicking on the option remove field name. It one wants
to create new field names, type the name in the Field name box, and then click Add field
Name.
iii) The save as dialog box appears in the field name box type the name that one wants to give
your data source and then click save.
iv) When Word displays a message box, whether one want to edit the data source on set up the
main document, click on the edit data source option.
v) When the data form dialog box appears, type your information into the form. If there is one
information for a particular field, leave the box blank. The set of information in each form
makes upon data record.
vi) After one type information for a record, click on the Add New option.
vii) After one odd all of the data, click ok to return, to the main document. Notice the mail merge
toolbar.
Page 9 of 48
 viii) Save the main document. At this point, by saving the main document, one can scan the data
source and attach it to the main document,
ix) Go back to step 3.
3) Step 3: Editing the main document:-
i) Type any text or add graphics that one want to include in once letter.
ii) Whenever one want to insert information at the desired location from the data source, click
on the Insert merge field button on the mail merge toolbar, and then click on the merge field
that one want to use.
iii) When one finish editing the main document, click save or save ad option on the file menu.
Name the file, and thin click save.
iv) Go back to step 4.
4) Performing the Merge:-
i) On the tools menu, click, on the mail merge options.
ii) In the merge the data with the document area click on the merge option
iii) In the merge dialog box, click on the option Merge To, and select new document to display
the merged document on the screen. One can print the merged document by clicking print on
the file menu.
iv) Click on the Merge option.

18) What is an indent? Type of indents how it differs from tab?

Ans:- Indentation defines the distance of a paragraph from either slide of left or right margin.
In word, paragraph indent is used to move the left edge of a paragraph to the right edge of a
paragraph to the left. It is a way to change the margin for paragraph text. Indents are used for
emphasizing a paragraph, to set off quotes, or for creating numbered, or bulleted lists normally.
Type of indent:-
a) Left indent:- The left indent controls the left edge of all lines with in the paragraph. This indent
is measured from the left margin.
b) Right indent:- A paragraph’s right indent controls the right edge of all lines within the
paragraph. This indent is measured from the right margin.
c) First- time indent:- A paragraph’s first-time indent controls only the left edge of the paragraph
first time. This indent is measured from the left margin.
d) Hanging indent: - It is the one in which the second and subsequent lines of a paragraph are
indented. More than the first time. When one create a hanging indent, one offset an element, such
as bullet, number, or word, to the left of the first time of text.

Page 10 of 48
19) Tables and its Arithmetic features?
Ans:- Tables are one of the most interesting and important features that make the word editor
different from convention editors one can create tables for arranging data that will look impressive
and present table. In addition to the obvious uses of tables like organizing and presenting tabular
data, word tables are perfect for placing and organizing items on a page.
Tables are used to format all or part of once document into rows and columns. They are
frequently used to present text in blocks called cells. Tables provide the following feature:
 Insertion and deletion of rows and columns.
 Border, shading and sizing
 Use of formulas e.g. to sum up the values in a column.

Creating a Table:-
Creating a table is a simple task. One can create a table in two ways using the table menu and the
Insert Table button on the standard toolbar. If one want to create a four columns five row table using
the menu, following the steps given below.
a) Place the cursor at the location, where one requires a table.
b) On the table menu, point to insert and then click table. The insert table dialog box will appear.
c) Type 4 in the number of columns fields and types 5 in number of rows field.
d) Select auto form the fixed columns width field. Selecting the auto option allows Microsoft word
to determine the width of once column.
e) Click ok.
Alternate method:-
Creating a table using the insert table icons:-

 This method is useful, for a small table. Place the cursor at the location, where one require a
table
 Click Insert table on the standard toolbar.
 Highlight the no. of rows and columns that one need. The maximum table size is four row by
given five column.

Page 11 of 48
MS-Excel

1. What is an electronic Worksheet/Spreadsheet?

Ans:- The worksheet is the primary document used in Excel, which allows one to list and display the
data. One can enter and edit data on several worksheets simultaneously and perform calculations on
multiple worksheets. A worksheet is made up of horizontal rows and vertical columns.
MS-excel worksheet contains 65,536 row and 256 columns.
Each intersection of a row and column is called a cell, where data can be stored.
Generally, a spreadsheet refers to the software package and a worksheet refers to the work file
created within the spreadsheet software.

2. What could be content of a cell? How can you select a cell?

Ans:- Cell is the basic unit of the spreadsheet. A cell is formed by the intersection of a column letter
and the row number. A cell can contain a numeric value, a formula, or a text entry.
Enter Data into a Cell:-
When one start the program, one will see the cursor positioned at cell A1. One can enter data right
from the first cell or according to once preference.
i) Type the heading & press enter pointer will move to the next cell.
ii) Enter the rest of the data.
iii) Use the arrow keys to move from one cell to another.
Select a Cell:- To Select a Cell. Click Once in the Cell. The cell will be selected.
3. How can you define formula in a cell? What is a function?
Various categories of functions.
Ans:-
Formula:- A formula calculates a new value from the existing values. An excel formula can contain
a combination of constant values; cell references range names, functions and /or operators.
Formula always begins with an equal sign (= or, +).
e.g.:- Constant values
(465+57)*32
Cell reference D3/F13 Range= D3*Tax
Functions: - Excel contains many predefined or in built formulas, which are be used to perform
simple or complex calculations. Some of the most frequently used functions are sum, Average, PMI
DLOOK up and IF function.
e.g.:- the sum functions adding cell addresses can be represented as:
= sum (D2:D5)

Page 12 of 48
About functions
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular
order, or structure. Functions can be used to perform simple or complex calculations. For example, the ROUND function
rounds off a number in cell A10.

Structure of a function
Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening
parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
Function name. For a list of available functions, click a cell and press SHIFT+F3.
Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays , error values such as
#N/A, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the
reference of the cell that appears at the intersection of column B and row 3 is B3.). The argument you designate must
produce a valid value for that argument. Arguments can also be constants (constant: A value that is not calculated and,
therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An
expression, or a value resulting from an expression, is not a constant.), formulas, or other functions.
Argument tooltip. A tooltip with the syntax and arguments appears as you type the function. For example, type
=ROUND( and the tooltip appears. Tooltips only appear for built-in functions.
Entering formulas When you create a formula that contains a function, the Insert Function dialog box helps you
enter worksheet functions. As you enter a function into the formula, the Insert Function dialog box displays the name of
the function, each of its arguments, a description of the function and each argument, the current result of the function,
and the current result of the entire formula.
Nested functions
In certain cases, you may need to use a function as one of the arguments of another function. For example, the following
formula uses a nested AVERAGE function and compares the result with the value 50.
Valid returns When a nested function is used as an argument, it must return the same type of value that the argument
uses. For example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or
FALSE. If it doesn't, Microsoft Excel displays a #VALUE! error value.
Nesting level limits A formula can contain up to seven levels of nested functions. When Function B is used as an
argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM
function are both second-level functions because they are arguments of the IF function. A function nested within the
AVERAGE function would be a third-level function, and so on.

The following sections list all of the worksheet functions by category.

Add-in and Automation functions

Cube functions
Database functions
Date and time functions
Engineering functions
Financial functions
Information functions
Logical functions
Lookup and reference functions
Math and trigonometry functions
Statistical functions
Text functions

Page 13 of 48
Worksheet functions listed by category
Database

Microsoft Excel includes worksheet functions that analyze data stored in lists or databases.

Each of these functions, referred to collectively as the Dfunctions, uses three arguments:

database, field, and criteria.

These arguments refer to the worksheet ranges that are used by the function.

DAVERAGE Returns the average of selected database entries

DCOUNT Counts the cells that contain numbers in a database

DCOUNTA Counts nonblank cells in a database

DGET Extracts from a database a single record that matches the specified criteria

DMAX Returns the maximum value from selected database entries

DMIN Returns the minimum value from selected database entries

DPRODUCT Multiplies the values in a particular field of records that match the criteria in a
database

DSTDEV Estimates the standard deviation based on a sample of selected database entries

DSTDEVP Calculates the standard deviation based on the entire population of selected database
entries

DSUM Adds the numbers in the field column of records in the database that match the criteria

DVAR Estimates variance based on a sample from selected database entries

DVARP Calculates variance based on the entire population of selected database entries

GETPIVOTDATA Returns data stored in a PivotTable

Page 14 of 48
SIN
Returns the sine of the given angle.
Syntax
SIN(number)
Number is the angle in radians for which you want the sine.
Remark
If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
A B
1 Formula Description (Result)
2 =SIN(PI()) Sine of pi radians (0, approximately)
3 =SIN(PI()/2) Sine of pi/2 radians (1)
4 =SIN(30*PI()/180) Sine of 30 degrees (0.5)
5 =SIN(RADIANS(30)) Sine of 30 degrees (0.5)

QUOTIENT
Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
How?
1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
3. If necessary, follow the instructions in the setup program.
Syntax
QUOTIENT(numerator,denominator)
Numerator is the dividend.
Denominator is the divisor.
Remark
If either argument is nonnumeric, QUOTIENT returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
A B
1 Formula Description (Result)
2 =QUOTIENT(5, 2) Integer portion of 5/2 (2)
3 =QUOTIENT(4.5, 3.1) Integer portion of 4.5/3.1 (1)
4 =QUOTIENT(-10, 3) Integer portion of -10/3 (-3)

Page 15 of 48
About calculation operators
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel
includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Types of operators
Arithmetic operators
To perform basic mathematical operations such as addition, subtraction, or multiplication; combine
numbers; and produce numeric results, use the following arithmetic operators.
Arithmetic operator Meaning (Example)
+ (plus sign) Addition (3+3)
Subtraction (3–1)
– (minus sign)
Negation (–1)
* (asterisk) Multiplication (3*3)
/ (forward slash) Division (3/3)
% (percent sign) Percent (20%)
^ (caret) Exponentiation (3^2)

Comparison operators You can compare two values with the following operators. When two values
are compared by using these operators, the result is a logical value either TRUE or FALSE.
Comparison operator Meaning (Example)
= (equal sign) Equal to (A1=B1)
> (greater than sign) Greater than (A1>B1)
< (less than sign) Less than (A1<B1)
>= (greater than or equal to sign) Greater than or equal to (A1>=B1)
<= (less than or equal to sign) Less than or equal to (A1<=B1)
<> (not equal to sign) Not equal to (A1<>B1)
Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text strings to
produce a single piece of text.
Text operator Meaning (Example)
& (ampersand) Connects, or concatenates, two values to produce one continuous text value ("North"&"wind")
Reference operators Combine ranges of cells for calculations with the following operators.
Reference
Meaning (Example)
operator
Range operator, which produces one reference to all the cells between two references, including the two
: (colon)
references (B5:B15)
, (comma) Union operator, which combines multiple references into one reference (SUM(B5:B15,D5:D15))
(space) Intersection operator, which produces on reference to cells common to the two references (B7:D7 C6:C8)

The order in which Excel performs operations in formulas

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign
tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated
(the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to
a specific order for each operator in the formula.
Operator precedence

Page 16 of 48
If you combine several operators in a single formula, Excel performs the operations in the order
shown in the following table. If a formula contains operators with the same precedence— for
example, if a formula contains both a multiplication and division operator— Excel evaluates the
operators from left to right.
Operator Description
: (colon) (single space), (comma) Reference operators
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison
Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the
following formula produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3
and then adds 5 to the result.=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to
produce 21.=(5+2)*3
In the example below, the parentheses around the first part of the formula force Excel to calculate B4+25 first and then
divide the result by the sum of the values in cells D5, E5, and F5.=(B4+25)/SUM(D5:F5)
Create a formula
Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=).
For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
Create a simple formula: =128+345
The following formulas contain operators and constants.
Example formula What it does
=128+345 Adds 128 and 345
=5^2 Squares 5
1. Click the cell in which you want to enter the formula.
2. Type = (an equal sign).
3. Enter the formula.
4. Press ENTER.
Create a formula that contains references or names: =A1+23
of other cells. The cell that contains the
The following formulas contain relative references to and names
formula is known as a dependent cell when its value depends on the values in other cells. For
example, cell B2 is a dependent cell if it contains the formula =C2.
Example formula What it does
=C2 Uses the value in the cell C2
=Sheet2!B2 Uses the value in cell B2 on Sheet2
=Asset-Liability Subtracts a cell named Liability from a cell named Asset
1. Click the cell in which you want to enter the formula.
2. In the formula bar, type = (equal sign).
3. Do one of the following:
o To create a reference, select a cell, a range of cells, a location in another worksheet, or a location in another
workbook. You can drag the border of the cell selection to move the selection, or drag the corner of the border to
expand the selection.
o To create a reference to a named range, press F3, select the name in the Paste name box, and click OK.
4. Press ENTER.
SUM : Adds all the numbers in a range of cells.
Syntax: SUM(number1,number2, ...)

Page 17 of 48
Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.
A

Data

-5

15

1 30
2
'5
3
4
TRUE
5
6 Formula Description (Result)
=SUM(3, 2) Adds 3 and 2 (5)
Adds 5, 15 and 1, because the text values are translated into numbers, and the logical value TRUE is translated
=SUM("5", 15, TRUE)
into the number 1 (21)
=SUM(A2:A4) Adds the first three numbers in the column above (40)
=SUM(A2:A4, 15) Adds the first three numbers in the column above, and 15 (55)
Adds the values in the last two rows above, and 2. Because nonnumeric values in references are not translated,
=SUM(A5,A6, 2)
the values in the column above are ignored (2)

SUMIF:Adds the cells specified by a given criteria.

Syntax: SUMIF(range,criteria,sum_range)
Range is the range of cells you want evaluated.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example,
criteria can be expressed as 32, "32", ">32", "apples".
Sum_range are the actual cells to sum.
A B
Property Value Commission
1 100,000 7,000
2 200,000 14,000
3 300,000 21,000
4 400,000 28,000
5 Formula Description (Result)
=SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property values over 160000 (63,000)

Page 18 of 48
LOOKUP
The LOOKUP function has two syntax forms: vector and array (array: Used to build single formulas that produce
multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a
common formula; an array constant is a group of constants used as an argument.).
A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column
range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column
range. Use this form of the LOOKUP function when you want to specify the range that contains the values you want to
match. The other form of LOOKUP automatically looks in the first column or row.
Syntax 1
Vector form
LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical
value, or a name or reference that refers to a value.
Lookup_vector is a range that contains only one row or one column. The values in lookup_vector can be text, numbers,
or logical values.
Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How?
1. Create a blank workbook or worksheet.
2. Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave
accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
A B
Frequency Color
4.14 red
4.19 orange
5.17 yellow
5.77 green
1
6.39 blue
2
Formula Description (Result)
3
Looks up 4.19 in column A, and returns the value from column B that's in the same row
4 =LOOKUP(4.91,A2:A6,B2:B6)
(orange)
5
Looks up 5.00 in column A, and returns the value from column B that's in the same row
6 =LOOKUP(5.00,A2:A6,B2:B6)
(orange)
Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value
=LOOKUP(7.66,A2:A6,B2:B6)
from column B that's in the same row (blue)
Looks up 0 in column A, and returns an error because 0 is less than the smallest value in
=LOOKUP(0,A2:A6,B2:B6)
the lookup_vector A2:A7 (#N/A)

Page 19 of 48
Summary functions for data analysis
Summary functions are used in automatic subtotals, data consolidations, and PivotTable and PivotChart reports. In
PivotTable and PivotChart reports, the following summary functions are available for all types of source data except
OLAP.
Function Summarizes
Sum The sum of the values. This is the default function for numeric data.
The number of data values. The Count summary function works the same as the COUNTA
Count
worksheet function. Count is the default function for data other than numbers.
Average The average of the values.
Max The largest value.
Min The smallest value.
Product The product of the values.
The number of data values that are numbers. The Count Nums summary function works the
Count Nums
same as the COUNT worksheet function.
An estimate of the standard deviation of a population, where the sample is a subset of the
StDev
entire population.
The standard deviation of a population, where the population is all of the data to be
StDevp
summarized.
An estimate of the variance of a population, where the sample is a subset of the entire
Var
population.
Varp The variance of a population, where the population is all of the data to be summarized.

Calculation differences between Microsoft Excel and Lotus 1-2-3 formulas

Mathematical operators
The following list compares the mathematical operators used by Microsoft Excel and Lotus 1-2-3.
Description Lotus 1-2-3 operator Microsoft Excel operator
Exponentiation ^ ^
Positive and negative + and – – (positive is assumed in Microsoft Excel)
Multiplication and division * and / * and /
Addition and subtraction + and – + and –
Comparison = < > <= >= <> = < > <= >= <>
Logical #not#, #and#, and #or# NOT, AND, and OR worksheet functions
Connect two strings of text (concatenation) & (Release 2.0 or later only) &

4. What is a Chart? Discuss about various types of charts and sub

charts options. Mention the steps of drawing a chart on a tabulated
data.
Ans:-

Page 20 of 48
Chart is a graphic representation of excel worksheet data charts make data attractive, interesting and
easily readable. Charts help one to convert numeric data into visual display in which one can identify
trends and pick out patterns at a glance thus helping one analyze and compare data.
When one create a new chart, excel allows one to place it on the same sheet or to embed a chart
object within the worksheet that contains the data one want to chart, excel, automatically maintains
links between worksheet data and its graphics representation on the chart. If one change the no. &
text in the data range, the columns pie slices & after graphics elements on the chart change.

Components of a charts:-
i) Page field:- A field is used to filter data by specific items.
ii) Data field:- A field from the source list or table that contains data.
iii) Series field:- A field that one assign to a series orientation in a pivot chart report.
iv) Field drop-down Arrow:- The arrow of the right hand side of each field
v) Item:- Items represent unique entries in the same field and appear in the drop down for the page
fields, category fields, and series fields.
vi) Category Fields:- A field from the underlying source data that is assigned to a category
orientation in a pivot chart (table) or report

Types of chart:-
i) Area chart:- It is used when one want to emphasize change overtime.
ii) 3-d Surface:- A surface chart is a bit like a topographical map. Colors and patterns indicate
areas that are in the same range of values. In this chart, temperature and time are plotted
together to show the tensile strength they produce.
iii) Bar- chart:- A bar chart compares values with each other.
iv) Radar chart:- In this chart, each category of information has its own line radiating out from
the center.
v) Column chart:- a column chart is very similar to a bar chart, except the bars reach vertically
instead of horizontally.
vi) Bubble chart:- A bubble chart shows three sets of variables, represented by the two axes and
the size of the bubble.
vii) Line chart:- A line chart is useful for comparing trends.
viii) Pie chart:- A pie chart can compare one set of figures.
ix) Scatter graph:- A scatter graph is useful for comparing a set of values with the average a
predicted values.
x) Doughnut chart:- A doughnut chart is very similar to a pie chart, except that it can show more
than one set of figures. Each ring of the doughnut represents a set of figures.

Creating an Embedded Chart:-

i) In excel, open a new spreadsheet and enter the data.
ii) Select the range of cells that one want to highlight in once chart. One can use the mouse to
highlight the area. With the cursor in the top left hand cell of the table, select the table by
clicking & dragging the cursor to the bottom right hand cell.

Page 21 of 48
iii) From the standard toolbar, select the chart wizard icon of pull down the insert menu & click on
the chart.
iv) The chart wizard step 1of 4 chart type dialog box will open up click on the standard types tab if
it is not selected.
v) Click in the next > button when one are ready. The chart wizard step 2 of 4 chart source
data dialog box will open up.
vi) Click on the next > buttons when one are ready. The chart wizard step 3 of 4 chart options
dialog box will appear.
Click on the titles tab if it is not selected. In has 3 text boxes.
i) Click once in the chart title box.
ii) Click once in the category (X) axis box.
iii) Click once in the value (Y) axis box.
vii) Click on the next > button when one are ready. The chart wizard step 4 of 4. Chart location
dialog box will appear. (As object in : click)
viii) Select the finish button to complete once chart click once anywhere in the spreadsheet ( outside
the area of the chart). Once chart is finished.
Creating Charts on a separate sheet:-
i) Follow the same steps 1-6 for creating an embedded chart.
ii) Now, click on next > button when one are ready, the chart wizard step 4 of 4 chart location
dialog box will open up. (As new sheet click)
iii) Select the finish button to complete once chart.

Page 22 of 48
Set up a chart for printing
An embedded chart
You can adjust where the chart will print on the page by sizing and moving the chart with the mouse in page break
view (page break preview: Worksheet view that displays the areas to be printed and the locations of page breaks. The
area to be printed is displayed in white, automatic page breaks appear as dashed lines, and manual page breaks appear as
solid lines.).
1. Click the worksheet outside of the chart area.
2. Click Page Break Preview on the View menu.
A chart sheet
You can size and scale the chart area, specify how it should be placed on the printed page, and then view it in the
preview window.
1. Click the tab for the chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is
beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a
PivotTable report.).
2. Click Page Setup on the File menu.
3. Select the options you want on the Chart tab.
Create a chart
Some of the content in this topic may not be applicable to some languages.
1. On the Insert menu, click Object, and then click the Create New tab.
2. In the Object type box, click Microsoft Graph Chart, and then click OK.
Graph displays a chart and its associated sample data in a table called a datasheet.
3. To replace the sample data, click a cell (cell: A box formed by the intersection of a row and column in a
worksheet or a table, in which you enter information.) on the datasheet, and then type the new text or
numbers. If needed, you can import data from a text file, a Lotus 1-2-3 file, or a Microsoft Excel worksheet. You
can also copy data from another program.
4. To return to Microsoft Word, click the Word document.

For more information about working with charts— for example, how to add data labels (data label: A label that provides
additional information about a data marker, which represents a single data point or value that originates from a worksheet
cell.), change the scale of the value axis (axis: A line bordering the chart plot area used as a frame of reference for
measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and
contains categories.), or troubleshoot charts— use the following procedure to see Microsoft Excel Help or Microsoft
Graph Help. To work with charts created in Graph or Excel, you must have Graph or Excel installed.
1. In Word, double-click the chart.
The menus and toolbars (toolbar: A bar with buttons and options that you use to carry out commands. To display
a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.) change to show the Graph or
Excel menus and buttons.
2. On the Help menu, click Microsoft Graph Help or Microsoft Excel Help

Page 23 of 48
.

Page 24 of 48
Page 25 of 48
5. Main features of Excel?
Ans:- MS-Excel works the way one want , i.e. it can be customized. It provides ease of work with
the following features.
i) Tip wizard:- Guides one to work more effectively while one work by displaying various helpful
tips and techniques based on what one are doing.
ii) Drag and Drop features:- It help one to reposition the data and text by simply dragging the
data with the help of mouse.
iii) Auto fill:- It helps one to fill rows or columns with the series of data.
iv) Auto sum:- One can add a large range of data by simply selecting a tool button.
v) Windows Interface:- As excel is windows based package therefore the user interface is
consistent.
vi) Short cut menus:- Commands appropriate to the task one is doing appear by clicking the right
mouse button.
vii) Built in spell check:- One can maintain a dictionary of the words usually used in one’s work and
spell check one’s spreadsheet.

6. Use of auto filter advanced filter, data tables, sub-tables, Goal

seek one/two input data tables.
Ans: - Auto filter:- Auto filter helps one to display the records, which meet a particular condition.
When this option is selected, drop down controls are placed next to each field name. one clicking on
this drop-down control the contents of the field without repetition are displayed.
Using Auto filter:-
i) Select any cell of the database, range and choose Data -> filter -> Auto filter command. MS-
excel inserts drop down arrows next to each column table in the list.
ii) Click the arrow in the column that contains the data one want to display.
iii) Select the item one want to display.
The various other options other than column items that available are: -
i) All –shows all records.
ii) (custom …..) to specify two criteria for a columns.
iii) (Blanks …….) shows all those records that have no entry in the particular column.
iv) (Non- Blanks…..) Shows all those records that have some intry in the columns.
To remove auto filters, again choose Data -> filter ->Auto filter.
Advanced filter:-
Advance filter criteria can include multiple conditions applied in a single column &
conditions created as the result of a formula.

Page 26 of 48
Using Advance filter:-
These filters are used to filter the data on the basic of more than one field.
Using the same database, suppose one want to view only those records where the sale was more than
300 in the fourth quarter.
To get the results, follow the given steps.
i) Enter the data as given below.
H I
3
4 QTR3 QTR4
5 >200 >300

ii) Choose Data -> filter ->Advanced filter command.

iii) A message dialog box is displayed that asks one to assume the top row of the data range as the
header row. Click on ok.
iv) A dialog box is displayed with two kinds of action.
a. Filter the list, in-place-displays only those record that match the criteria.
b. Copy to another location-to copy the records that match the criteria to another location in the
worksheet leaving the database ranges it is. Select the first action, i.e. filter the list, in-place.
v) Specify the list range as A5:E9 and the criteria range as H4:I5.
vi) Click on ok.
Data Tables:-
It provides a short cut for calculating the variation in one operation.
A data table, is a range of cells that shows the results of substituting different values in one or more
formulas.
There are two types of data tables:-
i) One –input table:- with a one input data tables one type different values for one variable and
see the effect on one or more formulas. Follow the given steps to create one input table.
a. Enter the data as shown below:

F H

Interest: 10%
Initial Amount: 8,000
Monthly Interest: 800.00

Page 27 of 48
In a single columns enter a list of values one want MS-Excel to substitute the first in the Input cell.
b. In the row above the first value and one cell to the right of the column of values. Enter the
formula +H9*H10 that refers to the input cell.

G H
=+H9*H10
12%
11%
9%
11%

c. Choose Data -> Table…..

d. In the column input cell box, enter the cell reference H9.
e. Click on ok button.
MS-Excel substitutes each value in the input cell and displays the result of the right of each input
value.
ii) Two–Input table:- with a two –input data table, one inter different values for two variable and
see the effect on one formula. Follow the steps the steps as given below to inter the two input
table.
a. Refer to the same data as in the above example.
b. In an empty cell, enter the formula +H9*H10.
c. Starting in the cell below the formula, enter the value that one want to substitute in the same
columns as the formula.
d. Starting in the cell to the right of the formula enter the values that one want to substitute in the
other input cell in the same row as the formula as given below.

G H I J K
=+H9*H10 10000 7500 9000 8500
12%
11%
9%
11%
e. Choose
f. Data -> Table……..
g. In the column input cell box, enter the cell reference H9.
h. In the row input cell box, inter the reference H10.
i. Click on ok button.

Page 28 of 48
Page 29 of 48
Subtotals:- MS-Excel can automatically calculate subtotal and grand total values in a lists.

1. the Sport column.

How?
For best results, the range you sort should have column labels.
1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort.
4. Select any other sort options you want, and then click OK.
2. Insert the outer subtotals.

How?
1. On the Data menu, click Subtotals.
2. In the At each change in box, click the column for the outer subtotals. In the example above,
you'd click Region.
3. In the Use function box, click the summary function (summary function: A type of
calculation that combines source data in a PivotTable report or a consolidation table, or
when you are inserting automatic subtotals in a list or database. Examples of summary
functions include Sum, Count, and Average.) you want to use to calculate the subtotals.
4. In the Add subtotal to box, select the check box for each column that contains values you
want to subtotal. In the example above, that column would be Sales.

Page 30 of 48
 5. If you want an automatic page break after each subtotal, select the Page break between
groups check box.
6. If you want the subtotals to appear above the subtotaled rows instead of below, clear the
Summary below data check box.
3. Insert the nested subtotals.

How?
1. On the Data menu, click Subtotals.
2. In the At each change in box, click the nested subtotal column. In the example above, that
column would be Sport.
3. Select the summary function (summary function: A type of calculation that combines source
data in a PivotTable report or a consolidation table, or when you are inserting automatic
subtotals in a list or database. Examples of summary functions include Sum, Count, and
Average.) and other options.
4. Clear the Replace current subtotals check box.
4. Repeat the previous step for more nested subtotals, working from the outermost subtotals in.

Insert a total row in a list

1. Make sure that the list (list: A series of rows that contains related data or a series of rows that you
designate to function as a datasheet by using the Create List command.) is active by selecting a cell
in the list.

2. Click Toggle Total Row on the List toolbar.

Steps:-
i) Make sure the data one want to subtotal is in list format: each columns has a label in the first row
& contains similar facts and there row and contain similar facts and there are no blank row or
column with in the list.
ii) Click a cell in the column to subtotal.
iii) Click sort ascending or sort descending
iv) On the data menu. Click subtotals.
v) In the at each change in box. Click the column to subtotal.
vi) In the use function box, click the sum function to calculate the subtotals.
vii) In the odd subtotals to box, select the check box for each column that contain values one want to
subtotals.
viii) It one want an automatics page break after each subtotals select the page break between
groups check box.
ix) If one want the subtotals to appear above the subtotaled rows instead of below, clear the
summary below data check box.
x) Click ok.
Goal Seek:-After one know the result one want a formula to return but not the input values the
formula, one can use goal seeking, with this. MS-Excel varies the value in a cell one specify until a
formula dependent on that cell returns the result one want.

Page 31 of 48
About Goal Seek
Goal Seek is part of a suite of commands sometimes called what-if analysis (what-if analysis:
A process of changing the values in cells to see how those changes affect the outcome of formulas on
the worksheet. For example, varying the interest rate that is used in an amortization table to
determine the amount of the payments.) tools. When you know the desired result of a single
formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that
together produce a new value. A formula always begins with an equal sign (=).) but not the input
value the formula needs to determine the result, you can use the Goal Seek feature available by
clicking Goal Seek on the Tools menu. When goal seeking (goal seek: A method to find a specific
value for a cell by adjusting the value of one other cell. When goal seeking, Excel varies the value in
a cell that you specify until a formula that's dependent on that cell returns the result you want.),
Microsoft Excel varies the value in one specific cell until a formula that's dependent on that cell
returns the result you want.

For example, use Goal Seek to change the interest rate in cell B3 incrementally until the payment
value in B4 equals $900.00.

Page 32 of 48
Goal seek saves one form performing time-
Consuming trial- and –error analysis.
To seek a specify solutions to a formula.
A B C
1 SUBJECT MARKS
2 ENGLISH 76
3 MATHS 94
4 BIOLOGY 88
5 CHEMISTRY 91
6 PHYSICS ---
7
8 PERCENTAGE = 69.8

i) Enter the data as given below.

ii) Choose Tools -> Goal seek…. Command. The goal seek dialog box is displayed.
iii) In the set all box, enter the reference or the cell containing the formula for which one want to
find a special solution.
iv) In the to value box, enter the value one want as the result.
v) In the by changing cell box, enter the reference of the cell containing the value to be changed
in solving for the result that one want as shown below.

GOAL SEEK

Set cell : $C$8 OK

To value: 85
CANCEL
vi) By changing cell: $B$6 Choose
HELP
the OK button.

Page 33 of 48
Define and solve a problem by using Solver
1. On the Tools menu, click Solver.
2. If the Solver command is not available on the Tools menu, you need to install the Solver add-
in (add-in: A supplemental program that adds custom commands or custom features to Microsoft
Office.) program.

3. In the Set Target Cell box, enter a cell reference (cell reference: The set of coordinates that a cell
occupies on a worksheet. For example, the reference of the cell that appears at the intersection of
column B and row 3 is B3.) or name (name: A word or string of characters that represents a cell, range of
cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to
understand ranges, such as Sales!C20:C30.) for the target cell. The target cell must contain a
formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that
together produce a new value. A formula always begins with an equal sign (=).).

4. Do one of the following:

o To have the value of the target cell be as large as possible, click Max.

Page 34 of 48
o To have the value of the target cell be as small as possible, click Min.

o To have the target cell be a certain value, click Value of, and then type the value in the box.

2. In the By Changing Cells box, enter a name or reference for each adjustable cell, separating nonadjacent
references with commas. The adjustable cells must be related directly or indirectly to the target cell. You
can specify up to 200 adjustable cells.

3. If you want to have Solver automatically propose the adjustable cells based on the target cell,
click Guess.

4. In the Subject to the Constraints box, enter any constraints (constraints: The limitations placed on a
Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are
directly or indirectly related to the target cell.) you want to apply.

Add a constraint

1. In the Solver Parameters dialog box, under Subject to the Constraints, click Add.
2. In the Cell Reference box, enter the cell reference (cell reference: The set of coordinates that a
cell occupies on a worksheet. For example, the reference of the cell that appears at the
intersection of column B and row 3 is B3.) or name (name: A word or string of characters that
represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such
as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) of the cell range for
which you want to constrain the value.

3. Click the relationship ( <=, =, >=, Int, or Bin ) that you want between the referenced cell and the
constraint (constraints: The limitations placed on a Solver problem. You can apply constraints to
adjustable cells, the target cell, or other cells that are directly or indirectly related to the target
cell.). If you click Int, "integer" appears in the Constraint box. If you click Bin, "binary"
appears in the Constraint box.

4. In the Constraint box, type a number, a cell reference or name, or a formula (formula: A
sequence of values, cell references, names, functions, or operators in a cell that together produce
a new value. A formula always begins with an equal sign (=).).

5. Do one of the following:

 To accept the constraint and add another, click Add.

 To accept the constraint and return to the Solver Parameters dialog box, click OK.

Change or delete a constraint

1. In the Solver Parameters dialog box, under Subject to the Constraints, click the
constraint (constraints: The limitations placed on a Solver problem. You can apply
constraints to adjustable cells, the target cell, or other cells that are directly or
indirectly related to the target cell.) that you want to change or delete.
2. Click Change and then make your changes, or click Delete.

Page 35 of 48
 5. Click Solve and do one of the following:

o To keep the solution values on the worksheet, click Keep Solver Solution in the Solver
Results dialog box.

o To restore the original data, click Restore Original Values.

7. Pivot- Table: - A Pivot –table is an interactive worksheet table one under to summaries and
analyze data from an existing list or table. One can update a pivot table whenever changes occur in
the original source data. The original data mains intact and the pivot table stays on the worksheet one
created it on.
a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and
analyzes data, such as database records, from various sources, including ones that are external to Excel.)
from multiple consolidation ranges.

One create a pivot table by using the pivot table WIZARD a series of interactive dialog boxes
that guide one through the steps of locating and retrieving the data one want to analyze. The pivot
table wizard also lets one define how one want to arrange the pivot table using row and column
labels and how one want to present data in the fields. One can create a pivot table from source data
that reside in:
a) A Microsoft Excel list or database.
b) An external data base, such as a table or file created in an application or database management
system external to Microsoft Excel.
c) Multiple Microsoft Excel consolidation ranges.
d) Another pivot table in the same workbook.
One can create a pivot table, one can rearrange, organize, and analyse the data by dragging and
dropping the fields in the pivot table. One can also include subtotals, change the summary function
for a field, or select a custom calculation for ones pivot table.
Creating pivot- table:-
To create the pivot table from an internal database, follows the given steps:
a) Create the database as shown:
G H I J
2. SALESMAN PRODUCT RELIGION SALES
3. SUMIL MONITOR NORTH 60000
4. MANISH KEYBOARD SOUTH 40000
5. SUDHIR PEINTER SOUTH 67000
6. ARIT CPU WEST 45000
7. VINAYAK KEYBOARD EAST 54000
8. ANJALI MONITOR WEST 70000

Create a PivotTable report

1. Open the workbook where you want to create the PivotTable report.

Page 36 of 48
 o If you are basing the report on a Web query, parameter query, report template, Office
Data Connection file, or query file, retrieve the data into the workbook, and then click
a cell in the Microsoft Excel list containing the retrieved data.

If the retrieved data is from an OLAP database, or the Office Data Connection returns
the data as a blank PivotTable report, continue with step 6 below.

o If you are basing the report on an Excel list or database, click a cell in the list or
database.
2. On the Data menu, click PivotTable and PivotChart Report.

3. In step 1 of the PivotTable and PivotChart Wizard, follow the instructions, and click PivotTable under
What kind of report do you want to create?

4. Follow the instructions in step 2 of the wizard.

5. Follow the instructions in step 3 of the wizard, and then decide whether to lay out the report
onscreen or in the wizard.

Usually you can lay out the report onscreen, and this method is recommended. Use the
wizard to lay out the report only if you expect retrieval from a large external data source to
be slow, or you need to set page fields to retrieve data one page at a time. If you aren't sure,
try laying out the report onscreen. You can return to the wizard if necessary.

6. Do one of the following:

Lay out the report onscreen

1. From the PivotTable Field List window, drag the fields with data that you want to display in rows
to the drop area labeled Drop Row Fields Here.

If you don't see the field list, click within the outlines of the PivotTable drop areas, and

make sure Show Field List is pressed in.

To see what levels of detail are available in fields that have levels, the click next to the field.

2. Drag fields with data that you want to display across columns to the drop area labeled Drop Column
Fields Here.
3. Drag fields that contain the data that you want to summarize to the area labeled Drop Data Items Here.

Only fields that have the or icon can be dragged to this area.

If you add more than one data field, arrange these fields in the order you want: Right-click a data
field, point to Order on the shortcut menu, and use the commands on the Order menu to move
the field.

Page 37 of 48
4. Drag fields that you want to use as page fields to the area labeled Drop Page Fields Here.
5. To rearrange fields, drag them from one area to another. To remove a field, drag it out of the PivotTable
report.

To hide the drop area outlines, click a cell outside the PivotTable report.

Note If data is very slow to appear as you lay out the report, click Always Display Items on
the PivotTable toolbar to turn off initial data display. If retrieval is still very slow or error
messages appear, click PivotTable and PivotChart Report on the Data menu, and lay out the
report in the wizard.

Lay out the report in the wizard

If you've exited from the wizard, click PivotTable and PivotChart Report on the Data menu to
return to it.

6. In step 3 of the wizard, click Layout.

7. From the group of field buttons on the right, drag the fields that you want onto the ROW and COLUMN
areas in the diagram.

8. Drag the fields that contain the data that you want to summarize onto the DATA area.

9. Drag fields that you want to use as page fields onto the PAGE area.

If you want Excel to retrieve data one page at a time, so you can work with large amounts of source
data, double-click the page field, click Advanced, click Query external data source as you
select each page field item, and then click OK twice. (This option is unavailable for some types
of source data, including OLAP databases and Office Data Connections.)

10. To rearrange fields, drag them from one area to another. Some fields can only be used in some of the
areas; if you drop a field in an area where it can't be used, the field won't appear in the area.
11. To remove a field, drag it out of the diagram.

12. When you are satisfied with the layout, click OK, and then click Finish.

b) Select the any cell from the database range and choose data -> pivot-table….

Page 38 of 48
c) In the dialog box, choose the option Microsoft Excel list or database and click on Next > button.

Page 39 of 48
d) Given the database range as G2:J12 and click on Next > button.
e) Drag sales man field to column area, Drag product field to Row area. Drag Region field to
page area. Drag sales field to data area, and click next >button.
f) Given pivot table starting cell as G14 and specify the pivot table name. By default the name
given is pivot table1.
Select the appropriate options and click on finish.
Validation:- It is used to implement data validation feature.
i) Select the column on which one want to apply the data validation feature then click on to data
them click on to validation from the resulting menu, one will get a menu. Then select data from
allow list box and fill in the validation criteria.
ii) Click on to ok.

Page 40 of 48
Sheet layout:-
Appearance of text or number in a cell can be changed to suit our requirement with the feature
provided by MS- Excel.

Page 41 of 48
8. What is Range? What are the naming conversions of range?
How can you name a Range?
Ans:
Ranges of cells:
A range of cells is a group of cells forming a rectangular area. A range may contain just a single cell
or group of cells but must form a rectangular in order to be valid. A range is specified by entering the
address of the first cell and the last cell of the range.
Naming conversion of Ranges:-
A name is recognize and easy to remember a cell or range of cells. Naming a cell or range of cells
adds clarity, speeding up the productivity ensures reference integrity and also simplifies data entry,
e.g. formulas consider the following point about variables naming convention:-
a) Names can be up to 255 characters long.
b) The name should be descriptive.
c) The first character must be a letter or on underscore. Other character can be letters, nos. or,
periods.
d) The name cannot look like a cell reference, e.g. ‘G9’ or ‘R1C1’.
e) The underscore character or a period must be used instead of a space to separate words.
f) The name is not case sensitive they can be uppercase or lowercase.
Name a range:-

i) By using name box:-

a) Select the cell or range of cells that one want to name.
b) Click the arrow on the right of the name box.
The active cell reference appears in the name box and is highlighted.
c) Type in the name for the selected cell or range of cells.
d) Press enter to apply the changes.
ii) By using Define dialog Box:-
a) Enter the given data.
b) Select the ranges of cells.
c) Choose Insert -> Name -> create. This will display the create Names dialog Box.
d) Press ok.

Page 42 of 48
 9. What is cell reference? Type of cell references in MS- Excel?
Ans:-Cell Reference:-
Reference means address. Whenever calculation is done one excel sheet cell addresses or references
are used. A cell is just like a variable that holds data or constant.
Referencing means now cell addresses are being called or used within a formula or functions.
Referencing is all about the addressing fashion of a cell.
Relative Referencing:-
When one copy formula from one location to another location of the worksheet with relative cell
address/reference, excel automatically records the position of cell relative to the cell that contains the
original formula.
e.g.:- Entering formula in cell A4.

Coping formula in B4 by relative reference.

Page 43 of 48
Absolute Referencing: An absolute cell reference refers to the same cell always no matter where we
are going to copy the formula. We can convert a relative reference into absolute by placing a ‘$’ sign
before column Leller $ row nos. e.g.:-

Mixed Reference:-For flexible calculations we often need to make either the row or the column
absolute or relative. An absolute reference and a relative reference combines forms a missed
reference. In mixed reference either the column litter or the cell no. is relative or absolute.
e.g.:- $A1, A$1,….etc.

10. Purpose of F1-F12 (Function keys) in MS-Excel.

Ans:-
F1 : Help.
F2 : Edit.
F3 : Name.
F4 : Absolute.
F5 : Go to key.
F6 : Next table (^+F6).
F7 : Query.
F8 : Data table.
F9 : Calculate(Recalculate).
F10 : GraphF11 or Alt+F1).
F11 :
F12 :

Page 44 of 48
10. What is CIRC? How can you generate CIRC?
Ans:-

When a formula refers back to its own cell it is called circular reference. Most MS-
Excel cannot automatically calculate all the open worksheets when one of them
contains circular reference. One can remove a circular reference or one can have Excel
calculate each cell involve in the circular reference once by using the results of the
previous iteration.
Iteration:- Repeated calculation of a work-sheet until a specify memory condition is
meet.
Unless one changes the default setting for iteration excels calculating after 100
iteration or after all value in the CIRC change by less than 0.001 between iteration
which even comes first.
Note:- The status bar display the word circular followed by a reference ( address of
cell ) to one of the cells contained in the CIRC. It the word circular appears without a
CIRC, the active work-sheet does not contain the CIRC.
Solver:- MS-Excel solver is a powerful optimization and resource allocation tool. It
can help one to uncover the but uses of scarce resources so that desired goals such as
profit can be maximize, or undesired goals such as cost be minimized Microsoft excel
solver answers question such as
i) What product price or promotion mix will maximize profit?
ii) How can I live within the budget?
iii) How fast we can grow without tuning out of cash.

Page 45 of 48
MS-Power-Point
1) Features of MS-power point.
Ans:-
i) Using power point one can create charts graphs, slides, handouts, overheads, and any other
presentation materials.
ii) Create slide shows which are electronic presentations.
iii) It contains a no. of professionally designed template. Using power point one can:-
a. Create a presentation using a wizard design template or a scratch presentation.
b. Add contents to the presentation with text and table.
c. Use different views.
d. Format a presentation by customizing color schemes, background and template
e. Present an onscreen presentation using a computer, projects or via the verb &
f. Add additional content with charts, pictures, clipart, and other shapes or objects.

2) Slide master slide.

Ans:- Slide master is used to make changes in all the slides present in the presentation except for the
title slides. It is primarily used if one want all the slides in the presentation to have an identical text,
font color, or bullet type. The slide master and the title master have many things in common except.
 The change made on the slide master applies to all the slides in a presentation other than the title
slide. The changes made in the title master applies only to the title slide, and in most
presentations that’s the first slide and
 The slide master includes a text placeholder designed primarily to hold the bulleted lists & thus
contains the core of most presentations.

3) What is presentation? How can you create a presentation

containing 10 slides on a particular theme?
Ans:- A picture is worth a thousand words. Human mind can grasp and conserve concepts learnt
through’ visual depiction no matter how good the product or for that matter a project is if the idea of
the prefect or the product is not communicated effectively & efficiently. Failure is guaranteed. One
of the best ways of expressing the ideas is the use of visual images. This includes usage of graphics
and pictures. Graphics, when used to Covey ideas, expression or thoughts are known as presentation
graphics.
Steps of power-point:-
i) Creating the slides:- We will create 10 slides in normal view with the outline pane displayed,
where we will see each slides along with the full outline in slide by slide window pane.
ii) Creating charts &tables:- If the information we need to convey in numeric we create charts and
tables to depict the type of information.
iii) Adding specific graphics:- power point sophisticated drawing tools and commands make it
easy to embellish, slides with specific graphics. One can even import a scanned photograph or a
graphic for another application.
iv) Customizing presentation:- We can rear range slides change the overall design and delete
extraneous Slides, normal view one can add a logo a change the color or design of the
background, change the font color and scheme or change the template, which governs the
overall look of the presentation.
v) Creating a slide show:- we can also create an on screen electronic presentation known as slide
show, we can also publish our slide show on the web or on a corporate internet.

Page 46 of 48
4) Custom, animation, slide transition, auto lay out etc.
Ans:- Custom animation:- Animation can be special sound a visual effects which when added to the
text or other objects such as a chart or picture helps to grab the audience’s attention as well as
reinforce the point that needs to be highlighted. Animated slides can be designed so that the test
appears from any of the choose corner.
slide transition:-Transitions are special effects that introduce a slide in a presentation. It refers to the
appearance of slide on the screen during on screen presentation.
Auto lay out:- There are the slide layout that have readymade place holders for tittles test and objects
such as clipart graph and charts.

5) Various views of slides presentation.

Ans:-
i) Normal view:- It displays three different panes, i.e. slides, outline and notes.

a. Slide pane is the largest of The three panes. The user can add text, graphics, tables, charts
and other objects to the presentation on the slide pane.
b. The outline pane is one the left side of the window and displays an outline of the
presentation this pane can also be used to add text content and animation to the slide,
rearrange and organize slides, or move the another slide.
c. The notes pane is one the bottom of the window. It includes space, which is used to write
speaker’s notes or information about the presentation.

ii) Outline view:- This views feature also displays the three panes, i.e. outline, slide and notes
pane but in different sizes. This view option provides more space to create the detailed outline.
The slide pane includes a slide miniature that helps to see all the changes the user bring about in
the outline pane, the buttons for working with outline becomes available on the standard and
formatting toolbars.

iii) Slide view:- This view option provides a close look of the slide. The notes page is not visible
when the option is embedded.

iv) Slide sorter view:- In the slide sorter view all the slides in the presentation are displayed on the
screen at the same time in a miniature form. This helps the user to add, delete, and move the
slides as well as odd timings and select animated transitions for moving from slide to slide in a
convenient manner.

v) Slide show view:- This view option allows the user to view the animations as they would
appear in a slide show, full screen without any power point menus, toolbars or other features.

Page 47 of 48
6) Slide master, Slide?
Ans:-
A slide has at most following six parts:-
i) Title:- It appears at the top or the slide.
ii) Body text:- This is the main part of the slide. The text in a slide is usually in the bulleted form
or numbered phrases. The text appears inside a resizable and movable container known as place
hold.
iii) Some slides contain one or more graphics pictures or video clips. Each graphic resides in its
own resizable placeholder.
iv) The data and time appears in the slide though it is optional.
v) The footer actually appears at the bottom or the slide.
vi) The slide number appears in the lower right corner.

Slide Master:- to change the formatting in the slide master follow the steps:-
i) From the view menu click master and then click slide master.
ii) To change the formatting of text in the first level bullet points, click the placeholder that displays
click to edit Master, text, styles and apply formatting from the formatting toolbar.
iii) Similarly to change the bullet used at the first level, click the text, click to edit master text styles,
then from the format menu, click bullets and Numbering and choose the bullets that suits once
presentation. Alternatively. One can even choose picture, bullets from the clipart.
iv) One can change the background of every slide in order to place logo graphics drawing in the
slide master.
Short note on:-
i) Fonts:- A family of alpha numeric characters symbols and punctuation marks that share the same design.
Modern applications provide many different fonts and enable users to use different fonts in the same
document. Also called type face.
ii) Internet:- Internet is worldwide system of computer network, that is network of network, which allows
the participant (users) to share information on those linked computers. It is derived in two words, Inter
connection and networks. The internet is loosely organized international collaboration of autonomous
inter connected networks, which support host to host communication through voluntary adherence to
open protocol and procedures defined by internal standard.
iii) E-mail:- Electronic mail is a fast and irresponsive way to communication with each other internal users
around the world. It has dual advantage of not only being folder but cheaper to use as well. Each user of
E-mail has a mail box address to which messages are sent and the mail box can be access in anywhere
and anytime. One can receive and send E-mails from all systems whether it is a windows PC or a Unix
machine.
iv) GUI:- GUI are systems that allows creation and manipulation of user interface employing windows
menus, icons, dialog boxes, mouse and keyboard, Macintosh toolbox, Microsoft windows & X-windows
are some example of GUIS.
v) MS-EXCEL FUNCTONS:- Excel contains many predefined and in built formulas which are known as
functions. Functions can be used to perform simple or complex calculations. Some of the Most frequently
used functions are sum, average, PMT, D look up and IF functions.
vi) Multimedia:- Multimedia has becomes a popular technology in the ever changing world up of words.
Multimedia is made up or two words. Multi means many & media is the source through which one can
convey information.
Multimedia is a blended technological progression rather than just a combination of hardware and
software. It is a combination of platform, communication tools and designing tools.
Multimedia is defined as a system, which exploits the computer to combine text, data, graphics,
animation voice and moving video into a single synchronized production, or presentation.

Page 48 of 48