Excel Tips and Tricks

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