Computer Education: Excel 2003 Notes in English

MS Excel – 2003

What is MSWord?

MS Excel is advanced version of lotus – 123. It is Application software .By using these software we can create reports, chart and calculation of any row or column or any type of data. The ms – excel provide many function like as mathematical, statically function etc.

Opening Microsoft Excel - 2003:

By Start Menu/ Mouse Command:

“Start” >> “Programs” >> “Microsoft Office” >> “Microsoft Office Excel 2003.”

By Run Command:

Window + R >> Run = Excel

· Extension Name: .XLS

· File Type Name: Workbook

· By Default File Name: Book1

Ø WHAT IS WORKBOOK?

Workbook such as a file in which are kept the spreadsheet and chart, in a workbook we can keep chart, in a workbook, in a workbook we can keep only one type of or many spreadsheet related to one work.

Ø WHAT IS WORKSHEET?

Spreadsheet is also said to the worksheet. Worksheet is a rectangular sheet in which there are made small boxes. Every box of worksheet is used to store the data as in the form of table and it can be moved or copied as per requirement this data can be calculated by giving formula according to our work.

Ø WORKSHEET

Spreadsheet is the most important and the biggest part of excel. Which appears like a graph? As spreadsheet is big in size it is not completely visible on screen but its right side is always seen on the screen, from there we can start to input our data. The each and every small box of spreadsheet is known as cell which we can turn to big or small and also format is, depending upon the size of our data.

Ø COLUMN HEADING

In spreadsheet, going from left to right is not recognized by the number but they are through the alphabets such as A, B, C they are known as column heading.

Ø Formula Bar:-

The formula bar displays the contents of the active cell including any formulas. As you enter or edit data, the changes will appear in the formula bar.

Ø Name Box:-

The name box displays the active cell reference.

Ø Row - Rows (horizontal) are labeled by their row headings from 1 thru 65,536.

Ø Column - Columns (vertical) are labeled along their column heading A thru Z, AA thru AZ, up to column XFD.

Ø FILL HANDLE AND FILL SERIES COMMAND

There are three ways to click and drag on a cell – each having a different effect.

1. When you have the mouse inside a cell, the cursor will be shaped like the Swiss Cross. If you click and drag when this symbol is showing you will select a range of cells.

2. When you point to any border of a cell the cursor changes to a 4-sided arrow (in Excel XP) or an arrow (versions prior to XP). Clicking and dragging with this symbol will drag and drop the contents of the cell (to copy instead of move, hold down the CTRL key as you drag the contents of the cell).

3. In the right-hand bottom corner of every cell there is a small square. If you hold the mouse above this, the cursor changes into a thick plus sign. In this mode, if you click and drag, you are using the FILL HANDLE to fill cells you drag over.

4. If the fill handle is not showing, go to TOOLS, OPTIONS, EDIT and ensure the ALLOW

5. CELL DRAG AND DROP option is enabled.

6. The fill handle is a copying tool for copying formulas, and values; however it is also used to fill in a variety of series such as sequential numbers, dates and mixed text.

Example:-

This entry	Can be extended to
1,2,3	4,5,6,7 etc
January	February, March, April etc
Jan	Feb, Mar, Apr etc
Monday	Tuesday, Wednesday, Thursday etc
Mon	Tue, Wed, Thu etc
Qtr 1	Qtr2, Qtr3, Qtr4
1 June	2 June, 3 June, 4 June etc
01/04/04	02/04/04, 03/04/04, 04/04/04 etc.
1^st period	2nd Period, 3rd Period etc.
9.00 am	10:00 AM, 11:00 AM etc.

F Overview of formulas

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then ads 5 to the result.

=5+2*3

A formula can also contain any or all of the following: functions, references, operators, and constants.

F Using calculation operators in formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using parentheses.

v Types of operators

There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

1. Arithmetic operators

2. Comparison operators

3. Text concatenation operator

4. Reference operators

1. Arithmetic operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition	3+3
– (minus sign)	Subtraction Negation	3–1–1
* (asterisk)	Multiplication	3*3
/ (forward slash)	Division	3/3
% (percent sign)	Percent	20%
^ (caret)	Exponentiation)	3^2

2. Comparison operators

You can compare two values with the following operators. When two values are compared by using this operator.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	=10=5
> (greater than sign)	Greater than	=10>5
< (less than sign)	Less than	=10<5
>= (greater than or equal to sign)	Greater than or equal to	=10>=5
<= (less than or equal to sign)	Less than or equal to	=10<=5
<> (not equal to sign)	Not equal to	=10<>5

