Microsoft Excel (Part I)

1. Microsoft Excel(Part I) 11 June 2009 John Chan GP Batteries International Limited

2. Agenda • Basic Features • Working with Functions（函數） • Tips • Q&A

3. Basic Features • Freeze Panes（凍結窗格） • Filter（篩選） • Paste Special（選擇性貼上） • Cell Reference • Print Options

4. Basic Features – Freeze Panes • Lock rows or columns by freezing panes. • When you freeze panes, you select specific rows or columns that remain visible when scrolling in the worksheet.

5. Basic Features – Freeze Panes • Lock rows and columns: • Click the cell below and to the right of where you want the split to appear. • Select: Window（視窗）Freeze Panes（凍結窗格） • Unlock rows and columns: • Select: Window（視窗）Unfreeze Panes（取消凍結窗格）

6. Basic Features – Filter • Apply filters to only one range on a worksheet at a time. • Click a cell in the range you want to filter. • Select: Data（資料）Filter（篩選）Auto Filter（自動篩選） • When you use the AutoFilter command, AutoFilter arrows appear to the right of the column labels in the filtered range.

7. Basic Features – Filter • Click the arrow to filter. (Only the first 1000 unique entries in a list appear when you click the arrow.) • Select Custom（自訂）for more filter options.

8. Basic Features – Paste Special • Use the Paste Special（選擇性貼上）command to paste specific cell contents or attributes such as Formulas, Formats, or Comments. • To change columns of copied data to rows, or vice versa, select Transpose.

9. Basic Features – Cell Reference • Relative Reference (e.g.: C1) • Relative references automatically change as they are copied down a column or across a row. • Absolute Reference (e.g.: $C$1) • Absolute references are fixed; they don’t change if you copy a formula from one cell to another. • Mixed Reference (e.g.: $C1 or C$1) • A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column. As a mixed reference is copied from one cell to another, the absolute reference stays the same but the relative reference changes.

10. Basic Features – Print Options • Print a worksheet to fit a paper width or a number of pages. Select: File（檔案）Page Setup（版面設定）

11. Basic Features – Print Options • Add headers and footers for printing. Select: File（檔案）Page Setup（版面設定）

12. Basic Features – Print Options • Print row and column headings or labels on every page. Select: File（檔案）Page Setup（版面設定）

13. Basic Features – Print Options • Replace error values when printing. Select: File（檔案）Page Setup（版面設定）

14. Basic Features – Print Options • Print a worksheet with formulas displayed. • Display formulas on your worksheet.(Ctrl + `) • Print as usual.

15. Working with Functions • Click the “fx” to displays the Insert Function dialog box.

16. Functions - SUM • Adds all the numbers in a range of cells. • SUM(number1,number2, ...) • Related Function: • SUMIF(range,criteria,sum_range)

17. Functions - SUM • 3-D Reference • A reference that refers to the same cell or range on multiple sheets. A 3-D reference is useful and convenient way to reference several worksheets that follow the same pattern and cells on each worksheet contain the same type of data, such as when you consolidate budget data from different departments in your organization. • SUM(SheetX:SheetY!A1)

18. Functions - AVERAGE • Returns the average (arithmetic mean) of the arguments. • AVERAGE(number1,number2,...)

19. Functions - COUNT • Counts the no. of cells that contain nos. and counts nos. within the list of arguments. • COUNT(value1,value2,...) • Related Functions: • COUNTA(value1,value2,...) • COUNTBLANK(range) • COUNTIF(range,criteria)

20. Functions - MAX / MIN • Returns the largest / smallest value in a set of values. • MAX(number1,number2,...) • MIN(number1,number2,...)

21. Functions - SUBTOTAL • Returns a subtotal in a list or database. • SUBTOTAL(function_num, ref1, ref2, ...) • Function_num(1:Average / 2:Count / 9:Sum /...) • The function ignores any rows that are not included in the result of a filter.

22. Functions - ROUND • Rounds a number to a specified number of digits. • ROUND(number,num_digits) • Related Functions: • ROUNDDOWN(number,num_digits) • ROUNDUP(number,num_digits)

23. Functions - VLOOKUP • Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) • Related Function: • HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

24. Functions - 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. • PROPER(text) • Related Functions: • LOWER(text) • UPPER(text)

25. Functions - TRIM • Removes all spaces from text except for single spaces between words. • TRIM(text)

26. Functions - CONCATENATE • Joins several text strings into one text string. • CONCATENATE (text1,text2,...)

27. Functions - SEARCH • 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. • SEARCH(find_text,within_text,start_num) • SEARCH is not case sensitive. If you want to do a case sensitive search, you can use FIND and FINDB.

28. Functions - 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. • MID(text,start_num,num_chars) • If start_num is greater than the length of text, MID returns "" (empty text).

29. Functions - IF • Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. • IF(logical_test,value_if_true,value_if_false) • Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.

30. Functions - ISERROR • Returns the logical value TRUE if value is any error values; otherwise it returns FALSE. • ISERROR(value) • Related Functions: • ISBLANK(value) • ISNUMBER(value) • ISTEXT(value)

31. Tips • Calculate the number of days between two dates • Convert the numbers stored as text into numbers. • Use the fill handle to fill data. • Use function keys and shortcut keys to help working.

32. Tips – Days Calculation • Excel stores dates as sequential serial numbers so they can be used in calculations. • By default, January 1, 1900 is serial number 1, and; • January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. • To calculate the number of days between two dates, you can simply subtract the 2nd date from the 1st date and you will have the days difference.

33. Tips – Convert Numbers • Convert the numbers stored as text into numbers. • Multiply the numbers by 1(e.g.: =1*C1)

34. Tips – Fill Handle • Use the fill handle to fill data. (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.) • Drag the fill handle across the cells that you want to fill. • Double click the fill handle to automatically fill downward for all adjacent cells that it applies to.

35. Tips – Function / Shortcut Keys • “F2” • Edits the active cell and positions the insertion point at the end of the cell contents. • “F4” • Repeats the last command or action, if possible. • Ctrl + S (Save) • Saves the active file with its current file name, location, and file format. • Ctrl + Shift + Arrow Key • Extends the selection of cells to the last nonblank cell in the same column or row as the active cell.

36. Tips – Function / Shortcut Keys • Ctrl + C (Copy) • Copies the selected cells. • Ctrl + V (Paste) • Inserts the contents of the Clipboard. • Ctrl + X (Cut) • Cuts the selected cells. • Ctrl + B / I / U • Applies or removes bold / italic / underlining formatting.

38. The End