Introduction to MS Excel!

MS EXCEL! 

Excel is a spreadsheet program, which uses ‘Grid’ for holding and manipulating data. Excel is a kind of general-purpose software that can be used for the following operations:

1. To maintain accounts and to prepare statements like Profit and Loss, Balance sheet etc.
2. To tabulate the numerical data like scores obtained by two countries in a cricket match and to analyze the performance by drawing graphs and charts.
3. To store a database in a group of cells arranged in a matrix form and to manipulate it.

The files that are created and maintained using Excel are called Workbooks. Each and every workbook contains one or more worksheets. Excel is used for storing the text, numbers, and date type data in a group of cells called worksheet.

Features provided by Excel for storing and manipulating the data are:

1. Cell Address List box for selection as well as display of the address of the active cell.
2. Formula Bar. This is a text box provided for the entry and editing of data in the active cell. Whatever we enter here will be updated in the cell.

Two things can be entered into a cell. They are:

1. Actual data like text, numbers or date.
2. Formulas

A formula is an expression that can be formed using the cell Address, constants and operators to calculate some value and to display it in a cell. To edit or enter the formula in a cell, the formula bar can be used or double click the cell to edit the formula in the cell itself.

The cell containing the formula will calculate and display the value based on the values of the cells that are referenced in the formula of the cell. Whenever there is a change in the referenced cell, the value in of the cell which refer those cell will change or update automatically.

Entered data can be displayed in various formats using the formatting options available in Excel. Reports can be made available to the end user in a table containing text and calculated values. More readable output can be produced using graphical charts.

Besides entering data in a grid, other skills that are required for working with Excel worksheet are: moving around the worksheet in a workbook, manipulating a range of cells in a worksheet and some more.

Entering data in a Worksheet

Data are entered in ‘Grid’ made up of cells, available in the worksheet. To do data entry in a grid, select a cell in the Grid and enter the data.

Cell Selection

Select a cell by clicking the cell with the left mouse button. Single click will do the selection. Double click will select as well as place the cursor inside the cell. Another way of doing the cell selection is by entering the cell reference in the name box, which is located just left to the formula bar.

A Cell Reference is a name given to refer a single cell or a range of cells. Single cell reference is formed, by combining the Column heading and the Row heading. For e.g., the cell reference B5 refers to the cell located at the intersection of second column (B) and fifth row (5). There may be as many as 256 columns and 65,536 rows in a single worksheet. So the last cell in the worksheet is referenced as IV65536.

Keyboard keys like Arrow keys, Tab key and Enter key are used for cell navigation. Arrow keys will move the cursor to the adjacent cell in the appropriate direction. Tab and Enter key will move one cell right or one cell down respectively. Holding the ‘Ctrl’ key when pressing any one of the arrow keys will select the last cell in that direction. For e.g., ‘Ctrl + -->’ selects the last cell in the selected column.

Data that can be entered into a cell are of three types: Text, Numbers & Date. Among these three, the last two are involved in calculations.

Entering Text and Number

Text and numbers are the two kinds of data often entered in a cell. Text data are left aligned, whereas numbers are right aligned. Text data are normally lengthier than numbers. To accommodate a lengthy text in a cell for display, the text may spread across many cells or wrapped in its own cell.

To fit the lengthy text in a cell, resize the cell by increasing the size of the column in which the cell exists. To automatically size column to fit the largest entry, place the cursor on the right border of the corresponding column heading. Pointer becomes a cross with arrows facing left and right. Then double click on the heading.

Entering Date

A Date refers to either a data of completion or a due date, when it is used as data. For instance, a date for referring ‘a Date of Completion’ may tell when a payment was made or when an item was received.

Date must be entered in the standard date format, which is ‘MM/DD/YY’. Later, it can be changed into any other date format using the ‘Format --> Cells’ menu command. Press ‘Ctrl + ;’ for inserting the current date into a cell.

Dates are often used in calculations to determine the duration between two dates, for e.g., finding out the number of days to charge for an overdue library book or calculating how many months of interest earned on a deposit.

