1 / 132

Excel Tips and Tricks

Excel Tips and Tricks Jon Paul Progressive System Solutions, Inc. 847-295-7179 jpaul@mba1980.hbs.edu (Note- this was produced in 1997 for the 97 version of Excel) How Cold Was It? So cold I know someone who slept with 16 sheets on their bed! Just Open 1 Sheet! Select Tools from Menu Bar

Download Presentation

Excel Tips and Tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.


Presentation Transcript

  1. Excel Tips and Tricks Jon Paul Progressive System Solutions, Inc. 847-295-7179 jpaul@mba1980.hbs.edu (Note- this was produced in 1997 for the 97 version of Excel)

  2. How Cold Was It? So cold I know someone who slept with 16 sheets on their bed!

  3. Just Open 1 Sheet! • Select Tools from Menu Bar • Select Options • Select General • Sheets in new workbook- change to 1

  4. Moving Between Worksheets • Find arrow buttons in lower left hand side of screen • Right mouse click on far right arrow button • You now get list of sheets • If large number of sheets, select more sheets on the bottom • Scroll down to find the sheet you want • Click OK- away you go!

  5. Moving or Copying a Worksheet • Right mouse click on worksheet name • Select move or copy • Choose a workbook you want to send it to • selects current workbook by default • Select where in the workbook you want it • Check Create a copy if you want to copy it • Otherwise you could be moving and not be expecting it!

  6. Inserting or Deleting a Worksheet • Right mouse click on any worksheet name in your workbook • Select Insert • Double click on Worksheet to add a blank worksheet to your workbook • Can use same technique to add chart • Deleting- follow same steps only select Delete rather than Insert- be careful!

  7. Driving With Half a Windshield • Give yourself a full view of your worksheet • One way to show the whole width- find Zoom box- on toolbar and shrink percentage • Even better way- • highlight every column in your worksheet • go to Zoom box on toolbar • choose Selection • will automatically resize worksheet for you • Just changes size of view, not column size

  8. Your Car Has More Than One Window- So Can Your Worksheet • Window menu- select New Window • Gives you a second view of your current workbook • Second window is not sized • If going back to one window, make sure you are closing out the second • Windows will be identified by :1 or :2

  9. Do You Leave Your Doors Unlocked? No But How About Your Spreadsheets Is Your Neighborhood Safe?

  10. Protect Your Work! • Protecting a Worksheet • First unlock cells you don’t want to protect- such as input cells • Then from Tools menu, select Protect Worksheet • Decide if you want Password protection- not needed if just protecting against input errors • Remember your password, or your work goes to spreadsheet heaven

  11. Add the Club to Your Workbook • Protect Your Whole Workbook • In Tools menu, select Protection • Then select Protect WorkBook • Decide if you want password protection • Structure- prevents modifying structure • Windows- prevents opening, closing windows

  12. You “Auto” Save This One! • Ever have your computer crash • Ever turned off your computer too quickly And • You forgot to save your file!

  13. Your Insurance- AutoSave • To enable AutoSave • Go to the Tools menu • Select Add-ins • The Add-in dialog box appears • Check AutoSave and click on OK

  14. AutoSave Options • AutoSave is now in your Tools menu • Go to Tools and select AutoSave • The AutoSave dialog appears • Select your choices • save automatically or prompt you • how often to save • open workbook or all workbooks

  15. Painting Your House With a Small Paintbrush? • Excel’s Paintbrush- like a power sprayer • Select range of cells with format you like • Click on the Paintbrush button • Click the cells you want to reformat • When you release, cells are reformatted • If you double click on Paintbrush button, you can select non adjacient cells

  16. Wrap That Text! • Put long strings of text together neatly in your columns without having to: • Run over to the next column • Make your column super wide • Select the cell or range (such as column) • Go to Format menu and select Cells • Go to Alignment Tab • Click in Wrap Text box

  17. Does Your Data Make ‘Cents’? Does anyone have $0.50 that I can borrow for the pop machine? No, but I might have 50 cents.

  18. Custom Formats- Adding Cents • Select a cell you want to format • From the Format menu select Cells • Choose Number and then Custom • Select a format for the number portion • Go to the type box and the right hand side • For the cent symbol, hold Alt key and type 0162- you see cent symbol on the screen • For the cent name, type a space and cents

  19. The Lawyers are Coming! © SM TM

  20. Make Your Lawyer Happy! • Copyright- • type ( c ) and enter

  21. Your Lawyer Is Getting Happier! • Supersript • type SM • highlight SM in formula bar • select format • check Superscript • cut down size of font

  22. Now Your Lawyer Wants You to Train Their Staff! • Trademark- much easier • type ( t m ) and enter • no spaces inbetween

  23. Come Together- Merging Text • Concatenate- easier to use than pronounce • Select cell where you want merged text • Click on formula bar or select Insert menu and then Function • In the Function dialog, choose Text on the left side and Concatenate on the right

  24. Concatenating Tips • Remember to include blank spaces inbetween items • Can refer to cells or put in text, but not at same time • Can copy concatenation formulas • Can use text and number results

  25. It’s Time to Concatenate • Save retyping the same thing • Allow for automatic updates • Copying text where only part of the text is the same

  26. If Only the Cubs Could Replace Their Lineup This Quick! • Find & Replace • Select Edit menu, then choose Replace

  27. Replace Only What You Want To • Highlight what you want to change • Select Edit menu, then choose Replace • Enter what you want to find and what you want to replace it with • Decide if you want to match case or entire cell

  28. Replacing- Finishing Steps- Selective Replacement • Choose option button to the right • Find Next- when you only want to replace in certain cases- you control the replacing • Replace- to go ahead and replace after you have found a cell you want • Click Find Next to find the next case • Click Replace for only those you want • Click OK when done

  29. Replacing- Finishing Steps- Automatic Process • Use when you are sure you want to replace every instance in the area you selected • Activate by choosing Replace All button in right side of dialog box • Replaces everything in the area you choose • cell, cell block, worksheet, workbook • If you overdid it, go to the Edit menu and select undo (or click Ctrl + Z)

  30. Replacing- Some Ways to Use • When something new is in the air! • Copying workbook, worksheet or formulas and using elsewhere • Changing links, text or formulas

  31. Help- I Can’t Get This Unstuck! Are you using Superglue when regular Elmers will do?

  32. Pick Your ‘Glue’Use the Paste Special Button • To begin, same step as full blown copy • Highlight area you want to copy • Go to area you want to paste to • Then select paste special one of two ways • Right mouse click or • Pull down from edit menu • Choose the Paste Special Option • Click on OK

  33. Paste Special Options • Formulas • Values • Formats • All Except Borders • Transpose • Arithmetic Operation • Paste Link

  34. They Have It In Rows, But I Want It In Columns- Transposing • Changing from rows to columns • Changing from columns to rows • When to use • Change layout on new sheet • Pull in information from a different layout (like he has it in columns and I want it in rows!) • Converting to database format

  35. I Value Your Information-Paste Special- Value Option • You want to capture the value, but: • don’t need the formula • can’t use the formula because it won’t work in the new spot

  36. I Like Your Layout-Paste Special- Format Option • You have a layout you want to copy, but don’t need the formulas or values • Particularly good if dealing with a whole block of cells • Consider the paintbrush button as another way to do this • Can do with another paste special option • first do the other option, then do format

  37. I Like Your Formula- But Where Did You Get That Layout? • Use the Paste Special- Formula option to just copy the formula • Can then format any way you like • Great to use when you already have your format set up in your destination and don’t want to override it

  38. I Like Your Formula- But Don’t Box Me In! • Paste Special- All Except Borders • Pastes formulas and formats except for the borders • Use where you like the formula and formats but have a different setup for borders

  39. Changing Data- We Won’t Tell Your Boss How Easy It Is • Paste Special- Operation option • Fast way to change data automatically • Add, subtract, multiply or divide • Usually use with the value option in Paste Special

  40. How to Operate • Put change in separate inactive cell • such as 1.10 to multiply by 10% • Copy that inactive cell • Go to where you want to make changes • this could be a whole range of cells • Select paste special and the operation • such as value and multiply • Click on OK to finish • Go back and erase the inactive cell

  41. How Many Kids Do You Have? We have an average size family 2.3 kids

  42. Get A ‘Round’ to This • Round functions for more meaningful numbers • 2 or 3 kids, not 2.3 kids • Changes to whole number so next calculation also makes sense • at 10,000 per kid, cost is 20,000 or 30,000 • Round, Roundup or Rounddown

  43. Round to Your Size • Round- rounds up (5-9) or down (0-4), depending on the digits being rounded • Roundup- rounds up, away from zero • Rounddown- rounds down, closer to zero • How to do: • = Round(calculation, decimal places) • decimal places can be negative number • substitute Roundup or Rounddown if wanted

  44. How Do You Eat at a Buffet? Do You Take Everything They Offer You? Rumor Has It That I Do!

  45. You Wouldn’t Build a House Without These • Ceiling- • Round up to a nearest multiple (such as 10, 25) • Floor- • Same thing, only now you round down to a nearest multiple

  46. You Don’t Have to Sum Everything Either • SUMIF function- choose just what you want to sum • Enter your criteria • Click on the cell where you want the sum • Pull down the Function dialog box • =SUMIF(Criteria Range,Criteria,Value Range)

  47. More on SUMIF • Criteria Range- list describing what you want to sum- • such as rate plan chosen • Criteria- which item from the list do you want to sum • such as which rate plan • Value Range- range which holds the values you want to sum • such as monthly bills by rate plan

  48. Okay- How Many Did You Take? • COUNTIF- like SUMIF, but instead gives you a count • Not adding any values, so you only need the Criteria Range and the Criteria • =COUNTIF(Criteria Range, Criteria) • = COUNTIF(A2:A20,Basic Rate Plan) • counts number of times in A2:A20 that people chose the Basic Rate Plan

  49. How Much or How Little? • MAXIMUM • =MAX(Range) • gives highest value in a range • MINIMUM • =MIN(Range) • gives lowest value in a range

  50. Who’s Number Two?-You Might Care • Get LARGE or SMALL • =LARGE(Range,2) • shows who is 2nd largest in the Range • =SMALL(Range,3) • shows who is 3rd smallest in the Range

More Related