Actors, the result are a logical value either TRUE or FALSE.

3. Text concatenation operator

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator	Meaning	Example
& (ampersand)	Connects, or concatenates, two values to produce one continuous text value	="North"&"wind"

4. Reference operators

Combine ranges of cells for calculations with the following operators.

Reference operator	Meaning	Example
: (colon)	Range operator, which produces one reference to all the cells between two references, including the two references	B5:B15
, (comma)	Union operator, which combines multiple references into one reference	SUM(B5:B15,D5:D15)
(space)	Intersection operator, which produces on reference to cells common to the two references	B7:D7 C6:C8

v The order in which Excel performs operations in formulas

In some cases, the order in which calculation is performed can affect the return value of the formula, so it's important to understand how the order is determined and how you can change the order to obtain desired results.

F Calculation order

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=). The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.

F The A1 reference style

The default reference style by default, Excel uses the A1 reference style, which refers to columns with letters (A through XFD, for a total of 16,384 columns) and refers to rows with numbers (1 through 1,048,576). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.

To refer to	Use
The cell in column A and row 10	A10
The range of cells in column A and rows 10 through 20	A10:A20
The range of cells in row 15 and columns B through E	B15:E15
All cells in row 5	5:5
All cells in rows 5 through 10	5:10
All cells in column H	H:H
All cells in columns H through J	H:J
The range of cells in columns A through E and rows 10 through 20	A10:E20

v The difference between absolute, relative and mixed references

F Relative references

A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

F Absolute references

An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

F Mixed references

A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.

F Common Error in Excel

1. ##### Error: When your cell contains this error code, the column isn't wide enough to display the value.

2. #NAME? Error: The #NAME? Error occurs when Excel does not recognize text in a formula.

3. #VALUE! Error: Excel displays the #VALUE! error when a formula has the wrong type of argument.

4. #DIV/0! Error: Excel displays the #DIV/0! Error when a formula tries to divide a number by 0 or an empty cell.

5. #REF! Error:

Excel displays the #REF! error when a formula refers to a cell that is not valid.

1. Cell C1 references cell A1 and cell B1.

2. Delete column B. To achieve this, right click the column B header and click Delete.

3. Select cell B1. The reference to cell B1 is not valid anymore.

5. To fix this error, you can either delete +#REF! in the formula of cell B1 or you can undo your action by clicking Undo in the Quick Access Toolbar (or press CTRL + z).

1. FILE MENU:

Save As Web Page - TO Saves the file in HTML format (a Web page), so that it can be viewed in a Web browser, and sets other options such as the Web page title and location where the file will be saved.
Save Workspace - Saves a list of the open workbooks, their sizes, and their positions on the screen To a workspace file so that the screen will look the same the next time you open the workspace file.
Search - Finds files, Web pages, and Outlook items based on the search criteria you enter.
Web Page Preview - To allow you To preview the current file as a Web page in your browser.
Page Setup - Sets margins, paper source, paper size, page orientation, and other layout options for the active file.

PRINT AREA:

Set Print Area - Defines the selected range as the print area, which is the only portion of the worksheet that will be printed.
Clear/Reset Print Area - Deletes the print area in the active sheet.

Prints Preview - Shows how a file will look when you print it.
Print - Prints the active file or selected items. To select print options, on the File menu, click Print.
Mail Recipient - In Microsoft Excel, sends the contents of the current worksheet as the body of the e-mail message.
Mail Recipient For Review - Sends the active document for review, creates a review request form, and enables and displays the reviewing Tools when a reviewer receives the document. If the document is stored in a shared location, the e-mail message will contain a link To the file To be reviewed.
Mail Recipient - Sends the entire document, presentation, or workbook as an attachment To an e-mail message.
Routing Recipient - TO sends the active file To reviewers To change and add comments. To use routing, you and those you are sending the document To must have installed both the application in which the document was created and Microsoft Exchange or Microsoft Outlook or a compatible mail package.
Exchange Folder - Sends this file To the Microsoft Exchange Folder you specify.
Properties - Displays the property sheet for the active file.

2. EDIT MENU:

Paste Special - To Pastes, links, or embeds the Clipboard contents in the current file in the format you specify.

FILL:

§ Down - TO Copies the contents and formats of the Topmost cells of a selected range inTo the cells below.

§ Right - TO Copies the contents and formats of the leftmost cell or cells of a selected range inTo the cells To the right. .

§ Up - TO Copies the contents and formats of the botTom cell or cells of a selected range inTo the upper cells of the range.