Continue reading →

Creating Letters and Envelopes in MS-Word!

USING MAIL MERGE IN MS-WORD! 

Mail merge is a tool available under “Tools” menu in Word; we can create the documents like Form letters, Mailing Labels and Batches of pre-addressed envelopes.

Form Letters: A form letter is a document that contains paragraphs full of text. Examples for form letters are personalized reports, letters, memos etc.

Mailing Labels: This is a document contains a list of Address labels. Each label may have a Name and an Address for mailing. It can also be used to create Price labels for the products we sell.

Envelopes: Envelopes are documents containing mailing addresses similar to that of the Mailing Labels. But, it prints the labels one at a time on an envelope, instead of printing them as a list of labels in a sheet.

Mail Merging

Mail merge is a process of combining two files: a data file containing a list of names and addresses and a main document that contains the static text with markers to show where each bit of data should go.

For e.g., there might be a text in the form letter like this: “Hello, <<First Name>> <<Last Name>>! How are you?” After merging this with the data in the data file that contains data “Sachin” as <First Name> and “Tendular” as <Last Name>, the merged document will be like this: “Hello, Sachin Tendulkar! How are you?”

Similarly, for each and every row of data in the data file, there will be a personalized document in the merged output. If there are ‘n’ rows of mailing addresses in the data file, then the output will have ‘n’ times the same document with different names and addresses.

Steps to follow for Mail Merging to produce a form letter are:

1. Create and select the main document file
2. Create the data file and fill the data in it
3. Select the data file and merge the two together and
4. Save the merged output in a separate file or print it on a paper directly.

Main Document Creation

Main document is a document file containing the static text that must be replicated in every form document with some field markers that specifies the location for dynamic data to be inserted to produce the personalized form letters. It contains the message that has to be copied in all the personalized documents with some personalization.

For creating the main document, open a blank document. Use the “Mail Merge” tool from the current document, which will open up the ‘Mail Merge Helper’ dialogue box. Under the ‘Main Document’ section, a button named ‘Create’ is provided for creating the main document.

Click the ‘Create’ button to have a list of options that include Form Letters, Mailing Labels, and Envelopes etc. Choose the required document type for e.g., Form Letter for creating personalized form letters. A dialogue box will appear to set the current document as the Main document or to open a new Main document.

Once a document is set as a main document, the Mail Merge toolbar appears automatically on the screen. The toolbar contains buttons for some of the most common mail merge commands. The toolbar also contains a drop-down list for inserting the fields of the data file in the main document.

Data file creation

A data file is a data source, which contains ‘n’ rows of data called ‘records’. Each row has ‘m’ columns called ‘attributes’. For e.g., a data file can have ‘n’ addresses as its records and each address having attributes like -Address, City, State, Pin code in the table. Data may be kept in various data sources like Address Book in Outlook, Worksheet in Excel, Database in Access or Table in Word. The simplest data source is a Word document file that contains a table of data.

Procedure for creating a Data File

Create a table in a new Word document and fill it with data. Save it as a separate file to be used as a data source. Don’t use “Mail Merge” tool from a data source to set it as a Main Document. Otherwise, it will be considered as an invalid data source.

(or)

Use the options given in the Mail Merge tool itself after creating the main document. In the ‘Mail Merge Helper’ dialogue box, click the ‘Get Data’ button under the ‘Data Source’ section, which will drop down a list box. Select ‘Create Data Source’ option for defining the structure of the data file and to enter data into it. The ‘Create Data Source’ wizard helps the user in defining the fields for the data file, entering data into it and to save it as a document file.

When the data source is created explicitly not using the “Create Data Source” option, open the data source using the “Open Data Source” provided under the “Get Data” button. Once the data source is set, a list of data fields will be available in the Insert Merge Field list box available in the Mail Merge toolbar. Then insert the data marker by selecting the data field from this list in the right spots.

Merging the document and the data

Merge the two files (document and data) by clicking the ‘Merge’ button available in the Merge section of the Mail Merge helper dialogue box. The merged output can be directed to a new document file or to the printer. Other options that can be set using Mail Merge are:

