B.COM (C.

A) CBCS SEMESTER-III 1

Unit- I
MICROSOFT-EXCEL

Introduction
 Excel is an electronic spreadsheet. Spreadsheet or worksheet stores information in the
memory of computer performs data manipulation and displays results quickly.
 The spreadsheet application includes preparation of reports, investment analysis and
production analysis.
 MS-EXCEL is used to perform
o Financial analysis
o Sales analysis
o Profit and loss analysis
o Mathematical analysis
o Statistical analysis etc.
 We can enter data into the worksheet, perform calculation and generate graphs and charts.
 Excel‟s basic file format is a workbook.
 Excel worksheet can store huge amount of information as it has a number of rows and
columns in a sheet.
 It has a number of menus and commands that help the user in creating a worksheet.
 It is user friendly with GUI representation and the processing is very fast.
 It has the facility to move within the document using the arrow keys.
 It has a number of formatting features to format the information in the worksheet.
 Each workbook can hold 255 worksheets.
 By default excel workbook contains three blank worksheets.
 An index tab bottom of the worksheet identifies each work sheet as sheet1, sheet2 etc.
 Worksheets are made up of cells arrayed in columns and rows.
 The rows are identified by numbers 1, 2….and the columns by letters like A, B, C.
 Each cell has a unique address made up of the column and row tables like A1, B6 etc.

Spread sheet:
1. A Spread Sheet is a collection of rows and columns.
2. Spread sheet contains 1,048,576 rows and 16,384 columns.
3. The intersection of row and column is called as a cell.
Work book:
1. In MS-EXCEL, a file is saved as a work book.
2. A work book is a collection of one or more worksheets (Spread sheets).
3. A work book when opened appears with three work sheets- sheet1, sheet2, sheet3.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 2

1. What is MS-Excel and what are the Features of MS-Excel?

Microsoft Excel is a powerful spreadsheet application. The spreadsheet has integrated three
components i.e. worksheet, graphs and database. We can enter data into the worksheet, perform
calculation and generate graphs and charts.
Features of MS-Excel:
1. Workbook: A Workbook is a collection of worksheets. A Workbook may contain one or more
worksheets. So, we can organize various kinds of data into a single file.
2. Worksheet: A worksheet is look like a table, which contains rows and columns. Worksheet is
also known as Spreadsheet.
3. What.. If Analysis: The worksheet of EXCEL is designed to perform What..If analysis
quickly. What...If analysis means, "Tryout various values for the formula in the sheet.”
4. Charts: A Chart is a graphical representation of data. MS-Excel generate various types of
charts (graphs) based on the values of the worksheet. Line graphs, bar charts, Pie charts etc.
5. Database Management: EXCEL sheet look like a table. So we can perform various database
operations like sorting, filtering, subtotals etc.
6. Functions and Formulas: The built-in formulas are called as functions. Ms-Excel provides
different functions for analyzing and manipulating data. Formulas are used in simple
calculations (Addition, subtraction, Multiplication, Division etc).
7. Auto-calculation: By using this feature, we can easily find out the sum, average, maximum,
minimum values of the selected cells, without giving any formula.
8. Auto-Complete: AutoComplete will try to figure out what we expect to type, once we have
entered a few letters.
9. Automatic: In Excel, the change in the value of the cell automatically recalculates the whole
worksheet.
10. Number Formatting: In EXCEL, we can display numbers in different formats like currency,
date, telephone numbers etc.
11. Better Drag-and-Drop: By using this feature, we can select a portion of worksheet by simply
dragging a mouse.
12. Improved sorting and filtering: we can quickly arrange our worksheet data to find the
answers that we need by using enhanced filtering and sorting.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 3

2. How to start MS-Excel? And explain Parts of Excel window.

Step1:- Click on start button and select programs.
Step 2:- Select the MS-Office 2007.
Step 3:- Select the MS-Excel 2007.
Step4:- Click OK.

Then it will display a window.

MS-Excel2007 screen displays several options to help you perform tasks efficiently. Click on a tab to
see a further explanation of what it does and how to use it.

The Microsoft Office Button

The upper-left corner of the Excel 2007 window is the Microsoft Office button. When we click the
button, a menu appears. We can use the menu to create a new file, open an existing file, save a file,
and perform many other tasks.
The Quick Access Toolbar
Next to The Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar
provides frequently used commands. By default Save, Undo, and Redo appear on the Quick Access
toolbar.
The Title Bar
Next to The Quick Access toolbar is the Title bar. The Title bar displays the title of the current
working document. It contains three buttons such as Minimize, restore/Maximize and close
buttons.
The Ribbon
In Microsoft Excel 2007, we use the Ribbon to issue commands. The Ribbon is located near the top
of the screen, below the Quick Access toolbar. At the top of the Ribbon are several tabs.
Clicking a tab displays several related command groups. Within each group are related command
buttons.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 4

Formula bar: It is located below the ribbon. The contents of the active cell always appear in the
formula bar. We use formula bar to enter and edit work sheet data.
Name box: The name box displays reference of the selected cells.
Rows and columns headings: Worksheet made up of rows and columns. Horizontal lines are called
as rows and vertical lines are called as columns.
Cell, Active cell and cell address: The area formed by the intersection of a row and column is
called a cell. The cell with the cell pointer is called active pointer is called active cell.
Worksheet area: This area contains all the cells of the current worksheet identified by column
headings, using letters along the top, and row headings, using numbers along the left edge with tabs
for selecting new worksheets.
Sheet tabs: Excel 2007 contains 3 blank worksheet tabs by default. Click on the intended tab will go
to the particular worksheet.
Status bar: It displays bottom of the screen. On the right and side zoom control and view buttons is
there and left hand side Ready, enter and edit modes is there. By default excel mode is ready mode.
Horizontal scroll bars: It is to move the worksheet area in left or right direction.
Vertical scroll bar: It is to move the worksheet area in top or bottom direction.

3) Explain various Ribbons in Excel 2007? Or different Menu’s in Excel.

Ribbons graphically display the changing features as you click on the menu-bar tabs.

Home tab
Home tab contains the most frequently used options such as cut-copy-paste, font formatting,
alignment, Number, Conditional formatting, etc. All the options are used to format the data.
Home tab contain we have 7 groups: Clip board, Font, Alignment, Number, Styles, Cells, and
Editing.
Insert tab
We use Insert tab to insert the picture, charts, filter, hyperlink etc. We use this option to insert the
objects in Excel.
Insert tab contain we have 10 groups: Tables, Illustration, Apps, Charts, Reports, Spark lines,
Filters, Links, Text, and Symbols.
Page layout tab:
In Page Layout tab, we use to prepare the workbook for printing and exporting to PDF format.
Through this command, we can adjust the page in the way we want to see after printing.
In this menu tab, we have 5 groups: Themes, Page setup, Scale to fit, Sheet options, Arrange.
Formulas tab:
We use Formula tab to insert functions, define the name, create the name range, review the
formula, etc. In ribbon, Formulas tab has very important and most useful functions to make
dynamic reports.
Formulas tab contain we have 4 groups: Function library, Defined names, Formula auditing,
Calculation.
Data tab:
We use Data tab for the large amount of data. It is useful to import the data by connecting with the
server, and we can import data automatically from web, MS Access etc. And sort & filter are very
helpful options we have in Excel.
Data tab contains 5 groups: Get external data, Connections, Sort& filter, data tools, out line.
Review Tab
Review tab contains the editing feature, comments, track changes and workbook protection options.
Review tab contains 4 groups: Proofing, language, comments, changes.
View Tab

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 5

