MICROSOFT EXCEL TRAINING Instruction: the following steps are the basic using of Microsoft Excel _files/image001.gif) Note: check on square bullets of complete steps. VIEW WORKSHEET Menu items q Normal _files/image002.gif) View\Normal q Page Break Preview _files/image003.gif) (see worksheet with page breaks) View\Page Break Preview q Custom Views View\Custom Views… q Zoom (scale windows to fit view) View\Zoom… PRINT WORKSHEET q Print q Print Preview _files/image004.gif) File\Print Preview q Page Setup _files/image005.gif) File\Page Setup… q Page (Orientation, Scaling, Paper Size,…) q Margins (Top, Bottom, Left, Right, Header, Footer, Center on page) q Header/Footer q Sheet (Set print area and titles repeated for every page) q Page Break Preview File\Page Break Preview (Set manual vertical and horizontal page breaks) Tip: Set page to print from Margins Page Breaks (Horizontal, Vertical) q Print _files/image008.gif) (Ctrl + P) File\Print… q Printer q Print range (number of pages to print) q Print Area _files/image009.gif) File\Print Area WORKSHEET QUICK ACCESS Right click on sheet tab to display shortcut menu. q Insert… (Worksheet, Chart, Templates,…) q Worksheet _files/image010.gif) (Shift + F11 or Alt + Shift + F1) Insert\Worksheet q Delete (remove selected sheet) Edit\Delete Sheet q Rename Format\Sheet\Rename q Move or Copy… Edit\Move or Copy Sheet… q Move sheet to another workbook q Copy (check ”Create a copy” on) q Within current workbook q To another workbook q Switch sheet q Move to the next sheet (Ctrl + Page Down) q Move to the previous sheet (Ctrl + Page Up) q Select sheets q Select the current and next sheet (Shift + Ctrl + Page Down) q Select the current and previous sheet (Shift + Ctrl + Page Up) EDIT CELLS q Edit content (F2) q Start a new line in the same cell (Alt + Enter) à this cell is “wrap text” q Copy cells q To clipboard _files/image011.gif) (Ctrl + C) Edit\Copy q Use mouse (Ctrl + drag mouse) q Cut cells q To clipboard _files/image012.gif) (Ctrl + X) Edit\Cut q Use mouse (drag mouse) q Paste cells q Paste all contents and formats _files/image013.gif) (Ctrl + V or Enter) Edit\Paste q Paste Special (right-click/Paste Special…) Edit\Paste Special… q All (similar to Ctrl + V or Enter) q Formulas q Values _files/image014.gif) (get only values) q Formats _files/image015.gif) (keep cells to have same format) q Multiply q Fill cells (dragging cell fill handle) q Down _files/image016.gif) (Ctrl + D) Edit\Fill\Down q Right _files/image017.gif) (Ctrl + R) Edit\Fill\Right q Fill Series (refers to menu Tools\Options\Custom Lists tab) Edit\Fill\Series… q xxx1, xxx2, xxx3, xxx4,… q Mon, Tue, Wed, Thu,… q Insert cells q Cells _files/image018.gif) (Ctrl +) Insert\Cells… q Rows _files/image019.gif) (Row selection & Ctrl +) Insert\Rows q Columns _files/image020.gif) (Column selection & Ctrl +) Insert\Columns q Delete cells q Cells _files/image021.gif) (Ctrl -) Edit\Delete… q Rows _files/image022.gif) (Row selection & Ctrl -) q Columns _files/image023.gif) (Column selection & Ctrl -) q Find and Replace _files/image024.gif) (Ctrl + F or Shift + F5) Edit\Find… or Replace…
FORMAT CELLS q Format cells _files/image025.gif) (Ctrl + 1) Format\Cells… q Number tab q General (default) (Ctrl + Shift + ~) q Text (keep content always to be text. The cell is displayed exactly as entered) q Custom q Date format: dd/mm/yyyy, dd-mmm-yy,… (Ctrl + Shift + #) q Time format: h:mm:ss AM/PM (Ctrl + Shift + @) q Alignment tab q Text alignment q Horizontal (Left _files/image026.gif) , Right _files/image027.gif) , Center _files/image028.gif) , Center Across Selection,…) q Vertical (Top _files/image029.gif) , Center _files/image030.gif) , Bottom _files/image031.gif) ,…) q Indent (Increase Indent _files/image032.gif) , Decrease Indent _files/image033.gif) ) q Text control q Wrap text (auto fit cell height to display its full content) q Merge cells _files/image034.gif) _files/image035.gif) (combine many cells to one cell) q Orientation _files/image036.gif) _files/image037.gif) (change rotation of text in cell) q Font tab q Font name (Ctrl + Shift + F) q Font style q Bold _files/image038.gif) (Ctrl + B) q Italic _files/image039.gif) (Ctrl + I) q Underline _files/image040.gif) (Ctrl + U) q Font size q Color _files/image041.gif) q Effects q Strikethrough _files/image042.gif) (Ctrl + 5) q Superscript _files/image043.gif) : 1st, 2nd, 3rd, 4th,… q Subscript _files/image044.gif) : H2O,… q Border tab q Add _files/image045.gif) (Ctrl + Shift + &) q Remove _files/image046.gif) (Ctrl + Shift + _) q Patterns _files/image047.gif) (Cell shading) q Protection _files/image048.gif) (only effect until the worksheet is protected) q Format painter _files/image049.gif)
FORMAT ROW/COLUMN q Row Format\Row q Height _files/image050.gif) q Autofit (Double-click at row header edge) q Hide (Ctrl + 9) / Unhide (Ctrl + Shift + 9) q Column Format\Column q Width _files/image051.gif) q AutoFit Selection (Double-click at column header edge) q Hide (Ctrl + 0)/ Unhide (Ctrl + Shift + 0)/ Standard Width… INSERT q Symbol _files/image052.gif) Insert\Symbol… q Function _files/image053.gif) (= or Shift + F3 to display dialog) Insert\Function… q SUM(sum_range) _files/image054.gif) (Alt + =) q SUBTOTAL(function_num,ref1,…) q SUMIF(range,criteria,sum_range) q COUNT(value1,value2,…), COUNTA(value1,value2,…) q COUNTIF(range,criteria) q AVERAGE(number1,number2,…) q MAX(number1,number2,…), MIN(number1,number2,…) q IF(logical_test,value_if_true,value_if_false) q HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) q VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) q LEFT(text,num_chars), RIGHT(text,num_chars) q MID(text,start_num,num_chars) q Name (Ctrl + F3) Insert\Name q Comment _files/image055.gif) (Shift + F2) Insert\Comment q Picture (From File _files/image056.gif) ) Insert\Picture q Page Break Insert\Page Break TOOLS q Check Spelling _files/image057.gif) (F7) Tools\Spelling… q Protection q Protect Sheet Tools\Protection\Protect Sheet… q Protect Workbook _files/image058.gif) Tools\Protection\Protect Workbook…
DATA ANALYSIS q Sort _files/image059.gif) _files/image060.gif) (select range with headers before sorting) Data\Sort… q Filter q AutoFilter _files/image061.gif) Data\Filter\AutoFilter q Advanced Filter Data\Filter\Advanced Filter… q Subtotals Data\Subtotals… (Using SUBTOTAL function with Group) WINDOWS q Split (divide active window in quarters) Window\Split (F6: switch to the next pane in a split sheet) (Shift + F6: switch to the previous pane in a split sheet) q Freeze Panes _files/image062.gif) Window\Freeze Panes | 
_img1.gif)