• Selecting the range (From… To…) of records to merge
• Specifying whether or not to print blank lines when a certain record field is empty.

Experiment

1. Create a Data File containing data as given below :

   Title	First Name	Last Name	Address	City	State
   Mr.	Jeffrey	Levine	2902 Karen Rd.	Seaford	NY
   Ms.	Liz	Mohoney	7865 Stuart Dr.	Leavittown	NY

2. Create the Form Document like this:

MEMORANDUM

Date : Today’s Date

To : <<Title>> <<First Name>> <<Last Name>>

<<Address>>

<<City>> <<State>>

Re : Creative Advances in Computer Design Conference

Dear <<Title>> <<Last Name>>

I am writing to invite you to participate in the local CREATIVE ADVANCES IN COMPUTER DESIGN CONFERENCE. The conference will be in <<City>> <<State>> next month.

<<Title>> <<Last Name>>, if you would be willing to share your innovative ideas with others, we would be honored to have you chair a session on Web page design. We hope you will join us in <<City>> next month. Please contact my office if you have any questions or if you need more information.

Sincerely,

Karin Paulo

Events Planner

3. Mail Merge both the Form Document and the data file.
4. Generate Envelopes from the same data source.

Continue reading →

Arranging Data in Tables and Columns in Word

  TABLES AND COLUMNS IN MS-WORD! 

A Table is a structure used for arranging the data in a rectangular area, in rows and columns. A table is partitioned into ‘n’ small rectangular areas, called cells. We use tables for presenting information in side-by-side columns. For e.g., a Price List, Invoice or a Resume. The basic operations that must be known when working with tables are:

1. Creating a Table and entering data into it
2. Cell selection and manipulation and
3. Formatting the entire Table structure

Table Creation

A table consists of ‘n’ rows and ‘m’ columns. The area of intersection of a column and a row is called a Cell. Lines drawn in between cells distinguish one cell from another.

The lines drawn inside and around the table for making its structure are called Gridlines. They are invisible naturally. When solid lines are used as gridlines they give a visible appearance to the structure of the table.

In a table, column positions are marked by column markers, which separate one column from another. Indents are displayed for each and every column so that margins can be set for them individually.

Procedure for creating a table

Use ‘Table --> Insert --> Table’ menu command to insert a new table at a specified location. In the ‘Insert Table’ dialogue box, mention the number of rows and columns of the new table, and press OK to create it.

(or)

Use ‘Tables and Borders’ toolbar, which is available under “View --> Toolbars” menu command to draw or insert a new table. Using the drawing tools given in the toolbar, we can draw a table. Also available in the tool bar is “Insert Table” button, which will popup the “Insert Table Dialogue box” for drawing a new table.

Cell Selection and Manipulation

After creating a table, data can be entered into the cells in it. Navigation between cells can be done using the Tab key or Arrow keys. Row height increases automatically to accommodate as much as data as we enter. Pressing enter in a cell, starts a new paragraph inside it.

Cell selection is done by placing the cursor inside the cell or by selecting the text in it. ‘Table --> Select’ menu command is also there for cell selection. Group of cells can be selected row wise or column wise. Selected cells can be manipulated as given below:

• Merging / Splitting cell(s) in a row(s)/Column(s)
• Inserting / Deleting row(s)/Column(s) and
• Aligning cells as per the requirement

Merging a group of cells means combining two or more cells to become one, whereas Splitting a cell means partitioning a single cell into many. Inserting/Deleting a row(s) or a Column(s) will insert/delete an entire row(s)/column(s) in the table. “Table” menu commands as well as short cut menu commands are available for inserting, deleting and merging the cells in a table.

Cell alignment means aligning the text in a cell and it can be done using the “Alignment” tools available in the formatting toolbar or using the alignment tabs. The decimal tab is used for aligning decimal numbers in a cell. Apart from normal alignment, cells can also be aligned vertically. A cell can be aligned vertically to align the text at the top, center or bottom of the cell. Use ‘Table --> Table Properties --> Cell Tab’ for selecting the desired vertical alignment for aligning the text vertically.