Every tab has its own importance in Excel ribbon in which View tab helps to change the view of
Excel sheet and make it easy to view the data. Also, this tab is useful for preparing the workbook for
printing.
View tab contains 5 groups:- Work book views, show, zoom, window, macros.
4). How to Create, save, open, close, prints and deletes a work sheet?
Creating a new workbook:
When we open MS-Excel, a new workbook will be opened. A work book contains three work
sheets- sheet1, sheet2, and sheet3.
Step1: Click the Office Button,
Step2: Select New
Step3: Click create button.
Or
Press ctrl + N.
Saving a workbook:
Step1: Click the save button on the quick access tool bar
Or
Press Ctrl+ S
Step2: Then the Save as dialog box appears, type the file name and click save button.
Opening a workbook:
To open an existing work Book, follow the below steps.
Step1: Click the Office Button
Step2: Select Open
Or
Press Ctrl+ O
Then the Open dialog box appears, select the file and click open button.
Printing a workbook:
Step1: Click on office button
Step2: Select print option
Step3: Click ok.
Renaming the workbook:
The SAVEAS option is used to rename the files. When we click on the Save as option the save as
dialog box appears to rename the file. Then enter the file name and click on the save button.
Closing the workbook: The CLOSE option can be used to close the current worksheet. (Or) press
Ctrl+ W.
5). Write about Entering data in worksheet.
To Enter Data in excel
1. Click on the cell.
2. Begin typing. (If you make a mistake. use the Backspace key)
3. Press Tab key to move the next cell.
4. Press Enter key to go to the next row.
Generally three types of data can be entered into worksheets: -
i. Text
ii. Numbers and
iii. Formulas.
Text Entry:
When the input starts with an alphabet in a cell, then it is treated as Text by MS-EXCEL. The text
is left justified in the cell. We can edit the alignment of text using formatting features.
Editing means changing default settings. You can edit data in your worksheet to copy, cut, remove

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 6

data or update data.

Number Entry:
When the input starts with a number in a cell, then it is treated as number by MS-EXCEL. The
number is right justified in the cell. We can change alignment of numbers using formatting features.
Formula Entry:
Formula is a self defined instruction for perform calculations. A formula must begins with a (=)
equal to symbol.
6). Write about how to editing data in worksheet. Or Editing features in Excel.
Editing is the process of modifying cells/worksheets and manipulating the entered data.

The Microsoft Office Clipboard contains COPY, CUT and PASTE options.
In the HOME TAB the EDITING group contains FIND, REPLACE, GOTO, and SORT &
Filter options.
To Edit a Cell’s Contents: Select the cell, Click the formula bar, and edit the cell contents.
And press enter when you finish.
Copying and Pasting Data: To move a copy of your data from its current location to a new
location:
 Select the cell or range of cells containing the data you wish to copy.
 From the Clipboard group of the Home tab, click on the Copy button.
 Click in the destination cell for your data.
 From the Clipboard group of the Home tab, click on the Paste button.

Cutting and Pasting Data: To move your data from its current location to a new location.
 Select the cell or range of cells containing the data you wish to cut.
 From the Clipboard group of the Home tab, click on the Cut button.
 Click in the destination cell for your data.
 From the Clipboard group of the Home tab, click on the Paste button.
Deleting data:
 To delete data in a cell, you just Select them and press delete.
Undo and Redo:
 The undo command is used to cancel the most recent action.
 The Redo command is used to recall the actions cancelled by the undo command.
Find and Replace: Find and Replace features is useful to locate data in the worksheet and replace it
with new data.
Find data: On the Home tab, in the Editing group, click Find.
1. In the Find what box, type the text that we want to search.
2. To find each word or phrase click, Find Next.
3. To find all words at one time, click Find All.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 7

Replace data: On the Home tab, in the Editing group, click Replace.
1. In the Replace with box, type the replacement text.
2. To find the next occurrence of the text, click Find Next.
3. To replace an occurrence of the text, click Replace.
4. To replace all occurrences of the text, click Replace All.
To insert a Column or row: Place the mouse pointer where you want to insert column or row.
From the cells group of the home tab click insert option
To delete a column or row: Place the mouse pointer where you want to insert column or row.
From the cells group of the home tab click delete option.
To insert comment: Select the cells where you want to insert a comment and click the review tab on
the ribbon. Click the new comment button in the comments group. Type a comment, then click
outside the comment text box. Point to the cell to view the comment.
7). Explain Number Formatting in Excel.
Formatting: Formatting means to change the default setting on a worksheet.

In the Home tab  cells group, select Format  format cells

The format cells dialog box appears -

1. To change the number option, click NUMBER TAB

2. Then the following options displayed like General, Number, percent, currency, accounting, date,
time and text etc.

3. Choose any option then click on ok.

General: The general format is default number format, if the cell is not wide enough to show the
entire number, the general format rounds numbers with decimals and uses scientific notation for
large numbers. We can reset a number format to general format Ex: 256

Number: It is similar to number format, but it allows to specify number of decimal places and sign if
the number of decimal places is 2. A number 256 represented as 256.00

Currency: It is similar to number format, it has currency symbol as prefix. If you have selected „$‟ as
a currency symbol. A number 256 represented as $256.00
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 8

Accounting: It arrangement the currency symbols and decimal places in a column.

Date: It display date and time serial numbers as date values.

Percentage: It multiplies the cell value by 100 and display with a percentage symbol. Ex: 25600.00%

Fraction: It contain fractional values like 2/3, ¼.

Scientific: It displays the numbers with +E or –E, ex: 1.23+E02 means 1.23 * 10 power 2.

Text format: Text format cells treated as a text even a number is in the cell. The numbers are left
aligned in this format.

8) Explain different cell References in Excel. Or

What is cell referencing? Explain the various types of cell referencing.

Cell referencing: The cell address is known as the “cell reference”. The cell referencing is used in
formulas. It can be a single cell address like D5 or a group of cells such as D5:D10.
The three methods of referencing cells are:
1. Relative referencing
2. Absolute referencing
3. Mixed referencing
1. Relative Referencing:
Excel recalculates any formula, when formulas are moved. They automatically change relatively to
the location to which they are moved. This is known as relative referencing.
Example: Suppose, if the cell C2 contains the formula =A2+B2, whenever we copy this formula to
the next row i.e. C3 then the formula in C3 is =A3+B3.
2. Absolute Referencing:
In absolute referencing the formula will not change relative to the position, when the formula is
moved or copied. In absolute address „$‟ sign is used to indicate the absolute position of the cell
address.
Example: The formula using Absolute reference =$A$2+$B$2

3. Mixed Reference: cell reference containing both relative and absolute reference.
Example:

$A10+$B10: Formula with absolute column reference and relative row reference.

While copying or moving the formula, Column A, B is fixed and row 10 varies.

A$10+B$10: Formula with absolute row reference and relative column reference.

While copying or moving the formula, Columns A, B vary and row 10 is fixed.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 9

9). what is a formula in Excel. Explain formulas with suitable examples?

What is formula? How to enter and edit a formula.
One of the most powerful features in MS-EXCEL is its capability to recalculate. This can be done
through formulas.
Formula:
 Formula is a self defined instruction for perform calculations.
 Formula works according to the cell addressing.
 Formula appears on the formula bar.
 A formula must begin with a = (equal to) symbol.
 A formula always starts with equal sign.
 A formula can contain up to 255 characters.
 Spaces should not be used.
