2007 MICROSOFT EXCEL INTERMEDIATE

1. 2007 MICROSOFT EXCELINTERMEDIATE CONCEPTS Matthew Jordan Jordan@TCCSA.Net

2. CELL ADDRESSING • RELATIVE • ABSOLUTE • MIXED

3. RELATIVE ADDRESSING • A relative address in a formula will change when it is copied to another location on the worksheet. • Example =d4 + e4

4. ABSOLUTE ADDRESSING • An absolute address in a formula does not change when it is copied to another location on the worksheet. The “$” sign locks the row and column. • Example =$d$4 + $e$4

5. MIXED ADDRESSING • A mixed address in a formula allows a row or a column to change when it is copied to another location on the worksheet. The “$” sign locks the row or column. • Example =$d4 + $e4

6. MULTI-DIMENSIONAL REFERENCES • Formulas can span multiple worksheets within a workbook • An ‘!’ in a reference separates a sheet name from a cell address • Ex) =Config!A3

7. VLOOKUP • Formula begins with an equal “=“ sign • VLOOKUP searches for a value in the left-most column of a table, and then returns a value in the same row from a column specified in the table. • Use Insert Function Dialog Box

8. VLOOKUP • Example • VLOOKUP(lookup_value,table,col_index_num,range_lookup) • lookup_value - Refers to the cell that contains the value you want to look for. • table_array - Refers to the range that contains both the data you are looking for, and the data you want to return. • col_index_num - Refers to the column number within the table array range that houses the data you want returned. • range_lookup - This value specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate or exact match can be returned. For this to work properly, the values in the first column of table_array must be placed in ascending sort order. If FALSE, VLOOKUP will find only an exact match.

9. LOGIC TESTING • Performs an action based on the value of true or false • If tests can be nested 64 levels deep • Operators • Example =If(A5=3,”OK”,”Error”)

10. LOGIC CALCULATIONS • SUMIF • COUNTIF

11. SUMIF • THE SUM CALCULATION IS PERFORMED BASED ON A LOGIC STATEMENT • SUMIF(range,criteria,sum_range) • EXAMPLE (=SUMIF(G4:G8,”>=30”,H4:H8)

12. COUNTIF • THE COUNT CALCULATION IS PERFORMED BASED ON A LOGIC STATEMENT • COUNTIF(range,criteria) • EXAMPLE (=COUNTIF(h4:h8,”RETIRE”)

13. CONDITIONAL FORMATTING • Conditional Formatting involves formatting based on a chosen condition • Select the Home tab from the ribbon and Conditional Formatting from the Styles section STRESSED?

14. PASTE SPECIAL • After copying data, you can use the Paste Special command (Home tab >> select paste dropdown arrow) to paste specific cell contents or attributes such as formulas, formats, or comments from the Clipboard into an Excel worksheet. 

15. PASTE SPECIAL

16. PASTE SPECIAL

17. MAIL MERGE • Use mail merge when you want to create a set of documents that are essentially the same but where each document contains unique elements. • We will work with MS Word and Excel.

20. MAIL MERGE Three Parts: • Main Document – Consists of data that is the same for all documents and Data source Merge Fields • Data Source – Unique data in each document • Finished Set of Documents – Individual letters

21. Mail Merge Steps • Select Document Type • Select Starting Document • Select Recipients • Write your Letter • Preview your Letters • Complete the Merge