Formatting the Table Structure

Formatting the structure of the table includes: changing the border of the table, aligning the table to the left, right or center of the page, and changing the background color of the cells etc. Table formatting can be done by setting the various properties of the table in the ‘Table Properties’ dialogue box (or) by choosing any one of the standard formats given in Word.

For formatting a table structure, move the cursor over the table to have a handle at the left top corner of it. Select the table by clicking on the handle and use one of the following methods: Select ‘Table --> Table Properties’ for setting the various properties of the table to format its structure. Or select ‘Table --> Table Auto Format’ to quickly apply any one of the standard formats, which are already predefined in Word application.

Column Setup

Column Settings are used for partitioning the entire page into ‘n’ number of equal portions horizontally. The entered text fills the page, column wise. First column is filled first, then the second column, then the third and so on. Each and every column can be manipulated separately.

Select ‘Format --> Columns’ menu command. The Column settings dialogue box will appear, in which you can set the number of columns, space between columns and the lines to be drawn in between columns etc.

Continue reading →

Creating, Editing & Previewing a Document

DOCUMENT CREATION IN MS-WORD!

Word is a program used for doing word processing. Using Word, the documents like company newsletters, book report can be created easily. Word has got options for dealing with both text and graphics to create a Professional - looking documents.

Many features are available for formatting a document, which include setting the margins, selecting typefaces and setting spaces between lines and paragraphs etc. Various tools are available in Word like Spell Checker, Mail Merge, Word Art for manipulating the text effectively.

Steps in Creating a Document in MS-Word

Word documents are stored in a file with the extension ‘.DOC.’ Other types of files that can be created using Word are: Text file (.TXT), Rich Text Format (.RTF) files, and Internet files (.HTML).

To create a new document in Word, first thing we need to do is to have a blank document. When we start a ‘Word’ application, it automatically opens a new blank document. It can also be opened explicitly, using ‘File --> New” menu command or using the very first tool available in the ‘Standard’ toolbar.

After entering the text into a new document file, the plain text must be edited to produce an error-free document. Editing is an important task in a document creation, which involves:

• Adding or removing some text
• Modifying a portion of text
• Moving or copying a part of text

Text Insertion

For inserting some text in a line/paragraph, the insert button must be ‘ON’. The status of the insert button is shown on the status bar by the word ‘OVR’. When the indicator ‘OVR’ is disabled, that means the ‘INSERT’ button is ON and whatever we type at a particular position will be inserted.

When ‘OVR’ is enabled, it indicates that the ‘OVERWRITE’ mode is ‘ON’. In ‘OVERWRITE’ mode, whatever we type will overwrite the text at the right of the cursor. Pressing the ‘Insert’ button will set the ‘INSERT’ mode on/off. Mouse double click on the ‘OVR’ indicator will also set the mode.

Text Deletion

To delete one character at a time, use ‘Del’ or ‘Backspace’ key in the keyboard. ‘Del’ key deletes a letter at right, whereas ‘Backspace’ key deletes one character at left of the cursor. Press ‘Ctrl’ key along with the ‘Del’ or ‘Backspace’ key to delete one word to the right or left of the cursor.

For deleting a block of text, select the text using ‘Shift + Arrow keys’ in the keyboard or using the mouse click and drag feature. Press ‘Del’ key to delete the selected block of text.

Replacing an existing Text

To replace an entire word or a sentence, it is easiest to select the word or the sentence first, and then type. This new text will replace the selected text. This eliminates the need to delete the old text before typing the new text.

Editing tool Find and Replace is also available for finding a text and to replace it with another text in one or more places in the entire document. Enter both texts in the text boxes given in the Find and Replace dialogue box and press ‘Ok’ to do the replacing of the existing text.

Copying and Moving the Text

Editing tools ‘Cut’, ‘Copy’ and ‘Paste’ are used for copying and moving the text around the document. ‘Copy’ and ‘Paste’ will make ‘n’ copies of the same text. ‘Cut’ and ‘Paste’ will move the text from one place to another. Mouse ‘Drag & Drop’ feature is a shortcut for moving the text.

