STUDY MATERIAL MS OFFICE

Page | 1
Unit 5: MS-EXCEL 2007 Introducing Microsoft Office Excel 2007
LEARNING OBJECTIVES
 Opening Ms-Excel
 Working with Spreadsheet
 Using data in spreadsheet
 Active cell
Microsoft Excel is a spreadsheet program that provides with powerful tools and features to analyze share and manage your data with least effort. A Spreadsheet is defined as a large sheet containing information arranged in a matrix of rows and columns. Spreadsheet software is a program or a set of programs for entering, calculating, analyzing and manipulating sets of number. A computer spreadsheet automatically recalculates all the formulae whenever you make a change in the value or number. There are so many other spreadsheet programs such as Lotus 1-2-3, Quattro Pro etc. are available, but Excel is by far the most popular and has become the world standard.
Steps to Open Microsoft Office Excel
1) Click Start MenuAll ProgramsMicrosoft OfficeMicrosoft Office Excel 2007
Alternative Way
1) Right-clickChoose New from the pop-up menu.
2) Click Microsoft Office Excel Worksheet from the sub-menu.
3) Double-click on the Microsoft Office Excel Worksheet icon to open.
When you open Microsoft Office Excel 2007, the first thing you will notice is the new interface. Now that we’ve opened Excel, let’s take a look at what’s on our screen. Refer to figure below.
A brief discussion about the different components of Excel’s opening Screen:
 Home: You’ll probably spend most of your time with the Home tab selected. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows or columns, plus an assortment of worksheet editing commands
 Insert: Select this tab when you need to insert something in a worksheet—a table, a diagram, a chart, a symbol, and so on.
 Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including settings that deal with printing.
 Formulas: Use this tab to insert a formula, name a range, access the formula auditing tools, or control how Excel performs calculations.
 Data: Excel’s data-related commands are on this tab.
 Review: This tab contains tools to check spelling, translate words, add comments, or protect sheets.
 View: The View tab contains commands that control various aspects of how a sheet is viewed. Some commands on this tab are also available in the status bar.
 Control Buttons: At the right end of the title bar are the three familiar buttons that have the same functions in all windows programs. You can temporarily hide the word window by clicking the Minimize button, adjust the size of the window with the Restore Down/Maximize Button,
and close the active document or quit excel with the close button.
 Name Box: Address bar shows the address of the active cell. In the above figure active cell is A1 that is why we are getting A1 in the name box.
 Formula Bar: The formula bar is located beneath the toolbar at the
top of the Excel worksheet. Use the formula bar to enter and edit
worksheet data. The contents of the active cell always appear in the
formula bar.
 Rows, Columns and Cells: Every worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled A through XFD). After column Z comes column AA, which is followed by AB, AC, and so on. After column AZ comes BA, BB, and so on. After column ZZ is AAA, AAB, so on. Each worksheet has 16,384 columns and
10,48,576 rows. So, each worksheet contains 16384 X 1048576 numbers of cells.
Home Ribbon
Ribbon Tabs
A cell is the intersection of a row and a column. For example, the first cell is referred to as A1 (column A, row 1). Data and formulas are entered into the cells. Each cell can hold only one piece of data at a time.
 Sheet Tabs: Sheet tabs holds list of worksheets by default it provides three Worksheets named Sheet1, Sheet2, and Sheet3
respectively.
 Horizontal and Vertical Scroll Bar: The Scroll Bar is used roll up/down/right and left and see the column/row number changes until you release the mouse button.
 Zoom Slider: A slider that magnifies or reduces the content in the worksheet area.
 Groups: On each tab, buttons are organized into groups. Depending on the size of the program window, in some groups the button you are likely to use most often is bigger than the rest. Dialog box launcher: Related but less common commands are not represented as buttons in the group. Instead they are available in a dialog box, which you can display by clicking the Dialog Box Launcher at the right end of the group’s title bar
Workbook and Worksheet
When you open Excel, the Excel application window opens with a new Excel workbook. A Workbook is a multi-page Excel document. After saving the Excel file or workbook it takes the default extension .XLSX in its filename. Each page in the workbook is called a Worksheet. The active worksheet is displayed in the document window. By default, a new workbook has 3 sheets. You can add or remove sheets from a workbook to suit your need. At the left end of the horizontal scroll bar are sheet tabs and navigation buttons. Click a sheet tab to move to another worksheet or use the navigation buttons to scroll through the sheet tabs.
Types of Data you can Enter in Excel
An Excel workbook can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data:
 Numerical values
 Text
 Formulas
A worksheet can also hold charts, diagrams, pictures, buttons, and other objects.
About Numerical Values
Numerical values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on. Values also can be dates (such as Nov-11-2009) or times (such as 3:24 p.m.).
About text entries
Most worksheets also include text in their cells. You can insert text to serve as labels for values, headings for columns, or instructions about the worksheet. Text is often used to clarify what the values in a worksheet mean.
Text that begins with a number is still considered text. For example, if you type “12 Employees” into a cell, Excel considers the entry to be text rather than a value. Consequently, you can’t use this cell for numeric
Calculations. If you need to indicate that the number “12” refers to employees, enter 12 into a cell and type Employees into the cell to the right.
About formulas
Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter powerful formulas that use the values (or even text) in cells to calculate a result. When you enter a formula into a cell, the formula’s result appears in the cell. If you change any of the values used by a formula, the formula recalculates and shows the new result.
Entering Data in Cell
Entering data in Excel sheet is very easy. You need not concentrate a lot to learn this thing. There are many types of data that you can enter into your worksheets including text, numbers, dates, times, formulae, and functions. Text is any combination of letters, numbers, and spaces. By default, text is automatically left aligned in a cell. However, Numbers and Dates are right aligned.
Active Cell
In an Excel worksheet, the active cell is the cell with the black border. Data can only be entered into the active cell. Even if more than one cell is selected, there is still only one active cell. Use the mouse pointer or the arrow keys to change the location of the active cell.
Number Entered
Date Entered
Text Entered
Also Known As: Current Cell
Examples: Click on cell B2 with the mouse pointer to make it the active cell before entering the data.
Moving Around a Worksheet
As you probably already know, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you’d expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)
Active Cell has a black border.
Name Box, Copy & Paste, Cell Editing, Allignment and Saving File
LEARNING OBJECTIVES
 Using name box
 Copy and paste function
 Cell editing
 Alignment
 Saving workbook Navigating using Name box
For larger worksheets (remember there are over 16,000 columns and more than one million rows in a single Excel 2007 worksheet) there has to be a better way to get around then endless scrolling with the mouse - and there is - by using the Name Box. Learn how to jump from one area of your spreadsheet to the next in this book.
Editing Cells in Excel
You can change a data in a cell directly by overwriting the same using mouse and keyboard.
Using Mouse
Suppose you have entered a wrong word “Janury” in Cell A1. You need to edit the text as “January”. Now follow the steps to edit the data using mouse.
Method
1) Double-click on Cell A1 for editing. Edit the part of the cell you want to change using Backspace key.
2) Finally type in the new data and press enter key.
Type the destination cell reference in the Name box.
Copying and Pasting data in Excel
You can save a great deal of time in Excel by copying data rather than repeating the steps needed to recreate or reapply them over and over. Learn how to use Excel's copy and paste options to copy items to different areas of the worksheet.
Using Mouse
You can copy an entry into surrounding cells by using copy and paste command under Home Tab using mouse.
Steps:
1) In the cell A1, type January and press enter key to accept the data.
2) Keep your cursor on the cell A1 and click on Copy command under Home tab.
(Suppose you want to copy the text January in B2, C2 & D2 Cells.)
Position the mouse pointer on the cell B2 and drag till Cell D2 as shown above.
3) Now click Paste command.
Moving data in Excel
Sometimes we don't need to copy data, headings or formulas, but just move them to a new location. Learn how to do this using Excel's cut and paste features.
Using Mouse
You need to move the data in Column-A to Column-C as shown below using mouse.
Steps:
1) Place the cursor on the Cell A1 and drag till Cell A4 by holding Left mouse button.
2) Click Cut (CTRL+X) command under home tab.
3) Place the cursor on target cell (C1) and then Click Paste Command to move the content from Column-A to Column-C.
Paste Command (CTRL+V)
Copy Command (CTRL+C)
Format Data using Font Group
There are many ways of smartening up a worksheet, for example changing font, font size, font style or using white text on a black background. Excel mainly supports two types of data: Text and Number. You can format them using font group. The font group provides the quickest way for controlling various fonts attributes such as the font and the font size, colour etc. To access the font group, just click the Home tab. All of these commands are covered below.
1) In cell A1 type the heading ‘Monthly Sales’.
2) Select the text and click Font drop lists to make it Stencil, 12 point and Bold or Click Increase/Decrease Font command.
3) To make white text on a black background. Choose white font colour and Black fill colour from Font colour drop lists and Fill colour drop lists respectively.
Border
4) Adding lines and borders is a quick way to format important information in Excel. For example, type amount 20000, 75000, 40000 and 60000 in Cell A2:A5. Select cell A3, Click Border drop lists and select ‘Thick Bottom Border’. The effect you can see below.
Alignment Group Options
There are many ways of formatting cell alignment, for example changing the alignment of the cell contents, position the cell contents in the cell, and change the direction of the cell contents. Combines two or more selected cells into a single cell and wraps text into multiple lines in a cell. All of these commands are covered below.
1) Here you can use another type of alignment command called Merge & Center. The Merge and Center button on the Alignment group lets you quickly center text in the left-most cell across the entire range of cells you select.
Select Cell A1:C1 and Click Merge & Center command. This feature is good for centering a title and subtitle at the top of a worksheet as shown below.
2) In the above example, Salesman names are very long and the full name is not visible. To make all content visible without increasing the column width, you need to use Wrap Text command under Alignment group. Wrap text command is use to place a long text on a multiple lines within a single cell. Select Cell A3:A6 and Click Wrap Text button under Alignment group of Home Tab.
Before Merging Cells
After Merging Cells
3) Now, you need to keep the month and Amount in proper alignment like center, Left, Top etc. Select Cell B3:B6 and Click Center and Middle Align button. Similarly the Cell C3:C6 and Click Middle Align button.
4) Now you want to rotate text to a diagonal angle or vertical orientation, which is often used to narrow columns. Place the cursor on Cell B2 and click Orientation drop lists to select the required button. You can also use Increase/decrease Indent command to move the content from any edge of the cell depending on your choice. Each increment in the Indent box is equivalent to the width of one character. To use indent, keep the cursor on a target cell and click Increase/decrease indent button.
Number Group Options
Use the options on the Number tab to apply a specific number format to numbers in cells on the worksheet. To type numbers in worksheet cells, you can use the number keys, or you can press NUM LOCK, and then use the number keys on the numeric keypad. Let’s take an example to explain the use of number group options as shown below.
To learn all the above commands, let take a database as shown below.
1) To change the amount in Indian currency (Rs.). First go to StartControl PanelDouble-Click on Regional and Language Options.
2) Click Customize button to open Customize Regional Options dialog box.
3) Then Click Currency Tab. Change the currency symbol as “Rs” instead of “$” symbol and Click OK.
4) Now, select Cell C3:C6 and choose Indian Currency from Accounting Number Format drop lists.
If you want to display thousands separator instead of Indian currency, then click Comma style button. You can also increase or decrease decimal places by clicking Increase/Decrease decimal place button as shown below.
5) To allow a Percentage (%) sign as suffix with the numeric data and multiply by 100, you need to click percentage style button. Select Cell D3:D6 and click percentage style as shown below.
Adjusting the System Date Settings
If you dates are being interpreted as April 1st instead of January 4th, this has to be altered in the Control Panel. You probably won’t be able to do this on a network but you can do it on your home machine or get the network manager to correct the settings.
1) Go to StartControl Panel.
2) Double-Click on Regional and Language Options.
3) Click Customize button to open Customize Regional Options dialog box.
4) Click Date Tab. Change the Short date format as “dd/mm/yy” and Click OK.
5) Save and reopen an existing workbook for new setting to take effect.
Saving a Workbook using Keyboard
 Press key to display Save As dialog box as shown above.
 Type a new file name and click Save button.
Opening an Existing Workbook
To open an existing file of Excel from the permanent disk:
1) Select Office Button Open or press.
2) The Open dialog box will appear, choose your file from relevant path and click Open button.
Closing an Excel Workbook.
To exit the active workbook window:
 Go to Office Button  Click Exit Excel.
 To Close Excel Workbook using Keyboard, press key
Class Exercise
1) Open a new worksheet and put the following information in Sheet-1.
2) Format field names (second row) with Font face: Cambria; Font Size: 12 points; Font Style: Bold; Colour: Blue; Border: All border; Fill Color: Olive Green and records (third to sixteen rows) with Font face: Arial; Font Size: 10 points.
3) Merge and center first seven columns fo the first row for the title ‘Student Details’.
4) Format the heading of the database “Student Details” with Font face: Bernard MT Condensed; Font Size: 18 points; Border: Thick Box border; Fill Color: Black and Font Colour: White.
5) Apply Indian Currency (Rs.) format on Fees column with two decimal places.
6) Apply long date format on Date of Admission column.
7) Create a new folder with your name followed by ‘ExcelWork’ under C:\Candidates folder (e.g. Arjun_ExcelWork) and Save the current workbook with a name ‘Excel Assignment’ under your folder.
Place the mouse pointer on the separator of two column letters or row numbers when mouse pointer converts a symbol like below double click. Column Width, Math Function, Workspace and Saving File
LEARNING OBJECTIVES
 Column width
 Mathematical function
 Workspace creation Column width and default size
If the cell width is not adequate for the contents of a column, you can change it to its exact required size using either Best Fit feature or AutoFit option. Best Fit is used not only to change the cell width as well as to change the row height also.
Place the mouse pointer on the separator of the column letters or row numbers, then double-click on it, immediately the Column width or Row height will be changed to display the widest entry within that particular column or row. Always you have to consider the separator between the current column or row and next column or row. This is known as Best Fit feature.
How to select a cell, a range, or text in a cell
Simple Mathematical Calculation
Formulas are used to obtain answers based on mathematical equations that you design. Formulas can be as simple as "2+2" or as complex as calculating the depreciation of fixed assets. When creating formulas, you may use actual values, cell addresses or a combination of the two.
The equal sign (=) is used to indicate to Excel that you are about to enter a formula. This also ensures that formulas beginning with a cell address are not mistaken for text.
Next, you must enter the actual formula for Excel to calculate. When entering these formulas, the following basic mathematical operators are used:
 + (Addition)
 - (Subtraction)
 * (Multiplication)
 / (Division)