Example:
In the work sheet, column A contains five values.
Name box shows active cell address A7.
Formula bar shows formula
= A2+A3+A4+A5+A6
The result of the formula is available in the cell A7: 320
Editing formula:
1. Click on the cell which contains the formula or
result.
2. Click in formula bar make necessary changes.
3. Press enter key or click on check mark.
10). Explain Auto-fill and custom fill options in Excel.
Auto fill:
Auto fill is one of the feature present in the ms excel. When you typing a day, month, year and
number the automatic series will be appeared by dragging it. This feature is called Auto fill. For
Example if your typed “jan” and then dragged then it displays months form” jan to dec”.

To use the simple Excel Auto fills:

1. Enter a value into the start cell.

2. Use the mouse to drag the 'fill handle' across the range of cells to be filled;
3. When you drag the 'fill handle' across the range of cells to be filled, Excel will fill the selected
cells, by either repeating the value in the first cell or by inserting a sequence from the first cell
value (e.g. 1, 2, 3, ...);

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 10

4. Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of
cells. This will give you the following different options:
 Copy Cells - copy the initial cell across the selected range;
 Fill Series - fill the selected range with a series, starting with the initial cell value;
 Fill Formatting Only - fill the selected range with the formatting, but not the values of the
initial cell;
 Fill Without Formatting - fill the selected range with values, but do not copy the formatting
from the initial cell.
Ex: Auto-fill Text Values.
 The Excel Auto fill will generally fill a column with text values by repeating the value(s) in
the first cell(s).
Months (abbreviated or full names):
Custom fill:
We can also create a list that is displayed like auto fill is known as the custom fill. It can be achieved
as by selecting the custom list option. To do these follow these steps.
Step1: click office button
Step2: choose “Excel Options” in the lower right corner of the menu.
Step3: in the displayed window click on “edit custom list” option.
Step4: In that window select new list and type required data click Ok button.

11). Write about printing options in Excel.

Printers are used to print textual and graphical information on

paper.
There are three print options accessible from the office button in
excel 2007. Follow these steps.
Step1: Click on the office button, then it will display drop down
menu.
Step2: From the displayed menu select the arrow to the right of
print.
Step3: Then it will display the following options.
1) Print
2) Quick print
3) Print preview
Print: - Print option will display standard dialogue box, it contain various options it contain various
options like printer, print range, number of copies etc.
Microsoft Office button Print
Then the print option dialogue box will
appeared.
i. Printer: - This option is used to change
Printer.
ii. Print range: It contain two options
 All: The default setting – This option
allows print all pages in the worksheet.
 Pages: This option allows specifying
start and ending page numbers for those
pages to be printed.
iii. Print what: - it contains the following
options.
Active sheet: - It is default setting, prints the
work sheet page that was on screen when the
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 11

print dialogue box was opened.

 Selection: - Prints a selected range on the active worksheet.
 Workbook: - Prints pages in the work book containing data.
 Copies: - It contains two options.
 No. of copies: set the number of copies to be printed.
 Collate: If printing more than one copy of a multi-page workbook, you can choose to print
copies in sequential order. It uses current print settings such as default printer and paper size.
Quick Print: - This option is used to print the current worksheet with one click. It uses the current
printer settings such as default printer and paper size etc. Generally it is used for to print draft copies
of work sheet for proofing.
Print preview: - When we click this option, it will display a separate window; it is used to check the
details before printing. It contains the following options.
 Print – Opens the Print dialog box.
 Page Setup – Opens the Page Setup dialog box.
 Zoom – Allows you to magnify the worksheet.
 Next and Previous buttons – Scroll through all pages being previewed.
 Show Margins – Displays margins for pages being previewed.
 Close Print Preview – Closes the preview window and returns you to the active worksheet.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 12

Unit-II
Formatting options
1). Write about formatting features are in excel:
Formatting Cells: In Ms-Excel, Formatting means to change the default setting of worksheet, row
column, cell etc. Ms-Excel provides various formatting features to create a worksheet more
impressive. They are:
 Change the Row height
 Change the column width
 Alignment of cell values.
 Changing font, font size, color etc.
 Showing borders to the cells etc.
Steps:
 In the Home tab  cells group, select Format option.
 It will display Row height, Column width:

Change the row height: if you want to change “ROW HIEGHT” in Excel follows the given steps

1. Select FORMAT  ROW HEIGHT

2. It displays Row Height dialogue box
3. Enter Row height and click on ok button.
Change the column width: if you want to change “COLUMN WIDTH” in Excel follows the given
steps
1. Select FORMAT  COLUMN WIDTH
2. It displays Column Width dialogue box
3. Enter Column Width and click on ok button.

Format Text: if you want to change the font name, font size, font style, borders, fill color and color
we use the format text option.

Step1: select the cell or range of cells.

Step2: Home tab font group

Format values: If you want to change the number format we use this option.

Step1: select the cell or range of cells

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 13

Step2: Home tab  Number

To copy Formatting with Format painter: Select the cells with the formatting you want to copy
and click the format painter button in the clipboard group on the home tab. Then select cells you
want to apply the copied formatting to.

To change cell alignment: Select the cells and click the appropriate alignment button ( Align left,
center, Align right) in the alignment group on the home tab.

To Add Cell Borders: Select the cell, click the border button list arrow in the font group on the
Home tab, and select a border type.

To add cell shading: Select the cells, click the fill color button list arrow in the font group on the
Home tab, and select a fill color.

To apply a document theme: Click the page layout tab on the ribbon, click the themes button in the
themes group, and select a theme from the gallery.

To insert header or footer: Click the insert tab on the Ribbon and click the Header & footer button
in the text group enter header text.

Format Sheet: Using this feature, we can do the following;

 Change the name of worksheet.
 Hide or display a worksheet.
 Fill the background of a worksheet with a picture; but this will not be printed.

FUNCTIONS

2) What are functions? Give its advantages

Function Definition
A function in Excel is a built-in formula that performs a mathematical operation or returns
information specified by the formula. As with every formula created in Excel, each function starts
with an equal (=) sign.
Function Syntax
The syntax of a function begins with the function name, followed by an opening parenthesis, the
arguments for the function separated by commas, and a closing parenthesis. If the function starts a
formula, an equal sign (=) displays before the function name.

Syntax = Function Name (Arguments)

Example: =SUM (D2:F2)

In the above example, the function name is Sum and the argument for the function is the range
“D2:F2”.
Parts of Functions:
Every function in MS-EXCEL contains two parts. They are
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 14

1. Function name and 2. Arguments

 Function name indicates the work to be done by a function like SUM( ), COUNT( ) etc.
 Arguments are the values to be given to the function. Arguments are enclosed in parenthesis.
They can be strings, numbers or both.
Steps:
 In the formulas tab, select Insert function.
 Insert function dialog box appears.
 We can select the required function type from the category.

Advantages of functions:
1. Functions are in-built, so the user need not write the functions to use it.
2. Functions are used to calculate simple and complex computations.
3. Complex calculations can be done easily and quickly using functions.
4. The result provided by the functions is accurate and reliable.
5. Some functions like IF helps to check the arguments and shows results based on the values.
6. Functions are used in our day to day activities to find the interest rate on deposits, repayment
of loan amount etc.

2). Write about the various types of functions available in MS-Excel.

Functions are predefined formulas that perform calculations by using the given values, called
arguments. The various types of functions available in MS-EXCEL are:
1. Mathematical functions
2. Statistical functions
3. Engineering functions
4. Logical functions
5. Date & time functions
6. Financial functions
7. Text functions
8. Financial functions
Mathematical functions: These functions are used to calculate simple and complex mathematical
and trigonometric operations such as finding square root, factorial, GCD, SIN, COS etc. There are
several functions available in this category. Some of the functions are:

1. Abs( ): This function returns the absolute value of a given number.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 15

Syntax: =abs (number) Example: =abs (-11.56) output:11.56

2. Sqrt ( ): This function returns the square root of a given number.

Syntax: =Sqrt (number) Example: =sqrt (625) output: 25

3. Fact ( ): This function returns factorial of a given number.

Syntax: =fact (number) Example: =Fact (5) output: 120

4. Gcd ( ): This function returns the greatest common divisor of two or more numbers.

Syntax: =GCD (number1, number2) Example: =GCD (24, 36) output: 12

5. LCM ( ): This function returns least common multiple of two or more numbers.

Syntax: =LCM (number1, number2) Example: =GCD (4, 3) output: 12

6. Mod ( ): This function returns the remainder.

Syntax: =Mod (number, divisor) Example: =Mod (10, 3) Output: 1

Statistical functions: Statistical functions perform statistical analysis on ranges of data. These
functions will take up to 30 arguments.
1. Sum( ): This function adds all the numbers in a range.

Syntax: =Sum (cell number: cell number) Example: =Sum (A1:A10)

2. Average ( ): This function returns the average value in a set of values on a worksheet.
Syntax: =Average (cell number: cell number) Example: =Average (A1:A10)

3. Max ( ): It returns the maximum value from a given range.

Syntax: =Max (cell number: cell number) Example: =Max (A1:A10)

4. Min ( ): It returns the minimum value from a given range

Syntax: =Min (cell number: cell number) Example: =Min (A1:A10)

5. Count ( ): Counts the number of cells that contain numbers within the list of arguments.
Syntax: =count (cell number: cell number) Example: =count(A1:A10)
6. Mode ( ) : It returns a number, which occurs more number of times in the specified list.
Syntax: mode (number1, number2, …)
7. Rank ( ): It is used to find the rank of a given number with in the specified list of values. Use
absolute address for reference and relative address for number.
A B C D E
1 S1 S2 S3 TOT RANK
2 55 96 87 244
3 77 78 88 249
4 86 87 88 249
5 67 56 67 196

SYNTAX: rank(number, reference)

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 16

To find rank of cell d2 by comparing the cells d2 to d5, type function in e2.
=rank(d2,$d$2:$d$5)
D2 is the number to which you are finding the rank.
Engineering functions: These functions are used to perform engineering operations like converting
data from one format to other format etc.

1. Bin2Dec( ): This function converts binary number into decimal number.

Syntax: =Bin2dec (number) Example: =Bin2dec(111) Output:

2. Bin2Oct( ): This function converts binary number into octal number.

Syntax: =Bin2Oct (number) Example: =Bin2Oct (111) Output:

3. Bin2hex( ) : This function converts binary number into hexadecimal number.

Syntax: =Bin2hex (number) Example: =Bin2hex (1010) Output:

4. Dec2bin( ): This function converts decimal number to binary number.

Syntax: =Dec2Bin(number) Example: =Dec2bin(7) Output:

5. Dec2Oct ( ): This function converts decimal number to octal number.

Syntax: =Dec2Oct (number) Example: =Dec2Oct(7) Output:

6. Oct2 bin( ): This function converts octal number to binary number.

Syntax: =Oct2Bin(number) Example: =Oct2Bin(7) Output:

7. Oct2 Dec( ): This function converts octal number to decimal number.

Syntax: =Oct2Dec(number) Example: =Oct2Dec(15) Output:

8. Hex2bin( ): This function converts hexadecimal number to binary number.

Syntax: =Hex2bin(number) Example: =Hex2bin (“A”) Output :

9. Hex2Dec( ): This function converts hexadecimal number to decimal number.

Syntax: =Hex2dec(number) Example: =Hex2dec( “A”) Output:

Logical functions: These functions will perform logical operations on the data. They are used to
compare values using relational expressions.
1. If ( ): If the condition is true then true statement will be printed. If the condition is false then
false statement will printed.

Syntax: =If (condition, true-statement, false-statement) Ex: =If (d5>=35,”pass”,”fail”)

2. And( ): This returns TRUE if all its arguments are TRUE; returns false if one or more arguments
are FALSE.

Syntax: =AND (condition1, condition2………..) Example: = AND(D5>35,A5>50)

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 17

3.OR( ): This returns TRUE if any condition is TRUE; returns FALSE if all conditions are FALSE.

Syntax: =OR (condition1, condition2………..) Example: =OR(100>10,50<10)

Date & Time functions: These functions work on date and time values.
1. TODAY ( ): This function returns the current date. This function does not require any
argument.

Syntax: Today ( )  Example: Today ( )  Result: 2/09/2012

2. NOW ( ): This function returns the current date and time. This function also does not require
any argument to be given by the user.

Syntax: Now ( )  Example: Now ( )  Result: 2/09/2012, 10.23

3. DATE ( ): This function returns the number that represents the current in Microsoft excel
format.

Syntax: date (year, month, day)  Example: date (2012, 12, 4) Result: 12/4/2012

4. DAY ( ): This function returns the day of the month from te given date. If 12/4/2012 is given
date in A1 cell, then the function returns 4,

Syntax: day (serial number)  Example: day (A1)  Result: 4

5. MONTH ( ): This function returns the month from the given date. If 12/4/2012 is given in A1
cell then the function returns 12.

Syntax: month (serial number)  Example: month (A1)  Result: 12

6. YEAR ( ): This function returns the year from the given date. If 8/3/2012 is given in A1 cell,
then this function returns 2012.

Syntax: year (serial number)  Example: year (A1)  Result: 2012

7. TIME ( ): This function returns a time format when hours, minutes and seconds are given as
numbers. Syntax: Time (hours, minutes, seconds)

Text functions: These functions, generally, will work on string values (text). Some functions will
take text and/or numbers as input and give text output; some take text as input and give numeric
output.
1. Char(): This function returns ASCII characters. The range is (0-255).
Syntax: =Char (number) Example: =Char (65) Output: A

2. Code ( ): This function returns ASCII values of a given character.

Syntax: =Code (character) Example: =Code (“A”) Output: 65

3. Upper( ): This function converts the given string into uppercase .

Syntax: =Upper (string) Example:=Upper (“mahesh”) Output: MAHESH

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 18

4. Lower( ): This function converts the given string into lowercase .

Syntax: =lower (string) Example: =lower (“MAHESH”) Output: mahesh

5. Len( ): This function returns the number of characters in a given string.

Syntax: =len(string) Example: =LEN("mahesh") Output: 6

8. Financial functions: Excel financial functions can be used to determine changes in dollar value
of investments and loans and other financial transactions. Most of the financial and cost
accountants use excel financial functions for their work. Some of the financial functions are:
1. RATE ( ): It calculates the rate of interest per period.
Syntax: RATE ( nper, pmt, pv, fv, type, guess)
Where
nper – Total payment period.
Pmt – Payment made per period
Pv – Present value of the total amount.
Fv – Future value
Type – number 0 or 1 depending on whether the payment is to be made at the end of the period or
at the beginning respectively.
RATE
Data Description
4 Years of the loan
-200 Monthly payment
8000 Amount of the loan
1% Monthly rate of the loan with the above terms (1%)
9.24% Annual rate of the loan with the above terms (0.09241767 or 9.24%)
RATE(B21*12, B22, B23)*12
2. FV( ) : It calculates the future value of any investment
Syntax: fv(rate, nper, pmt, pv,type)
rate – periodic rate of interest
Nper – total payment period
Pmt –payment made per period
Pv – present value of the total amount
Type – number 0 or 1 depending on whether the deposit is to be made at the end of the period or at
the beginning respectively.
FV
Data Description
6% Annual interest rate
10 Number of payments
-200 Amount of the payment
-500 Present value
1 Payment is due at the beginning of the period
$2,581.40 Future value of an investment with the above terms ( 2581.40)
FV(B12/12, B13, B14, B15, B16)

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 19