Previewing a Document

Previewing is nothing but seeing the output on the screen as exactly as it is printed on the paper. Previewing can be done by, clicking the preview tool available on the Standard tool bar or using the ‘File --> Print Preview’ menu command.

SETTING MARGINS and TABS

A Margin is an amount of space between the text and the edge of the page on all 4 sides. They are in inches. Default margin is 1 inch at the top and bottom, 1.25 inches at the left and right sides.

Procedure for Page Setup

Use the “File --> Page Setup” menu command to set the margins for the entire page. The other way of doing this is by dragging and drop as discussed below.

While in the Print Layout View, position the cursor over the left margin or right margin on the ruler. The shape of the cursor will change to Double Edged arrow. Then, click and drag using the mouse to change the page margin.

Tab Setup

Tabs are used for setting the margins temporarily for a paragraph or to align the text in a line. There are two kinds of TABS: Indent tabs and Alignment tabs. Indent tabs are used for setting the margins of a paragraph, while Alignment tabs are used for text alignment.

Indent Tabs

An Indent is nothing but a temporary left or right margin for lines or paragraphs. They are used to call attention to a paragraph, achieve a particular visual effect or to leave white spaces along the margins for notes or illustrations. There are five types of indents. They are: Left Indent, Right Indent, Double Indent, First Line Indent, and Hanging Indent.

1. Left Indent: is used to set left margin for a paragraph
2. Right Indent: is used to set right margin for a paragraph.
3. Double Indent: combination of both left and right margins
4. First Line Indent: is used to set the left margin for the first line of a paragraph.
5. Hanging Indent: is used to set left margins for all lines, except the first line of the paragraph. Hanging indent is nothing but Left Indent when it is set next (right) to the first line indent.

Procedure for setting Indent Tabs

1. Select the paragraph. The indent tabs are displayed on the ruler for the selected paragraph. Set the position of the Indent tabs using Mouse or Keyboard.
2. To set the tab using the mouse, select the tab using the left mouse button, hold it and move it across the ruler to the new tab position.
3. To set the tab position, using keyboard, use the menu command ‘Format --> Tabs’ and type the position in inches for the required indent tabs. Press ‘Set’ and ‘Ok’. An alternate method is to use the ‘Tab Key’ in the keyboard for setting the first line and the left indent. Pressing the Tab key once will set the first line indent, pressing it multiple times will change the First Line indent as well as the Left indent.

Alignment Tabs

Alignment refers to the position of the text at left, right or center. For text alignment, Word application needs a reference point based on which it will align the text left, right or center. For left alignment, the reference point is the left margin or the starting position of the text. For right alignment, the reference point is the right margin. For center alignment, the reference point is center of the document.

When we use tabs for text alignment, the text is aligned based on the position of the alignment tab, set on the ruler. The text will be aligned according to the type of tab used at the corresponding position on the ruler. There are 4 types of alignment tabs: Left tab, right tab, Center tab and Decimal tab. The 1^st 3 tabs align the text to the left, right and center respectively, whereas the Decimal Tab aligns the decimal point of a decimal number at the position set by it on the ruler.

Procedure for using Alignment Tabs

To use the alignment tabs for text alignment, first you have to set the tabs in their position and then use the TAB button for aligning the text at the tab positions set already.

Setting the Tab using Mouse:

1. Place the cursor in the line for which you want to set the tab.
2. Select the tab from the top left corner of the ruler by clicking on it to get the one of your choice.
3. Set the tab in its position by clicking at the appropriate position on the ruler. Any number of alignment tabs can be set for a single line.

Setting the Tab using Keyboard:

Use the Menu option ‘Format --> Tabs’ which would pop up the Tab setup dialogue box. In the Tabs Dialogue box, select the type of the alignment tabs their position in inches. Press the Set Button for setting multiple tabs. Press Ok to have the alignment tabs on the ruler for the current line/paragraph.

Continue reading →