When a formula is complete and the key has been pressed, the result will be displayed in the current cell. The formula itself is displayed in the formula bar (located in the upper-left of the screen next to the cell address).
Let’s take a database to explain the above mathematical operator’s.
1) In the above example, to calculate amount, place the cursor on Cell E3 and type “=” sign, then click Cell C3, type asterisk sign (*) and then click Cell D3 and press enter to accept the formula. Now drag the fill handle till Cell E5 to copy the formula in the respective cells.
2) Calculation of Total Amount, Vat, Discount and Net Total is discussed below. Operators Used in Formulas Operator Name Formula What It Does
+
Addition
=5+6 will return 11
–
Subtraction
=6-5 will return 1
*
Multiplication
=6*5 will return 30
/
Division
= 6/2 will return 3
^
Exponentiation
=6^3 will return 216
Drag the fill handle
Let’s take another database to explain the “/” Division operator as shown below.
Working with Worksheet
Changing Sheet Name
You can change the name of the sheet tabs:
1) Double-click on the sheet tab of the worksheet you want to rename or right-click on the tab, type the name and press enter.
2) Select Rename option from the context sensitive popup menu , type the name and press enter
3) The current name will be highlighted. Type a new name and press Enter key.
Adding a New Worksheet
1) Right-click on the sheet tab
2) Choose and click Insert option from the popup menu.
To quickly insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet tab at the bottom of the screen. Click the drop lists of Insert from the Cell group under home tab.
Removing a Worksheet
1) Right-click on the sheet tab
2) Choose and click Delete option from the popup menu.
Caution: Be careful when deleting worksheets as you cannot undo the deletion.
OR
1) Click the drop lists of Delete from the Cell group under home tab.
2) Choose and click Delete sheet.
Working with Rows and Columns
Rows and columns make up an Excel worksheet. Every worksheet has exactly 1,048,576 rows and 16,384 columns, and these values can’t be changed. You can insert/delete Row(s) and Column(s) but here rows and columns will be deleted automatically from the last according to the number of rows or columns inserted.
Inserting rows in excel is very simple. Right click on row number before which you want to insert the row and Select insert option from the popup menu that appears. Accordingly you can insert column(s) right clicking on the selected columns and clicking on the insert option. Columns always inserts just before your selected column(s).
Alternatively,
You can also insert Row(s) or columns from the home Tab ribbon clicking on the Insert button and clicking on the Insert sheet rows or columns option.
Deleting Rows or Columns
Row(s) or Column(s) can be deleted by right clicking on the selected Row(s) or Column(s) and clicking on the Delete option.
Right click on the selected rows and click on Delete option from the Dropdown list, selected row(s) or column(s) will be deleted. Here one row is selected naturally one row will be deleted.
Accordingly you can delete column(s) right clicking on the selected columns and clicking on Delete option from the Drop down list.
Alternatively,
You can also delete selected row(s) or column(s) clicking on Delete sheet Rows or Columns from the insert button under the home tab ribbon’s Cells group.
Right click on the selected rows and click on Delete option from the Dropdown list, selected row(s) or column(s) will be deleted. Here one row is selected naturally one row will be deleted.
Accordingly you can delete column(s) right clicking on the selected columns and clicking on Delete option from the Drop down list.
Hide/Unhide rows or columns
To get the options of hide and unhide rows and columns, go to format button of Home tab
To hide the selected rows Click on Hide Rows and for hiding columns click on Hide Columns. Contents of the hidden rows/columns will not be printed. To get back the hidden row(s)/column(s), select the previous and next rows of the hidden row(s)/column(s) or select the entire worksheet, and then choose any of these options accordingly. All the hidden row(s)/column(s) will come back immediately.
Adjusting Row Height
Row Height – This option is used to change the row height of the selected row or rows to the defined value. The following dialog box appears, when you click this option, type the value and click on OK button.
AutoFit Row Height – This feature is used to change the height of the selected cell(s) to fit the contents accordingly. This can be done by Best Fit option also.
Column Width – This option changes the width of the selected column or columns. The following dialog box will appear, define the value and then click on OK button.
Class Exercise
1) Open your last saved workbook and go to Sheet-1.
2) Best fit the columns and rows if required.
3) Rename the Sheet-1 as ‘Student List’.
4) Add two more field name after Fees field as ‘Service Tax @10.3%’ and ‘Total Fees’. Apply similar cell formatting in this two field using format painter.
5) Adjust column width in such a way, so that the text ‘Service Tax’ only appears in a single line and the rest text in the second line of the same cell.
6) Adjust row height as required.
7) Calculate Service tax @ 10.3% on Fees.
8) Calculate Total Fees as Fees+Service Tax.
9) Merge and center first cell containing text ‘Student Details’ till Cell I1.
10) Go to a new worksheet and prepare the table (electricity) as follows:
Meter no
Opening reading
Closing reading
Metered units
Rate/unit
Total amt
Rental charge
Service tax 10.3%
Bill Amount
DE/023
4568
5528
2.25
84.40
AP/153
3210
3285
2.25
84.40
FE/092
1102
1125
2.25
84.40
JU/120
6521
8543
2.25
84.40
11) Save and Close the workbook.
Style, Table & Conditional Formatting and Format Painter
LEARNING OBJECTIVES
 Style formatting
 Table formatting
 Conditional formatting
 Using format painter Style Group
A style in Excel 2007 is a combination of formatting options that is named and saved as part of your current spreadsheet file. The new style can then quickly be applied to data and cells in the spreadsheet. Let’s take an example to explain the use of Style group options as shown below.
Cell Styles
Cell styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles also helps to ensure a consistent look. You can also defined you own style.
1) Select the desired cells (D3:D6).
2) Click on the Cell Styles option on the Style Group under Home Tab to open the Cell Styles gallery.
3) Click on any style name at the top of the gallery as required.
4) The style's formatting is immediately applied to the selected cells.
Format as Table
Pre-defined table style offers a quick way to apply a standard format to all or part of a worksheet. Before you use Pre-defined table style, select the cells to be formatted. Choose Home Tab  Styles group  Format as Table and then select any desired style from the display list.
After applying Cell Styles Before Applying Style
After Applying Style
Simple Conditional Formatting
Conditional formatting has improved significantly in Excel 2007, and it’s now a useful tool for visualizing numeric data. Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells.
Specifying Conditional Formatting
To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Tab  Style group  Conditional Formatting drop-down to specify a rule. The choices are:
 Highlight Cell Rules: Examples rules includes highlighting cells that are greater than a particular value, between two values, contain specific text string, or are duplicated.
 Top Bottom Rules: Examples include highlighting the top 10 items, the items in the bottom 20 percent, and items that are above average.
 Data Bars: Applies graphic bar directly in the cell, proportional to the cell’s value.
 Color Scales: Applies background colour, proportional to the cell’s value
 Icon Sets: Displays icons directly in the cells. The icons depend on the cell’s value.
When you apply conditional formatting rules, Excel displays a dialog box that’s specific to that rule. These dialog boxed have one thing in a common: a drop-down list with common formatting suggestions.
Above Figure shows the dialog box that appears when you choose Home TabStylesConditional FormattingHighlight Cells Rules Between. This particular rule applies the formatting if the value in the cell falls between two specific values. In this case, you enter the two values, and then use the drop-down control to choose the type of formatting to display if the condition is met.
Figure below shows a worksheet with nine ranges, each with different types of conditional formatting rule applied. Here‘s a brief explanation of each.
Type a Value
Select any format
 Greater than 10: Values greater than 10
are highlighted with a different
background colour. This rule is just one
of the many numeric value related rules
that you can apply.
 Above Average: Values that are higher
than the average value are highlighted.
 Duplicate Values: Values that appear
more than one time are highlighted.
 Words that contain X: If the cell contains
X (upper or lower case), the cell is
highlighted.
 Data Bars: Each cell displays a horizontal
bar, proportional to its value.
 Color Scale: The background colour varies, depending on the value of the cells. You can choose from several different colour scales or create your own.
 Icon Set: One of many icon sets. It displays a small graphic in the cell. The graphic varies, depending on the cell value.
Removing Conditional Formats
If you no longer want to conditionally format your data, you can remove any rules applied to it and return it to its original format. To remove conditional formats from the selected cells, sheet, or table, click Conditional Formatting in the Styles group of the Home tab. Point to the Clear Rules option and select the appropriate option from the fly-out menu.
Format Painter
Format Painter option is use to copy the formats from one part of a sheet to another, or to another sheet in the same workbook. This option is available under Home TabClipboard Group. Example: 1
To use Format Painter to copy formatting options to a second cell as shown below.
1) Add all the formatting options you want to use to the first cell range.
2) Click on that cell with the mouse pointer to make it the active cell.
3) Click on the Home tabFormat
Painter icon under Clipboard
group.
4) Clicking on a second cell range will
copy the formats from the active
cell to this second cell.
Class Exercise
1) Open your last saved workbook and go to Sheet-1.
2) Highlight course B.COM using conditional formatting.
3) Highlight Fees between Rs.50,000 to 100,000 using conditional formatting.
4) Apply Cell Style named ‘Assent 4’ on all the centre names.
5) Select the table leaving the first row and apply Table Style Light 20 format.
6) Delete all un-used worksheets.
7) Save and close the workbook.
Cell Reference, Fill Series, Custom List and Formatting Numbers
LEARNING OBJECTIVES
 Cell reference
 Filling series
 Custom list
 Formatting numbers Cell Reference
Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed values. For example, if your formula refers to cell A1 and you change the value contained in A1, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.
When you use a cell (or range) reference in a formula, you can use three (3) types of references:
1) Relative Reference: It refers to the cell by their position in relation to the cell that contains the formula. The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.
2) Absolute Reference: It refers to the cell by their position in relation to the cell that contains the formula. The row and column references do not change when you copy the formula because the reference is to an actual cell address.
3) Mixed Reference: Any of the row or column reference is relative, and the other is absolute.
By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell. The formula in cell D2, which multiplies the quantity by the price, is =B2*C2. This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the references adjust in a relative manner. For example, the formula in cell D3 is =B3*C3
Filling Series
Excel supports a wide variety of fill options. Go to Home Tab Editing Group Fill Series to display the series dialog box.
Creation of Linear Series
Linear series is for the series that increases by adding the same value to each successive cell.
Creation of Growth Series
Growth series is for the series that increases geometrically multiplying by a value.
Custom List
Entering a Series is similar to fill the content of cell for a group of cells. Suppose you want to create a series of days, such as Sunday, Monday, and so on. Type Sunday in a cell and drag the fill handle of the cell in any direction, and check a series of days will be generated. Similarly you can create a series of months, i.e.,
Figure demonstrates the use of mixed references. The formulas in the C3:E7 ranges calculate the phone bill for various call made and phone rate. The formula in cell C3 is =$A3*C$2
Select any one of the options from the series in options according to your requirement. Here we are selecting columns.
Select the option Growth from the type list.
Input step value and stop value as your requirement. Here we are using 5 as step value and 400 as stop value. It will create a series like 3, 15, 75, 375
Click on OK to finish.
Select the cell based on which you want to generate the series.
Show Multiplication of 5
January, February, and so on using same way. These series are generated because they are already defined in Excel. You can create your own series with text or number etc.
1) To define your own list Go to Office Button Excel Options Popular Tab Edit Custom Lists
2) Click on NEW LISTType the List itemsClick on Add.
Formatting numbers using the Format Cells dialog box
In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, shown in Figure below. For formatting numbers, you need to use the Number tab.
Number Formats
Select Number control tab, to apply any number formatting.
General – The default format of number will be applied. Mainly it is required to remove or cancel any number format from the selection.
Number – This type of formatting allows you to set number of decimal places, with a thousand separators, and formatting of negative numbers.
Currency – Numbers are preceded with a default Currency sign immediately. You can also set here number of decimal places and the formatting of the negative numbers. Zero values are displayed.
Accounting – Currency sign and decimal places can set with the numbers. But the setting of Currency symbol with number is different from Currency format. Currency symbols are coming with left alignment whereas numbers or values are right aligned in a cell.
Date – This formatting gives a variety of Date formats.
Time – Different time formats provided by this option.
Percentage – This formatting attached a percentage sign with the number as suffix and multiply the number by 100 (hundred).
Fraction – Includes formats based on either the number of digits to display in the divisor (1, 2, or 3) or the fractional unit (halves, quarters, tenths, and so on).
Scientific – Numbers are displayed in scientific notation. For example: 1.01E+03.
Text – Changes a number to text without adding other formatting. This is useful for numeric labels that may include leading zeros. All the regular numeric formats strip off leading zeros. You must apply the Text format before entering the cell’s contents.
Special – This is mainly used for Telephone numbers, Zip Codes etc. those are mainly having some special formats.
Example of cell formatting:
In the following sections, you see how to use many of Excel’s formatting options to quickly improve the appearance of your worksheets.
Text Alignment
Horizontal: Select an option in the Horizontal list box to change the horizontal alignment of cell contents. By default, Microsoft Office Excel aligns text to the left, numbers to the right and logical and error values are centered. The default horizontal alignment is General. Changing the alignment of data does not change the data type.
Vertical: Select an option in the Vertical box to change the vertical alignment of cell contents. By default, Excel aligns text vertically on the bottom of a cell. The default horizontal alignment is General.
Indent: Indents cell contents from any edge of the cell, depending on your choice under Horizontal and Vertical. Each increment in the Indent box is equivalent to the width of one character.
Orientation: Select an option under Orientation to change the orientation of text in selected cells. Rotation options may not be available if other alignment options are selected. Degrees: Sets the amount of text rotation in the selected cell. Use a positive number in the Degree box to rotate the selected text from lower left to upper right in the cell. Use negative degrees to rotate text from upper left to lower right in the selected cell.
Text control
Wrap text: Wraps text into multiple lines in a cell. The number of wrapped lines is dependent on the width of the column and the length of the cell contents.
Shrink to fit: Reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically if you change the column width. The applied font size is not changed.
Merge cells: Combines two or more selected cells into a single cell. The cell reference for a merged cell is the upper-left cell in the original selected range.
Text direction: Select an option in the Text direction box to specify reading order and alignment. The default setting is Context, but you can change it to Left-to-Right or Right-to-Left.
In this table there are some examples of formatted and unformatted text. There it is also mentioned type of format we have used for that particular cell.
Font Control Tab
1) Select the font from the font list.
2) Select font style as required.
3) Select font size to increase/decrease the font size.
4) Select underline style.
5) Choose font colour.
6) Apply text Effects.
7) See the preview of your applied formats.
Border and Fill Control Tab
Class Exercise
1) Open the last saved workbook.
2) Insert a new worksheet . Name the worksheet as ‘Reference’ and prepare the following table in it with necessary formatting.
3) Complete the above table using Mixed Cell Referencing concept with necessary formatting.
4) Prepare another table just beside the City-Wise Conveyance chart as shown below. Fill Tab
Background Color - Select a background color for selected cells by using the color palette.
Fill Effects - Select this button to apply gradient, texture, and picture fills to selected cells.
More Colors - Select this button to add colors that are not available on the color palette.
Pattern Color - Select a foreground color in the Pattern Color box to create a pattern that uses two colors.
Pattern Style - Select a pattern in the Pattern Style box to format selected cells in a pattern that uses the colors that you select in the Background Color and Pattern Color boxes.
Sample - See a sample of the color; fill effects, and pattern options that you select.
5) Calculate Bill Amount using Absolute Cell Referencing concept.
6) A Partnership firms earned a profit of Rs.800,000 in the year 2009. The partners decide to share 40% of the profit amongst themselves in the ratio of their capital. Calculate their individual shares of profit in the same sheet.
7) Change the value of Akbar’s capital to Rs.600,000 and check whether the table of Partner’s profit has been reflected or not.
Mathematical Functions
LEARNING OBJECTIVES
 Mathematical Function
 Sum
 Average
 Max
 Min
 Round
 Sqrt
 Power
