Excel Tips and Tricks

1. Excel Tips and Tricks Presented by Darryl Geoffrey My Other Brother Software, LLC myotherbrother@aol.com 413-535-4244

2. Hands-on vs Demonstration

3. Microsoft Office Excel 2007 • Results-oriented user interface – RIBBON • Expanded number of Rows & Columns • 1,048,576 rows/16,384 columns vs65,536 and 256 • Office Themes and Excel Styles • Improved Sorting and Filtering • Improved Chart Creation • Improved connection to External Data 1st Tip – Make that Ribbon go away – Double Click the current tab name

4. Downloading the Sample File • Start Internet Explorer • Type www.myotherbrotherweb.com/ncyf/main.asp • Right click on Excel Examples Spreadsheet • Click Save As • ExcelExamples.xlsx • Save

5. Two words about Keyboard Shortcuts USE THEM!!! • Already familiar with these: • CTRL B Bold • CTRL U Underline • CTRL C Copy • Some of my favorites: • CTRL 1 Format Cells Dialog Box • CTRL A Select All • F2 Edit Active Cell • ALT Enter New line in Same Cell • CTRL F Find and Replace Dialog • Microsoft provides a list of all of them: • http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx • Or you can Google: microsoft excel keyboard shortcuts

6. Customizing the Quick Access Toolbar • Click Drop Down to the right of the Quick Access Toolbar • Click the individual item you want to add • Go back to Step 1 OR 1. Click Drop Down to the right of the Quick Access Toolbar • More Commands • Double Click each command you want • Click OK

7. Selecting Ranges • Shift Cursor Keys (Up, Down, Left, Right, Home, End, PageUp, PageDown) • Click Mouse in First Cell in Range, Hold Shift, Click Mouse in Last Cell in Range • Select Current Region – Ctrl * • Nonadjacent Range – Select first range as above, then hold Ctrl while selecting additional cells/ranges

8. Example: Selecting Cell Ranges • Open the ExcelExamples.xlsx Workbook • Click on the sheet named: Select • Watch first, then try

9. AutoFill • Works with Days, Months, Dates, Numbers • Patterns – 2,4,6; Jan-01, Feb-01 • Double Click – Extends Fill Along Adjacent Current Region • Copy • Ctrl – Drag • Right Click – Drag • Custom Lists

10. Auto Fill Custom Lists • Office Button • Excel Options • Edit Custom Lists • Type New List • Click Add Button • Click OK OR 4. Click in Import List Box 4a. Highlight List Items 5. Click Import

11. Formatting Output • Insert Header/Footer • Insert Tab, Header and Footer in Text Group • Customize • Repeat Rows/Columns • Page Layout Tab, Dialog Box Launcher on Page Setup Group • Sheet Tab • Rows/Columns to Repeat • Click on Sheet • Adjust Page Breaks • View Tab, Page Break Preview in Workbook Views • Drag to Adjust Blue Lines

12. Splitting Windows and Freezing Panes Click in the cell to be the new upper left cell To Split into Multiple Windows • Double Click either Split Box, or Click View Tab, Split in Window Group • Adjust as necessary To Freeze Panes • Click View Tab, Freeze Panes in Window Group

13. Outlines • Summarizes data on successive levels • Up to 8 levels • Default – Detail above or left of summary (customizable) • Excel can create Summary Rows • Not affected by “Undo” • May require multiple “tries” • Clear Outline removes the outline

14. Example: Outline You want to create a summary so you can see how each rep did in various levels, from extreme detail (per region per month) to total summary (overall total, all reps, 6 month period) • Insert AutoSums for Each Qtr and for 6 month period (Shortcut: Alt =) • Highlight Current Region…Click Subtotal…Subtotal each Column at each change in Rep • Highlight Jan/Feb/Mar columns, click Group • Highlight Apr/May/Jun columns, click Group • Highlight Jan – 2nd Qtr columns, click Group

15. How to Dress Up A Table Using the Outline we just created, we will do some quick formatting • Select Current Region • Home Tab, Format as Table • Turn off Filter • Select Style (Ctrl *)

16. Paste Special

17. Paste Special – Values - Example You have a workbook containing one sheet for raw data for the year. At the end of the year, you transfer summary information to the “Summary” sheet and wipe out the raw data. The annual totals are calculated based on formulas using the raw data. • Highlight the summary column • Press Ctrl C • Go to the Summary Sheet • Highlight the new year column, click Paste Special • Choose Values

18. Paste Special – Formats (excludes Column Widths) • You notice the new column has no currency formatting • Highlight the original data range • Press Ctrl C • Return to Summary • Click in 2010 • Paste special, formats

19. Paste Special - Transpose You may wish to take some data and transpose it (change rows to columns and columns to rows) • Highlight desired data • Ctrl C • Click in target location • Paste Special – Checkmark in Transpose • Click OK

20. Protection and Passwords • A locked cell cannot be changed • A hidden cell’s formula cannot be seen • By default, all cells are locked, but not hidden • Locking/hiding cells has no effect until you protect the worksheet • You can assign a password when you protect a sheet • Cell Protection is found on Format Cells (Ctrl 1) • Sheet/Workbook Protection is on Review Tab Go to Protection Sheet

21. Protect Sheet/Workbook Options Go to OrderFormExample Sheet

22. So you want to Password Protect the file? Office Circle Save As Tools General Options

23. Formulas and Functions • Insert Function Button

24. Status Bar Calculations • Calculations are automatically performed on highlighted cells • Results are displayed in Status bar • Default Calculations are: Average, Count and Sum • Other options available: • Numerical Count, Max, Min • Right click Status Bar to Customize

25. Copying Worksheets • Familiar with Right Click Sheet Name…Move or Copy • Familiar with Reordering Sheets by Dragging • TIP: To copy a worksheet • Drag as if moving to new location • Press Ctrl after clicking Sheet name, but before releasing in new location • Release click NOTE: This works from Workbook to Workbook as long as both workbooks are visible.

26. Too Many Sheets…Too Little Time Have you ever had a workbook with so many sheets that it takes forever to scroll to the sheet? Try this: • Right click on the Sheet Selector Arrows • Select desired Sheet from list that appears or click More Sheets… • Scroll to Desired Sheet

27. Group Worksheets • Select Multiple Sheets using Ctrl or Shift • The word Group appears next to File Name • Any Editing or Formatting will apply to the same cell in each Grouped Sheet • Click another sheet to Ungroup • Avoid saving file while sheets are grouped

28. Moving/Copying Data Between Sheets(Alternative to Cut and Paste) • Select desired range on SourceSheet • Move mouse to border – becomes 4 headed arrow • Hold down Alt to Move/Ctrl Alt to Copy and click border • Drag Selection down to desired sheet tab • TargetSheet appears – drag to new position

29. Naming Ranges • You can assign a name to a cell or range, by selecting the range and clicking in the Name Box • Names can be used in place of cell references • Names give your formulas more meaning • =TotalSales-Expenses vs =B3-C2 • Names box allows you to “jump” to cells • Names are absolute…copying or moving formulas does not change cell reference • Names can be edited using Name Manager, on the Formula Tab in the Defined Names group

30. Other Tips as Time Permits • Number Formatting – Buttons/Shortcuts • Creating a Custom Format • Merge and Center – Multiple Cells • Rotating Text in a Cell • Wrapping Text in a Cell • Changing Text Indent • Changing Column Width and Row Height • Hiding/Unhiding Columns and Rows • Drawing/Erasing Borders • Format Painter • Conditional Formatting • Creating Comments • Creating Charts