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

Download Microsoft Excel XP/2003 Fonctions Cheat Sheet and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity! 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 Text to Columns… to open the wizard. 3. Step 1 of 3:Be sure Delimited 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 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, Level 300 Become a Microsoft Excel Power-User page 2 Shared Computing Services t Use HLOOKUP You must have a data table for which to lookup information indexed in horizontal rows. =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 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) Use VLOOKUP You must have a data table for which to lookup information indexed in vertical columns. =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 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) Use Data Validation 1. Select the cell(s) for which you will define the data validation criteria. 2. Go to Data Validation… to open the Data Validation dialog box. The Settings tab is active. 3. Click the Allow: arrow to display a list of options, and then select the option you desire. 4. Click the Da a: arrow to display a list of conditional operators, and then select the option you desire. Complete the remaining fields that are pertinent to your choices. 5. Click the Input Message tab and click in the Title: field. Type the title of the dialog box to appear. 6. Click the Input Message: field and type the message you’d like the dialog box to display. 7. Click the Error Alert tab, click the Style: arrow to choose an icon that will display. 8. Click the Title: field and type the title to appear. 9. Click the Error Message: field and type the message you’d like the dialog box to display. 10. Click OK . • Click Circle Invalid Data on the Auditing toolbar to locate cells that don’t meet the validation criteria. • Click Clear Validation Circles on the Auditing toolbar to clear the circle. Use Data Validation with Lookup Tables When creating the data validation, use a formula to reference the lookup table’s column in the Source: field. Visual Basic Record a Macro 1. Go to Tools Macro Record New Macro… to record your actions for the macro. 2. Give the macro a name and description and click Close 3. Perform the actions you would like recorded. 4. Click Stop on the Macros toolbar when finished OR go to Tools Macro Stop Recording. • To run a macro, press the shortcut key if you provided one or go to Tools Macro Macros…, select the macro and click Run . You may also assign the macro to a button as described below. Edit a Macro • Open the Visual Basic window by going to Tools Macro Macros… OR press ALT+F8. You can edit any macro using Visual Basic for Applications (VBA) code. Debug a Macro • Open the Visual Basic window by going to Tools Macro Macros… • Select the macro under Macro Name: and click Step Into . • Go to Debug Step Into OR press F8 to move to execute the next line of code.