Some Popular Functions of Excel
Excel includes many math, trigonometry and text functions. These functions perform a wide variety of Calculations such follows:
1) SUM
2) AVERAGE
3) MAX
4) MIN
5) ROUND
6) SQRT
7) POWER
SUM Function
General mathematical built-in functions are provided with Excel to carry out calculations on data within the spreadsheet and can take the place of certain types of formulas.
Functions begin with the = sign just as formulas do. For example, if you had a large column of numbers to be added (A1:A100), you might think you need a long formula to include all of the addresses (=A1+A2+A3....+A99+A100). However, Excel provides a mathematical function which is used primarily to add blocks of numbers. The formula could be re-written as =SUM (A1:A100) which is much shorter.
AVERAGE Function
This function returns the average value for a block of cells. The syntax is: =AVERAGE (FIRST CELL : LAST CELL)
Example:
=AVERAGE (23,89,34,20) The return value will be 41.5.
=AVERAGE (D2:D10) It will return the average value of the range D2 to D10.
Note: You can use non-contiguous cell references also using comma (,) separator.
MAX Function
This function returns the largest value in a block of cells. The syntax is: =MAX (FIRST CELL : LAST CELL)
Numbers are asking for the values or numbers from which you want to find out the maximum value.
Example:
Cell Range from A1 to A10 (A1:A10)
SUM is one of the most commonly used functions. It adds all the numbers in the range of cells. The syntax of the SUM function is: =SUM (FIRST CELL : LAST CELL)
Range is a group of cells to be calculated by the function. You can also provide values as arguments.
Example:
=SUM (15,20, 45,83) You can use values in the function
=SUM (A1:A10) For contiguous cells, give the reference of the first cell, then a colon sign (:) and then cell reference of the last cell of the range. (See figure).
=SUM(B5:B10,B15,B20:B25) For non-contiguous references, use comma as a separator.
Using the Average function to find the Average value in the range of cells
=MAX (45, 12, 78) This function will returns the value 78 which is the highest value out of the given
number.
=MAX (D2:D10)  It will return the largest value from the defined range.
MIN Function
This function returns the smallest value in a block of cells. The syntax of the function is: =MIN (FIRST CELL : LAST CELL)
Numbers are the arguments from which the minimum value will be finding out.
Example:
=MIN (45,12,56) The return value will be 12.
=MIN (D2:D10) It will return the minimum value from the defined range.
ROUND Function
This function Rounds a number to a specified number of digits. The syntax of the function is: =ROUND (<Number>, <Num_digits>)
Number is the number you want to round.
Num_digits specifies the number of digits to which you want to round number
Example:
=ROUND (235.75,1)  The return value will be 235.8, because you want to round off the number to one decimal place. Since the second decimal value is 5, Round function will add 1 with the first decimal value.
SQRTFunction
This function returns the square root value of a given numbers. The syntax of this function is: = SQRT (NUMBER)
Example:
=SQRT(25)  Square root of 25 is 5.
POWER Function
This function returns the result of a number raised to a power. The syntax of this function is: =POWER (Number,Power)
The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.
Example:
=POWER(5,2)  The return value will be 25, due to 5 squared.
=POWER(3,1/3)  The return value will be 1.44225 , due to 3 raised to the power of 1/3.
Concept of Range
A group of cells is called a range cells. The cells in a range may all be in one column, or one row, or any combination of columns and rows, as long as the range forms a rectangle. A range also can be a single cell. Ranges are referred to by their anchor points, means the upper-left cell address and its lower-right cell address, separated by colon. Some examples of range addresses C24
A range that consists of a single cell. A1:B1
Two cells that occupy one row and two columns. A1:A100
100 cells in column A. A1:D4
16 cells (four rows by four columns). C1:C1048576
An entire column of cells; this range also can be expressed as C:C. A6:XFD6
An entire row of cells; this range also can be expressed as 6:6. A1:XFD1048576
All cells in a worksheet.
Adding Comments to Cells
In excel a note can be added on a certain cell through inserting a comment. You can edit the comment and delete those comments which are no longer useful.
Select the cell and then go to Review tab  Comments group  New Comment. ~Or~
Right-click on the cell and choose Insert Comment from the popup menu.
After you finished writing, click anywhere outside the comment area to hide the comment box.
A small red arrow at the top right corner of the cell indicates that a comment is inserted on that cell.
Class Exercise
1) Open your last saved workbook and go to the Student List sheet.
2) Delete all the Student Code list and set the Student Code range (A3:A16) in such a way that entering ‘1’ will automatically display ‘ST/0001’. [Hint: Use Custom number format]
3) Feed student code as 1, 2, 3, ……., 14 in the respective cell.
4) Insert a new worksheet after Reference sheet. Name the worksheet as ‘Result ’ and prepare the following dataset in it.
5) Calculate Total, Avearage, Maximum and Minimum marks in the above table.
6) Insert a note ‘Fundamental of Accounts’ on cell F1.
7) Prepare the following table with in ‘Result’ worksheet and do the necessary operations to give resulted figures under appropriate column head as per given instructions.
a. Find out the Square root of the original numbers under the head Square Root.
b. Round off the square root figures upto 2 (two) decimal places and keep under Rounded Figure head.
c. Find out Power of 2 of the Rounded figures under the Power head.
8) Save and close the workbook.
Date Value Function, Workspace Creation and Paste Special
LEARNING OBJECTIVES
 Date value function
 Workspace creation
 Paste special Date-Related Functions
Excel has quite a few functions that work with dates. These functions are accessible by choosing Path: FormulasFunction Library Date & Time.
DATE Function
Returns the sequential Excel date / time serial number that represents a particular date. Syntax: =DATE (Year, Month, Date)
The result will normally be displayed in the format set in regional setting of your computer. By using the number format this format can be changed.
TODAY Function
This function is used to show the current date in a cell. Syntax: =TODAY()
Creation of Workspace
If you frequently work with the same group of workbooks you may need to create a customized workspace. A Workspace allows you to open a group of workbook in one step. Information about the open files, such as their locations, window size, and screen positions, are stored in a workspace file. Therefore, instead of opening individual workbook you can open the workspace file, all the individual workbooks forming a part of the file are opened.
1) To create a Workspace, first open the workbooks you want to include in the workspace
2) Go to View Tab  Window Group  Save Workspace, the saving dialog box will appear. Enter a name for the Workspace file in the File name box. The default extension for a workspace file is .XLW.
Using the Paste Special Dialog box
For maximum flexibility in what gets pasted, choose Home  Clipboard  Paste  Paste Special to display the Paste Special dialog box (see Figure below). You can also right-click and select Paste Special to display this dialog box. This dialog box has several options, which is explained in the following list.
Data Validation
Excel’s data validation feature enables you to set up certain rules that restrict an user to enter invalid data. For example, you may want to limit data entry in a particular cell to whole numbers between 3000 and 20000. If the user makes an invalid entry, you can display an error message. Excel makes it easy to specify the validation criteria, and you can also use a formula for more complex criteria. Types of Validation Criteria You Can Apply
The Settings tab of the Data Validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow drop-down box. Keep in mind that the other controls in the Settings tab vary, depending on your choice in the Allow drop-down box.
 Any Value: Selecting this option removes any existing data validation. Note, however, that the input message, if any, still displays if the check box is checked in the Input Message tab.
 Whole Number: The user must enter a whole number. You specify a valid range of whole numbers by using the Data drop-down list. For example, you can specify that the entry must be a whole number greater than or equal to 100.
 Decimal: The user must enter a number. You specify a valid range of numbers by using the Data drop-down list. For example, you can specify that the entry must be greater than or equal to 0 and less than or equal to 1.
 List: The user must choose from a list of entries you provide. In situations where the some few items are to be entered into a column, you can create a drop-down list containing the options from which the user must choose. You can define the values within Source text box using comma separator or a range of data can be defined using an equal sign (=) as prefix.
 Date: The user must enter a date. You specify a valid date range by using the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2007, and less than or equal to December 31, 2007.
 Time: The user must enter a time. You specify a valid time range by using the Data drop-down list. For example, you can specify that the entered data must be greater than 12:00 p.m.
 Text Length: The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).
 All: Pastes the cell’s contents, formats, and data validation from the Windows Clipboard.
 Formulas: Pastes values and formulas, with no formatting.
 Values: Pastes values and the results of formulas (no formatting). The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values.
 Formats: Copies only the formatting.
 Comments: Copies only the cell comments from a cell or range. This option doesn’t copy cell contents or formatting.
 Validation: Copies the validation criteria so the same data validation will apply. Data validation is applied by choosing Data ➪ Data Tools ➪ Data Validation.
 Custom: To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either True or False). You can enter the formula directly into the Formula control (which appears when you select the Custom option), or you can specify a cell reference that contains a formula. This session contains examples of useful formulas.
The Settings tab of the Data Validation dialog box contains two other check boxes:
 Ignore Blank: If checked, blank entries are allowed.
 Apply These Changes to All Other Cells with the Same Setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria. Specifying Validation Criteria
To specify the type of data allowable in a cell or range, follow these steps:
1) Select the cell or range.
2) Choose Data tab  Data Tools group  Data Validation. Excel displays its Data Validation dialog box.
3) Click the Settings tab (see Figure).
4) Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, displaying controls based on your choice. To specify a formula, select Custom.
5) Specify the conditions by using the displayed controls.
6) (Optional) Click the Input Message tab and specify which message to display when a user selects the cell.
7) (Optional) Click the Error Alert tab and specify which error message to display when a user makes an invalid entry.
8) Click Ok.
Class Exercise
1) Open the last saved workbook.
2) Insert a new worksheet after Result sheet. Name the worksheet as ‘Drugs ’ and prepare the following dataset in it.
Allowing only whole no. to selected cells
Setting conditions
Message box Title
Error Message
3) Find out Manufacture date using date function.
4) Change Expiry Date range as text and feed the following dates in the respective cell.
12-Jun-2010; 25-May-2010; 01-Sep-2012; 14-Mar-2009; 11-Nov-2011; 11-Apr-2012; 03-Dec-2009; 01-Sep-2008; 31-Jul-2009; 18-Jan-2012.
5) Days of today column should display the current date.
6) Calculate the number of expiry days as of today in ‘Days Until Expiry’ column.
7) Save and close the workbook.
In this example, we have to check two conditions to offer discount to the customer, one is whether the product is on Special Offer and another one is whether the Order Value is Rs.1000 or above. That can be checked through AND() function [ =AND(B3=”Yes”,C3>=1000)]. Now based on that conditions we have to take decision; whether to offer discount or not. So this above function has to be incorporated with if() function. Thus the final formula is: =IF(AND(B3=”Yes”,C3>=1000),C3*10%,0) Logical Functions
LEARNING OBJECTIVES
 Logical Function
 IF function
 OR Function
Logical Function
Excel’s logical functions are used to perform logical tests, as to reveal whether a statement is true or false. Depending on the outcome of the logical test, a specific result is returned. If Function
This function returns a value depending on the logical test given as an argument of the function. The syntax of the function is: =IF( Logical_test, Result_if_true, Result_if_false).
Logical_test is any value or expression that can be evaluated to true or false. Result_if_true is the value that will return if the Logical_test is true. Result_if_false is the value that will return if the Logical_test is false. Example-1: Simple IF
OR Function
The OR functions checks whether any of the arguments are TRUE. It returns a FALSE only if all the arguments are FALSE. If any argument is TRUE, the function returns TRUE. Syntax: =OR(Logical_test 1,Logical_test 2,……) Example-1:
A handling charge of Rs.50/- is made on all orders paid by Visa or Delta cards. The =OR() function has been used to determine whether the charge needs to be applied.
The figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text: either Pass (a score of 65 or higher) or Fail (a score below 65). The formula in cell C2 is: =IF (B2>=65,”Pass”,”Fail”)
NOT Function
NOT function is used to reverse the value of its argument. NOT function accepts a logical value as its argument.
Class Exercise
1) Open the last saved workbook.
2) Create a new worksheet. Rename the sheet as ‘Payroll’ and prepare the following dataset in it. Provide all required formatting and use series concept at employee code.
3) Calculate DA @ 40% of Basic pay.
4) Calculate TA @ 10% of Basic or Rs.1000/- which ever is higher.
5) Provide House for Audit department only and “No” for others.
6) Calculate HRA @ 15% of Basic pay for those who do not get accommodation from company and for other it will be 0.
7) Calculate Gross salary (Basic+DA+TA+HRA).
8) Calculate P.F @ 12.5% of Basic pay.
9) Calculate P.Tax as per slab:
Upto Rs.5000 --- Nil
Rs.5001 – Rs.6000 --- Rs.60/-
Rs.6001 – Rs.10,000 --- Rs.80/-
Rs.10,001 – Rs. 15,000 --- Rs.100/-
Rs.15,001 – Rs.20,000 --- Rs.150/-
Rs.20,001 and above --- Rs.200/-
10) Calculate Net Salary (Gross-P.F-P.Tax)
11) Now replace HRA amount with 20% of Basic whose Basic is more than 15,000 and also who do not posses a house. For other who do not posses house and who’s Basic is below 15,000, don’t replace their HRA.
12) Save and close the workbook.
In this example, any of the two conditions (whether the payment type is Visa or Delta) has to be satisfied to charge Handling Charge of Rs. 50. So, Or() function has to be used within If() function. The final formula is: IF(OR(C2=”Visa”,C2=”Delta”),50,0).
Other Logical Functions, Data Form and Freeze Panes
LEARNING OBJECTIVES
 Other Logical Functions
o Sumif
o Countif
o Count
o Isblank
 Data Form
 Freeze panes Use of Sumif Function
The SUMIF function calculates the total of only those cells that meet a given condition or criteria. The syntax for the SUMIF function is: =SUMIF (Range, Criteria, SumRange)
 Range: The range containing the values that determine whether to include a particular cell in the sum.
 Criteria: An expression that determines whether to include a particular cell in the sum.
 Sum_range: The range that contains the cells you want to sum
