Canton Public Library



76648419214341474470921385Excel Tips & Tricks36250682930329Instructor : Jim LawEmail : lawjim@Handout Internet Path : Tips and Tricks/Excel Tips&Tricks.docx34925-1308108607879-163285How to use this tutorialUnless otherwise specified always perform a left mouse clickUnless otherwise specified always click in the center of a cellPrecede all formulas with an equal sign (=) e.g. =SUM(A1:A5)Formulas can be entered as upper or lower caseNotes will appear below instructions as needed and will be shaded like thisWhen you see an instruction such as : Select the Home tab > AutoSum buttonThis means to Select the home tab then Select the AutoSum function232410113665063649861002919 OrDo instruction #1 first > Do instruction #2 nextFollow the arrows during multi-step instructionsAll tips and tricks used in this tutorial have been thoroughly tested usingMicrosoft Excel ? 200734925-1308108607879-163285Practice SessionToDo thisScreen Shot ToDo thisScreen Shot1) Select a cellwith the keyboardUse arrow keys to move to cell9080538107) Copy & Paste with mouseSelect cell(s) then Right mouse click & select copy, move to blank cell, right mouse click & select paste75565-241302) Select a cellwith the mouseLeft mouse click in center of cell18224508) Cut & Paste with keyboardSelect cell(s) then press Ctrl+X, move to blank cell then press Ctrl+V106045165103) Select a range of cells w/ keyboardPress the shift key then select cells with arrow keys167005-139709) Cut & Paste with mouseSelect cell(s) then Right mouse click & select cut, move to blank cell, right mouse click & select paste102235-19054) Select a range of cells w/ mousePress the left mouse button then select cells167005-1460510) Delete cell data with keyboardPress the shift key prior to selecting cell(s) then press Delete on the keyboard106045-31755) Use AutoFill buttonLeft mouse click on AutoFill button then drag up-down, left-right151765-2921011) Delete cell data with mouseSelect cell(s) then Right mouse click & select delete106045-298456) Copy & Paste with keyboardSelect cell(s) then press Ctrl+C, move to blank cell then press Ctrl+V179070-1333512) Undo previous actionsPress & hold down the Ctrl key then press the letter Z as many times as needed to undo previous actions93345-22860*** Practice using this sheet before moving on to the following pages ***34925-1308108607879-163285Navigating a Workbook41833802997207048500284480Tip #1 - Moving to cell A11) Press and hold down the Control key2) Press the Home key to move to cell A14149090224536041986206261107048500626110Tip #2 - Move to a specific cell1) Press Function key 5 (F5)2) Enter a cell reference to move to (e.g. A50000)3) Press Enter on the keyboard or OKTip #3 - Moving to a range name1) Select a cell you wish to quickly return to2) Select Formulas > Define Name3) Name the cell then press OK to close4) Press F5 (GoTo) then select the name from step 1Tip #4 - Moving from Sheet to Sheet1) To move to the previous sheet tab press Ctrl+PgUp2) To move to the next sheet tab press Ctrl+PgDn34925-1308108607879-163285Fill Cells #14508604640559Left mouse clickDrag AutoFill button down7652004692912Tip #5 - AutoFill original cell content 1) Enter the number 1 in cell A12) Left mouse click in cell A13) Drag the AutoFill button down4513580602615Right mouse click Drag AutoFill button down7655755674321Tip #6 - Fill Series - Right mouse click1) Enter the number 1 in cell A12) Left mouse click in cell A13) Right mouse click on the AutoFill buttonand drag down4) Release the mouse button to stop then select Fill Series76557556384194523594568325Select both cellsLeft mouse clickDrag AutoFill button downTip #7 - Create series - Left mouse click1) Enter the number 1 in cell A12) Enter the number 3 in cell A23) Highlight cells A1 and A24) Left mouse click on the AutoFill button to increase the series34925-1308108607879-163285Fill Cells #2453009051689070485005740404604004280212870548502802255Tip #8 - AutoFill dates 1) Enter a date in a cell2) Right mouse click on the AutoFill button and drag down3) Release the mouse button and choose Fill : Days, Weekdays, Months or YearsTip #9 - Copy values from cells above1) Move to a blank cell below data in a cell then press Ctrl + '70460026981774538345640715Tip #10 - Autofill down1) Select a cell that is adjacent to the current cell2) Double-click on the fill handle to automatically fill downNOTE: Column B will fill down until there is a blank cell in Column A34925-1308108607879-163285Data ValidationTip #11 - Create a pulldown list7008642287909046121512821940462838816027470542151479551) Create or use an existing list of items2) Select data > Data Validation > Settings tab3) Check the Ignore blank and In-cell dropdown checkboxes4) Click the pulldown arrow from the Allow: text box and select list5) Select the list items from step1 from the Source: range select button6) Accept the range by clicking the range accept button7) Click OKTip #12 - Create a tool tip effect1) Move to a cell you wish to add a tool tip2) Select Data > Data Validation > Input Message tab3) Type your tool tip message in the :Input message: text box4) Click OK5) Mouse click in cell A134925-1308108607879-163285Advanced Filter4696239460601470415154605655469582525514306074465255192746962394315797036435436880Tip #13 - Filter Unique Items & Remove Duplicates1) Enter data as shown at right then select a cell2) Select Data > Filter > Advanced Filter3) Place a checkmark in the :Filter the list, in-place checkbox4) Place a checkmark in the Unique records only5) Click OKNote1: This method hides duplicatesNote2: Select Data > Clear to show allTip #14 - Remove Duplicates1) Enter data from step one in Tip #13 then select a cell2) Select Data > Remove Duplicates3) Place a checkmark in the column with duplicates4) Click OKNote1: This method deletes duplicatesTip #15 - Filter Unique items and Copy to another location1) Select Data - Filter - Advanced Filter2) Place a checkmark in the :Copy to another location checkbox3) Enter a cell reference in the Copy to: textbox4) Place a checkmark in the Unique records only5) Click OK34925-1308108607879-163285Concatenating Data & Separating Cell DataTip #16 - Concatenate cell data1) Enter some text in cell A12) Enter different text in cell B13) Move to cell C14368018561734) Press the equal key (=) then select cell A15) Type Shift+7 (ampersand) and then type " " (This inserts a space between the two cells when joined)6) Type Shift+7 then select cell B17) Press the enter keyNOTE: Your formula should appear as : =A1&" "&B14316730362585Tip #17 - Separating cell data1) Enter the data from cells A1:A32) Highlight column A3) Select Data > Text to Column4) Choose Delimited then select Next5) Select Comma then select Finish34925-1308108607879-163285Entering Fractions – Date - Time50275433466327503047018510257213092183896050304703390907213092327152Tip #18 - Fractions - Entering fractions Fractions less than 1* For fractions less than 11) Enter a 0 then enter a space 2) Enter the numerator (top value) followed by the "/" key3) Enter the denominator (bottom) value4) Press Enter on the keyboard * For fractions greater than 1 Fractions greater than 1Follow steps 1-4 but enter a whole number in step 1NOTE1: Decimal representations will appear in the formula bar NOTE2: Type Ctrl+1 then Format cells as generalTip #19 - Inserting today’s date1) Type Ctrl+; (semi-colon) to insert today's date503072426543Tip #20 - Inserting the current time1) Type Ctrl+: (colon) to insert today's date34925-1308108607879-163285Quick Data Entry - Sheets - Charts6339840364490Next cell entry482346055880710819023698205867400315341048223712370183Tip #21 - Quick data entry1) Select a range of cells you want to fill with data2) Enter data in the first (active) cell3) Press enter4) Repeat step 2 and step 3 until all cells are filledTip #22 - Quick Sheet Duplication1) Left mouse click on a sheet tab to duplicate2) Press and hold down the Control key (a sheet with a + sign will appear)3) Drag the sheet tab to either the left or rightto duplicateNOTE: Duplicated sheet tab names will increment47815504243737124699415925Tip #23 - Quick charts Clustered column chart1) Select a cell or range of cells that includes values (text and values are preferred)2) Pressing function key F11 produces a quickClustered column chart34925-1308108607879-163285Data Manipulation5322570431165077070441561416531622025615905328666221234I-beamTip #24 - Swap Column or Row Data1) Enter the values 1,2,3 in cells A1:C12) Select cells A1:B13) Hold down the shift key then select the border4) Drag the mouse to the right to cell C15) Look for the vertical I-beam (for column data)6) Release the mouse buttonNOTE: Look for a horizontal I-beam for row dataTip #25 - Transpose Data (Copy & Rotate)1) Enter the values 1,2,3 in cells A1:A32) Copy the range of cells in step 1 to be transposed3 ) Select a blank cell to place the transposed data4) Select Home > Edit Paste Special > Values > Transpose5) Click OKTip #26 - Paste Special Values - fill blanks1) Enter data similar to the table at left2) Copy the data range B2:B6 then move to cell A23) Select Home > Paste pulldown arrow > Skip blanks4) Click OK to close34925-1308108607879-163285Working with Lists #1560324048869607493508488696055968902623058560222462738Tip #27 - Create an A to Z Custom list1) In cell A1 enter : =CHAR(ROW()+64) then press enter2) Fill the formula in cell A1 down to row 26 > then select the cell range from A1:A263) Copy the highlight range from cell A1:A26 > paste special values3) Click the Office Button > Excel Options > Popular > Edit Custom Lists4) Click Import > Click OK5) Enter the letter A in a blank cell and fill either up, down, left, or rightTip #28 - Create a quick calendar1) In cell A3 enter : =TODAY() then press enter2) In cell B3 enter : = A3+1 then copy the formulato the right or down to fill by days3) In cell A4 enter : =A3 +1 then copy to the right or downNOTE1: In step 2 enter A3+7 to fill by weeks or A3+365 to fill by yearsTip #29 - Seeing all characters in a Font style1) Enter =CHAR(ROW()) into Cell A12) Copy cell A1 down the column to cell A2553) Click the Column A header to select the entire column4) Loop through and choose a font from the font drop-down list34925-1308108607879-163285Working with Lists #242608504745990705485047415954307205277177570827902743835430212555880Tip #30 - Grouping and Outlining1) Enter data similar to the table at left2) Sort the data in Column A3) Select Data > Subtotal4) Change : At each change in : textbox to Region5) Use function : Sum6) Check : Add subtotal to : Sales7) Click OK8) Click the outlining number to expand or collapse the grouped dataTip #31 - AutoComplete - Pick from list1) Move to the first blank row in a list of data2) Press Alt+down arrow3) Select an item from the listNOTE: This feature works only with textTip #32 - Create a Random list of Numbers1) Enter the formula below in a blank cell : =randbetween(1st number, 2nd number)2) Press Enter3) Copy the formula down to create a randomList of numbers34925-1308108607879-163285Converting between Measurement Systems5130947450068Tip #33 - Convert Celsius to Fahrenheit (old way)1) Enter a few Celsius temperatures in Column A2) In Cell B4 enter : =(A4*1.8)+32 then press enter3) Copy cell B4 down as far as desiredNOTE : Use =(A4-32)/1.8 then press enter to convert from :Fahrenheit to Celsius610332798659540152990209Tip #34 - Using the Convert function (new way)1) Refer to the table below when usingthe convert functionMethod 11) Enter values in formulas as shown at right e.g. =convert(12,”in”,”ft”)610332769801Method 21) Enter a cell reference in the formulas shown at right e.g. =convert(A2,”in”,”ft”)34925-1308108607879-163285Creating & Using Range Names556031432326584957445452755Tip #35 – Creating a range name to represent a Sales Discount1) Enter the values 1,2,3 in cells A1:A32) Select Formulas > Define Name3) Enter a range name such as Salesdiscount to represent a sales discount4) Enter .1 (equals 10%) in the Refers to: text box5) Click OK to close6) Move to cell B1 and enter : =A1*Salesdiscount7) Copy cell B1 down as many cells as needed NOTE : Modify the SalesDiscount by editing the range name and modifying the .1 value (.01=1%, .2=20%, .25=25%, etc.)Tip #36 – Creating a range using the name box1) Enter text & values from the 1st box at left into thecell range A1:B42) Select the cell range A2:A4 > Enter north in the name boxand press enter3) Select the cell range B2:B4 > Enter south in the name boxand press enter4) Move to cell A5 and enter : =sum(north,south)and press enterNOTES ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download