• Absolute cell reference: Cell contents will not change when copied or moved to another cell.
• Active cell: Cell on which the cell pointer is located.
• AutoFormat: Pre-designed formats that add a professional look to spreadsheets.
• Cell: Point at which a column and row meet.
• Cell reference: Cell’s name.
• Chart: Visual representation that shows statistical information.
• Footer: Information that appears at the bottom of every page.
• Formula: Statement that performs a calculation.
• Formula prefix: Equal sign (=) that is entered before a formula.
• Function: Built-in formula that is a shortcut for common calculations, such as addition and average.
• Header: Information that appears at the top of every page.
• Label: Alphabetical text.
• Order of evaluation: Sequence of calculation.
• Range: Group of cells.
• Relative cell reference: Contents of the cell will not change when copied or moved to another cell.
• Spreadsheet: Row and column arrangement of data used to enter, calculate, manipulate, and analyze numbers.
• Value: Numeric text.
• Workbook: Group of related spreadsheets.
• Worksheet: Grid with columns and rows. The term worksheet is used interchangeably with spreadsheet.
Microsoft Office
Thursday, August 6, 2009
Saturday, August 1, 2009
Word Processing
Word Processing
One of the most common computer applications
Handles text and graphics
Creates and modifies reports, letters, flyers, brochures, newsletters, and so on
Many different word-processing programs
Microsoft Word
Corel WordPerfect
Wordstar
Open Office Writer
Star Office Text Document
Microsoft Word Screen Elements
Title bar
Menu bar
Standard toolbar
Formatting toolbar
Insertion point
Ruler
Document window
View buttons
Scroll bars
Status bar
Task pane
Taskbar
File Menu Options
New
Open
Close
Save
Save As
Print
Page Setup
Print Preview
Exit
Saving, Printing, Closing, and Opening a File
To make a permanent copy of a document, use the File > Save As command.
To produce a hard copy of a document, use the File > Print command.
To close a file, use the File > Exit command.
To open a file, use the Open button on the Standard toolbar.
Selecting and Editing Documents
Selecting text
Click at the beginning of the text and drag to the end of the text you want to select.
Making corrections
Backspace key
Delete key
Insert key
AutoCorrect
Spelling and Grammar checker
Spell Check and Thesaurus
Spell Check tool
Does not eliminate the need to proofread
Thesaurus
Search for synonyms
To use the Spell Check and Thesaurus tools, click the Spelling and Grammar icon on the Standard toolbar.
Formatting
Character formatting
Attributes applied to fonts
Paragraph formatting
Line spacing and indents
Document formatting
Margins and paper size
Character Formatting
Font styles
Point size
Attributes
Format Painter
Line and Paragraph Formatting
Line spacing
Single
Double
Justification
Left
Right
Center
Full
Indenting
Tab stops
Default
Custom
Bullets and numbers
Document Formatting
Margins
Headers and footers
Breaks
Styles
Borders and shading
Custom tab stops
Graphics
Tables
Graphics
Adds visual appeal
To insert clip art:
Click Insert on the menu bar.
Point to Picture.
Click Clip Art.
To insert symbols:
Click Insert on the menu bar.
Click Symbol.
Tables
A table is an arrangement of data in a row-and- column format.
Click the Insert Table button on the Standard toolbar.
Table Example
3 rows and 2 columns
Getting Help
Click Help on the menu bar.
Click Microsoft Word Help.
Type the topic with which you need help in the Search for text box.
Click the arrow to start the search.
Summary
Word-processing software is used to create documents such as letters, reports, memos, brochures, and even Web pages.
Commands for using the features in a word-processing program are selected from menus and toolbars.
You also can use keyboard shortcuts to execute some commands.
Word-processing programs come with powerful editing tools.
You can make changes to text, cut or copy text, and delete it.
The Spell Checker checks documents for possible misspelled words.
Word also has a grammar-checking tool that identifies grammatical mistakes and a Thesaurus that display synonyms for a selected word.
You can apply formatting to characters, paragraphs, or the entire document.
Word comes with a variety of formatting tools that enable you to enhance the appearance of documents.
Clip art can be added to documents to enhance their visual appearance.
Tables are used to present information in an organized manner.
Word’s Help system offers a variety of resources for getting help and information on features and tools.
One of the most common computer applications
Handles text and graphics
Creates and modifies reports, letters, flyers, brochures, newsletters, and so on
Many different word-processing programs
Microsoft Word
Corel WordPerfect
Wordstar
Open Office Writer
Star Office Text Document
Microsoft Word Screen Elements
Title bar
Menu bar
Standard toolbar
Formatting toolbar
Insertion point
Ruler
Document window
View buttons
Scroll bars
Status bar
Task pane
Taskbar
File Menu Options
New
Open
Close
Save
Save As
Page Setup
Print Preview
Exit
Saving, Printing, Closing, and Opening a File
To make a permanent copy of a document, use the File > Save As command.
To produce a hard copy of a document, use the File > Print command.
To close a file, use the File > Exit command.
To open a file, use the Open button on the Standard toolbar.
Selecting and Editing Documents
Selecting text
Click at the beginning of the text and drag to the end of the text you want to select.
Making corrections
Backspace key
Delete key
Insert key
AutoCorrect
Spelling and Grammar checker
Spell Check and Thesaurus
Spell Check tool
Does not eliminate the need to proofread
Thesaurus
Search for synonyms
To use the Spell Check and Thesaurus tools, click the Spelling and Grammar icon on the Standard toolbar.
Formatting
Character formatting
Attributes applied to fonts
Paragraph formatting
Line spacing and indents
Document formatting
Margins and paper size
Character Formatting
Font styles
Point size
Attributes
Format Painter
Line and Paragraph Formatting
Line spacing
Single
Double
Justification
Left
Right
Center
Full
Indenting
Tab stops
Default
Custom
Bullets and numbers
Document Formatting
Margins
Headers and footers
Breaks
Styles
Borders and shading
Custom tab stops
Graphics
Tables
Graphics
Adds visual appeal
To insert clip art:
Click Insert on the menu bar.
Point to Picture.
Click Clip Art.
To insert symbols:
Click Insert on the menu bar.
Click Symbol.
Tables
A table is an arrangement of data in a row-and- column format.
Click the Insert Table button on the Standard toolbar.
Table Example
3 rows and 2 columns
Getting Help
Click Help on the menu bar.
Click Microsoft Word Help.
Type the topic with which you need help in the Search for text box.
Click the arrow to start the search.
Summary
Word-processing software is used to create documents such as letters, reports, memos, brochures, and even Web pages.
Commands for using the features in a word-processing program are selected from menus and toolbars.
You also can use keyboard shortcuts to execute some commands.
Word-processing programs come with powerful editing tools.
You can make changes to text, cut or copy text, and delete it.
The Spell Checker checks documents for possible misspelled words.
Word also has a grammar-checking tool that identifies grammatical mistakes and a Thesaurus that display synonyms for a selected word.
You can apply formatting to characters, paragraphs, or the entire document.
Word comes with a variety of formatting tools that enable you to enhance the appearance of documents.
Clip art can be added to documents to enhance their visual appearance.
Tables are used to present information in an organized manner.
Word’s Help system offers a variety of resources for getting help and information on features and tools.
The Microsoft Excel Spreadsheets
Spreadsheets Defined
A row and column arrangement of data
Used to perform calculations
Budgets
Payrolls
Grades
Used to make forecasts and decisions
Anatomy of a Spreadsheet
256 columns identified by letters
65,536 rows identified by numbers
A cell is the point at which a row and column meet (16,777,216 cells!).
Selecting Cells
Select a single cell
Point to the cell and click.
Select a range of cells
Click cell and then drag to select the range.
Keyboard Shortcuts
Types of Data
A label is alphabetic text entered into a cell.
Labels align at the left of the cell.
A value is numeric text entered into a cell.
Numbers align at the right of the cell.
A formula is a statement that performs a calculation.
A function is a built-in formula that is a shortcut for common calculations.
Entering Formulas
Type an “=“ before entering a formula or function.
Formulas include numbers, cell references, arithmetic operators, and/or functions.
The arithmetic operators include addition (+), subtraction (-), multiplication (*), and division (/).
Entering Functions
A function is a pre-written formula that performs calculations automatically.
Some functions include SUM, AVG, MIN, MAX, and IF.
A function is entered as =SUM(A1:A10).
This will add the contents of cells A1 through A10.
Copying Cell Contents
Cell contents can be copied to other cells.
Formulas can be copied to other cells.
Methods of copying
Copy and paste
Drag and drop
Fill cells
Copying Cell Contents (cont.)
To copy cells that contain formulas
Absolute cell references
Relative cell references
Formatting a Spreadsheet
Change column width and row height.
Align labels in cells.
Font style, font size, and color can be changed.
Text in cells can be rotated or wrapped.
The Format Painter ensures consistency.
Formatting a Spreadsheet (cont.)
Styles and Formatting provides additional formatting options.
Add borders and shading to spreadsheet.
Titles can be centered over several columns.
Numbers can be formatted with decimals, commas, and dollar signs.
Formatting a Spreadsheet (cont.)
Borders and shading of cells can be changed.
AutoFormats add professional-looking formats to spreadsheets.
Printing a Spreadsheet
Print the entire spreadsheet.
Print a selected portion of the spreadsheet.
Print in landscape or portrait orientation.
Divide the spreadsheet into pages with a forced page break.
Set data to fit on a specified size of paper and number of pages.
Additional Features
Sorting rearranges data in ascending or descending order.
Headers and footers allow the same information to appear at the top or bottom of each page of the spreadsheet.
Adding objects, such as clip art and drawn objects, enhances the spreadsheet.
Creating Charts
Use Charts to display the data visually.
Use the What-if analysis feature to test different scenarios.
Summary
The primary use of spreadsheets is to enter, calculate, manipulate, and analyze numbers.
Columns in spreadsheets are identified by letters, and rows are identified by numbers.
The point at which a row and a column intersect is a cell.
Summary (cont.)
A cell that has been selected (highlighted or outlined with a black border) is referred to as the active cell.
A range of cells is a group of closely situated cells.
Alphabetic information in cells is referred to as labels; numeric information in cells that can be calculated is referred to as values.
Summary (cont.)
A formula is a type of data that performs a calculation.
To enter a formula in a cell, you must first type an equal sign.
A function is a built-in formula that performs calculations ranging from simple to complex.
Summary (cont.)
You can copy data by using the Copy and Paste commands, the drag-and-drop method, or the Fill command.
A relative cell reference refers to cells that change when they are copied into other locations.
An absolute cell reference refers to cells that do not change when they are copied into other locations.
Summary (cont.)
The AutoSum feature enables you to quickly add a range of cells.
You can change the appearance of data by using a variety of formatting tools and options or by applying one of Excel’s AutoFormats.
Selected data in a spreadsheet can be hidden so it will not be displayed or printed.
Summary (cont.)
The contents of a spreadsheet can be displayed in chart format.
A chart displays the spreadsheet data visually so that data can be understood more easily.
A row and column arrangement of data
Used to perform calculations
Budgets
Payrolls
Grades
Used to make forecasts and decisions
Anatomy of a Spreadsheet
256 columns identified by letters
65,536 rows identified by numbers
A cell is the point at which a row and column meet (16,777,216 cells!).
Selecting Cells
Select a single cell
Point to the cell and click.
Select a range of cells
Click cell and then drag to select the range.
Keyboard Shortcuts
Types of Data
A label is alphabetic text entered into a cell.
Labels align at the left of the cell.
A value is numeric text entered into a cell.
Numbers align at the right of the cell.
A formula is a statement that performs a calculation.
A function is a built-in formula that is a shortcut for common calculations.
Entering Formulas
Type an “=“ before entering a formula or function.
Formulas include numbers, cell references, arithmetic operators, and/or functions.
The arithmetic operators include addition (+), subtraction (-), multiplication (*), and division (/).
Entering Functions
A function is a pre-written formula that performs calculations automatically.
Some functions include SUM, AVG, MIN, MAX, and IF.
A function is entered as =SUM(A1:A10).
This will add the contents of cells A1 through A10.
Copying Cell Contents
Cell contents can be copied to other cells.
Formulas can be copied to other cells.
Methods of copying
Copy and paste
Drag and drop
Fill cells
Copying Cell Contents (cont.)
To copy cells that contain formulas
Absolute cell references
Relative cell references
Formatting a Spreadsheet
Change column width and row height.
Align labels in cells.
Font style, font size, and color can be changed.
Text in cells can be rotated or wrapped.
The Format Painter ensures consistency.
Formatting a Spreadsheet (cont.)
Styles and Formatting provides additional formatting options.
Add borders and shading to spreadsheet.
Titles can be centered over several columns.
Numbers can be formatted with decimals, commas, and dollar signs.
Formatting a Spreadsheet (cont.)
Borders and shading of cells can be changed.
AutoFormats add professional-looking formats to spreadsheets.
Printing a Spreadsheet
Print the entire spreadsheet.
Print a selected portion of the spreadsheet.
Print in landscape or portrait orientation.
Divide the spreadsheet into pages with a forced page break.
Set data to fit on a specified size of paper and number of pages.
Additional Features
Sorting rearranges data in ascending or descending order.
Headers and footers allow the same information to appear at the top or bottom of each page of the spreadsheet.
Adding objects, such as clip art and drawn objects, enhances the spreadsheet.
Creating Charts
Use Charts to display the data visually.
Use the What-if analysis feature to test different scenarios.
Summary
The primary use of spreadsheets is to enter, calculate, manipulate, and analyze numbers.
Columns in spreadsheets are identified by letters, and rows are identified by numbers.
The point at which a row and a column intersect is a cell.
Summary (cont.)
A cell that has been selected (highlighted or outlined with a black border) is referred to as the active cell.
A range of cells is a group of closely situated cells.
Alphabetic information in cells is referred to as labels; numeric information in cells that can be calculated is referred to as values.
Summary (cont.)
A formula is a type of data that performs a calculation.
To enter a formula in a cell, you must first type an equal sign.
A function is a built-in formula that performs calculations ranging from simple to complex.
Summary (cont.)
You can copy data by using the Copy and Paste commands, the drag-and-drop method, or the Fill command.
A relative cell reference refers to cells that change when they are copied into other locations.
An absolute cell reference refers to cells that do not change when they are copied into other locations.
Summary (cont.)
The AutoSum feature enables you to quickly add a range of cells.
You can change the appearance of data by using a variety of formatting tools and options or by applying one of Excel’s AutoFormats.
Selected data in a spreadsheet can be hidden so it will not be displayed or printed.
Summary (cont.)
The contents of a spreadsheet can be displayed in chart format.
A chart displays the spreadsheet data visually so that data can be understood more easily.
What is Microsoft Excel?
Microsoft Excel is a spreadsheet program that you can use to organize, analyze and attractively present data such as a budget or sales report. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns, designated by letters, and rows, designated by numbers. The letters and numbers of the columns and row called labels are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. Cells can contain text, numbers, or mathematical formulas.
Formulas and Functions
The unique feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions.
Formulas
Formulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operators placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. See the example to the right to view the formula for calculating the subtotal for a number of textbooks. The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.
Linking Worksheets
When working with formulas, you may want to use a cell from a worksheet other than your current worksheet. For example, the value of cell A1 in the current worksheet and cell A2 in the second worksheet can be added using the format "sheetname! cell-address". The formula for this example would be "=A1+Sheet2! A2" where the value of cell A1 in the current worksheet is added to the value of cell A2 in the worksheet named "Sheet2".
Formatting Worksheets
To change the name of the Sheet Tab, double-click it then type the desired sheet name/label. You may also apply tab color, just right-click on the Sheet Tab and select the command “Tab Color” and select the desired color from the palette.
Relative, Absolute, and Mixed Referencing
Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C1 "= (A1+B1)" is copied to cell C2, the formula would change to "= (A2+B2)" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, the formula in cell C1 would read "= ($A$1+$B$1)" if the value of cell C2 should be the sum of
Formulas and Functions
The unique feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions.
Formulas
Formulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operators placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. See the example to the right to view the formula for calculating the subtotal for a number of textbooks. The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.
Linking Worksheets
When working with formulas, you may want to use a cell from a worksheet other than your current worksheet. For example, the value of cell A1 in the current worksheet and cell A2 in the second worksheet can be added using the format "sheetname! cell-address". The formula for this example would be "=A1+Sheet2! A2" where the value of cell A1 in the current worksheet is added to the value of cell A2 in the worksheet named "Sheet2".
Formatting Worksheets
To change the name of the Sheet Tab, double-click it then type the desired sheet name/label. You may also apply tab color, just right-click on the Sheet Tab and select the command “Tab Color” and select the desired color from the palette.
Relative, Absolute, and Mixed Referencing
Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C1 "= (A1+B1)" is copied to cell C2, the formula would change to "= (A2+B2)" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, the formula in cell C1 would read "= ($A$1+$B$1)" if the value of cell C2 should be the sum of
Wednesday, July 29, 2009
To put comment in Excel
A.Right click the Cell area, and select Insert Comment.
B.Type a comment for that cell
a green triangular color will appeat at the top of the cell
B.Type a comment for that cell
a green triangular color will appeat at the top of the cell
AutoFill
Fill in a series of numbers, dates, or other built-in series items
Using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.), you can quickly fill cells in a range with a series of numbers or dates, or with a built-in series for days, weekdays, months, or years.
Select the first cell in the range that you want to fill.
Type the starting value for the series.
Type a value in the next cell to establish a pattern.
For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.
More examples of series that you can fill
When you fill a series, the selections are extended as shown in the following table. In this table, items that are separated by commas are contained in individual adjacent cells on the worksheet.
Initial selection Extended series
1, 2, 3 4, 5, 6,...
9:00 10:00, 11:00, 12:00,...
Mon Tue, Wed, Thu,...
Monday Tuesday, Wednesday, Thursday,...
Jan Feb, Mar, Apr,...
Jan, Apr Jul, Oct, Jan,...
Jan-07, Apr-07 Jul-07, Oct-07, Jan-08,...
15-Jan, 15-Apr 15-Jul, 15-Oct,...
2007, 2008 2009, 2010, 2011,...
1-Jan, 1-Mar 1-May, 1-Jul, 1-Sep,...
Qtr3 (or Q3 or Quarter3) Qtr4, Qtr1, Qtr2,...
text1, textA text2, textA, text3, textA,...
1st Period 2nd Period, 3rd Period,...
Product 1 Product 2, Product 3,...
Select the cell or cells that contain the starting values.
Drag the fill handle across the range that you want to fill.
To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left. See Microsoft Excel help
Using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.), you can quickly fill cells in a range with a series of numbers or dates, or with a built-in series for days, weekdays, months, or years.
Select the first cell in the range that you want to fill.
Type the starting value for the series.
Type a value in the next cell to establish a pattern.
For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.
More examples of series that you can fill
When you fill a series, the selections are extended as shown in the following table. In this table, items that are separated by commas are contained in individual adjacent cells on the worksheet.
Initial selection Extended series
1, 2, 3 4, 5, 6,...
9:00 10:00, 11:00, 12:00,...
Mon Tue, Wed, Thu,...
Monday Tuesday, Wednesday, Thursday,...
Jan Feb, Mar, Apr,...
Jan, Apr Jul, Oct, Jan,...
Jan-07, Apr-07 Jul-07, Oct-07, Jan-08,...
15-Jan, 15-Apr 15-Jul, 15-Oct,...
2007, 2008 2009, 2010, 2011,...
1-Jan, 1-Mar 1-May, 1-Jul, 1-Sep,...
Qtr3 (or Q3 or Quarter3) Qtr4, Qtr1, Qtr2,...
text1, textA text2, textA, text3, textA,...
1st Period 2nd Period, 3rd Period,...
Product 1 Product 2, Product 3,...
Select the cell or cells that contain the starting values.
Drag the fill handle across the range that you want to fill.
To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left. See Microsoft Excel help
Remove Gridlines
To remove the gridlines:
In Microsoft Excel 2003:
a. Go to Tools menu,
b. Select Options
c. Select View Tab
d. In the Window Options section, clear the Gridlines check box.
Return to the destination document by clicking outside the boundaries of the Microsoft Excel object.
In Microsoft Excel 2007:
a. Go to View menu and uncheck Gridlines
In Microsoft Excel 2003:
a. Go to Tools menu,
b. Select Options
c. Select View Tab
d. In the Window Options section, clear the Gridlines check box.
Return to the destination document by clicking outside the boundaries of the Microsoft Excel object.
In Microsoft Excel 2007:
a. Go to View menu and uncheck Gridlines
protect selected cell in a worksheet and hide a formula in the formula bar
You may hide the formula in the Formula bar. This is how to do it.
In Microsoft Excel 2003:
a. Select all the worksheet by pressing CTRL+A.
b. Go to Format menu and select Cells or Right click the Worksheet area and select
Format Cells
c. Choose Protection TAB
d. Uncheck the "Locked" option and click the OK button.
e. Select a cell or cells to hide the formula.
f. Go to Tools menu, and select Protection sub menu,and Protect Sheet
g. Put a password to unprotect the sheet.(Remember your password)and click OK button.
You will see your formula is a cell is hidden.
in Microsoft Excel 2007
a. Select all the worksheet by pressing CTRL+A.
b. Right click the worksheet area and select Format cells
c. Choose Protection TAB
d. Uncheck the "Locked" option and click the OK button.
e. Select a cell or cells to hide the formula.
f. Go to review menu, and select Protect Sheet
g. Put a password to unprotect the sheet.(Remember your password)and click OK button.
You will see your formula is a cell is hidden.
In Microsoft Excel 2003:
a. Select all the worksheet by pressing CTRL+A.
b. Go to Format menu and select Cells or Right click the Worksheet area and select
Format Cells
c. Choose Protection TAB
d. Uncheck the "Locked" option and click the OK button.
e. Select a cell or cells to hide the formula.
f. Go to Tools menu, and select Protection sub menu,and Protect Sheet
g. Put a password to unprotect the sheet.(Remember your password)and click OK button.
You will see your formula is a cell is hidden.
in Microsoft Excel 2007
a. Select all the worksheet by pressing CTRL+A.
b. Right click the worksheet area and select Format cells
c. Choose Protection TAB
d. Uncheck the "Locked" option and click the OK button.
e. Select a cell or cells to hide the formula.
f. Go to review menu, and select Protect Sheet
g. Put a password to unprotect the sheet.(Remember your password)and click OK button.
You will see your formula is a cell is hidden.
Wednesday, July 15, 2009
Autosum Functions
1. =AVERAGE(number1, [number2],...)
Description
Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
=AVERAGE(A2:A6) Average of the numbers in cells A2 through A6.
2. =MAX Returns the largest value in a set of values.
Syntax
MAX(number1,number2,...)
Number1, number2, ... are 1 to 255 numbers for which you want to find the maximum value.
Example: If the value A3 has the largest number from A1 through A5 then it displays the value of A3.
=max(A1:A5)
3. =MIN(number1,number2,...) it is the opposite of =MAX. It returns the smallest value in a set of values.
4. The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20:
=COUNT(A1:A20)
In this example, if five of the cells in the range contain numbers, the result is 5.
Syntax
COUNT(value1, [value2],...)The COUNT function syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
value1 Required. The first item, cell reference, or range within which you want to count numbers.
value2, ... Optional. Up to 255 additional items, cell references, or ranges within which you want to count numbers.
Example: If A2 has value of 1 and A5 is 5 and A6 is equal to letter C then
=COUNT(A2:A8) is equal to 2. With this example, it counts the number of cells that contain numbers in cells A2 through A8.
Description
Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
=AVERAGE(A2:A6) Average of the numbers in cells A2 through A6.
2. =MAX Returns the largest value in a set of values.
Syntax
MAX(number1,number2,...)
Number1, number2, ... are 1 to 255 numbers for which you want to find the maximum value.
Example: If the value A3 has the largest number from A1 through A5 then it displays the value of A3.
=max(A1:A5)
3. =MIN(number1,number2,...) it is the opposite of =MAX. It returns the smallest value in a set of values.
4. The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20:
=COUNT(A1:A20)
In this example, if five of the cells in the range contain numbers, the result is 5.
Syntax
COUNT(value1, [value2],...)The COUNT function syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
value1 Required. The first item, cell reference, or range within which you want to count numbers.
value2, ... Optional. Up to 255 additional items, cell references, or ranges within which you want to count numbers.
Example: If A2 has value of 1 and A5 is 5 and A6 is equal to letter C then
=COUNT(A2:A8) is equal to 2. With this example, it counts the number of cells that contain numbers in cells A2 through A8.
Common Microsoft Excel-Functions
These are some Excel functions commonly used:
* Today()
Just type "=today()" in a cell and it will display the current date.
* Hour(serial)
* IF
IF(logical_test, value_if_true, [value_if_false])
Example:
=IF(A2<75,"Failed","Passed")
*NOT
Syntax
NOT(logical)
Logical is a value or expression that can be evaluated to TRUE or FALSE.
Example:=
NOT(FALSE) Reverses FALSE (TRUE)
=NOT(3+1=4)
*=OR(logical1,logical2,...)
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax
OR(logical1,logical2,...)Logical1,logical2,... are 1 to 255 conditions
Example:
OR(TRUE) One argument is TRUE (TRUE)
=OR(1+1=1,2+2=5) All arguments evaluate to FALSE (FALSE)
=OR(TRUE,FALSE,TRUE)
* T
Returns the text referred to by value.
Syntax
T(value)
Value is the value you want to test.
Remarks
If value is or refers to text, T returns value. If value does not refer to text, T returns "" (empty text).
Example:
=T(A2)
Whatever be the value of A2 it will be copied to where your active cell is positioned.
=TEXT(value, format text)
Description
The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 41.5. To format the number as a dollar amount, you can use the following formula:
=TEXT(A1,"Php0.00")
In this example, Excel displays Php41.50.
* =VLOOKUP
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:=VLOOKUP(1,A2:C10,3,TRUE)- Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.
PMT(rate,nper,pv,fv,type)
For a more complete description of the arguments in PMT, see the PV function.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.
Example:
A2=12%, B2=10, C2=10000
Then the Formula is =PMT(A2/12, B2, B3)
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
RATE(nper,pmt,pv,fv,type,guess)
For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv is the present value— the total amount that a series of future payments is worth now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due.
This example is from the Excel Help:
A2=4: Years of the loan
A3=-200: Monthly payment
A4=8000: Amount of the loan
Formula Description
=RATE(A2*12, A3, A4) Monthly rate of the loan with the above terms (1%)
=RATE(A2*12, A3, A4)*12 Annual rate of the loan with the above terms (0.09241767 or 9.24%)
* Today()
Just type "=today()" in a cell and it will display the current date.
* Hour(serial)
* IF
IF(logical_test, value_if_true, [value_if_false])
Example:
=IF(A2<75,"Failed","Passed")
*NOT
Syntax
NOT(logical)
Logical is a value or expression that can be evaluated to TRUE or FALSE.
Example:=
NOT(FALSE) Reverses FALSE (TRUE)
=NOT(3+1=4)
*=OR(logical1,logical2,...)
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax
OR(logical1,logical2,...)Logical1,logical2,... are 1 to 255 conditions
Example:
OR(TRUE) One argument is TRUE (TRUE)
=OR(1+1=1,2+2=5) All arguments evaluate to FALSE (FALSE)
=OR(TRUE,FALSE,TRUE)
* T
Returns the text referred to by value.
Syntax
T(value)
Value is the value you want to test.
Remarks
If value is or refers to text, T returns value. If value does not refer to text, T returns "" (empty text).
Example:
=T(A2)
Whatever be the value of A2 it will be copied to where your active cell is positioned.
=TEXT(value, format text)
Description
The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 41.5. To format the number as a dollar amount, you can use the following formula:
=TEXT(A1,"Php0.00")
In this example, Excel displays Php41.50.
* =VLOOKUP
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:=VLOOKUP(1,A2:C10,3,TRUE)- Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.
PMT(rate,nper,pv,fv,type)
For a more complete description of the arguments in PMT, see the PV function.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.
Example:
A2=12%, B2=10, C2=10000
Then the Formula is =PMT(A2/12, B2, B3)
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
RATE(nper,pmt,pv,fv,type,guess)
For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv is the present value— the total amount that a series of future payments is worth now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due.
This example is from the Excel Help:
A2=4: Years of the loan
A3=-200: Monthly payment
A4=8000: Amount of the loan
Formula Description
=RATE(A2*12, A3, A4) Monthly rate of the loan with the above terms (1%)
=RATE(A2*12, A3, A4)*12 Annual rate of the loan with the above terms (0.09241767 or 9.24%)
Microsoft Excel-Functions (AND)
=AND(logical1, [logical2], ...)
Description
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.
Syntax
AND(logical1, [logical2], ...)
The AND function syntax has the following arguments:
logical1 Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.
logical2, ... Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.
Remarks
Example 1
5 DATA
6 50
7 104
Note: number 5 through 7 are rows and DATA, 50, 104 are values of column A.
=AND(1>A6, A6<100)
Description
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.
Syntax
AND(logical1, [logical2], ...)
The AND function syntax has the following arguments:
logical1 Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.
logical2, ... Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.
Remarks
Example 1
5 DATA
6 50
7 104
Note: number 5 through 7 are rows and DATA, 50, 104 are values of column A.
=AND(1>A6, A6<100)
Monday, July 6, 2009
Show or hide the rulers
Show or hide the rulers
The horizontal and vertical rulers in Word are often used to align text, graphics, tables, and other elements in a document.
To view the horizontal ruler across the top of your Word document and the vertical ruler along the left edge of your document, you must be in Print Layout view (Print Layout view: A view of a document or other object as it will appear when you print it. For example, items such as headers, footnotes, columns, and text boxes appear in their actual positions.).
Show or hide the horizontal and vertical rulers
To show or hide the horizontal and vertical rulers, click View Ruler at the top of the vertical scroll bar.
Note The vertical ruler will not appear if it is turned off. To turn on the vertical ruler, do the following:
1. Click the Microsoft Office Button , and then click Word Options.
2. Click Advanced.
3. Under Display, select the Show vertical ruler in Print Layout view check box.
The horizontal and vertical rulers in Word are often used to align text, graphics, tables, and other elements in a document.
To view the horizontal ruler across the top of your Word document and the vertical ruler along the left edge of your document, you must be in Print Layout view (Print Layout view: A view of a document or other object as it will appear when you print it. For example, items such as headers, footnotes, columns, and text boxes appear in their actual positions.).
Show or hide the horizontal and vertical rulers
To show or hide the horizontal and vertical rulers, click View Ruler at the top of the vertical scroll bar.
Note The vertical ruler will not appear if it is turned off. To turn on the vertical ruler, do the following:
1. Click the Microsoft Office Button , and then click Word Options.
2. Click Advanced.
3. Under Display, select the Show vertical ruler in Print Layout view check box.
Top margin is missing
When you open a document, is the insertion point at the top left corner of the page, as if there were no top margin?
If you're reading the document in Print Layout view and all of the pages appear to be cut off, with no top or bottom margin, the option for hiding white space between pages has been turned on.
If the top and bottom margins are not showing, you can restore them by pointing and clicking in the document, or by using the View tab.
When top and bottom margins are not showing, point to the top or bottom of a page until the pointer becomes a double-pointed arrow, and then double click.
When white space between pages is showing, the entire top and bottom margins are visible.
To use the View tab to show white space between pages, do the following:
1. On the View tab, in the Document Views group, click Print Layout.
2. Click the Microsoft Office Button , and then click Word Options.
3. Click Display.
4. Under Page display options, select the Show white space between pages in Print Layout view check box.
If you're reading the document in Print Layout view and all of the pages appear to be cut off, with no top or bottom margin, the option for hiding white space between pages has been turned on.
If the top and bottom margins are not showing, you can restore them by pointing and clicking in the document, or by using the View tab.
When top and bottom margins are not showing, point to the top or bottom of a page until the pointer becomes a double-pointed arrow, and then double click.
When white space between pages is showing, the entire top and bottom margins are visible.
To use the View tab to show white space between pages, do the following:
1. On the View tab, in the Document Views group, click Print Layout.
2. Click the Microsoft Office Button , and then click Word Options.
3. Click Display.
4. Under Page display options, select the Show white space between pages in Print Layout view check box.
Page Layout
Select page orientation
You can choose either portrait (vertical) or landscape (horizontal) orientation for all or part of your document. When you change the orientation, the galleries of predesigned page and cover page options also change to offer pages that have the orientation that you choose.
--------------------------------------------------------------------------------
Change the orientation of your entire document
Use portrait and landscape orientation in the same document
--------------------------------------------------------------------------------
Change the orientation of your entire document
1. On the Page Layout tab, in the Page Setup group, click Orientation.
2. Click Portrait or Landscape.
Use portrait and landscape orientation in the same document
Select the pages or paragraphs that you want to change to portrait or landscape orientation.
If you select some but not all of the text on a page to change to portrait or landscape orientation, Word places the selected text on its own page, and the surrounding text on separate pages.
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click Custom Margins.
3. On the Margins tab, click Portrait or Landscape.
4. In the Apply to list, click Selected text.
Note Microsoft Word automatically inserts section breaks before and after the text that has the new page orientation. If your document is already divided into sections, you can click in a section (or select multiple sections), and then change the orientation for only the sections that you select.
These steps are provided in Microsoft Word 2007 HELP
You can choose either portrait (vertical) or landscape (horizontal) orientation for all or part of your document. When you change the orientation, the galleries of predesigned page and cover page options also change to offer pages that have the orientation that you choose.
--------------------------------------------------------------------------------
Change the orientation of your entire document
Use portrait and landscape orientation in the same document
--------------------------------------------------------------------------------
Change the orientation of your entire document
1. On the Page Layout tab, in the Page Setup group, click Orientation.
2. Click Portrait or Landscape.
Use portrait and landscape orientation in the same document
Select the pages or paragraphs that you want to change to portrait or landscape orientation.
If you select some but not all of the text on a page to change to portrait or landscape orientation, Word places the selected text on its own page, and the surrounding text on separate pages.
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click Custom Margins.
3. On the Margins tab, click Portrait or Landscape.
4. In the Apply to list, click Selected text.
Note Microsoft Word automatically inserts section breaks before and after the text that has the new page orientation. If your document is already divided into sections, you can click in a section (or select multiple sections), and then change the orientation for only the sections that you select.
These steps are provided in Microsoft Word 2007 HELP
Change or set page margins
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click the margin type that you want. For the most common margin width, click Normal.
3. When you click the margin type that you want, your entire document automatically changes to the margin type that you have selected.
4. You can also specify your own margin settings. Click Margins, click Custom Margins, and then in the Top, Bottom, Left, and Right boxes, enter new values for the margins.
Notes
5. To change the default margins, click Margins after you select a new margin, and then click Custom Margins. In the Page Setup dialog box, click the Default button, and then click Yes. The new default settings are saved in the template on which the document is based. Each new document based on that template automatically uses the new margin settings.
6. To change the margins for part of a document, select the text, and then set the margins that you want by entering the new margins in the Page Setup dialog box. In the Apply to box, click Selected text. Microsoft Word automatically inserts section breaks (section break: A mark you insert to show the end of a section. A section break stores the section formatting elements, such as the margins, page orientation, headers and footers, and sequence of page numbers.) before and after the text that has the new margin settings. If your document is already divided
2. Click the margin type that you want. For the most common margin width, click Normal.
3. When you click the margin type that you want, your entire document automatically changes to the margin type that you have selected.
4. You can also specify your own margin settings. Click Margins, click Custom Margins, and then in the Top, Bottom, Left, and Right boxes, enter new values for the margins.
Notes
5. To change the default margins, click Margins after you select a new margin, and then click Custom Margins. In the Page Setup dialog box, click the Default button, and then click Yes. The new default settings are saved in the template on which the document is based. Each new document based on that template automatically uses the new margin settings.
6. To change the margins for part of a document, select the text, and then set the margins that you want by entering the new margins in the Page Setup dialog box. In the Apply to box, click Selected text. Microsoft Word automatically inserts section breaks (section break: A mark you insert to show the end of a section. A section break stores the section formatting elements, such as the margins, page orientation, headers and footers, and sequence of page numbers.) before and after the text that has the new margin settings. If your document is already divided
Setting gutter margins for bound documents
A gutter margin setting adds extra space to the side margin or top margin of a document that you plan to bind. A gutter margin helps ensure that text isn't obscured by the binding.
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click Custom Margins.
3. In the Multiple pages list, click Normal.
4. In the Gutter box, enter a width for the gutter margin.
5. In the Gutter position box, click Left or Top.
The Gutter position box is not available when you use the Mirror margins, 2 pages per sheet, or Book fold option. For those options, the gutter position is determined automatically.
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click Custom Margins.
3. In the Multiple pages list, click Normal.
4. In the Gutter box, enter a width for the gutter margin.
5. In the Gutter position box, click Left or Top.
The Gutter position box is not available when you use the Mirror margins, 2 pages per sheet, or Book fold option. For those options, the gutter position is determined automatically.
Setting margins for facing pages
When you choose mirror margins, the margins of the left page are a mirror image of those on the right page. That is, the inside margins are the same width, and the outside margins are the same width.
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click Mirrored.
To change the margin widths, click Margins, click Custom Margins, and then, in the Inside and Outside boxes, enter the widths that you want.
1. On the Page Layout tab, in the Page Setup group, click Margins.
2. Click Mirrored.
To change the margin widths, click Margins, click Custom Margins, and then, in the Inside and Outside boxes, enter the widths that you want.
View page margins in Word 2007
1. Click the Microsoft Office Button
2. And then click Word Options.
3. Click Advanced, and then click the Show text boundaries check box under Show document content.The page margins appear in your document as dotted lines.
You can view page margins in either Print Layout view or Web Layout view, and
the text boundaries do not appear on the printed page.
2. And then click Word Options.
3. Click Advanced, and then click the Show text boundaries check box under Show document content.The page margins appear in your document as dotted lines.
You can view page margins in either Print Layout view or Web Layout view, and
the text boundaries do not appear on the printed page.
You can't insert text before a table
You can't insert text before a table that is located on the first line of the first page in a document.
1. Click in the first cell in the first row of the table.
If text in that cell contains text, position the insertion point before the text.
2. Press ENTER key.
3. Type the text that you want.
1. Click in the first cell in the first row of the table.
If text in that cell contains text, position the insertion point before the text.
2. Press ENTER key.
3. Type the text that you want.
Insert a blank line before a table
This procedure will insert a blank line before a table that is on the first line of the first page in a document:
1. Click in the upper-left cell in the first row of the table.
If you have text in the upper-left cell, place the insertion point before the text.
2. Press ENTER key.
1. Click in the upper-left cell in the first row of the table.
If you have text in the upper-left cell, place the insertion point before the text.
2. Press ENTER key.
Subscribe to:
Posts (Atom)