§ Left - TO Copies the contents and formats of the rightmost cell or cells of a selected range inTo the cells To the left.

Across Worksheets - TO Copies the contents of the range of cells selected on the active worksheet To the same range of cells on all the other selected sheets in a group.
Justify - TO redistributes the text contents of cells To fill the selected range.

Clear All – To Removes all cell contents and formatting, including comments and hyperlinks, from selected cells.

§ Formats - TO Removes only the formatting from your selection; the content and comments are unchanged.

§ Contents - TO Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.

§ Comments - TO Removes only the comments attached To selected cells; cell contents and formats are unchanged.

Delete - To removes the selected object. In Outlook, removes the selected item from the view and moves it To the Deleted Items folder.
Delete Sheet - To Deletes the selected sheets from the workbook. Through this command, we can delete the sheet permanently.
Go To - In Microsoft Excel, scrolls through the worksheet and selects the cell, range, or cells with special characteristics you specify.

3. VIEW MENU

Normal - To Switches To normal view, which is the default view for most tasks in Microsoft Excel, such as entering data, filtering, charting, and formatting?

Page Break Preview - To Switches the active worksheet To page break preview, which is an editing view that displays your worksheet as it will print. In page break preview, you can move page breaks by dragging them left, right, up, or down.
Task Pane - To displays the task pane, an area where you can create new files, search for information, view the contents of the clipboard, and perform other tasks.
Toolbars - To Displays or hides Toolbars. To display a Toolbar, select the check box next To the Toolbar name.
Formula Bar - To Displays or hides the formula bar.
Ruler - To Displays or hides the horizontal ruler, which you can use To position objects, change paragraph indents, page margins, and other spacing settings.
Status Bar - To Shows or hides the status bar.
Header and Footer - To Adds or changes the text that appears at the Top and bottom of every page or slide.
Comments - To put a comment on the selected cell and put the any message. We can hide and show the comment on the selected cell.
Custom Views - To Creates different views of a worksheet. A view provides an easy way To see your data with different display options. You can display, print, and store different views without saving them as separate sheets.
Full Screen - To Hides most screen elements so that you can view more of your document. To switch back To your previous view, click Full Screen or press ESC.
Zoom - To Controls how large or small the current file appears on the screen.

4. INSERT MENU:

Insert Cells - To Inserts cells starting at the insertion point. You can choose To shift other cells in the table To the right or down. You can also insert an entire row or column.

Insert Rows - To inserts the number of cells, rows, or columns you select. In Word, this command is available only when you've selected one or more end-of-cell marks.
Insert Columns - To Inserts the number of cells, rows, or columns you select. In Word, this command is available only when you've selected one or more end-of-cell marks.
Worksheet - To inserts a new worksheet To the left of the selected sheet.
Chart Wizard - To Starts the Chart Wizard, this guides you through the steps for creating an embedded chart on a worksheet or modifying an existing chart.
Symbol - To Inserts symbols and special characters from the fonts that are installed on your computer.

Page Break - To Inserts a page break above a selected cell. This command changes To Remove Page Break if you have a cell selected that is adjacent To a manually inserted page break.
Insert Function - To Displays a list of functions and their formats and allows you To set values for arguments.
Define - To Creates a name for a cell, range, or constant or computed value that you can use To refer To the cell, range, or value.
Paste - To inserts the selected name into the formula bar. If the formula bar is active and you begin a formula by typing an equal sign (=), clicking Paste pastes the selected name at the insertion point. If the formula bar is not active, double-clicking a name in the Paste Name box pastes an equal sign (=) followed by the selected name into the formula bar.
Create - To Creates names by using labels in a selected range.
Comment - Inserts a comment at the insertion point.

PICTURE:

§ Clip Art - To Opens the Clip Gallery where you can select the clip art image you want To insert in your file or update your clip art collection.

§ Picture (Insert menu)

§ From File - To inserts an existing picture in the active file at the insertion point.

§ From Scanner or Camera - To Loads an image by using a scanner or digital camera and then inserts the resulting picture at the insertion point.

§ Picture Organization Chart - To Inserts a Microsoft Organization Chart object into your presentation, document, or worksheet.

§ Auto Shapes Menu - TO Displays the AutoShaped categories you can insert. Click an AutoShaped category, click the AutoShaped you want, and then click or drag in the active window where you want To insert the AutoShaped.

§ Insert WordArt - To Creates text effects by inserting a Microsoft Office drawing object.

§ Insert Diagram - To Creates an organization chart or a cycle, radial, pyramid, Venn, or target diagram in your document.

