WHAT IS OFFICE APPLICATIONS
This section will introduce the world of office applications that are used widely by all of us to carry on our day to day tasks like writing a project document, preparing our monthly budget, corresponding with each other on email or scheduling our daily calendar.
Word Processing
A word processor (more formally known as document preparation system) is a computer application used for composition, editing, formatting and printing of any sort of printable material.
Adding Header, Footer or Page Number to a Document
To add a graphic or text on the top or bottom of a document, a Header or Footer has to be added.
1. Click on Insert Tab, select the Header or Footer or Page Number from Header & Footer group.
2. You can select from the available gallery and choose the design required.
3. After selection, the same design chosen will appear in the document.
To add a customized design for the Header or Footer, double click on the Header or Footer area in the document. The same is opened in the design mode. After making the required changes to the design, close the Header/Footer area.
Creating a Table in Word
In Word, a Table can be inserted by either by choosing from a gallery of preformatted table templates or by using the table menu to choose the number of rows and columns that are required.
To Choose from gallery of table templates:
1. Click in the document where the table has to be inserted.
2. On the Insert tab, in the Tables group click Table, and then choose Quick table.
3. Choose the required template from the gallery displayed.
4. The chosen table will be inserted into the document.
To insert a simple table with required number of row and columns:
1. Click in the document where the table has to be inserted.
2. On the Insert tab, in the Tables group click Table, and then under Insert Table, select the number of rows and columns required in the table.
3. The table will be drawn on the document.
Creating a Table of Contents
Table of Contents (TOC) is created by applying heading styles to the text that is to be included in the TOC. Word will search for those headings and create the TOC in the document. The Table of Contents can be selected from design gallery provided in word.
1. Select the text that is to be appear in the TOC
2. On the Home tab, in the Styles group, click the Style (Heading1,
| |
Heading2….) that you want to choose
|
3. Click the place in the document where the TOC has to be inserted.
4. ON the Reference Tab, click Table of Contents on the Table of Contents group
5. Choose the TOC Style that is required
In order to create a customized Table of Contents, ON the Reference Tab, click Table of Contents on the Table of Contents group and choose the Insert Table of Contents option.
Here it will be possible to change the heading levels, overall look of TOC etc.
Creating a memo for multiple recipients – Mail Merge
Mail merge is a software function describing the production of multiple (and potentially large numbers of) documents from a single template form and a structured data source. This helps to create personalized letters and pre-addressed envelopes or mailing labels for mass mailings from a word processing document which contains fixed text, which will be the same in each output document, and variables, which act as placeholders that are replaced by text from the data source. The data source is typically a spreadsheet or a database which has a field or column matching each variable in the template. When the mail merge is run, the word processing system creates an output document for each row in the database, using the fixed text exactly as it appears in the template, but substituting the data variables in the template with the values from the matching columns.
You use mail merge when you want to create a set of documents, such as a form letter that is to be sent many customers or a sheet of address labels. Each letter or label has the same kind of information, yet the content is unique. For example, in letters to your customers, each letter can be personalized to address each customer by name. The unique information in each letter or label comes from entries in a data source.
The mail merge process entails the following overall steps:
1. Set up the main document. The main document contains the text and graphics that are the same for each version of the merged document. For example, the return address or salutation in a form letter.
2. Connect the document to a data source. A data source is a file that contains the information to be merged into a document. For example, the names and addresses of the recipients of a letter.
3. Refine the list of recipients or items. Microsoft Office Word generates a copy
| |
of the main document for each item, or record, in your data file. If your data
| |
file is a mailing list, these items are probably recipients of your mailing. If you
| |
want to generate copies for only certain items in your data file, you can
| |
choose which items (records) to include.
|
4. Add placeholders, called mail merge fields, to the document. When you perform the mail merge, the mail merge fields are filled with information from your data file.
5. Preview and complete the merge. You can preview each copy of the document before you print the whole set.
Thus, Mail Merge is used to create documents that are essentially the same but have a few unique data elements that vary for each document.
WHAT IS SPREADSHEET
A Spreadsheet, as you all would know, is a grid made of columns and rows known as cells and is used for making tables and charts used for mathematical and statistical analysis as well as business calculations. One of the widely used spreadsheet software is MS-Excel. A few features of MS Excel 2007 are described below.
Introduction
Each cell on a spreadsheet can have any of the following data:
- Text or Labels
- Numbers or Constants
- Formulae which are mathematical equations to do all calculations
Each cell is designated with a name which is actually the COLUMN Name and
ROW NUMBER.
E.g. A2 means the cell A21 is the intersection of column A and row 2
The following example shows the three basic types of data that a cell may have:
- Labels Text which simply is the column heading like cell B2, C2, C3 etc.
- Values which are constant fixed numbers entries like C3, C4, D3, E4 etc
- Formulas which are mathematical calculations as in the highlighted column E8. Formula cells always begin with an EQUAL SIGN (=). It is best to use the reference to data within the formula cells by giving the cell location. This helps in keeping the formula unchanged even if the values change.
Basic Features
The spreadsheet consists of various sheets like sheet1, sheet2, sheet3 etc. within the workbook. These sheets can be can be given meaningful names. New sheets can be inserted and worksheets can be deleted from the workbook.
The format of the cells on the sheets can be modified using the Home->Cells->format->format cells option on the menu. This will give the following dialog box which is used to format datatype of the cell, alignment, font etc.
The contents of the cells can be cut, copied and pasted using the Home->Clipboard options. There is an option of ―Paste Special‖ (Home -> Clipboard-> Paste -> Paste Special) which allows only to paste certain aspects of the cells copied to the clipboard.
Formulas : Only pastes the formulas from the cells on the clipboard.
Values : Will paste the values of the cells on the clipboard. If the cells copied contained formulas, only the results of the formulas will be pasted.
Column Widths : Will paste only the column widths of the cells that you copied to the clipboard.
Operations : This is useful if you want to scale a group of numbers or add/subtract a certain amount from each. Whatever value is copied to the clipboard, the current operation will be done using that number on the cells in which you are pasting to.
Skip blanks : Will paste data, skipping any blank cells.
Transpose : Will transpose the data and paste it (columns to rows, rows to columns).
It is possible to protect the cells so that the data cannot be changed or modified. By default all cells in a worksheet have the locked property turned on (the cell is capable of being protected). is also possible to protect worksheet or the whole workbook using password protection.
Protecting a Worksheet
1. Click Tools, click Protection, and click Protect Sheet.
2. Enter a password (only if desired).
3. Confirm password (only if entered).
4. Click OK.
Protecting a Workbook
1. Click Tools, click Protection, and click Protect Workbook.
2. Enter a password (optional).
3. Choose to protect the Structure, Windows, or both.
4. Click OK.
Formulae and Functions
There are different categories of functions that can be incorporated in the sheets like Date & Time, Mathematical, Statistical, Logical, Text functions etc.
The different categories of functions are:
Date and Time:
MONTH - Converts a serial number to a month
YEAR - Converts a serial number to a year
NOW - Returns the serial number of the current date and time
Math and Statistical:
SUM – Adds its arguments
MEDIAN – Returns the median of the given numbers
SUMPROUCT – Returns the sum of the products of corresponding array components
COUNTIF - Counts the number of cells within a range that meet the given criteria
Text:
CONCATENATE – Joins several text items into one text item
RIGHT - Returns the rightmost characters from a text value
LEN - Returns the number of characters in a text string
EFT - Returns the leftmost characters from a text value
Logical:
IF - Specifies a logical test to perform
NOT - Reverses the logic of its argument
AND - Returns TRUE if all of its arguments are TRUE
Advanced Functions:
MATCH- Looks up values in a reference or array
INDEX - Uses an index to choose a value from a reference or array
SEARCH - Finds one text value within another (not case-sensitive)
REPLACE - Replaces characters within text
Database Functions:
DGET- Extracts from a database a single record that matches the specified criteria DCOUNT - Counts the cells that contain numbers in a database
DSUM - Adds the numbers in the field column of records in the database that match the criteria
DPRODUCT - Multiplies the values in a particular field of records that match the criteria in a database
Lookup & Reference:
VLOOKUP - Looks in the first column of an array and moves across the row to return the value of a cell
HLOOKUP - Looks in the top row of an array and returns the value of the indicated cell
INDIRECT - Returns a reference indicated by a text value
Charting
Charts and Graphs can be created based on data in the sheets. To create a chart to represent data graphically:
1. Select the data
2. Go to Insert
3. Select the chart type from the options available like Bar, Line, Pie, Scatter etc.
4. The chart will get automatically populated with the selected data on which the chart is to be based.
Macros
A macro is a short program written using VBA that can be used to carry out a specific task. VBA is the language that Excel macros are written in. It is a programming language that is included with all of the Microsoft Office applications e.g. Word, Access, Power Point, Excel as well as others.
VBA is a subset of Microsoft Visual Basic, an extremely popular programming language that has been around for over 10 years. The Macro has to be recorded as follows:
1. Go to the Tools menu, go to ―macro‖ and then ―Record New Macro‖
2. Assign a name to your macro if you'd like, as well as type a short description.
3. You can also assign a keyboard shortcut to it (so you can press a sequence of keys to run the macro).
4. Now click on OK. You'll be returned to Excel.
5. Simply perform the actions you want the macro to do.
6. Once you're done recording your macro, press the Stop button which should now be visible on your screen.
Once the Macro is recorded it can be executed in the following ways:
1. Run a macro by using the menu command
2. By pressing a CTRL combination shortcut key
3. Clicking a toolbar button or an area on an object, graphic, or control
4. Run a macro automatically when the workbook is opened.
A Small Example in Excel 2007
Take a small example given in the table below for calculation of Salary and Income tax deduction and compute the result in excel to give the net monthly and annual income as shown below:
No comments:
Post a Comment