Based on the above table, to find out total cost of all Mouse bought:
=SUMIF(B2:C9,"Mouse”,”C2:C9)
COUNT Function
This function counts the number of numeric entries in a list. It will ignore blanks, text and errors. The syntax of this function is: =COUNT(Range1,Range2,Range3... through to Range30)
The following figure evaluates the use of Count Function:
Range
Criteria
Sum Range
COUNTIF Function
The COUNTIF function counts the number of cells in a given range that meet a specific condition. The syntax for the function is:=COUNTIF (Range, Criteria)
Range is used to declare a group of cells from which you want to count those cells, which meet the criteria. Criteria is used to declare the searching matter, it can be a number as well as a value. ISBLANK FUNCTION
This function will determine if there is an entry in a particular cell. Syntax: =ISBLANK(CellToTest)
It can be used when a spreadsheet has blank cells which may cause errors, but which will be filled later as the data is received by the user will be filled later as the data is received by the user. DATA FORM
Many users use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a data entry form that Excel can create automatically.
Data Form command is not available in the Ribbon, to use the data form, you must add it to your Quick Access Toolbar (QAT): Freeze Panes
If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right. Excel provides a handy solution to this problem: freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet.
1) To freeze panes, start by moving the cell pointer to the cell below the row that you want to remain visible as you scroll vertically, and to the right of the column that you want to remain visible as you scroll horizontally.
2) Then, Choose View Window  Freeze Panes and select the Freeze Panes option from the dropdown list.
3) Excel inserts dark lines to indicate the frozen rows and columns. The frozen row and column remain visible as you scroll throughout the worksheet.
To remove the frozen panes, choose ViewWindow Freeze Panes, and select the Unfreeze Panes option from the drop-down list.
Class Exercise
1) Open your last saved workbook.
2) Go to the Payroll sheet and calculate the following at the end of the dataset:
a. Calculate the Total take home money of Audit department
b. Calculate the Total take home money of Sales department
c. Calculate the gross salary of Media department.
d. Total number of employee in Media department
e. Total number of employee in Acct department
3) Create a new worksheet. Rename the sheet as ‘Installment’ and prepare the following dataset by copying St.Code , Name, Course and Fees from ‘Student List’ sheet.
4) Calculate Outstanding Fees (Fees-Installment), if Date of Installment Paid is mentioned otherwise full Fees amount will appears as outstanding fees. [Hint: Use ISBLANK function]
5) Save and close the workbook.
Financial Functions
LEARNING OBJECTIVES
 Financial Function
 Loan Calculation
o PV
o FV
o PMT
o PPMT FINANCIAL FUNCTION
Excel is often used to perform calculations involving money, from basic home budgets to complex financial decision making. Consequently, Excel’s function library contains over 50 financial functions that can be used to calculate everything from loan repayments to the future value of an investment. LOAN CALCULATIONS
This section describes how to calculate various components of a loan. Think of a loan as consisting of the following components:
 The loan amount
 The interest rate
 The number of payment periods
 The periodic payment amount
If you know any three of these components, you can create a formula to calculate the unknown component. Note: The loan calculations in this section all assume a fixed-rate loan with a fixed term.
This section describes six functions: PMT, PPMT, IPMT, RATE, FV and PV. The PMT Function
The PMT function returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate.
The syntax for the PMT function is PMT(Rate,Nper,Pv,Fv,Type) Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Nper
The total number of payment periods.
PV
This is the present value, or the loan amount; it is also known as the principal.
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
You need to find out the monthly payment amount for a Loan of Rs.500,000 with a 14% annual interest rate. The loan has a term of four years (48 months).
The procedure to calculate monthly payment amount are as follows:
1) Place your cursor on Cell B6. Type <=PMT> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box: Rate
Cell B3/12 (14%/12). It is divided by 12 as the interest is given in annual basis and the payment is in monthly basis. Nper
Cell B4*12 (4*12). As payment is made monthly for the period of four (4) year. PV
-Cell B2 (-500,000). This will be negative for a loan. FV
Omit FV as it is assumed to be 0. Type
‘0’. It indicate the payment is due at the end of the period.
3) Finally click OK button to display monthly payment amount. This formula returns Rs.13,663.24 (approx) as the EMI.
The PPMT function
The PPMT function returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate.
The syntax for the PPMT function is PPMT(rate,per,nper,pv,fv,type). Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Per
This is the period for which you want to find the principal
Nper
The total number of payment periods.
PV
Present value, the amount you have invested or you have taken as loan.
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
In the above example, you need to find out the principal amount for the first month of the EMI that you have calculated in the earlier example.
The procedure to calculate Principal amount are as follows:
1) Place your cursor on Cell B8. Type <=PPMT> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below.
Per: Particular number of Installment. Suppose you have total 48 installments, among them you need to know the principal amount of 1st installments. Then type “1” in Per argument box. The period must be less than or equal to nper.
3) Click OK button to get the Principal value (PPMT). This formula returns Rs.7,829.90 (approx) as the principal amount.
The PV function
The PV function returns the present value (that is, the original loan amount) for a loan, given the interest rate, the number of periods, and the periodic payment amount.
The syntax for the PV function is: PV(rate,nper,pmt,fv,type) Function Argument Description
Rate
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it by the number of periods.
Nper
The total number of payment periods.
PMT
The payment made each period (a constant value that does not change).
FV
The future value after the last payment is made. If you omit FV, it is assumed to be 0. (The future value of a loan, for example, is 0.)
Type
Indicates when payments are due—either 0 (due at the end of the period) or 1 (due at the beginning of the period). If you omit type, it is assumed to be 0.
Let’s take an example as shown below:
You need to find out the original loan amount for four (4) years, which has a monthly payment of Rs.13,663.24.
The procedure to calculate the present value of loan is as follows:
1) Place your cursor on Cell B6. Type <=PV> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below. Rate
Cell B3/12 (14%/12). It is divided by 12 as the interest is given in annual basis and the payment is in monthly basis. Nper
Cell B4*12 (4*12). As payment is made monthly for the period of four (4) year. PMT
-Cell B2 (-13,663.24). It is the amount of the periodic payments FV
Omit FV as it is assumed to be 0. Type
Omit type. It indicates the payment is due at the end of the period.
3) Click OK button to get the present value (PV) of loan. This formula returns Rs.500,000 as the initial loan amount.
The FV function
In financial models the future value of an investment is equal to the present value plus interest or the combined value of all the deposits over the term of the investment plus any interest.
The FV function will return the future value of an investment based on periodic, constant payments and a constant interest rate.
The syntax for the FV function is: FV(rate,nper,pmt,pv,type)
Function Argument Description
Rate
Rate is interest rate per period; if payments are monthly this will be divided by 12.
Nper
Nper is the total number of payments.
PMT
Pmt is the fixed amount deposited each period (assumed to be zero).
PV
PV is the initial deposit or present value (assumed to be zero).
Type
Type is 0 if payment is due at the end of a period or 1 if payment is due at the beginning of a period (the default is 0).
Let’s take an example as shown below:
The above example shows that you are depositing Rs.1000 at the beginning of each month for 5 years with 2.13% quarterly interest. Here you need to find out the matured amount. To do this task you need to use FV function.
The procedure to calculate the future value of loan is as follows:
1) Place your cursor on Cell B6. Type <=FV> and press <Ctrl+A> to display function argument dialog box.
2) Fill the following function arguments box as shown below. Rate
Cell B3/4 (2.13%/4). It is divided by 4 as the interest is given in quarterly basis and the payment is in monthly basis. Nper
Cell B4*12 (5*12). As payment is made monthly for the period of five (5) year. PMT
-Cell B2 (-1,000). It is the amount of the monthly payment. PV
0 ( No initial deposit) Type
1 (payment is due at the beginning of a period)
3) Click OK button to get the future value (FV) of investment. This formula returns Rs.70,849.10 as the matured amount.
Class Exercise
1) Open your last saved workbook and insert a new worksheet after ‘installment’, name the sheet as ‘Financial’.
2) You can get Rs.500,000/- in 3 years time by investing certain amount on monthly basis at 7% p.a. Find out the monthly investment amount. [ Rs.12,522 approx ]
3) If I deposit Rs.1000 at the beginning of each month (starting today) in an account earning 1% per month, how much will I have after two years? [ Ans: 27,243.20 ]
4) If you have deposited Rs.100,000 with compounded interest @ 6.25% per annum for 8 years, find out the return value at the end of 8 Years. [ Rs.162,417 approx ]
5) If the previous deposit follows an MIS (Monthly Income Scheme) then how much amount will you receive at the end of each month? [ Rs.1326 approx ]
6) In every MIS, find out how much amount is paid back as Principal and Interest seperately. Summation of Principal parts would be Rs.100,000 and summation fo Interest parts would be Rs.27,329.56.
7) As per the investment scheme, you have to deposit Rs.835 per month to get Rs.150,000/- at the end of 10 years, find out rate of interest per annum. If you are investing sum of amount to get the same return value after 10 years with same rate of interest per annum, then find out the amount to be deposited now. [ 7.56 (approx) ; Rs.72,379 approx ]
8) Suppose you are going to deposit Rs.2.646 quarterly in a recurring account to get back Rs.1,00,000 after a certain period with 8% interest per annum. Find out how many years are required to get back the amount. [ 7 approx ]
9) Save and close the workbook.
Sorting, Subtotal and Filtering Data
LEARNING OBJECTIVES
 Sorting a table
 Subtotal
 Filtering data Sorting a table
Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the totals sales made. Excel’s Sort feature helps organize the data in a list. Column fields can be quickly sorted in ascending or descending order. To sort the data in a column, select any cell in the column by which you want to sort.
To sort in ascending order, click the Sort Ascending button on the Data Tab (Data Tab  Sort & Filter Tab). To sort in descending order, click the Sort Descending button on the Data Tab. If you want to sort a list by more than one field, you can use the Data Tab  Sort & Filter Tab  Sort.
After applying sort command the data table will look like as shown below.
Note: You can sort on any number of columns. The trick is to sort the most significant column first and then proceed until the least significant column is sorted. For example, in the real estate table, you may want the list to be sorted by agent. And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Agent column, and then sort by the Area column, and then sort by the List Price column. Figure above shows the table sorted in this manner.
Calculating Subtotals
The most convenient way to create an outline for your worksheet data and to insert subtotals is to use the Subtotal command, which is now situated in the Outline group of the Data tab.
To create automatic subtotals, follow the steps:
1) Sort the data by the column that you want to base your calculation on (this will allow excel to group the data correctly).
2) Click the Subtotal command in the Outline group on the Data tab. The Subtotal dialog box will open.
3) Select the column that you want to base the summary calculations on from the ‘At each Change in’ dropdown list.
4) Select ‘SUM’ function that you want to use the summary calculation from the ‘Use function’ dropdown list.
5) From the ‘Add Subtotal to’ list, select the columns for which you want to include summary calculation.
6) Select or deselect the ‘Replace current subtotals’, ‘Page break between groups’, and ‘Summary below data’ options as appropriate. If you deselect the ‘Summary below data’ option, the subtotals will appear above their respective groups, with the grand total at the top of the data.
7) When you have finished making your selections, click OK. Excel will outline your data according to values in the specified column and will insert the appropriate subtotals and grand total into your worksheet.
Filtering of Data
Many a times, perhaps you need to work with a particular group of records in the database only. A Filter is used to select records that meet specific criteria and temporarily hide all other records. Excel supports mainly two ways to find out specific records by defining criteria: AutoFilter and Advanced Filter. AutoFilter
Use to get the necessary data from a huge database.
1) After selecting the database, click on Filter.
2) Dropdown Menu will come automatically. Start filtering from that menu as per as the criteria.
3) To filter product, click on the product drop lists button to appear a dialog box as shown below. Now deselect all products and select the only Mobile and click on Ok button. (See figure below)
Shows appropriate Subtotals and Grand total.
ADVANCED FILTER
Advanced Filter is identified for its role in searching for specific records meeting a certain criteria. With an advanced filter you can denote the exact criteria to be found. This option helps to find records within database as well as to create another database based on criteria to another location (i.e. Subset of the original database).
Class Exercise
1) Open your last saved worksheet. Go to Student List sheet.
2) Find out the records from the Student List who has taken admission in B.COM course. ( Use Filter)
3) Remove filter from the database.
4) Insert a new worksheet after Student List sheet. Form a database in this worksheet only for B.COM and B.B.A student extracting from Student List database. (Use Advanced filter). Rename the sheet as Extract.
5) Insert a new sheet, name the sheet as “ITEM LIST” and prepare the following dataset.
6) Complete the above table using suitable calculation. Now prepare a data form for the Item List and insert the following records. Don’t put or calculate the values for Amount, Discount Amount and Net Amount field.
7) Go to the Student List sheet and sort the records as per student name in A to Z order.
8) Freeze first two rows and columns of Student Details database.
Filtered data based on Product: Mobile
9) Save and close the workbook.
Charts and Protection
LEARNING OBJECTIVES
 Type of charts
 Creating chart
 Worksheet protection
 Workbook protection Creating Charts
A chart is a visual representation of numeric values. A chart is also known as graphs. A chart can be created from the data of a database to provide more visual presentation of the data. Excel’s chart feature provides a variety of charts including Bar, Column, Pie, Area and many more.
Types of Charts
You can create different types of charts. The chart type you choose depends on the kind of data you are trying to chart, and on how you want to present that data. The followings are the major chart types.
 Column – Similar to the bar chart; use this chart type to emphasize the difference between items.
 Pie – Use this chart type to show the relationship among parts of a whole.
 Bar – Use this chart type to compare values at a given point in time.
 Line – Use this chart type to emphasize trends and the change of values over time.
 Scatter – Similar to a line chart; use this chart type to emphasize the difference between two sets of values.
 Area – Similar to the line chart; use this chart type to emphasize the amount to change in values over time. Most of these basic chart types also come in three-dimensional varieties.