3.Pv() : It calculates the present value of any investment.

Syntax: pv( rate, nper, pmt, fv, type)
Where rate – periodic rate of interest
Nper – total payment period
Pmt – payment received per period
Fv – future value of the total amount
Type number 0 or 1 depending on where the payment is received at the end of the period or
beginning respectively.
PV
Data Description
500 Money paid out of an insurance annuity at the end of every month
8% Interest rate earned on the money paid out
20 Years the money will be paid out
($59,777.15) Present value of an annuity with the terms above (-59,777.15).
PV(A4/12, 12*A5, A3, 0)

4.NPER( ): It calculates the number of payment periods required to calculate the investment to a
specified future value.
Syntax: nper(rate, pmt, pv,fv,type)
NPER
Data Description
12% Annual interest rate
-100 Payment made each period
-1000 Present value
10000 Future value
1 Payment is due at the beginning of the period
59.67386567 Number of periods =60
NPER(B3/12, B4, B5,B6,1)
5. NPV ( ) : Calculates the net present value of an investment by using a discount rate and a series of
future payments (negative values) and income (positive values).
Syntax : NPV(rate,value1,value2, ...)
Rate is the rate of discount over the length of one period.
Value1, value2, ... are 1 to 254 arguments representing the payments and income.
Value1, value2, ... must be equally spaced in time and occur at the end of each period
NPV
Data Description
10% Annual discount rate
-10,000 Initial cost of investment one year from today
3,000 Return from first year
4,200 Return from second year
6,800 Return from third year
$1,188.44 Net present value of this investment (1,188.44)
NPV(A3,A4,A5,A6,A7)

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 20

Unit-III
Charts
1) What is a chart? How to create charts in MS excel.
Chart: A chart is a graphical representation of numerical data in a work sheet. Charts
make data easy to read, understand and analyze. By using excel we can create 2
Dimensional and 3 Dimensional charts.
Creating charts:
1. Select the data in a worksheet
2. Insert tab charts group  click create chart option
3. Then the Insert Chart dialog box appears.

4. Select the Chart type and click on the ok button.

2) What is chart? Explain various types of Charts in Ms-Excel?
Chart: A chart is a graphical representation of numerical data in a work sheet. Charts
make data easy to read, understand and analyze. By using excel we can create 2
Dimensional and 3 Dimensional charts.
TYPES OF CHARTS:
 Column chart
 Line chart
 Pie chart
 Bar chart
 Area chart
 x y (scatter) chart
 Stock chart
 surface chart
 Doughnut chart
 bubble chart
 radar chart

Column chart:
It compares the values across the category but displays them in vertical bars. The
height of the column is proportional to the value of the data point according to a scale.
Line chart:
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 21

The data points in a series are equally spaced horizontally. The points in a series are
joined by a single line.
Pie charts:
It displays the contribution of each value for a total value. The Pie chart is drawn for
the first data series when more than one data series is selected.
Bar charts:
It also compares the values across the category but displays them in vertical bars. The
length of the horizontal bar is proportional to the value of the data point according to
a scale.
Area chart:
The data points in a series are equally spaced horizontally. The points in a series are
joined by straight lines.
Scatter (xy) chart:
In XY chart two or more data series are plotted. The first data series is plotted on X-
axis and the result of the series are plotted on Y-axis.
Stock charts:
Three series must be selected to display stock charts. For example - high, low and
close values of a stock price. The chart consists of a vertical line between high value,
low value and closing value.
Surface chart:
A surface chart is useful, when we want to find optimum combination between two
sets of data.
Doughnut chart:
The doughnut chart is like a pie chart, but the circle has a hollow center and more
than one series may be shown in it.
Bubble chart: it is similar to scattered chart, it compares 3 sets of values, the third
value displays at the size of the bubble marker.
Radar chart:
Data that is arranged in columns or rows on a worksheet can be plotted in a radar
chart. Radar charts compare the aggregate values of a number of data series.

Q) What is chart? What the parts are in excel chart?

Explain chart terminology in MS Excel.
How work sheet data is represented in a chart?
Chart: A chart is a graphical representation of numerical data in a work sheet. Charts
make data easy to read, understand and analyze. By using excel we can create 2
Dimensional and 3 Dimensional charts.
Creating charts:
1. Select the data in a worksheet
2. Insert tab charts group  click create chart option
3. Then the Insert Chart dialog box appears.
Chart parts and Terminology: A chart consist the following components.
Data Series: A chart represents the relationship between two sets of data. One set of
data represents X- axis and other set of data represents Y- axis.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 22

Data Markers: Data markers are the bars, lines, dots, pictures or other elements used
to represent a particular data point (a single value in the series). When charts have
more than one data series, the markers for each series look different.
Axes: MS- EXCEL uses three axes - X, Y and Z axes. Usually the X-axis can be seen
horizontally (left to right) and Y-axis is vertically (bottom to top). In 3-D charts, the Z-
axis displays vertically and the X and Y axes are at angles to display values.
Category Names: Category names are worksheet labels for the data being plotted
along the X-axis. Some charts show labels on Y-axis.
Legend: Legends are displayed in a box. A sample of the colour, shape or pattern is
used for each data series.
Gridlines: These are the lines that are displayed on chart horizontally and vertically
to show X and Y axes.
Data table: It is the source of data for plotting the chart.
Explain Data sorting features in Excel?
Sorting is the process of arranging the data in Ascending or Descending order.
Procedure to Sorting data
Step1: Select the range of cells you want to sort.
Step2: Select sort and filter group from the Data tab.
Step3: Click the Sort command to open the Custom Sort dialog box

Step4: Click the drop-down arrow in the Column Sort by field, and then choose one of the
option.

Step5: Choose what to sort on. In this example, we'll leave the default as Value.

Step6: Choose how to order the results. Leave it as A to Z so it is organized

alphabetically.
Step 7: Click OK.

The spreadsheet has been sorted. All of the categories are organized in alphabetical
order.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 23

What Is Filtering? Explain filtering features in Excel?

Filter is the process of extracting the rows that matches the criteria. Filtering or
temporarily hiding data in a spreadsheet is simple. This is used to focus on specific
spreadsheet entries.
Procedure to filter data:
Step1: Select the labels
Step2: Click the Filter command on the Data tab from sort and filter group.
Then Drop-down arrows will appear besides each column heading.
Step3: Click the drop-down arrow next to the heading we would like to filter.
Step4: Uncheck Select All.
Step5: Click OK. All other data will be filtered, or hidden, and only the select data is
visible.
To clear filter:
 Select one of the drop-down arrows next to a filtered column.
 Choose Clear Filter From...
