1 / 35

Microsoft Excel Tips & Tricks

Microsoft Excel Tips & Tricks. Laura Nelson Manager, Support & Quality Assurance. Quick Access Toolbar. Add Commonly used functions by right clicking them and select, Add to Quick Access Toolbar. Auto Fill. Little Box on the bottom of each cell

harger
Download Presentation

Microsoft Excel Tips & 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.

E N D

Presentation Transcript

  1. Microsoft Excel Tips & Tricks Laura Nelson Manager, Support & Quality Assurance

  2. Quick Access Toolbar • Add Commonly used functions by right clicking them and select, Add to Quick Access Toolbar.

  3. Auto Fill • Little Box on the bottom of each cell • You can click and manually drag the formula down • You can double click that little box and it will auto fill all the way down your spreadsheet

  4. Text To Columns • Split text into columns that either has a delimiter or uses fixed width.

  5. Find/Replace • Find or replace text and numbers on a worksheet

  6. Remove Duplicates

  7. Highlight Duplicates

  8. Watch Window • Watch a formula and the result by using a watch window.

  9. Freeze Panes w/Total in Header • Watch a formula and the result by using a watch window.

  10. Sparklines • A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.

  11. Concatenate Formula • Joins two or more text strings into one string

  12. & Join Formula • Joins two or more text strings into one string

  13. Len Formula (Length) • Count the number of character in a text string including letters, numbers, special characters, non-printable characters, and all spaces from a cell.

  14. Vlookup Formula • Lookup and retrieve data from a specific column in table • (What you are looking up, Where you are looking, What Column you want to return, Exact Match True/False)

  15. Trim Formula •  Remove extra spaces from text.

  16. IFError • Returns a value you specify if a formula results in an error, otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula.

  17. Using IF Formula to Sequence

  18. Grouping • When you are not able to use subtotal to auto group the data, you can manually create the groups.

  19. Flash Fill • Start to type the next value, and, it provides a preview of the names formatted the way you want. Press Enter, and the names are all filled in for you using the Flash Fill, new in Excel 2013.

  20. Turn Off Flash Fill

  21. Automatically include Decimals

  22. Default File Location

  23. Tips Using Outside Data • When you request the data, include only the columns you need returned rather than an entire table. • Sort the data during the pull rather than using the Excel resources to do so.

  24. Large Spreadsheets • Turn off Auto Calculations until you have completed all of the formulas. • After populating formulas, do a copy/paste special and past values so it isn’t constantly calculating. • Save a Master-to go back to if necessary. • Freeze the Header/Column so it is static as you scroll down the spreadsheet.

  25. Keyboard Shortcuts

  26. Keyboard Shortcuts

  27. Keyboard Shortcuts

  28. Keyboard Shortcuts

  29. Keyboard Shortcuts

  30. Keyboard Shortcuts

  31. Keyboard Shortcuts

  32. Keyboard Shortcuts

  33. Demo

  34. Questions?

  35. Microsoft Excel Tips & Tricks Laura Nelson lauran@abilitycommerce.com 561-330-3151 x5145

More Related