Creating a Column or Bar Chart from Data in a Worksheet
Once you have entered the data you want to display as a chart into a worksheet and decided which type of chart you require, you can create it with just a few mouse clicks. Let’s take a database to create column or bar chart.
1) Select Cells A1:B7, Click the drop lists of Column.
2) Go to Insert tabCharts groupClick the drop lists of Column or Bar command to display subtypes.
3) Choose and click the appropriate Sub-type charts. (See figure below)
Creating of Pie Chart from Data in a Worksheet
1) To create a pie chart enters the data into the worksheet as shown below. Let’s create a pie for Wipro.
2) Select Cells A1:A6 and Cells C1:C6 holding Ctrl key.
3) Go to Insert tabCharts groupClick the drop lists of Pie and choose and click the appropriate pie chart type. (Refer to figure below)
4) Click Chart Tools Layout TabClick the drop lists of Data LabelsChoose and click Inside End.
Wipro
2008
2007
2006
2005
2004
Sheet Protection
Excel users protect a sheet for a variety of reasons. One reason is to prevent himself or others from accidentally deleting formulas or other unwanted modifications.
1) To protect a sheet, activate the sheet and choose ReviewChanges Protect Sheet.
2) On clicking Protech Sheet, the following screen appears. Enter a password, although a password is optional. If you enter a password, that password will be required to unprotect the sheet.
3) Turn on the required option and click OK button to display Confirm Password dialog box. Retype the same password for confirmation and click OK button. Finally save the worksheet. Protecting a Workbook
To protect a worksheet’s structure:
1) Choose ReviewChangesProtect Workbook to display the Protect Workbook dialog box
2) In the Protect Workbook dialog box, place a check mark next to Structure.
3) Enter a password and Click OK.
4) Reenter the password again for confirmation and Click OK button.
5) Finally save the workbook.
To unprotect the workbook’s structure, choose ReviewChangesUnprotect Workbook. If the workbook’s structure was protected with a password, you are prompted to enter the password. Feed the correct password to unprotect workbook.
When a workbook’s windows are protected, the user can’t change anything related to the window size or position. For example, if the workbook window is maximized when the windows are protected, the user cannot unmaximize the window. The windows can, however, be zoomed.
To unprotect the workbook’s windows, go to Review TabClick the drop lists of Protect Workbook. Choose and click Protect Structure & Windows to open the following dialog box.
If the workbook’s windows were protected with a password, you are prompted to enter the password. Feed the correct password to unprotect the workbook. Protecting an Excel File
Excel lets you save a workbook with a password. After doing so, whoever tries to open the workbook must enter the password.
To add a password to a workbook:
1) Go to Office button PrepareClick Encrypt Document to open the following dialog box as shown below.
2) Type a password and click OK.
3) Type the password again for confirmation and click OK button.
4) Finally Save and close the workbook.
Now, when you reopen the same excel file, it will ask for a password to open. You need to feed the correct password to open the file as shown below.
Class Exercise
1) Open your last saved worksheet. Go to Installment sheet set an image to display as the background of the sheet.
2) Go to payroll sheet and set first row to repeat at top on each printed page.
3) Insert a new worksheet name “Age group” and prepare the following data set:
4) Produce a Column chart with the above data in the same sheet.
5) Insert a new worksheet name “Trading Status” and create the following database.
6) Produce a Pie chart to get at a glance of trading status for the month of march in the same sheet.
7) Go to the Payroll sheet and protect the entire worksheet. Now try to change any basic salary of any employee.
8) Save and close your workbook with password for opening and modification level.
9) Try to open the workbook without supplying the password.
10) Close your workbook.
Page Setup, Lookup Functions and Splitting Worksheet
LEARNING OBJECTIVES
 Page setting for printing
 Lookup & reference functions
 Splitting worksheet Page Setup Group Options
In Excel, you can print your worksheets just the way they look after you enter the data, or you can enhance the printout using several page layout options. When you select Excel's Page Layout Tab, the Page Layout tab contains several options as shown below.
Margin Option
This option is used to select the margin sizes for the entire worksheet.
1) To set a margin, Click the drop lists of Margins under Page Layout tab and then click Normal, Narrow, or Wide as required.
2) You will notice a dotted vertical and horizontal line, which indicate print area per page. Orientation
This option is used to switch the pages between portrait and landscape layouts.
1) To change the page layout, click the drop lists of Orientation option under Page Layout tab and then Click Portrait (vertical), which is the default, or Landscape (horizontal).
2) You will notice a Landscape layout (horizontal view) as shown below. Size
This option is used to select a paper size for a current section.
1) To select required paper size, click the droplists of Size option under Page Layout tab and then select A4 size page or any other page size as required.
2) Show the view of A4 size page. Print Area
This option is used to mark a specific area of the sheet for the printing.
1) To set a print area, select the range of cells.
2) Click the droplists of Print Area option under Page Layout tab and select set print area.
3) Dotted lines indicate the printable area. See figure below Breaks
This option is used to specify where a new page will begin in the printed copy. Page breaks are inserted above and to the left of the selection.
1) To apply a page break, place your cursor on a particular cell (A15).
2) Then click the droplists of Break option under Page Layout tab and select Insert page break option.
Background
This option is used to select an image to display as the background of the sheet.
1) To set a background image, click Background option under Page Layout tab and then select an image and click Insert button to add image as background. See figure below. Print Title
This option is used to specify rows and columns to repeat on each printed page.
1) To set a print title, click Print Title option under Page Layout tab and then select a row to repeat at top and column to repeat at left as required.
2) Then Click OK. Now if you take a printout, you will notice that the title is getting printed in each page. See figure below.
Previewing Worksheets Before Printing
You can view your Excel 2007 worksheet as it will be printed by clicking the Microsoft Office Button, pointing to Print, and then clicking Print Preview. When you do, Excel 2007 displays the active worksheet in the Print Preview window.
When the Print Preview window opens, it shows the active worksheet as it will be printed with its current settings. In the lower-left corner of the Print Preview window, Excel 2007 indicates how many pages the worksheet will require when printed and the number of the page you are viewing. Lookup & Reference Functions
These functions are useful when user has to extract a single set of data from a long list of data set. VLOOKUP( )
The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically (which explains the V in the function’s name). The syntax for the VLOOKUP function is:
The VLOOKUP function’s arguments are as follows:
 Lookup_Value : The value to be looked up in the first column of the lookup table.
 Table_Array : The range that contains the lookup table.
 Col_index_num : The column number within the table from which the matching value is returned.
 Range_lookup : Optional. If TRUE or omitted, a nearest match is returned. (If an exact
Match is not found, the next largest value that is less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function returns #N/A.
Tips: If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A.
If the lookup_value argument is text, it can include wildcard characters * and ?.
Splitting the Worksheet Window into Panes
Sometime, you may face difficulties in managing large sheets. Problem may arise to view different part of the sheet with in a single window. To solve this problem Excel provides another option for viewing multiple parts of the same worksheet. Choosing View  Window  Split .It splits the active worksheet into two or four separate panes. The split occurs at the location of the cell pointer. If the cell pointer is in row 1or column A, this command results in a 2-pane split. Otherwise, it gives you four panes. You can use the mouse to drag the individual panes to resize them.
Class Exercise
1. You are having the following database, and you want to find any record in such a way so that when you enter the Salesman Name, his entire details will automatically appear. Configure the blank fields except Salesman name, After configuring the cells enter any salesman name and checks whether respective d-ata for that salesman appears or not:
Example:
Figure above shows a simple worksheet that uses two lookup formulas. This worksheet contains a table of employee data, beginning in row 7. This range is named EmpData. When you enter a last name into cell C2, lookup formulas in D2:E2 retrieve the matching information from the table. The following lookup formulas use the VLOOKUP function:
D2  =VLOOKUP(C2,EmpData,2,FALSE)
E2  =VLOOKUP(C2,EmpData,3,FALSE)
Pivot Table, Database Function and What-if-Analysis
LEARNING OBJECTIVES
 Creating Pivot Table
 Database Function
 Scenario Manager
 Goal Seek
 Solver Pivot Table
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data Creating a Pivot Table
If your data is in a worksheet range, select any cell in that range and then choose Insert ➪ Tables ➪ PivotTable, which displays the dialog box shown in
Excel attempts to guess the range, based on the location of the active cell. If you’re creating a pivot table from an external data source, you need to select that option and then click Choose Connection to specify the data source.
Specifying the location for the pivot table
Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is on a new worksheet, but you can specify any range on any worksheet, including the worksheet that contains the data. Database function DMIN
DMIN function is useful to find out the lowest value of a field based on the criteria provided by you. The syntax of the function is =DMIN (Database,”Field”,”Criteria”). Database is the table from where the result has to be extracted. Field is the field name (Column name) or the position of the column in the table of which minimum value has to be found out and criteria is the range of cells where you specify the condition.
This function is extremely helpful when you have multiple records for a particular item and you need to find the lowest value among them on certain criteria.
DMAX
This function is used to return the largest number of the field or column in a database based on certain criteria. The syntax of the function is: =DMAX(Database,Field,Criteria). Meanings of the arguments are same as DMIN function. Scenario Manager
Scenario Manager allows you to set up several different scenarios. You can use scenarios to predict the outcome of different situations in your spreadsheet. Suppose a company trying to establish a budget for the upcoming year does not know what the sales for the year will be. The sales figures obviously affect the rest of the budget. With the Scenario Manager you can create a “Best Case” and a “Worst Case” scenario. The best-case scenario would show that the budget figures would look like if sales for the year were especially good. The worst-case scenario would show the budget figures would look like if sales were especially poor.
Goal Seek
It determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell. The following example shows you how single-cell goal seeking works.
The figure above is showing an investment of Rs.1200 per month @9.5% rate of interest for 5 years can yield a future value of Rs.91706.70. Now the problem with the investor is to get Rs.150000 after the 5 years span. Now he/she wants to know how much should be invested to get that much of amount. This can be solved through Goal Seek option of excel what-if analysis tool. Following are the steps to be followed:
1. Place your cursor on cell B4. Chose Data  Data Tools  What-If Analysis  Goal Seek. Excel displays the Goal Seek dialog box, shown in Figure below:
2. The following information box will appear.
Click OK to accept the result or Click Cancel to restore your worksheet to the form that it had before you chose the Goal Seek command. Solver
The Excel Goal-Seek feature is a useful tool, but it clearly has limitations. It can solve for only one adjustable cell, and it returns only a single solution. Excel’s powerful Solver tool extends this concept by enabling you to do the following:
1) Specify multiple adjustable cells
2) Specify constraints on the values that the adjustable cells can have
3) Generate a solution that maximizes or minimizes a particular worksheet cell
4) Generate multiple solutions to a problem
Set cell B4; the cell you where want to set the value.
Set value 150000 To value field.
By changing cell field should contain the address of the cell you want to change [B1]; i.e, the monthly premium.
Result
Class Exercise - 1
1. Create a new workbook and prepare the following data base with necessary functions.
P_Code
QTY
Rate
Amount
Discount Rate
Discount Amount
Net Amount
P001
42
20
10%
P002
45
15
5%
P003
50
10
5%
P004
100
11
10%
P005
52
21
4%
P006
84
33
3.33%
2. A partnership firm as earned a profit of Rs. 500000 in a particular year. The partners decide to share 45% of book profit amongst themselves in the ratio of their capital. Calculate their share of profit.
Partners
Capital
Share
T.Todi
150000
S.Laha
275000
M.Tewari
650000
3. Complete the following data base on another sheet of the same workbook. Rename the page as Salary.
Code
Basic
Dept
PF
House (Yes/No)
HRA
DA
Gross
PE/001
5000
ACCT
PE/002
4500
ACCT
PE/003
5200
MKT
PE/004
4100
ACCT
PE/005
3200
STCK
PE/006
3550
ADMIN
PE/007
5600
ADMIN
a. PF will be 12% of basic or Rs.600 whichever is higher.
b. Provide House for Accounts dept only
c. Calculate HRA @ 15% for those who do not get accommodation from the company.
d. DA will be 10% of Basic.
e. Calculate the gross salary.
4. Prepare the following layout for salary slip.
This is an auto generated pay slip, means, if any one put the Employee code the entire amount will be generated automatically. Use necessary functions for that.

Class Exercise - 2
5. Complete the following database with necessary functions.
a. If the total amount is more than Rs.1,00,000 then payment date will be 15 days after inward date.
b. Find out the total quantity of all products.
c. Find out the total value of Nokia Mobile
d. Find godown wise total report
e. Create a bar chat for the above data base, based on godown.
f. Find out minimum value product.
g. Highlight the product values which are more than Rs.70,000
h. Which godown is having more quantity of items?
i. Find out maximum value product.
j. Create a filter for the above database.
Class Exercise - 3
Complete the following questions.
1. If you have deposited Rs. 1,00,000 simple interest @ 6.25% p.a, how much you will get after 10 years?
2. If you have deposited Rs. 250000 in simple interest @3.25% p.a, how much you will get after 5 years?
3. Suppose you are going to deposit Rs. 2,646 quaterly in arecurring account to get back Rs. 100000 after certain period with 8% interest p.a. Find out how many years are required to get back the amount?
4. Suppose you have purchased a car for Rs. 5,50,00. You have pad Rs.1,00,000 in cash immediately. Rest of the amount will be paid 10 monthly equal installments with 12.75% interest p.a. Find out the EMI.
5. Present interest rate is 5% p.a. A financial scheme is giving Rs. 50000 at the end of 5 years. How much you have to pay in present to get the said value?
6. Complete the following trading Account for two financial years. Data are given below.
Particulars 2009-10 2010-11 Opening Stock 25000 20000
Purchase
1250000
150000 Purchase Return 36000 40000
Sales
1100000
1350000 Sales return 65000 51000
Wages
50000
52000

MS WORD 2007 NOTES

MS-WORD 2007

Introducing Word 2007

 Opening Ms-Word

 Working with word document

 Editing and proofing of documents

 Saving a document

Microsoft Office Word 2007 is a stylish word processing program that helps you quickly and efficiently formats all the business and personal documents you are ever likely to need. You can use Word to:

 Creation of a simple and business document like letter, report, notice, newspaper article and etc.

 To give a uniform look in documents with the help of fonts, font size, font colour and the page background.

 A personalized letter can be easily sent to multiple receivers without typing the content repetitively.

Opening Microsoft Word

1) Click Start buttonAll ProgramsMicrosoft OfficeMicrosoft Office Word 2007.

Alternative Way

1) Right-clickChoose New from the pop-up menu.

2) Click Microsoft Office Word document from the sub-menu.

3) Double-click on the Ms-word icon to open.

The Word window has many features to help you in creating and editing documents.

Working with Word Document

Microsoft Office Word 2007 allows you to create and edit documents, such as letters, reports, invoices, plays, and books. The book you are reading now was written in Word. In the computer, a document is called a file, an object that has been given a name and is stored on a disk drive.

Simply starting Word opens up a blank document pane into which you can start typing a new document immediately. The blinking bar in the upper-left corner of the document pane, called the insertion point, indicates where the text you type will appear. Simple Text Formatting

1) Select the desired text that you to format

2) Click Bold, Italic, Font colour command etc on the Home tab in the Font group.

Title Bar

Office Menu

Quick Access Toolbar

Tabs on the Ribbon

Group

Document Area

Ruler

Dialog Box Launcher

Close

Scroll Bar

Status Bar

View Toolbar

Zooming Scale

Minimize

Maximize/Restore

You can also use the MiniToolbar. The Mini Toolbar is activated when you select text to be formatted. This toolbar is small and semi-transparent to help you work with fonts, font styles, font sizing, alignment, text color, indent levels, and bullet features.

Formatting using Font Group

Font group provides the quickest way for controlling various fonts attributes such as the font and the font size, color etc. you can also access several more font attributes in the Font dialog box. To access the Font group, just click the Home ribbon.

Insertion point

Font

Font is the Type face you use for entering any text in your document. Each font has a particular look and feel that makes it unique. Each font is identified with different names such as Arial, Courier, Times New Roman etc.

Different Fonts and their looks:

Institute of Computer Accounts Font used “Britannic Bold”

Institute of Computer Accounts Font used Algerian

Size

Change the font size. Enter or select the desired size like 10, 12 etc