Explain how to validate data through Data validation feature?
It defines what data is valid for individual cells or cell ranges, restricts the data
entry to a particular type such as whole numbers, decimal numbers or text and sets
limits on valid entries.
Whenever we enter data into the worksheet, normally, it allows any type of data. But
sometimes we should enter valid values (set or range) in the work sheet. For example,
if we want to enter only a particular range of values i.e. from 0 to 100 in marks
column.
We can apply this feature for numbers, text, date and time data entries. The data
validation restricts invalid data entries in the specified range. But this method works
only at the time of data entry into the cells.
Procedure to apply data validation
Step1: Select cell or range of cells.
Step2: Click on data validation from the data tools group on the data tab.
Then it will display data validation dialogue box.

 In the Settings tab, click List in the Allow drop-down list like whole numbers,
decimal, list, date, time etc.
 By default, the Ignore blank and In-cell Dropdown check boxes are selected.
Do not change them.
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 24

 In the input message tab type the title and message

 In the error alert tab select the style and type the title and message.
Step3: Click OK to apply the setting in the selected range of cells.

Explain how to Grouping cells using the Subtotal command?

Grouping is a useful Excel feature. It gives to control over how the information is
displayed. You must sort before you can group. In this section, we will learn how to
create groups using the Subtotal command.
Subtotals: It calculates subtotals and grand total values for the labeled columns we
select. Excel automatically inserts and labels the total rows and outlines the list.
To create groups with subtotals:
 Select any cell with information in it.
 Click the Subtotal command from the outline on the Data
tab.
 The information in your spreadsheet is automatically
selected, and the Subtotal dialog box appears.
 Decide how you want things grouped. In this example, we
will organize by Region.
 Select a function. In this example, we will leave the SUM
function selected.
 Select the column where you want the Subtotal to appear.
In this example, Amount is selected by default.
 Click OK. The selected cells are organized into groups
with subtotals.
To collapse or display the group:
 Click the black minus sign, which is the hide detail
icon, to collapse the group.
 Click the black plus sign, which is the show detail icon, to expand the group.
 Use the Show Details and Hide Details commands in the Outline group to collapse and
display the group as well.
To ungroup select cells:
 Select the cells you want to remove from the group.
 Click the Ungroup command.
 Select Ungroup from the list. A dialog box will appear.
 Click OK.
To ungroup the entire worksheet:
 Select all cells with grouping.
 Click Clear Outline from the menu.
Product store Region Month Amount
SYSTEM ASSAM East MAR 987
BOOK Arunachalpradesh East JAN 357
East Total 1344
Book tamilnadu South JAN 123
Video karnataka South MAR 321
SYSTEM Andhrapradesh south MAR 753
South Total 1197
Video Gujarath west FEB 456
Book Gova West APR 654
Video Himachapradesh West JAN 789
BOOK Punjab West FEB 159
west Total 2058
Grand Total 4599

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 25

Describe in detail Scenarios in excel.

What-If Analysis in Excel we use to try out different values (scenarios) for formulas. In Excel, a
scenario can be described as a set of cell values that is saved and substituted into worksheet as
required. If you have multiple scenarios saved, we can load different scenarios into your worksheet,
compare and contrast them to see which one gives the best results.
Generally we use scenarios to represent different budget options, evaluate different financial
forecasts, or to compare different data projections based on a number of factors.
Creating a Scenario
When we are creating scenarios for a worksheet, it is a good idea to create a base scenario with the
actual or current data for the worksheet.
To create a base scenario in Excel:
 Select the Data Ribbon, Data Tools group, click the What–If-Analysis button and select the
Scenario Manager
 Click the Add button to display the Add Scenario dialogue box
 Enter a name in the Scenario Name text box
 In the Changing Cells text box, select the cells whose values will be changing with your
mouse (limited to 32 changing cells)
 You can also add some remarks describing the scenario in the Comment area of the dialogue
box if you wish. Click OK to show the Scenario Values box.
 Click OK to return to the Scenario Manager Dialogue box.
To create a second scenario to compare with the base scenario
 Click the Add button to display the Add Scenario dialogue box
 Enter a name for your new scenario in the Scenario Name text box
 Click the OK button to show the Scenario Values box.
 Click the OK button to create the scenario.
To view a scenario:
 Select the Data Ribbon, Data Tools group, click the What–If-Analysis button and select the
Scenario Manager
 Select a scenario from the Scenario Manager
 Click the Show button to see the results of the given scenario in the spreadsheet.
Describe in detail Using Goal Seek.
Goal Seek is a useful what-if analysis tool provided by Excel. With Goal Seek, we can set a formula to a
value (goal) that we would like to reach, and then specify one of the cells that the formula references as a
cell that Excel can adjust in order to reach the goal.
To use Goal Seek:
 Select the cell that contains the formula that you wish to set
to a certain value (goal)
 On the Data Ribbon, Data Tools group, click the What-If
Analysis button and select the Goal Seek option.
 The selected cell is entered into the Set Cell text field
automatically
 In the To Value text field enter the value/result you want
Excel to adjust the Set Cell to
 In the By changing cell text field, select the cell which contains the formula input you want
Excel to adjust to reach your goal.
 Click the OK button, The Goal Seek Status box reports that a solution has been found.
 Clicking the Cancel button will restore the original worksheet values.
 Clicking OK will enter the Goal Seek solution values into the worksheet.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 26

Describe in detail Using Solver

Sometimes, when dealing with more complex problems, the Goal Seek feature cannot provide the
kind of forecast or analysis you are looking for. In this type of situation, Excel 2010’s Solver feature
might be able to help.

The Solver is an Excel feature that is designed for optimizing systems of equations subject to specific
constraints.

The Solver can be used to find optimal solutions for linear programming problems involving multiple
equations and multiple unknowns.

An optimal solution might be one that maximizes profit, or it could be one that minimizes costs.
Basically, the optimal solution will depend on the context of the situation and what you are looking
for.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 27

UNIT-IV
Microsoft Access
What is MS Access? Explain its features.

MS Access is a Relational Database Management System (RDBMS). It is used to store

large amount of data and to retrieve desired pieces of information. For example
telephone numbers in telephone directory, employee‟s records of an industry, student
records of an examination board etc.

Features:
 Access there are four major areas, they are:
Tables: It is Collection of rows and columns, used to store data in database.
Queries: Question or requesting, designed based on tables.
Forms: Used to view data stored in our tables
Reports: Used to print data based on queries/tables that you have created.
 Microsoft Access is a simple desktop application for individual users and
smaller teams.
 The MS Access software can be used efficiently in organizing, the database.
 In MS Access Entering of data, data manipulation, and data retrieval etc. works
can be done easily.
 Therefore MS Access e called as „database management system‟ i.e. DBMS.
 Previously the data so collected, preserved in appear files manually written.
When it is required in future, searching of those preserved files becomes very
difficult. Therefore such problems can be avoided using MS Access.
 By using MS Access we can eliminate Duplication of data.
 We can prepare the forms, reports with the available data in tables in MS
Access.
 By using data types we can store individual data items like text, numbers,
sound, date pictures etc.
 „Macros‟ technique may be used to avoid repetitive works for simple data.
 For large size database, „modules‟ technique can be used in MS Access in
avoiding repetitiveness.
 Import and export to other Microsoft Office and other applications
 A user friendly feature „Tell Me‟ for assistance is available.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 28

What are the components of database?

Data, Databases and tables:

Data: - It is a collection of “Raw facts” from which conclusions can be drawn.

Database: - It is a collection of related and meaningful information stored centrally at

one location. (Or)
 It is a collection of interrelated data.
 Interrelated means relation among data values.
 The main objective of database is record the history for the future use.
 The database always stores data in the Hard disk in form of records.