Object - To inserts an object ¾ such as a drawing, WordArt text effect, or an equation ¾ at the insertion point.
Hyperlink - To inserts a new hyperlink or edits the selected hyperlink.

5. FORMAT MENU

Cells - To apply formats to the selected cells. This command might not available if the sheet is protected.
ROW:

Height -
TO Changes the height of the selected rows. You need To select only one cell in a row To change the height for the entire row.
Auto Fit -
TO adjusts the row height To the minimum necessary To display the height of the tallest cell in the selection. If you change the cell contents later, you must fit the selection again.
Hide - To Hides the selected rows or columns.
Unhide - To Displays rows or columns in the current selection that were previously hidden.

COLUMN:

Width - TO Changes the width of the selected columns. You need to select only one cell in a column To change the width for the entire column.
Auto Fit Selection - TO Adjusts the column width to the minimum necessary to display the contents of the selected cells.
Hide - TO Hides the selected rows or columns. Hiding rows or columns does not delete them from the worksheet.
Unhide - TO Displays rows or columns in the current selection that were previously hidden.
Standard Width - TO Changes the standard width of columns on a worksheet.

SHEET:

§ Rename - TO renames the active sheet.

§ Hide - TO Hides the active sheet. The sheet remains open and accessible to other sheets, but it is not visible.

§ Unhide - TO Displays any hidden sheets.

§ Background - TO inserts a tiled graphic image in the worksheet background, based on the bitmap you select.

§ TAB COLOR - To set the color of the sheet by this command. We can make the different identification by the tab color.

Auto Format - To Applies a built-in combination of formats, called an auto format, To a cell range or a PivotTable report. If a single cell is selected, Microsoft Excel automatically selects the range surrounded by blank cells and applies the auto format To that range.
Conditional Formatting - To Applies formats to selected cells that meet specific criteria based on values or formulas you specify.
Style - To Defines or applies To the selection a combination of formats, called a style.
Spelling - To Checks spelling in the active document, file, workbook, or item.
Speech - To Sets up and customizes speech recognition for dictating text, as well as selecting menu, Toolbar, and dialog box items.
Show Text To Speech Toolbar - To Displays or removes the Speak On Enter, Stop Speaking, By Rows, By Columns, and Speak On Enter buttons.
Shared Workbooks - To Switches To shared workbook mode, which allows you and other users on your network To edit and save changes To the same workbook.
Highlight Changes - To Highlights changes To cell contents in a shared workbook, including moved and pasted contents and inserted and deleted rows and columns.
Track Changes Accept or Reject Changes - To Finds and selects each tracked change in a document so that you can review, accept, or reject the change.
Protect Sheet - To Prevents changes To cells on worksheets, items in a chart, graphic objects on a worksheet or chart sheet, or code in a Visual Basic EdiTor form.
Allow Users To Edit Ranges - To Allows you To designate users, computers or groups who may make changes To the specified cells without entering a password.
Protect Workbook - To protect a workbook's structure and windows. You can prevent changes To the structure of a workbook so that sheets can't be deleted, moved, hidden, unhidden, or renamed, and new sheets can't be inserted.
Protect for Sharing - To Protects the sharing and change history tracking in a shared workbook so the features can't be turned off. If you select this check box and click OK when the workbook isn't a shared workbook, you're asked if you want to save it as a shared workbook.
Meet now (on line meeting) – To starts an impromptu online meeting by sending an invitation to participants. The participants you invite to the meeting must be running Microsoft NetMeeting on their computers.
Schedule Meeting - To Schedules an online meeting by using names from the address book of your e-mail program and checks the availability of the meeting participants.
Web Discussions - To Displays the Discussions Toolbar, where you can insert a new discussion about the file or perform other discussion tasks.
Goal Seek - To Adjusts the value in a specified cell until a formula that is dependent on that cell reaches a target value.
Scenarios - To Creates and saves scenarios, which are sets of data you can use To view the results of what-if analyses.
Trace Precedents (formula) - To Draws tracer arrows from the cells that supply values directly To the formula in the active cell (precedents). To trace the cells that supply values indirectly To the formula in the active cell, click the Trace Precedents button again.

6. TOOLS MENU:

Tools on the Web - Connects you To Microsoft Office Tools on the Web for information about integrated eservices that are available.
Macros - Opens the Macros dialog box, where you can run, edit, or delete a macro. Use Record New Macro To record a series of actions as a macro, or click Visual Basic Editor to write a macro.
Record New Macro - To Records a series of actions as a macro that you can later "play back."
Add-Ins -To Specifies which add-ins are automatically available when you start Microsoft Office. You can load or unload add-ins that come with Microsoft Office as well as add-in programs that you create.
AutoCorrect Options - To Sets the options used To correct text automatically as you type, or To store and reuse text and other items you use frequently.
Customize - To Customizes Toolbar buttons, menu commands, and shortcut key assignments.
Options - To Modifies settings for Microsoft Office programs such as screen appearance, printing, editing, spelling, and other options.

7. DATA MENU:

Sort - To arrange the information in selected rows or lists alphabetically, numerically, or by date.
Auto Filter - To The quickest way To select only those items you want To display in a list.

Advanced Filter - To Filters data in a list so that only the rows that meet a condition you specify by using a criteria range are displayed.
Show All - To displays all of the rows in a filtered list.
Form - To displays a data form in a dialog box. You can use the data form to see, change, add, delete, and find records in a list or database.
Subtotals - To Calculates subtotal and grand Total values for the labeled columns you select.
Validation - To Defines what data is valid for individual cells or cell ranges; restricts the data entry to a particular type, such as whole numbers, decimal numbers, or text; and sets limits on the valid entries.
Table - To Creates a data table based on input values and formulas you define. Data tables can be used To show the results of changing values in your formulas.
Convert Text To Table - To converts the selected text to a table.
Consolidate - To Summarizes the data from one or more source areas and displays it in a table.
Hide Detail - In a PivotTable or PivotChart report, hides displayed detail data.
On an outlined worksheet, hides the detail rows or columns of a selected summary row or column.
PivotTable and PivotChart Report - To Starts the PivotTable and PivotChart Wizard, which guides you through creating or modifying a PivotTable or PivotChart report?

8. Window Menu:

New Window - To opens a new window with the same contents as the active window so you can view different parts of a file at the same time.
Arrange - To Displays all open files in separate windows on the screen. The Arrange command makes it easier to drag between files.
Hide - To Hides the active workbook window. A hidden window remains open.
Unhide - To Displays hidden workbook windows.
Split - To splits the active window into panes, or removes the split from the active window.
Freeze Panes - Freezes the Top pane, the left pane, or both on the active worksheet. Use the Freeze Panes button to keep column or row titles in view while you're scrolling through a worksheet. Freezing titles on a worksheet does not affect printing.

9. HELP MENU:

· Show or hide the Office Assistant - To Displays or removes the Office Assistant from view.

Microsoft Excel Functions

v Math & Trig Functions

1. ABS: Returns the absolute value of a number. The absolute value of a number is the number without its sign.

Syntax: ABS (number)

Example: =ABS (-4) Ans : 4

2. SIN: Returns the sine of the given angle.

Syntax: SIN (number)

Example: =SIN(30*PI()/180) Ans: 0.5 (Sine of 30 degrees ½)

3. COS: Returns the cosine of the given angle.

Syntax : COS(number)

Example: =COS(60*PI()/180) Ans: 0.5 (Cosine of 60 degrees ½)

4. TAN: Returns the tangent of the given angle.

Syntax : TAN(number)

Example: =TAN(45*PI()/180) Ans: 1 (Tangent of 45 degrees 1)

5. DEGREES: Converts radians into degrees.

Syntax: DEGREES(angle)

Example: =DEGREES(PI( )) Ans : 180

6. EVEN: Returns number rounded up to the nearest even integer.

Syntax: EVEN(number)

Example: =EVEN(3) Ans: 4

7. EXP: Returns ‘e’ raised to the power of number. The constant ‘e’ equals 2.71828182845904, the base of the natural logarithm.

Syntax: EXP(number)

Number is the exponent applied to the base ‘e’.

Example: =EXP(2) Ans: 7.389056

FACT: Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.

Syntax: FACT(number)

Example: =FACT(5) Ans: 120 (5! = 5×4×3×2×1=120)

9. GCD: Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.

Syntax: GCD(number1,number2, ...)

Number1, number2, ... are 1 to 255 values. If any value is not

an integer, it is truncated.

Example: =GCD(25,15) Ans: 5

10. INT: Rounds a number down to the nearest integer.

Syntax: INT(number)

Example: =INT(8.9) Ans: 8

11. LCM: Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on.

Syntax: LCM (number1, number2…)

Example: =LCM(5, 2) Ans: 10

12. LOG: Returns the logarithm of a number to the base you specify.

Syntax: LOG(number,base)

· Number is the positive real number for which you want the logarithm.