Basic formatting

Bold: Click the bold button () on the Home ribbon or press Ctrl + B.

Italics: Click the italics button () on the Home ribbon or press Ctrl + I.

Underline: Click the underline button () on the Home ribbon or press Ctrl + U.

Strikethrough

Simply it places horizontal line through the text. Example : Sumit Gupta

Superscript &

Subscript

Select the text that you want to format as superscript or subscript.

Superscript: (Press CTRL+SHIFT+= ) Text is made smaller and placed above the base line. Examples: Superscript a2

Subscript: (Press CTRL+= ) Text is made smaller and placed below surrounding the text .Examples: Subscript : H2O

Change Case

Text can be changed in Sentence case, lowercase, Uppercase, Capitalize Each Word or Toggle case.

Font Color

Change the text color.

Text Highlight Color

You can use it to highlight text to remember or to point out information for others.

Grow/Shrink Font

Grow Font button or the Shrink Font button to increase or decrease the font size.

Clear formatting

Clear all the formatting from the selection leaving only the plane text.

Editing and Proofing Documents Selecting Text

 Select a word: Click and drag or Double-click on the word.

 Select a sentence: Click and drag or click in the sentence while holding down the CTRL key.

 Select a paragraph: Click and drag or triple-click in the paragraph or double-click in the selection area to the left of the paragraph.

 Select a block of text: Click and drag or click to the left of the first word, hold down the SHIFT key, and then immediately click to the right of the last word.

 Select a line: Click and drag or click in the selection to the left of the line.

 Select an entire document: Triple-click in the selection area or hit the CTRL + A keys.

 Deleting text: Select the text and hit the DELETE key.

 Undo an action: On the Quick Access Toolbar click the Undo key. Deleting Text

When deleting text in your document you can either use the Delete key or the Backspace key. The Delete key deletes text to the right of your cursor while the Backspace key deletes text to the left of your cursor.

Cut, Copy and Paste

Often times you will need to move text from one location in a document to another, or to a different application. In this case you will perform a Cut and Paste. To reproduce a specific part of a document and place it elsewhere, you will perform a Copy and Paste.

To Cut Text:

1) Select the text you want to move.

2) Click on the Cut icon located on the Home tab in the Clipboard group.

~OR~ Press CTRL+X from keyboard to cut the selected text.

To Copy Text:

1) Select the text you want to copy.

2) Click on the Copy icon located on the Home tab in the Clipboard group.

~OR~ Press CTRL+C from keyboard to copy the selected text.

To Paste Text:

1) Click in the area of the document where you want to paste your text.

2) Click on the Paste icon located on the Home tab in the Clipboard group.

~OR~ Press CTRL+V from keyboard to paste the copied text.

Simple Paragraph Formatting

A paragraph is a select group of text that can have its own formatting characteristics, such as alignment, spacing and styles.

Formatting using Paragraph Group

To access the Paragraph group, just go to the Home tab.

Left Alignment

Aligns each line of the paragraph at the left margin.

Center Alignment

Aligns the center of each line in the paragraph between the left and right margins

Right Alignment

Aligns each line of the paragraph at the right margin.

Justify Alignment

Align text to both left and right margin, adding extra space between words as necessary.

Line Spacing

Change the spacing between lines of text.

Shading

Colour the background behind the selected text or paragraph.

Border

Apply border on the selected text.

Show/Hide

Show Paragraph marks and other hidden formatting symbols.

Sort

Alphabet-wise sorting text or sort numerical data.

Decrease Indent

Decreases the indent level of the paragraph.

Increase Indent

Increased the indent level of the paragraph.

Multilevel List

Create different multilevel list styles.

Numbering

Used to create numbered list.

Bullets

Used to create a bulleted list. Measurement Units

Ms Word uses different measure units. By default all measurement values are set in Inches. However, you can change it. Following are the steps to change measurement:

 Click Office MenuWord OptionsAdvanced

 Select the required measurement system from the Show Measurement in Units drop down.

The following Figure shows how the Advanced dialog box containing measurement units drop-down list appears.

Saving a New Document

1) On the Quick Access Toolbar, click the Save button

~OR~

2) Click the Office Button.

3) Click Save As, the Save As dialog box will appear.

4) In the File Name field, type the name of the document.

5) Click Save button. Closing a Document

1) Click the Office ButtonClose.

2) If a Microsoft Office Word dialogue box appears displaying a “Do you want to save…?” message:

3) Click Yes to save, No to discard, or Cancel to close the dialogue box.

Opening an Existing Document

1) Select the file from a specific location and double-click on it to open.

~OR~

2) Open a new word document, click Office ButtonOpen to display Open dialog box.

3) Select the file name and click Open button. Class Exercise

1) Open a new word document and keep the following text within your document.

2) Keep a heading as ‘Tips for Writing a Precis’ at the beginning of the text matter.

3) Select the heading ‘Tips for Writing a Precis’ and apply the following formatting :

(a) Font Face - Britannic Bold

(b) Font Size - 20 Points

(c) Colour - Blue

(d) Alignment - Center

4) Select the text ‘Goals of the Precis’ and apply the following formatting:

(a) Font Face - Stencil

(b) Font Size - 14 Points

(c) Colour - Red

(d) Alignment - Center

5) Format rest of the content as follows:

(a) Font Face - Cambria

(b) Font Size - 12 Points

(c) Alignment - Justify

6) Make a copy of the entire content at the end of the current document area.

7) Keep the following matter at the end of the document as given below

Sulphuric Acid= H2SO4 ; Water=H2O ; Algebraic Formula: a2+2ab+b2

8) Finally save your work under C:\Candidates folder with your name followed by Word-01 (e.g. Arjun_Word-01).

Formatting of text

LEARNING OBJECTIVES

 Using the clipboard

 Font and paragraph Formatting

 Change Case

 Drop cap

The Clipboard is a location in the computer’s memory that is used to store information temporarily. The Office Clipboard can store up to 24 objects, both text and pictures, and pass those objects within or among other Office programs. Once the Office Clipboard is enabled, all objects that are cut or copied to it are kept on the Office Clipboard until the 25th object is cut or copied, which will replace the first object. All objects on the Office Clipboard are lost from the Clipboard when the computer is turned off.

To enable office clipboard, follow the steps:

1) Click Clipboard dialog box launcher on the Home tab to display clipboard task pane.

2) Now copy or cut any text item, it will automatically appear in the clipboard. You will also see a notification in the bottom right hand corner of your screen, confirming that the item has been collected.

3) To paste an item from the clipboard, place your cursor to a location where you want to paste. Then click the down-arrow next to the copied item on clipboard task pane and click Paste.

Clipboard task pane

Note: To delete all items, click Clear All on the Clipboard task pane. To delete a single item, click the down arrow next to the item and click delete.

Advanced Font Formatting

When you type text in a document, it displayed in a particular font. Each font consists of characters, number and symbols that share a common designed. By default the font used for text in a new Word document is Calibri, but you can change the font at any time.

There’s a place in Word document where all your font-formatting delights are kept in a neatly organized fashion. It’s the Font dialog box, shown in the Figure below.

1) To call upon the Font dialog box, click the Dialog Box Launcher button in the lower-right corner of the Font group on the Home tab or press Ctrl+D.

2) In the Font tab, select the font, font style and font size.

3) To change font colour, click Font Color and then click a color.

4) If you want, click Underline list arrow, click a style, click Underline Color list arrow, and then click a color.

5) Click OK button.

Changing Character Spacing:

1) Select the text that you want to format.

2) Click the Home tabClick Font Dialog Box Launcher.

3) Click the Character Spacing tab.

4) Click the Spacing list arrow, click an option, and then specify a point size to expand or condense spacing by the amount specified.

5) Click the Position list arrow, click an option, and then specify a point size to raise or lower the text in relation to the baseline (bottom of the text).

6) Select the Kerning for fonts check box, and then specify a point size.[Kerning is the amount of space between each individual character that you type.]

7) Check the results in the Preview box.

8) To make the new formatting options the default for all new Word documents, click Default, and then click Yes.

9) Finally click OK button. Advanced Paragraph Formatting

As you know, you create a paragraph by typing text and then pressing the enter key. The paragraph can be a single sentence or multiple sentences. You can change the look of a paragraph by changing its alignment and line spacing. You can also put borders around it and shade its background. Collectively, the settings you use to vary the look of a paragraph are called paragraph formatting.

1) Go to the Home tabclick the Dialog Box Launcher button in the lower-right corner of the Paragraph group.

2) In the Indents and Spacing tab, select the Alignment, Indentation and Spacing as required.

3) Click Line and Page Breaks tab and select the required check box as required.

The Font dialog box contains all the commands for formatting text, even quite a few that didn’t find their way into the Font group.

Please note the lovely Preview window, at the bottom of the Font dialog box. This window allows you to preview changes to your text.

Changing the CASE of the Text

Now there’s a handy way to change the case of text. For example, after entering text in a document, you decide you want to highlight one of the words by changing all the letters in the word to all caps. To change the case of text in Word, use the Change Case command button in the Font group.

To change the case of text:

1) Select the text you want to change.

2) On the Home tabClick the down arrow of Change Case commands to display a menu of option,

The Change Case command offers five options:

Tips: You can also use the Shift+F3 command to change the case of selected text. But that keyboard shortcut cycles between only three of the menu options: ALL CAPS, lowercase, and Capitalize Each Word.

Drop Capital

A drop cap is an especially large letter, usually at the beginning of a paragraph. The letter drops from the top of its own line down several lines below. Drop caps are usually used for just the first letter in a book, chapter or article.

Select the paragraph or leave the cursor within the paragraph that you want to begin with a dropped capital letter.

1. On the Insert tab  go to Drop Cap button, from the drop down list point to the different choices to see the effect. Refer to figure above.

Click Drop Cap Option, Font style for the dropped capital letter and for how many lines you want to drop. If Dropped letter is overlapping with the normal text of the paragraph you can set some distance between dropped letter and normal text by declaring the amount of space on Distance from text. Refer to figure above.

Widow/Orphan control: The fist line and the last line of a paragraph are known as Widow and Orphan lines. If this option is ON then word does not allow the singly standing first line of a paragraph at the end of the page or the singly kept apart last line at the beginning of the page.

Keep lines together: Prevents a page break within a paragraph.

Keep with next: Prevents a page break between the selected paragraph and the following paragraph.

Page break before: Inserts a page break before the selected paragraph.

Suppress line numbers: Prevents line number from appearing next to selected paragraphs.

Don’t hyphenate: Excludes a paragraph from automatic hyphenation.

Class Exercise

1) Open your last save word document.

2) Select all the ‘Goals of the Precis’ from the document and apply Bullet format.

3) Select first Paragraph “ A well-written …………constitutional reform” and apply the following formatting decisions:

(a) Left Indent - 1.5 Cm

(b) Before Spacing - 12 Points

(c) After Spacing - 12 Points

(d) Line Spacing - 1.15 Lines

4) Enter the following text at the end of your current document and format as given below.

OFFICE ONE NOTE

Office OneNote is essentially an online notepad, a program that you use as you would a three-ring binder. You can divide a OneNote notebook into sections and add pages for notes, drawing, lists and records. You can also add electronic printouts to a OneNote notebook, audio and video recordings and copies of and links to web sites. You need to learn the following:-

Types of Notebook

Organising Notebook

Tagging Types of Notes

Printing to One Note.

5) Apply first-line indent on the paragraph of Office One Note.

6) Apply upper case on the sentence “Types of Notebook”.

7) Interchange the position of texts ‘Organising Notebook’ and ‘Tagging Types of Notes’.

8) Apply toggle case on the sentence “ Printing to One Note”.

9) Make three (3) copies of ‘Office One Note’ using the clipboard.

10) Save the document with the your under under C:\Candidates folder followed by Word-02 (e.g. Arjun_Word-02).

Bullets, numbering and borders- shading

LEARNING OBJECTIVES

 Bullets and numbering

 Borders and shading

Creating Bulleted and Numbered Lists

The best way to draw attention to a list is to format the items with bullets or numbers. You can even create multi-level lists. For different emphasis, change any bullet or number style to one of Word’s many predefined formats. For example, switch round bullets to check boxes or Roman numerals to lowercase letters. You can also customize the list style or insert a picture as a bullet Create a Bulleted List

1) Click where you want to create a bulleted list.

2) Click the Home tabClick the Bullets button arrow and then select a bullet style.

3) Type the first item in your list, and then press Enter.

4) Type the next item in your list, and then press Enter.

5) Click the Bullets button, or press Enter key again to end the list. Create a Numbered List

1) Click where you want to create a numbered list.

2) Click the Home tabClick the Numbering button arrow, and then select a numbering style.

3) Type the first item in your list, and then press Enter.

4) Type the next item in your list, and then press Enter.

5) Click the Numbering button, or press Enter key again to end the list.

Tips: You can quickly create a numbered list. Click to place the insertion point at the beginning of a line, type 1., press the Spacebar, type the first item, and then press Enter. Type the next line in the list or press Enter or Backspace to end the list.

Create a Multi-Level Bulleted or Numbered List

The new Multilevel List button makes it easy for you to create a list of items at different levels. There are several multilevel list styles from which you can choose. After choosing a style, Word will automatically format the paragraph indents and number each paragraph.

1) Choose the number formats, the font settings, and the position for each level.

2) Choose whether you want the list style to be available only in the current document or all new documents based on this template.

3) When you have defined each level, click OK.

Applying Borders and Shading

To enhance the appearance of the text in a paragraph, you can format it using the buttons in the Paragraph group on the Home tab. You can quickly add a border and shading to selected text.

Apply a Border:

1) Select the paragraph text you want to format.

2) Click the Home tab.

3) Click the Borders and Shading button arrow, and then click to select the border commands to add or remove a border.

Apply Paragraph Shading and Borders:

1) Select the paragraph text you want to format.

2) Click the Home tab.

3) Click the Borders and Shading button arrow, and then click Borders and Shading.

4) Click the Borders tab.

5) Click to select the type of Setting you

want for your border.

6) Click to select the type of Style.

7) Apply any other options you want.

8) Look in the preview box to see the new

border.

9) Click the Shading tab.

10) Click to select the shading fill color you

want to apply to your table.

11) Apply any other options you want.

12) Look in the preview box to see the new shading color.

13) Finally Click OK.

Shading

Border

Class Exercise

1) Open a new word document and prepare the following matter as it is with necessary formatting.

Word Features

 Margins

 Zoom

 Header & Footer

 Page Setup

 Tab Settings

2) Prepare the following matter just below the Word Features as it is with necessary formatting to give a smooth look.

 TOP 3 FIFA CHAMPION

 Brasil- 5 times

 In the Year

1. 1958

2. 1962

3. 1970

4. 1994

5. 2002

 Italy- 4 times

 In the Year

1. 1934

2. 1938

3. 1982

4. 2006

 Germany-3 times

 In the Year

1. 1954

2. 1974

3. 1990