Database window:
The database window allows the user to work effectively. In the window, all objects of
a database are stored in a single file with .mdb extension name. The database window
provides a managed view of various elements. It displays information about the
database and types of objects it contains.
Tables: - A table is a basic element of the database. A table consists of rows and
columns. The rows are called records. Each row in the table represents a single record
and columns called as fields. Generally field names empno, ename, job, sal etc.

Queries: - Query means question or requesting, the user can arrange the data into
meaningful steps or pieces by using queries. The result of a query is displayed in the
form of datasheet.

Forms: - Forms displays the data from a particular table or query as required by the
user. The required records and fields are placed in the forms and can be edited
according to the need of the user.

A form can be saved separately and in case of need it can be modified, deleted within
the respective table.

Report: -A report displays the information in a format of user‟s choice. Reports are
used to organize our data for a structured presentation. We can use the reports to
manipulate the data into groups and subtotals based on queries, so that only the data
that meets our criteria is printed. Reports can also be based on queries. So that only
the data meets our criteria are printed.

MS -Access provides an efficient way to produce reports by adding charts, graphs,

tables, etc in different styles.

How to create a database?

Step1: Click on start button

Step2: click on MS Office 2007

Step3: Select MS Access 2007

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 29

Step4: In the displayed window click template category in the list and click the
template we want to use like featuring, local templates, business, personal, sample,
education etc. Then click create or click blank database option.

4.1: Templates category  Featuring  Blank database

Step5: Now select the path where we want to store database

Step6: Finally click create button in the right corner of the window.

Draw a neat diagram of MS Access Window and explain its parts.

Parts of Access
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 30

Microsoft Office Button

The upper-left corner of the Access 2007 window is the Microsoft Office button. When we click the
button, a menu appears. We can use the menu to create a new file, open an existing file, save a file,
and perform many other tasks.
Quick Access Toolbar
Next to The Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar
provides frequently used commands. By default Save, Undo, and Redo appear on the Quick Access
toolbar.
Title Bar
Next to The Quick Access toolbar is the Title bar. The Title bar displays the title of the current
working document. It contains three buttons such as Minimize, restore/Maximize and close
buttons.
Ribbon
In Microsoft Access 2007, we use the Ribbon to issue commands. The Ribbon is located near the
top of the screen, below the Quick Access toolbar. At the top of the Ribbon are several tabs.
Clicking a tab displays several related command groups. Within each group are related command
buttons.
In any Access 2007 you are given the following tabs:
1. Home
2. Create
3. External Data
4. Database Tools
Database objects: This area of Access shows a list of the various database components in
your database. Specifically there are three types that will be in this list: table, report, form.
Each has its own unique properties. To make the component active simply click on it in the
list.

Active Component: This area is linked to the database component area and shows the active
component you have clicked on from the list.

Help Button: This button is a useful tool because often times it will answer many question
you have about something in Access.

What is Table and what are the different ways to create table in access?
How to create a Table in design view?
How to create a table without using wizard?
Tables: - A table is a basic element of the database. A table consists of rows and
columns. The rows are called records. Each row in the table represents a single record
and columns called as fields. Generally field names empno, ename, job, sal etc.
Generally we can create tables in three ways. 1. Design view 2. Data sheet view 3.
Table wizard.
Creating table using design view:
Creating a table in Design view is very common because it offers several benefits. In the Design
view, we can create field names, data types and any field descriptions in the upper window pane.
Properties and field attributes for each field display in the bottom pane of the design view
window.
1. Click on Start buttonProgramsMS Office 2007 Microsoft Access2007.
2. Select Blank Access Database option to create a new database.
3. Click on Home tab  Select views groupclick view button select design view.
4. Then it will display save as dialogue box in that window enter the table name and click ok.
OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA
B.COM (C.A) CBCS SEMESTER-III 31

5. The design view of a new table will open for toy to start typing in field names, data types,
and descriptions.
6. Type in the first field name and press Tab or Enter to moves to the data type.
7. Select the data type you want from a dropdown arrow and press Tab or Enter again to
move to the description field.
8. The description field optional, it is use for directions to the person input the data.
9. The Table window displays as shown in below format.

10. Now type the field name and select the data types.
11. Field description: This description is optional; but helps us to remember the purpose of
the particular field.
12. Save the table and enter the data.
Write about data types in MS Access.

Data types: Data types will decide what kind of values need to hold into the field
names. MS – ACCESS supports the following data types.

Text: The field can contain any characters. The Field Size property defines the
maximum number of characters. The maximum cannot be above 255 characters.
Number: The field can contain a number. The Field Size property defines what
kind of number:
Memo: Like a text field, but the maximum number of characters is 65,535.
Access takes more time to process a memo field, so use text fields if sufficient.
Currency: Stores numbers and symbols that represent money.
Date/time: It is used to store date or time.
Yes/No it stores Boolean values (true/false)
OLE – Object: It is used to store objects created in other programs such as
graphic, excel spread sheet or word document.
Hyperlinks: It is used to store clickable links to web pages on the internet or files
on a network.
Lookup wizard: A wizard that helps to create a field whose values are selected
from another table, query or list of values.
Attachment: It is used to attach files and images to the database.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 32

Creating a Table in Datasheet View.

By using data sheet view we can easily create table. In this approach we can enter data directly
without defining structure. It looks like an excel work sheet. By default all the fields data type is
Text If we entered all numbers in a particular column MS Access sets the field data type number. If
we want to more field properties we have to open the table in design view and change the necessary
modifications.
Datasheet view displays a grid of rows and columns. Field names are entered as column headings.
Procedure:
Click Home tabvie group  select data sheet view

What is a Form? What are the different ways to create a form? Explain the procedure to
create using the Form Wizard.
In access forms can be used to create a friendly data entry interface to enter information into
tables and / or queries.

Many forms provide a customized interface for to enter new records into tables and you to format
and print individual records. With the help of queries and relationships, they allow you to enter
data into more than one table at a time.
Different ways to create form:
 Design view
 Form wizard
 Auto Form: Columnar
 Auto Form: Tabular
 Auto Form: Data Sheet
 Chart wizard
Create a form – using Design view: If you wish to design a form according to your taste without using
standard predefine designs, and then we go for design view.
Procedure:

Click create tab and select Forms group and then click Form design.

Now you can use control buttons and then use your own design.

Creating a Form using wizard:

It is a best method to create a form. It is used to read the data from the table or tables, in the same
way it is also used to write the data into the table or tables.
Step1: Create a data base table or open already created table.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 33

Step2: On the Create tab, in the Forms group, click More Forms, and then click Form Wizard.
Then it will display Form wizard dialogue box

Step3: Follow the directions on the pages of the Form Wizard.

Step4: On the last page of the wizard, click Finish.

Auto Form: Columnar: Displays only one record at a time. Data for each record is displayed
vertically. Technically, columnar form's Default View property is set to Single.
Auto Form: Tabular: The field in each record appear on one line, with the labels displayed once at
the top of the form.
Auto Form: Data sheet: The fields in each record appear in row and column format, with one
record in each row and one field in each column. The field names appear at the top of each column.
Chart wizard: Dynamically analyzes information and summarizes it into a chart.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 34

Unit – V
Finding, sorting and displaying data
What is query? Explain creating and using select queries in MS Access.

Queries: - Query means question or requesting, the user can arrange the data into meaningful
steps or pieces by using queries. The result of a query is displayed in the form of datasheet.

Step1: Select the Create tab in the toolbar at the top of the screen. Then click on the Query
Design button under the other group.

Step2: highlight the tables that you wish to use in the query. In this example, we've selected the
Employees table and clicked on the Add button. When you are done selecting the tables, click on
the Close button.

