Microsoft Excel XP/2003 Fonctions Cheat Sheet | Cheat Sheet MS Microsoft Excel skills

SHARED COMPUTING SERVICES

Microsoft Excel XP/2003, Level 300

Become a Microsoft Excel Power-User

Updated 12/14/05

Logical Functions

Use the IF Function

=IF(Statement,IFtrue,IFfalse)

Nest IF functions in the IFfalse

area of the parent function.

Statement

- comparison statement to which you try to match.

IFtrue

- result if the comparison matches the statement.

IFfalse

- result if the comparison does not match the statement.

Combine Logical Functions

=IF(AND(Statement1,Statement2),IFtrue,IFfalse) —

Ensures that both statements must be true

=IF(OR(Statement1,Statement2),IFtrue,IFfalse) —

Tests both statements, either of which can be true

=IF(NOT(Statement),IFtrue,IFfalse) —

Returns the reverse value fur the condition

Concatenate & Parse Cells (Merge & Split)

Concatenate (Merge) Data in Separate Cells

A B C

1 John Smith Smith, John

=CONCATENATE(B1,“, ”,A1)

2 Jane Jones Jones, Jane

=B2&“, ”&A2

Parse (Split) Data into Separate Cells

1. Select the cells to parse (split)

2. Go to Data J Text to Columns… to open the wizard.

Step 1 of 3

:Be sure

Delimited

is selected, then click Next >

Step 2 of 3

:Select the delimiter type, then click Next >

Step 3 of 3

: Choose the data type for the column if necessary, then click Finish

 When passing the interval code

to the DATEDIF function, enclose it

in quotes if you are passing a literal

value to the function.

Use the DATEDIF Function

Syntax: =DATEDIF(Date1,Date2,Interval)

Date1

— first date, in standard Excel serial-date format.

Date2

— second date, in standard Excel serial-date format.

Interval

— unit of time for the result.

Date1 must be ≤ Date2 or a #NUM! error will be returned. If either Date1 or Date2 is not a date, a #VALUE! error will be returned.

Interval must be one of the following codes:

Code Meaning Description

"m" Months Number of complete months between Date1 and Date2.

"d Days Number of days between Date1 and Date2.

"y" Years Number of complete years between Date1 and Date2.

"ym" Months Excluding Year Number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years Number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months & Years Number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and year.

When passing the interval code to the DATEDIF function, enclose it in quotes if you are passing a literal value to the function.

Microsoft Excel XP/2003 Fonctions Cheat Sheet, Cheat Sheet of MS Microsoft Excel skills

Related documents

Partial preview of the text

Download Microsoft Excel XP/2003 Fonctions Cheat Sheet and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!

Become a Microsoft Excel Power-User

Statement - comparison statement to which you try to match.

IFtrue - result if the comparison matches the statement.

IFfalse - result if the comparison does not match the statement.

=IF(AND(Statement1,Statement2),IFtrue,IFfalse) — Ensures that both statements must be true.

=IF(OR(Statement1,Statement2),IFtrue,IFfalse) — Tests both statements, either of which can be true.

=IF(NOT(Statement),IFtrue,IFfalse) — Returns the reverse value fur the condition.

Concatenate (Merge) Data in Separate Cells

A B C

Parse (Split) Data into Separate Cells

3. Step 1 of 3:Be sureDelimited is selected, then click Next >

4. Step 2 of 3:Select the delimiter type, then click Next >

5. Step 3 of 3: Choose the data type for the column if necessary, then click Finish

Date1 — first date, in standard Excel serial-date format.

Date2 — second date, in standard Excel serial-date format.

Interval — unit of time for the result.

t

lookup_value is the reference or address of the comparison cell.

table_array is the data table range that is searched (including row labels).

row_index_num indicates how many rows to move down.

range_lookup is a logical argument that returns a true or false value (optional)

lookup_value is the reference or address of the comparison cell.

table_array is the data table range that is searched (including column labels).

col_index_num indicates how many columns to move the right.

range_lookup is a logical argument that returns a true or false value (optional)

2. Go to DataJValidation… to open theData Validation dialog box. TheSettings tab is active.

3. Click theAllow: arrow to display a list of options, and then select the option you desire.

4. Click theDa a: arrow to display a list of conditional operators, and then select the option you desire. Complete the remaining

5. Click theInput Message tab and click in theTitle: field. Type the title of the dialog box to appear.

6. Click theInput Message: field and type the message you’d like the dialog box to display.

7. Click theError Alert tab, click theStyle: arrow to choose an icon that will display.

8. Click theTitle: field and type the title to appear.

9. Click theError Message: field and type the message you’d like the dialog box to display.

Use Data Validation with Lookup Tables

When creating the data validation, use a formula to reference the lookup table’s column in theSource: field.

OR go to ToolsJMacroJStop Recording.

ToolsJMacroJMacros…OR press ALT+F8. You can edit

(^) Displays a digit, if one exists in the position.

1. Go to ToolsJCustomize… J Commands tab.

2. Scroll to and select New Menu underCategories:

3. Click and hold New Menu underCommands: and drag it up to the menu bar in any location you desire.

1. Go to ToolsJCustomize… J Toolbars tab.

2. Click theCommands tab and select a category and drag the command of your choice to the new toolbar.

1. Edit any button while theCustomize dialog box is still open by right-clicking any button and select Edit Button Image.

4. Reset any toolbar to the default by going to ToolsJCustomize… J Toolbars tab and click Reset.

1. Tools J Options J Edit tab.