· Base is the base of the logarithm. If base is omitted, it is assumed to be 10.

Example: =LOG(2,10) Ans: 0.30103

13. LOG10: Returns the base-10 logarithm of a number.

Syntax: LOG10(number)

Number is the positive real number for which you want the base-10 logarithm.

Example: =LOG10(2) Ans: 0.30103

14. MOD: Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Syntax: MOD (number, divisor)

Example: =MOD(5, 2) Ans: 10

15. ODD: Returns number rounded up to the nearest odd integer

Syntax: odd(number)

Example: =odd(2) Ans: 3

16. PI: Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.

Syntax: PI( )

Example: =PI ( ) AnS: 3.14159265358979

17. POWER: Returns the result of a number raised to a power.

Syntax: POWER (number, power)

Example: =POWER (5, 2) Ans: 25 (5² = 25)

18. PRODUCT: Multiplies all the numbers given as arguments and returns the product.

Syntax: PRODUCT (number1, number2…)

Example: =PRODUCT (5, 4, 2) Ans: 40

19. ROMAN: Converts an arabic numeral to roman, as text.

Syntax: ROMAN (number, [form])

Example: =ROMAN(20,2) Ans: XX

20. ROUND: Let's say you want to round a number to the nearest whole number because decimal values are not significant to you or round a number to multiples of 10 to simplify an approximation of amounts.

Syntax: ROUND(number, num_digits)

Example: =ROUND(12.3456,2) Ans: 12.35

21. SQRT: Returns a positive square root.

Syntax: SQRT(number)

Example: = SQRT(16) Ans: 4

22. SUM: Adds all the numbers in a range of cells.

Syntax: SUM(number1, number2, number3…)

Example: =SUM(4,5,6) Ans: 15

23. SUMIF: Adds the cells specified by a given criteria.

Syntax: SUMIF(range, criteria, sum_ range)

Example:

A	B
Property Value	Commission
100,000	7,000
200,000	14,000
300,000	21,000
400,000	28,000
Formula	Description (Result)
=SUMIF(A2:A5,">160000",B2:B5)	· Sum of the commissions for property values over 160,000 Ans: 63,000
=SUMIF(A2:A5,">160000")	· Sum of the property values over 160,000 Ans: 900,000
=SUMIF(A2:A5,"=300000",B2:B3)	· Sum of the commissions for property values equal to 300,000 Ans: 21,000

24. TRUNC: Truncates a number to an integer by removing the fractional part of the number.

Syntax: TRUNC(number,num_digits)

· Number is the number you want to truncate.

· Num_digits is a number specifying the precision of the truncation. The default value for num_digits is 0 (zero).

Example: =TRUNC(1256.5687,2) Ans: 1256.56

v Logical Functions

1. AND: Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

Syntax: AND(logical1,logical2, ...)

Example: =AND(5>2,6>3) Ans: True

=AND(5<2,6>3) Ans: False

=AND(5>2,6<3) Ans: False

=AND(5<2,6<3) Ans: False

2. IF: Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

Syntax: IF(logical_test,value_if_true,value_if_false)

Example: =IF(10>100,"Excellent","Not Excellent") Ans: Not Excellent

3. NOT: Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

Syntax: NOT(logical)

Example: =NOT(5>2) Ans: False

=NOT(5<2) Ans: True

4. OR: Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

Syntax: OR(logical1,logical2,...)

Example: =OR(5>2,6>3) Ans: True

=OR(5<2,6>3) Ans: True

=OR(5>2,6<3) Ans: True

=OR(5<2,6<3) Ans: False

v Statistical Functions

1. AVERAGE: Returns the average (arithmetic mean) of the arguments.

Syntax: AVERAGE(number1,number2,...)

Number1, number2, ... are 1 to 255 numeric arguments for which you want the average.

	A
1	Data
2	10
3	7
4	9
5	27
6	2

Example: =average(A2:A6) [from table 1] Ans: 11

	A
1	Data
2	Sales
3	6/9/1990
4
5	19
6	22.24
7	True
8	#DIV/0!

2. COUNT: Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that is in a range or array of numbers.

Syntax: COUNT(value1,value2,...)

value1, value2, ... are 1 to 255 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Example: =COUNT(A2:A8) [from table 2]

Counts the number of cells that contain numbers in the list.

Table 2

Ans: 3

3. COUNTA: Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax: COUNTA(value1,value2,...)

value1, value2, ... are 1 to 255 arguments representing the values you want to count.

Example: =COUNTA(A2:A8) [from table 2] Ans: 6

4. COUNTBLANK: Counts empty cells in a specified range of cells.