Step3: Add the fields to the query. You can do this by double-clicking on the field name. In this
example, we've added the LastName, FirstName, and Address fields.
Step4: Then click on the Save button at the top left of the window. The Save As window should
appear. Enter the name that you'd like to assign to the query and click on the OK button. In this
example, we've saved the query as Query1.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 35

Write about Multilevel sorting in MS Excel.

Access provides multiple sorts while we designing the query. This is used to view our data exactly
the way we want.

A sort that includes more than one sorted field is called a multilevel sort. First we apply an initial
sort, and then further organize data with additional sorts. For instance, if you a table contain
customers and their addresses, first we must choose the records by city, and then further sort them
alphabetically by last name.

When more than one sort is included in a query, Access reads the sorts from left to right. This
means the leftmost sort will be applied first. In the below example, customers will be sorted first by
the City and then by the Zip Code within that city.

To apply a multilevel sort:

1. Open the query and switch to Design view.
2. Locate the field you want to sort first. In the Sort: row, click the drop-down arrow to select
either an ascending or descending sort.
3. Repeat the process in the other fields to add additional sorts.
4. To apply the sort, click the Run command.
Or.
We can also apply multilevel sorts to tables that don't have queries applied to them. From
the Home tab on the Ribbon, select the advanced drop-down command in the Sort & Filter group.
Select Advanced Filter/Sort, and create the multilevel sort normally. When we finished, click
the Toggle Filter command to apply your sort.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 36

Describe Flat file versus Relational database.

File: A collection of information stored together in a computer under a particular name: to access/
copy/create / delete/ save a file.
Keeping all the data elements in a single file is known as flat file.
Every file on the same disk must have different name. a file contain information. For example your
application will be kept on file. Collection information on the missing childs.
Files disadvantages:
No data type support
It will not support automatic error handling.
It will not support dynamic memory allocation.
It support limited information
It provides poor security.
It will not support multiple users.
Data retrievals and manipulations are time taking process.
Relational database: Relational database management system.
Data base: It is a collection of “Related and Meaningful information “stored centrally at one
location.
Storing different elements in different tables or databases, and programming relationships
between them is known as relational database.
 The main objective of database is record the history for the future use.
 The data base always stores the data in the hard disk in form of records.
 Program data independent
 Controlled duplication of data
 High sharing
 High reliable
 Processing speed is high.
 Easy to maintain.
What is relationship? Explain relationship types.
Relationship:-A relationship is an association among entity for example, a relationship exists between
CUSTOMER and AGENT can serve many customers and each customer may be served by one
customer.
AGENT SERVES CUSTOMER

Data models use 3 types of relationships.

1. One-to – Many relationship (1:M)

2. Many- to- Many relationship (M:N)
3. One –to- One relationship (1:1)
One-to – many Relationship: - A painter painting many different paintings but each one of them
is painted by only one painter.

Painter Paints Paintings

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 37

Many- to- Many Relationship: - A student can take many classes and each class can
be taken by many students.

student takes class

One –to- one relationship: - Each employee is assigned exact only one parking place
and each parking place must be assigned one employee.

employee assigns Parking place

Explain about creating and deleting relations in MS –Access.

After we have created a table for each subject in our database, we must provide Office Access 2007
with the means by which to bring that information back together again when needed. You do this
by placing common fields in tables that are related, and by defining table relationships between your
tables. You can then create queries, forms, and reports that display information from several tables
at once.
Create a table relationship
You can create a table relationship in the Relationships window, or by dragging a field on to a
datasheet from the Field List pane. When you create a relationship between tables, the common
fields are not required to have the same names, although it is often the case that they do. Rather, the
common fields must have the same data type.
Create a table relationship by using the Relationships document tab

Step1: Click the Microsoft Office Button , and then click Open.
Step2: In the Open dialog box, select and open the database.
Step3: On the Database Tools tab, in the Show/Hide group, click Relationships.

If you have not yet defined any relationships, the Show Table dialog box automatically appears.

If it does not appear, on the Design tab, in the Relationships group, click Show Table.

Step4: The Show Table dialog box displays all of the tables and queries in the database.
Step5: Select one or more tables or queries and then click Add. Then click close.
Step6: Drag a field (typically the primary key) from one table to the common field (the foreign key)
in the other table.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 38

The Edit Relationships dialog box appears.

Step7: Verify that the field names shown are the common fields for the relationship. If a field name
is incorrect, click on the field name and select the appropriate field from the list.
Step8: Click Create.

Delete a table relationship

To remove a table relationship, you must delete the relationship line in the Relationships document
tab. Carefully position the cursor so that it points to the relationship line, and then click the line.
The relationship line appears thicker when it is selected. With the relationship line selected, press
DELETE.

Step1: Click the Microsoft Office Button , and then click Open.
Step2: In the Open dialog box, select and open the database.
Step3: On the Database Tools tab, in the Show/Hide group, click Relationships

The Relationships document tab appears.

If you have not yet defined any relationships and this is the first time you are opening the
Relationships document tab, the Show Table dialog box appears. If the dialog box appears, click
Close.

Step4: On the Design tab, in the Relationships group, click All Relationships.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 39

All tables that have relationships are displayed, showing relationship lines.

Step5: Click the relationship line for the relationship that you want to delete. The relationship line
appears thicker when it is selected.

Step6: Press the DELETE key.

Step7: Access might display the message Are you sure you want to permanently delete the
selected relationship from your database?. If this confirmation message appears, click Yes.

How to create a report and print in MS Access?

If we need to share information from our database with someone but don't want that
person to actually work with your database, consider creating a report.
Reports allow you to organize and present your data in a reader-friendly, visually
appealing format.
We can perform the following tasks on forms.
Create a report
Modify the report
Print reports
Creating reports:
Reports give you the ability to present components of your database in an easy-to-
read, printable format.
To create a report:
1. Open the table or query we want to use in your report. We want to print out a
list of last month's orders, so we'll open up our Orders Query.
2. Select the Create tab on the Ribbon Reports group and Click
the Report command.
3. Access will create a new report based on our object.
4. To save our report, click the Save command on the Quick Access toolbar.
When prompted, type a name for your report, then click OK.
Deleting fields:
If our report contains some fields we don't really need to view. For instance, our
report contains the Zip Code field, which isn't necessary in this list of orders.
We can delete fields in reports without affecting the table or query.
To delete a field in a report:
1. Click any cell in the field you want to delete.
2. Press the Delete key.
Printing and saving reports in Print Preview
Print command is used to print reports. , we can also use Print Preview. Print
Preview shows you how your report will appear on the printed page. It also allows
you to modify the way your report is displayed, print it, and even save it as a
different file type.
To print a report:
1. On the Home tab of the Ribbon, click the View command and select Print
Preview from the drop-down list.
2. Your report will be shown as it will appear on the printed page.
3. If necessary, modify the page size, margin width, and page
orientation using the related commands on the Ribbon.
4. Click the Print command.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 40

5. The Print dialog box will appear. Set any desired print options, and then
click OK.
Saving reports:
You can save reports in other formats so they will be viewable outside of Access.
This is called exporting a file, and it allows you to view and even modify reports in
other formats and programs.
Access offers options to save your report as an Excel
file, text file, PDF, XPS file, email attachment, rich text file, or HTML
document.

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 41

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 42

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 43

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 44

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 45

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 46

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 47

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 48

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 49

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA

B.COM (C.A) CBCS SEMESTER-III 50

OFFICE AUTOMATION TOOLS Prepared by Mahesh MCA