3) Select the text ‘Word Features’ and give double-line border and a light pink shade.

4) Apply 3-D page border to the entire document. The colour of the border should be orange.

5) Save your work under C:\Candidates folder with your name followed by Word-03 (e.g. Arjun_Word-03).

Page Setup and column styles

LEARNING OBJECTIVES

 Page orientation

 Page setup

 Page margin

 Page number

 Columns

Every document you produce and print might need a different page setup. You can achieve the look you want by printing on a standard paper size (such as letter, A4 or Legal), international standard paper sizes, or any custom size that your printer accepts. The default setting is 8.5 x 11 inches, the most common letter and copy size. You can also print several pages on one sheet. You can also select the page orientation (portrait or landscape) that best fits the entire document or any section. Portrait orients the page vertically (taller than it is wide) and landscape orients the page horizontally (wider than it is tall). Set the Page Orientation and Size Quickly

1) Click the Page Layout tab.

2) To quickly change the page orientation, click the Orientation button, and then click Landscape or Portrait.

3) To quickly change the page size, click the Size button, and then click the size you want.

Set Custom Page Size Options

1) Click the Page Layout tab.

2) Click the Page Setup Dialog Box Launcher, or click the Size button, and then click More Paper Sizes.

3) Click the Paper tab.

4) Click the Paper Size list arrow, and then select the paper size you want, or specify a custom size.

5) Select the paper source for the first page and other pages.

6) Click the Apply To list arrow, and then click This Section, This Point Forward, or Whole Document.

7) Verify your selections in the Preview box.

8) To make your changes the default settings for all new documents, click Default, and then click Yes. Setting Page Margin

If your document has multiple pages, your margin settings will depend upon how you want to publish the pages. For example, will you bind the pages? Punch holes in the margin and put the pages in a binder? Fold the pages into a booklet? Word offers several options to help you choose the appropriate margin settings for all of these options.

1) Click the Page Layout tab.

2) Click the Margins button in the Page Setup group.

3) Choose any margin setting from a list of predefined margin settings option or you can customize your own by clicking Custom Margins at the bottom of the menu. The Page Setup dialog box will display

4) Enter your own desired settings like Top, Bottom, Left, Right and gutter margin. Specify the gutter position as left or top.

5) To make your changes the default settings for all new documents, click Default, and then click Yes.

6) Finally click OK button.

Inserting Page Number to a Document

Page numbers help you keep your document in order or find a topic from the table of contents. Number the entire document consecutively or each section independently; picks a numbering scheme, such as roman

numerals or letters. When you insert page numbers, you can select the position and alignment of the numbers on the page. Insert and Format Page Numbers

1) Click the Insert tab.

2) Click the Page Number button.

3) Point to the position you want (Top of Page, Bottom of Page, Page Margins, or Current Position), and then select a position.

4) Click the Page Number button, and then click Format Page Numbers.

5) The Page Number Format dialog box appears.

6) Click the Number format list arrow, and then select a numbering scheme.

7) Select the starting number.

8) Click OK.

Arranging Text in Columns

Newspaper-style columns can give newsletters and brochures a more polished look. You can format an entire document, selected text, or individual sections into columns. You can create one, two, or three columns of

equal size. You can also create two columns and have one column wider than the other. Word fills one column with text before the other, unless you insert a column break. Create Columns

1) Click the Page Layout tab.

2) Select the text you want to arrange in columns.

3) Click the Columns button.

4) Select the number of columns you want.

Insert a Column Break

1) Click where you want to insert a column break.

2) Click the Page Layout tab.

Column Divider

3) Click the Break button, and then click Column.

4) The text jumps up to the top of the next column.

Cursor Position

Class Exercise

1) Open a new word document with the following specification:

Page Size : A4 (8.27’’x11.69’’)

Orientation : Portrait

Margin : 1’’ each

Gutter : 0.5’’

2) Keep the following text within your document.

Care and Maintenance

Occasional cleaning of your scanner helps ensure higher-quality scans. The amount of care necessary depends upon several factors, including the amount of use, and the environment. You should perform routine cleaning as necessary. For all care and maintenance procedures required for the scanner, please see the Care and maintenance section for your scanner in the onscreen Help.

In HP Routine cleaning and maintenance should include cleaning the scanner glass and the transparent materials adapter (TMA). Avoid placing items that have sharp edges in the scanner. Doing so can damage the scanner. Before placing an item on the scanner glass, make sure that the item does not contain wet glue, correction fluid, or other substances that can be transferred to the glass.

3) Divide the above content in two (2) columns and it should look like as shown below.

4) Insert page number at the bottom of each page and the format of page number. It should look like ‘Page|1, Page|2, etc.

5) Save the document under C:\Candidates folder with your name followed by Word-04 (e.g. Arjun_Word-04).

Page break and header - footer

LEARNING OBJECTIVES

 Page break

 Header & Footer

Page Breaks and Section Breaks Using Page layout Tab

Word uses breaks to specify parts of a document that have different page orientation, columns, or headers and footers and also allow the user to specify where the different formatting will begin and end.

The following gives a brief description of the different types of break available in Word 2007: Page Breaks Page

A page break will force everything after the break onto a new page. In effect it marks the point at which one page ends and the next page begins. Column

A column break will force everything after the break into the next column Text Wrapping

Specifically for web pages and blog entries, a text wrapping break separates text around objects, such as caption text from body text. Section Break Next Page Section Break

A next page section break firstly marks a section break in the document, and then starts a new page, just like a Page Break.

Ways of breaking your text or a whole document.

Continuous Page Section Break

This is the same as the Next Page Section Break, except it does not start a new page. Even Page Section Break

An even page break is just like a Next Page Section Break, except that it will start a new section on the next even-numbered page. Odd Page Section Break

Similar to the Next Page Section Break and Even Page Section Break, except that it will start a new section on the next odd numbered page. Page Break

Create Page Breaks

1) Place your cursor where you want to insert a column break.

2) Go to Page Layout tabclick the Breaks button on the Page Setup group.

3) In the gallery that appears, click on Page option.

4) The second paragraph moves to the next page. Header and Footer

A header is text printed in the top margin of every page within a document. Footer text is printed in the bottom margin. Commonly used headers and footers contain your name, the document title, the filename, the print date, and page numbers. If you divide your document into sections, you can create different headers and footers for each section.

Create and Edit Headers and Footers

1) Click the Insert tabClick the Header button.

Cursor Position

2) Click a built-in header; click Edit Header to modify an existing one.

3) The Design tab under Header & Footer Tools displays on the Ribbon.

4) If necessary, click the Go to Footer button on to display the header or footer text area.

5) Click the footer box, and then type the text you want or insert page number. Edit and format footer text as usual.

6) To insert common items in a header or footer, click a button (Date & Time, Quick Parts, Picture, or Clip Art) in the Insert group.

7) When you’re done, click the Close Header and Footer button.

Finding and Replacing Text

LEARNING OBJECTIVES

 Find and replace

 Grammar checking

 Tab setting

The Find and Replace commands make it easy to look for text and make changes as necessary. For example, you might want to find each figure reference in a long report to verify that the proper graphic appears. When you use the Find command, Word highlights the word or phrases that it finds in the document. You can use the Reading Highlight button to specify whether to highlight the current find or all the items found in the document.

Find Text

1) Click at the beginning of the document.

2) Click the Home tabFindclick Find.

3) Type the text you want to find in the ‘Find what ‘ box.

4) To highlight all items found, click the Reading Highlight button, and then click Highlight All.

5) To specify where you want to find, click the Find in button, and then click Current Selection or Main Document.

6) Click Find Next until the text you want to locate is highlighted.

7) You can click Find Next repeatedly to locate each instance of the content.

8) To find all places with the contents you want, click Find All.

9) If a message box opens when you reach the end of the document, click OK.

10) Click Close or Cancel. Replace Text

1) Click at the beginning of the Office document.

2) Click the Home tab.

3) Click the Replace button, and then click Replace.

4) Type the text you want to search for in the ‘Find what’ box

5) Type the text you want to substitute in the ‘Replace with’ box.

6) Click Find Next to begin the search, and then select the next instance of the search text.

7) Click Replace to substitute the replacement text, or click Replace All to substitute text throughout the entire document.

8) You can click Find Next to locate the next instance of the content without making a replacement.

9) If a message box appears when you reach the end of the document, click OK.

10) Click Close or Cancel. Checking Spelling and Grammar

When you type a document, a red wavy line appears under words not listed in word’s dictionary (such as misspellings or names) or duplicated words. A green wavy underline appears under words or phrases with grammatical errors. You can correct these errors as they arise or after you finish the entire document. Before you print your final document, use the Spelling and Grammar Checker to ensure that your document is error free. Word does not automatically check grammar and spelling during the document creation process.

The document below shows spelling and grammatical mistake.

Correct Spelling and Grammar as you type

To rectify spelling and grammatical mistake, using dialog box:

1) Go to the Review tab Click Spelling and Grammar button.

2) The Spelling and Grammar dialog box opens; the spelling and Grammar dialog box also suggests a

corrected version.

Choose a correct spelling from the ‘Suggestion’ box and click change button to make a substitution. Click the

Ignore Once button to skip the word or rule, or click the Ignore All button or the Ignore Rule button to skip

every instance of the word or rule. If no suggestion is appropriate, click in the document and edit the text

yourself.

1) Click the OK button to return to the document.

Tabs Settings

Tabs can be used to create parallel columns, vertically aligning text within a document. Tab stops are markers.

Pressing the Tab key moves the cursor from one tab stop to the next. One of the most common uses of a tab is

to indent the first line of a paragraph. A new document includes tab stops at half-inch (0.5”) intervals. These

default tab settings do not display on the Ruler. The default tab stops are dark gray tick marks that appear on

the gray bar at the bottom of the ruler. Using the ruler can change the default or the Tabs dialog box. You need

to choose both the alignment type and the location for each tab stop you want to use.

There are five basic types of tab stops.

Left This is the default type; text appears to the right of the tab stop.

Center Text is centered under the tab stop.

Right Text appears to the left of the tab stop.

Decimal This type is used for numeric entries. Text lines up with the decimal point.

Bar This type is used to create a vertical line between columns of tabbed data.

1) Press Tab. Type the text for the first column.

2) Press Tab. Type the text for the second column.

Spelling mistake

Grammatical mistake

3) Press Tab. Type the text for the third column.

4) Press Enter to end that line and start a new line.

5) Repeat the steps from 3 to 5 for the next lines

6) To add leader, double-click on tab marker set on the horizontal ruler to display to display Tabs dialog box.

7) Choose tab stop position and then select a leader style. Click OK to apply leader. Footnote and Endnote

Footnotes are used to provide additional information that is inappropriate for the body of the text, and to document your reference for information or quotes presented in the body of the document. A Footnote is printed at the bottom of page where it is referenced. Creating and manipulating Endnotes is identical to perform the same functions for footnotes. An Endnote is also an explanatory note or reference but it is printed at the end of document. Footnote or Endnote Creation

To insert a footnote or endnote follow the steps:

1. Place the cursor at the right end of the text, for which you want to insert a reference text.

2. From the References tab  click the Insert Footnote button for a footnote or the Insert Endnote button for an endnote. Then type your footnote or endnote text.

3. To create a customized footnote or endnote, click the Footnotes Dialog Box Launcher.

4. Click the Footnotes or Endnotes option, click the list arrow next to the option, and then select the location where you want to place the footnote or endnote.

5. Then select nature of numbering system for reference marker.

6. Symbol button can be used to insert any customized symbol when select Customize mark for Numbering.

7. Click Insert to insert a reference mark in the text. Word moves the insertion point to the bottom of the page corresponding to the number of the reference mark.

8. Type the text of your footnote or endnote.

9. Click the Show Notes button and the Next Footnote button to show/hide notes and locate them.

10. Click in the document to continue with your work. Bookmark

Bookmarks are named locations in a document. In lengthy documents, sometimes we require to mark different locations as it can move us quickly to that location. Creation of bookmarks

To create a bookmark, position the insertion point at the location of the document where the bookmark is to appear,

1. On the Insert tab  click the Bookmark button to display the Bookmark dialog box.

2. Type any name for the bookmark in Bookmark name text box, and then click add button. Repeat these steps as many times as may be required in a document to insert bookmarks.

3. To switch between bookmarks, use Go To option. Go To dialog box, select Bookmark option from the list then select the name of bookmark from the drop down list. Click Go To button. Immediately it will take you to the location where bookmark has been set.

4. To remove an existing bookmark, open the Bookmark dialog box, select the name of the required bookmark from the given list and click on Delete button. Creating Hyperlink using Bookmark

Word will automatically format your text as a hyperlink, and the tooltip will appear when the user hovers over the link. The hyperlink points to the bookmark, which is where the hyperlink is. That is, it links to itself. So a user who clicks the link will, effectively, jump to nowhere.

1. To create a hyperlink bookmark, select the text or position where you want to insert a hyperlink.

2. Go to Insert tab Click the Hyperlink or press Ctrl + K. The following hyperlink dialog box will appear.

3. To create a link with a Bookmark,

click on the Bookmark drop-down

menu. Choose your bookmark from

the dropdown list. Click on OK.

4. After create a link when the visitor

clicks on the link, it goes to the

target.

Class Exercise

1) Open a new word document.

2) Type =RAND() and press enter to insert random text as shown below.

3) Keep each paragraph in separate page.

4) Place a header for all pages as “Annamalai University”; give alignment as Center and font size = 16.

5) Allow page number at the footer and give alignment as right.

6) Do the following tabular job in the first page of your document using suitable tab setting.

SL.NO BILL.NO. PARTY NAME AMOUNT

01 SAF/001 K.R.Biswanath ---------------------------------- 270,000.00

02 SAF/002 Raja Jhunjhunwala ----------------------------- 520,000.00

03 SAF/003 Manoj Singh ------------------------------------- 170,000.00

04 SAF/004 Suman Dey -------------------------------------- 150,000.00

05 SAF/005 Mimi Basak -------------------------------------- 270,000.00

7) Search the text “you” from your current document and replace with the text “We”

8) Run the spell checker for the entire document.

6) Save the document under C:\Candidates folder with your name followed by Word-05 (e.g. Arjun_Word-05).

Styles and formatting

LEARNING OBJECTIVES

 Style

 Clip art

 Word art Changing a Style Set

You can use style sets in Word to quickly format a document with a distinct style. Word provides predefined style sets —such as Classic, Elegant, Simple, Modern, Formal, Fancy, and Distinctive—to make it easy to format an entire document. Each style set consists of a variety of different formatting style combinations, which you can view using the Quick Style gallery.

Creating and Modifying Styles

Word provides a variety of styles to choose from. But sometimes you need to create a new style or modify an existing one to get the exact look you want. When you create a new style, specify if it applies to paragraphs or characters, and give the style a short, descriptive name that describes its purpose so you and others recall when to use that style.

Create a New Style:

1) Select the text whose formatting you want to save as a style.