Syntax: COUNTBLANK(range)

Range is the range from which you want to count the blank cells.

Example: =COUNTBLANK(A2:A8) [from table 2] Ans: 1

5. COUNTIF: Counts the number of cells within a range that meet the given criteria.

Syntax: COUNTIF(range,criteria)

· Range is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

· Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Example:

	A	B
1	Data	Data
2	Apples	32
3	Oranges	54
4	Peaches	75
5	Apples	86

Table 3

[from table 3]

=COUNTIF(A2:A5,"apples") Ans: 2

=COUNTIF(A2:A5,A4) Ans: 1

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) Ans: 3

=COUNTIF(B2:B5,">55") Ans: 2

=COUNTIF(B2:B5,"<>"&B4) Ans: 3

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") Ans: 3

6. 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: =MAX(A2:A7) [from table 4]

	A
1	Data
2	20
3	50
4	40
5	30
6	10
7	30

Ans: 50

7. MIN: Returns the smallest number in a set of values.

Syntax: MIN(number1,number2,...)

number1, number2, ... are 1 to 255 numbers for which you want to find the minimum value.

Example: =MIN(A2:A7) [from table 4] Ans: 20

8. RANK: Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)

Syntax: RANK(number,ref,order)

· Number is the number whose rank you want to find.

· Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

· Order is a number specifying how to rank number.

o If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.

o If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

Example: =RANK(G8,G3:G11,1)

Data

3.5

Table 5

[from table 5]

=RANK(A3,A2:A6,1) Rank of 3.5 in the list above Ans: 3

=RANK(A2,A2:A6,1) Rank of 7 in the list above Ans: 5

v Date & Time Functions

1. DATE: Returns the sequential serial number that represents a particular date.

Syntax: DATE(year,month,day )

Example: =DATE (1990,09,06) Ans: 9/6/1990

2. DATE VALUE: Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.

Syntax: DATEVALUE(date_text)

Date_text is text that represents a date in a Microsoft Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates.

Example: =DATEVALUE("09/06/1990") Ans: 33122

3. DAY: Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax: DAY(serial_number)

Example: =DAY(“6-September-1990”) Ans: 6

4. HOUR: Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Syntax: HOUR(serial_number)

Example: =HOUR(“2:30:25”) Ans: 2

5. MINUTE: Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

Syntax: MINUTE (serial_number)

Example: =MINUTE (“2:30:25”) Ans: 30

6. MONTH: Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Syntax: MONTH(serial_number)

Example: =MONTH(“6-September-1990”) Ans: 9

7. SECOND: Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

Syntax: SECOND(serial_number)

Example: =SECOND(“2:30:25”) Ans: 25

8. NOW: Returns the serial number of the current date and time.

Syntax: NOW( )

Example: =NOW( ) Ans: 9/6/1990 14:32

9. TIME: Returns the decimal number for a particular time.

Syntax: TIME(hour,minute,second )

Example: =TIME (2,30,16) Ans: 2:30 AM

10. TODAY: Returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.

Syntax: TODAY( )

Example: TODAY( ) Ans: 09/06/1990

11. WEEKDAY: Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax: WEEKDAY(serial_number,return_type)

· Serial_number is a sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

· Return_type is a number that determines the type of return value.

Example: =WEEKDAY("9/6/1990",2) Ans: 4

12. YEAR: Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.

Syntax: YEAR(serial_number)

Example: =YEAR(“9/6/1990”) Ans: 1990

v Text Functions

1. CHAR: Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.

Syntax: CHAR(number)

Example: =CHAR(65) Ans: A

=CHAR(90) Ans: Z

2. CODE: Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer

Syntax: CODE(text)

Example: =CODE(A) Ans: 65

=CODE(Z) Ans: 90

3. CONCATENATE: Joins two or more text strings into one text string.

Syntax: CONCATENATE (text1, text2...)

Example: =CONCATENATE (“WEL”,”COME”)

Ans: WELCOME

4. DOLLAR: The function described in this Help topic converts a number to text format and applies a currency symbol. The name of the function (and the symbol that it applies) depends upon your language settings.