2) Click the Home tab.

3) Click the scroll up or down arrow, or click the More list arrow in the Styles group, and then click Save Selection as a New Quick Style.

4) Type a short, descriptive name.

5) Click Modify button.

6) Click the Style type list arrow, and then click Paragraph to include the selected text’s line spacing and margins in the style, or click Character to include only formatting, such as font, size, and bold, in the style.

7) Click the Style for following paragraph list arrow, and then click the name of style you want to be applied after a paragraph with the new style.

8) Select the formatting options you want.

9) To add the style to the Quick style gallery, select the Add to Quick Style list check box.

10) Click OK.

11) Click OK.

Locating and Inserting Clip Art

To add a clip art image to a document, you can click the Insert Clip Art button on the Insert tab to open the Clip Art task pane. The Clip Art task pane helps you search for clip art and access the clip art available in the Clip Organizer and on Office Online, a clip gallery that Microsoft maintains on its Web site.

Locate and Insert Clip Art

1) Click the Insert tab.

2) Click the Clip Art button.

3) Type the keyword(s) associated with the clip you are looking for.

4) To narrow your search, do one of the following:

 To limit search results to a specific collection of clip art, click the Search In list arrow, and then select the collections you want to search.

 To limit search results to a specific type of media file, click the Results Should Be list arrow, and then select the check box next to the types of clips you want to find.

 To access clip art on Office Online, click the link at the bottom of the Clip Art task pane. Search and download images from the Office Online Web site.

5) Click Go.

6) Clips matching the keywords appear in the Results list.

7) Click the clip you want, and then resize it, if necessary.

8) Click the Close button on the task pane.

Inserting a Picture

Word makes it possible for you to insert pictures, graphics, scanned photographs, art, photos, or artwork from a CD ROM or other program into a document. Insert a Picture from a File

1) Click the Insert tab.

2) Click the Picture button.

3) Click the Look in list arrow, and then select the drive and folder that contain the file you want to insert.

4) Click the file you want to insert.

5) Click Insert button.

Adding a Quick Style to a Picture

1) Click the picture you want to change.

2) Click the Format tab under Picture Tools.

3) Click the scroll up or down arrow, or click the More list arrow in the

4) Picture Styles group to see additional styles.

5) The current style appears highlighted in the gallery.

6) Point to a style.

7) A live preview of the style appears in the current shape.

8) Click the style you want from the gallery to apply it to the selected picture.

Did You Know ? You can quickly return a picture back to its original form. Select the picture, click the Format tab, and then click the Reset Picture button.

Creating WordArt Text

The WordArt feature lets you create stylized text to draw attention to your most important words. Insert WordArt Text

1) Click the Insert tab.

2) Click the WordArt button, and then click one of the WordArt styles.

3) A WordArt text box appears on the document with selected placeholder text.

4) Type the text you want WordArt to use.

5) Use the Font options to modify the text you entered.

6) Finally Click OK button.

Did You Know? You can add more formatting to WordArt text. Select the WordArt object, click the Home tab, and then use the formatting button in the Font and Paragraph groups. You can change the WordArt fill color to match the background. Click the WordArt object, right-click the object, click Format Shape, click the Background option, and then click Close.

Class Exercise

1) Create a new word document with your name followed by Word-06 (e.g. Arjun_Word-06) under C:\Candidates folder. Enter the following text and clip art in the document. Format the clip art as required.

2) Create two styles with the following specification:

Style Name : Your Name followed by Style1 (e.g. Arjun_Style1)

Style Type : Paragraph

Font : Bernard MT Condensed

Font Size : 20

Font Colour : Red

Alignment : Center

Style Name : Your Name followed by Style2 (e.g. Arjun_Style2)

Style Type : Paragraph

Font : Cambria

Font Size : 11

Font Colour : Purple

Alignment : Justify

Indentation : First Line indent

3) Apply Style1 on the heading and rest of the document will be formatted with Style2.

4) Add your full name below the paragraph using WordArt style.

5) Insert Smiley Face auto shapes beside the header text “Inspecting a Document”.

6) Save and close your document.

Table and its features

LEARNING OBJECTIVES

 Table

 Simple formula using in table

A table is an object that is inserted into the Word document that displays text in rows and columns. You can set up your table with existing text, or create the table, even draw it out, and enter in new text.

Create a Table from Existing Text

1) Select the text for the table.

2) Click the Insert tab.

3) Click the Table button, and then click Convert Text to Table.

4) Enter the number of columns.

5) Select an AutoFit column width option.

6) Click a symbol to separate text into cells.

7) Click OK.

Did You Know? You can convert a table back to text. Select the table, click the Layout tab under Table Tools, click the Convert to Text button, select the Separate text with option (typically Tabs), and then click OK.

Create a New Table Quickly

1) Click to place the insertion point

2) Where you want to insert a table.

3) Click the Insert tab.

4) Click the Table button, and then drag to select the number of rows and columns you want, or click Insert Table, enter the number of columns and rows you want, and then click OK.

5) Release the mouse button to insert a blank grid in the document.

6) When you’re done, click outside of the table.

Draw a Custom Table

1) Click the Insert tab.

2) Click the Table button, and then click Draw Table.

1) Draw the table.

a) A rectangle creates individual cells or the table boundaries.

b) Horizontal lines create rows.

c) Vertical lines create columns.

d) Diagonal lines split cells.

2) If necessary, press and hold Shift, and then click one or more lines to erase them.

3) When you’re done, click outside of the table. Sorting Table Contents or Lists

After you enter contents in a table, you can reorganize the information by sorting the information. For example, you might want to sort information in a book list alphabetically by author name or numerically by their price amount. Ascending order lists information from A to Z, earliest to latest or lowest to highest. Descending order lists information from Z to A, latest to earliest, or highest to lowest. Sort Table Contents or Lists

1) Select the table column, adjacent columns, or list you want to sort.

2) Click the Home tab or the Layout tab under Table Tools.

3) Click the Sort button.

4) If necessary, click the Sort by list arrow, and then select a column name.

5) Click the Type list arrow, and then click table cell content type.

6) Click the Ascending or Descending option.

7) If necessary, click the second Sort by list arrow, select another column name, and then select the related sorting options you want.

8) Click the Header row or No header row option as it applies to the table.

9) Click OK.

Insert Additional Rows or Columns

1) Select the row above which you want the new rows to appear or select the column to the left of which you want the new columns to appear.

2) Drag to select the number of rows or columns you want to insert.

3) Click the Layout tab under Table Tools.

4) Click the Row & Column buttons you want:

a. Insert Above.

b. Insert Below.

c. Insert Left.

d. Insert Right.

Did You Know? You can show or hide gridlines in a table. Select the table you want to change, click the Layout tab under Table Tools, and then click View Gridlines to toggle it on and off. You can resize a table. Drag a corner or middle resize handle to resize the table manually.

Delete Table, Rows, Columns, or Cells

1) Select the rows, columns, or cells you want to delete.

2) Click the Layout tab under Table Tools.

Descending wise sorting

3) Click the Delete button, and then click the delete option you want:

 Delete Cells. Select the direction in which you want the remaining cells to shift to fill the space, and then click OK.

 Delete Columns.

 Delete Rows.

 Delete Table.

Did You Know? You can set column widths to fit text. Word can set the column widths to fit the cells’ contents or to fill the space between the document’s margins. Click in the table, click the Layout tab under Table Tools, click the AutoFit button in the Cell Size group, and then click AutoFit to Contents or AutoFit to Window. You can evenly distribute columns and rows. Select the columns or rows you want to distribute evenly in a table, click the Layout tab under Table Tools, and then click the Distribute Vertically or Distribute Horizontally button in the Cells Size group.

Applying simple formula in a table cell

1) Click the cell in which you want the sum to appear.

2) Click the Layout tab under Table Tools.

3) Click the Formula button. If Word proposes a formula that you do not want to use, delete it from the Formula box.

4) Type the formula =PRODUCT(LEFT) in formula box.

5) Click OK button to get the output of the formula

6) Another way of feeding formula in cell is by pressing CTRL+F9 and type {=C3*D3}

7) Press F9 key to get the result of the formula. A

1 C D E

2

3

4

5 B

8) To find out total amount, keep your cursor in cell E5 and press CTRL+F9 and type {=SUM(Above)}

9) Press F9 key to get the sum of an amount in the respective cell.

Nesting Tables

A nested table is a table inside a table cell. This technique is primarily used in building Web pages. It can give you more control over the appearance of your table, by allowing you to more precisely control where information in your table appears, the table is inserted inside one of the table cells used in the structure. You can create a nested table by inserting a table in a table cell. You can also create a nested table by copying an existing table and then pasting it into a table cell.

Class Exercise

1) Open a new word document.

2) Prepare the following expenses table in your document.

SL.NO.

Particulars

Jan

Feb

Mar

Total

1

DVD-Production

75000

89000

100000

2

Packing

15000

18000

20000

3

Handling

5000

7000

8500

4

Shipping

12000

16000

18000

Total Expenses / Month -------

3) Calculate the Total expenses of each head.

4) Sort the table in ascending order of total.

5) Re-arrange the SL.No.

6) Calculate the total expenses month-wise.

7) Add a row before the heading row. Merge all the cells and keep a heading “Quarterly Expenses Details”.

8) Format the field heading with the Font Face: Cambria; Font size: 14; Font colour: Blue; Alignment: Center.

9) Select the entire table and apply Medium Grid 3 – Accent 4 format to the table using table styles.

10) Go to the next page and prepare the following using tab separator.

Assets Cost(Rs)

Car 600000

Bike 65000

Plant-A 2000000

Plant-B 3500000

11) Now convert the above text into columns.

12) Prepare the following Invoice format in a new page.

INVOICE / CHALLAN

K.R. BISWANATH

45, Karl Max Sarani, 1st Floor, Room No.-105

Kolkata-700023

Bill Number : SAF/201/09-10 Date: 21/05/2010

Delivery No: CHL/301 Date: 18/05/2010

SL

PARTICULAR

QUANTITY

RATE

AMOUNT

1

Battery BL-6C

10

1800

2

Nokia N-73M

12

11500

GROSS AMOUNT

VAT @ 12.5%

NET AMOUNT

13) Computer the amount for each product, gross amount, vat and net total amount at appropriate cell.

14) Save the document under C:\Candidates folder with your name followed by Word-07 (e.g. Arjun_Word-07).

Mail Merge

LEARNING OBJECTIVES

 Mail merge

 Features of mail merge

Did you ever send the same letter to several people and spend a lot of time changing personal information, such as names and addresses? If so, form letters will save you time. Mail merge is the process of combining names and addresses stored in a data file with a main document (usually a form letter) to produce customized documents. There are four main steps to merging.

First, select the document you want to use. Second, create a data file with the variable information. Third, create the main document with the boilerplate (unchanging information) and merge fields. Finally, merge the main document with the data source to create a new document with all the merged information. When you start the mail merge, you need to open the letter that you want to mail merge or type one. Don’t worry about addressing the letter or adding a greeting line, you can accomplish that with the Mail Merge Wizard.

Start the Mail Merge Wizard

1) Type body letter and then click Mailing tab.

2) Click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.

3) The Mail Merge task pane opens, displaying Step 1 of 6.

4) Select the type of document you are working on (in this case the Letters option).

5) Click Next: Starting document on the task pane to display Step 2 of 6.

6) Click a starting document option (such as Use the current document).

7) Click Next: Select recipients on the task pane to display Step 3 of 6.

8) On Step 3 of 6 in the Mail Merge task pane, click the Type a new list option. Click Create.

9) The ‘New Address List’ display.

10) Delete the unnecessary field name and Add required filed name using Delete and Add button respectively. Then click OK button.

11) Type your client address in the ‘New Address List’ box.

12) Click New Entry after each one until all records have been entered and then Click OK button.

13) The Save Address List dialog box opens, displaying the My Data Sources folder.

14) Enter a name, and then click Save button to save your address list.

15) The Mail Merge Recipients dialog box opens, displaying the data source for the merge.

16) Edit the recipient data (if necessary), and then click OK.

17) Click Next: write your letter on the task pane to display Step 4 of 6.

18) On Step 4 of 6 in the Mail Merge task pane, position the insertion point in the letter where you want the address block to appear.

19) Click Address Block on the task pane.

20) Click OK to insert the block in the document.

21) Position the insertion point where you want the greeting to appear.

22) Click OK to apply the style to the merge field.

23) If you want to add other merge fields in the body of the form letter, position the insertion point where you want the information, and then click More Items on the task pane.

24) Select the merge field you want to place, click Insert, and then click Close.

25) When you’re done, click Next: Preview your letters on the task pane to display Step 5 of 6.

26) On Step 5 of 6 in the Mail Merge task pane, click the double arrows on the task pane to scroll through the merge letters one at a time.

Insertion Point

27) Click Next: Complete the merge on the task pane to display

Step 6 of 6.

28) If you want to make additional changes to the letters, click

Edit Individual Letters.

29) Specify the settings you want to use for the merged records

and the selected range of the records are saved to a

separate file for editing.

30) Click OK.

31) When you’re ready to print, click Print on the task pane.

32) Click the All option to print the entire merge or click

another option to print only a selected portion of the

merge.

33) Click OK to open Print dialog box.

34) Finally click OK button to print the merged letters.

Print a Document

When you’re ready to print your document, you can choose several printing options. You can print all or part of any document and control the appearance of many features, such as whether to print document properties, document markup, styles, or building blocks entries. You can quickly print a copy of your document to review it by clicking the Quick Print button on the Quick Access Toolbar or on the Office menu Print submenu. Print All or Part of a Document

1) Click the Office button, point to Print, and then click Print.

TIMESAVER :To print without the Print dialog box, press Ctrl+P.

2) If necessary, click the Name list

arrow, and then click the printer you

want to use.

3) To change printer properties, click

Properties, select the options you

want, and then click OK.

4) Select whether you want to print

the entire document or only the

pages you specify.

5) Select what you want to print:

Document, Document properties,

Document showing markup, List of

markup, Styles, Building Block

entries, or Key assignment.

6) Click the Number of copies up or

down arrow to specify the number

of copies you want.

7) Specify the number of pages per sheet and the scale to paper size you want. Click OK button.

Class Exercise

1) Open a new word document based on your template for mail merge.

2) Create a database for your employers with the following information.

(i) Mr. Sujoy Kumar (ii) Mr. Dilip Sharaf

S.Kumar & Company D.S. Infotech Ltd.

21, N.S.Road 30, J.L.Nehru Road

Kolkata-700001. Kolkata-700016

(iii) Mr. Ashraf Ali (iv) Miss.Geeta Sharma

Ali Associates Sharms & Sons

18, Paharpur Chowk 27, N.C.R. Road

Delhi-100045 Delhi-100078

3) Apply for the post of an Accountant to the above employer on 14th June, 2010.

4) Merge all the records.

5) Save all the files with any name of your choice under C:\Candidates\YourName folder.

6) Close all the word documents.