This function converts a number to text using currency format, with the decimals rounded to the specified place. The format used is ($#,##0.00_);($#,##0.00).

Syntax: DOLLAR (number, decimals)

Example: =DOLLAR(1234.567,2) Ans: $1234.57

5. EXACT: Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores

formatting differences. Use EXACT to test text being entered into a document.

Syntax: EXACT (text1, text2, ……..)

Example: =EXACT(“a”,”A”) Ans: False

=EXACT(“A”,”A”) Ans: True

6. FIND: FIND locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Syntax: FIND (find_text, within_text,start_num)

· Find_text is the text you want to find.

· Within_text is the text containing the text you want to find.

· Start_num specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Example: =FIND("i","India",1) Ans: 4

7. LEFT: LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Syntax: LEFT (text, num_chars)

· Text is the text string that contains the characters you want to extract.

· Num_chars specifies the number of characters you want LEFT to extract.

Example: =LEFT (“RAMAYAN”,3) Ans: RAM

8. LEN: LEN returns the number of characters in a text string.

Syntax: LEN(text)

Text is the text whose length you want to find. Spaces count as characters.

Example: =LEN("Computer") Ans: 8

9. LOWER: Converts all uppercase letters in a text string to lowercase.

Syntax: LOWER(text)

Example: =LOWER(“COMPUTER”) Ans: computer

10. MID: MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntax: MID(text,start_num,num_chars)

· Text is the text string containing the characters you want to extract.

· Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

· Num_chars specifies the number of characters you want MID to return from text.

Example: =MID (“RAMAYAN”,3,4) Ans: MAYA

11. PROPER: Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Syntax: PROPER(text)

Example: =PROPER(“COMPUTER”) Ans: Computer

12. REPLACE: REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.

Syntax: REPLACE(old_text,start_num,num_chars,new_text)

· Old_text is text in which you want to replace some characters.

· Start_num is the position of the character in old_text that you want to replace with new_text.

· Num_chars is the number of characters in old_text that you want REPLACE to replace with new_text.

Example: =REPLACE (“Input”,1,2,“Out”) Ans: Output

13. REPT: Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax: REPT(text,number_time)

· Text is the text you want to repeat.

· Number_times is a positive number specifying the number of times to repeat text.

Example: =REPT (“*”,10) Ans: **********

14. RIGHT: RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

Syntax: RIGHT (text,num_chars)

· Text is the text you want to repeat.

· Number_times is a positive number specifying the number of times to repeat text.

Example: =RIGHT (“RAMAYAN”,3) Ans: YAN

15. TRIM: Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Syntax: TRIM(text)

Text is the text from which you want spaces removed.

Example: =TRIM(“I LOVE MY INDIA”) Ans: I LOVE MY INDIA

16. UPPER: Converts all uppercase letters in a text string to lowercase.

Syntax: UPPER(text)

Example: =UPPER(“Computer”) Ans:COMPUTER

Microsoft Excel – 2003 Shortcut Key

F2 : Edit the selected cell

F5 : Go to a specific cell

F7 : Spell check selected text and/or document

F11 : Create chart

Ctrl + Shift + ; : Enter the current time

Ctrl + ; : Enter the current date

Alt + Shift + F1: Insert new worksheet

Shift + F3 : Open the Excel® formula window

Shift + F5 : Bring up search box

Ctrl + A : Select all contents of worksheet

Ctrl + B : Bold highlighted selection

Ctrl + I : Italicize highlighted selection

Ctrl + C : Copy selected text

Ctrl + V : Paste

Ctrl + D : Fill

Ctrl + K : Insert link

Ctrl + F : Open find and replace options

Ctrl + G : Open go-to options

Ctrl + H : Open find and replace options

Ctrl + U : Underline highlighted selection

Ctrl + Y : Underline selected text

Ctrl + 5 : Strikethrough highlighted selection

Ctrl + O : Open options

Ctrl + N : Open new document

Ctrl + P : Open print dialog box

Ctrl + S : Save

Ctrl + Z : Undo last action

Ctrl + F9 : Minimize current window

Ctrl + F10 : Maximize currently selected window

Ctrl + F6 : Switch between open workbooks/windows

Ctrl + Page up:

& Page Down : Move between Excel® worksheets in the same document

Ctrl + Tab : Move between two or more open Excel® files

Alt + = : Create formula to sum all of above cells

Ctrl + ‘ : Insert value of above cell into current cell

Ctrl + Shift + ! : Format number in comma format

Ctrl + Shift + $ : Format number in currency format

Ctrl + Shift + # : Format number in date format

Ctrl + Shift + % : Format number in percentage format

Ctrl + Shift + ^ : Format number in scientific format

Ctrl + Shift + @: Format number in time format

Ctrl + g : Move to next section of text

Ctrl + Space : Select entire column

Shift + Space : Select entire row

Computer Education

Thursday, 17 November 2016

Excel 2003 Notes in English

No comments:

Post a Comment

Blog Archive