1 / 52

Excel Tips and Tricks

Excel Tips and Tricks. Important. Learn how to use help!. Excel Tips. Working with Hyperlinks Task: create 2 .xls files, with mutual hyperlinks Shortcut to enter today’s date Ctrl + ;. Excel Tips. Right click status bar, choose ‘quick functions’ Can select multiple non-contiguous cells

iliana-neal
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.

E N D

Presentation Transcript

  1. Excel Tips and Tricks PgP MIS 462

  2. Important • Learn how to use help! PgP MIS 462

  3. Excel Tips • Working with Hyperlinks • Task: create 2 .xls files, with mutual hyperlinks • Shortcut to enter today’s date • Ctrl + ; PgP MIS 202

  4. Excel Tips • Right click status bar, choose ‘quick functions’ • Can select multiple non-contiguous cells • AutoFill to speed up data entry • Office Button>Excel Options>Edit Custom Lists… PgP MIS 202

  5. Excel Tips • Creating a custom template (.xlt) • \Program Files\Microsoft Office\Templates\1033 • Seven available- Billing, BloodPressure,Expenses, Loan, Budget, Sales, TimeCard • Open and examine PgP MIS 202

  6. Breakeven Analysis • Search for template(s) at http://office.microsoft.com • 3 available • Download and examine “Breakeven analysis” PgP MIS 202

  7. Custom Formats PgP MIS 202

  8. Columns & Rows • What is maximum row height? • AutoFormat PgP MIS 202

  9. Conditional Formatting • Examine CondForm.xls • Find Conditional Formatting- Edit>GoTo> special… PgP MIS 202

  10. Page Breaks • Page Break Preview PgP MIS 202

  11. Workbook Organization

  12. Understand Locking and Protection • Format>Cells>Protection tab • All cells initially locked • Add protection to invoke locking selected cells PgP MIS 202

  13. Protecting Workbook • Structure-cannot insert, delete…worksheets • Windows-prevent resizing PgP MIS 202

  14. Protecting Worksheets • Prevent inadvertent changes to formulas… PgP MIS 202

  15. Worksheet Tabs • Add names • Change tab colors PgP MIS 202

  16. InterWorksheet References • Examine SalesSum.xls PgP MIS 202

  17. Inter Workbook References • Examine SalesLnk.xls PgP MIS 202

  18. Shared Workbooks • Create one on \\store\classes\200900..\shared • Experiment with settings, Tools>Share Workbook… • Create shared workbook on Portal, My Site PgP MIS 202

  19. Customizing Excel

  20. Custom Views • For current workbook • Save time • View>Custom Views… PgP MIS 202

  21. Printing • Examine tabs in File>Page Setup… • Scaling, Header/Footer, Print titles, Gridlines, Row and column headings, comments PgP MIS 202

  22. Panes • Freeze • Split PgP MIS 202

  23. Options • Calculation-F9, Manual, Iteration • Formulas-R1C1 reference • Custom Lists • Security-Digital Signatures PgP MIS 202

  24. Add-Ins, .xla, .xll • Provide special functionality • Analysis Toolpak • Analysis Toolpak-VBA • Solver PgP MIS 202

  25. Add-Ins • Create your own • See MSDN PgP MIS 202

  26. Formulas and Functions

  27. Formulas vs Results • Toggle using Ctrl + ~ • Formula vs Values PgP MIS 202

  28. Relative, Absolute and Mixed References • Examine Log.xls from Companion Content • Highlight formula bar, cycle references using F4 • Do exercise in book, Building a Formula PgP MIS 202

  29. Operator Precedence • Search Help! • Parentheses to override! PgP MIS 202

  30. Built-In Functions • Formulas>Insert Function… • 200 functions by category • Examine arguments • Note required, optional PgP MIS 202

  31. Function Errors PgP MIS 202

  32. Named Ranges • Simplify Formulas • Formulas>Name Manager • Review section material carefully, important! PgP MIS 202

  33. Charts

  34. Charts • Insert>Chart • Examine SalesSum.xls PgP MIS 202

  35. Chart Planning • Data set? • Most other options can be changed later PgP MIS 202

  36. Chart Formatting • Review options, choices • Examine SalesSum.xls PgP MIS 202

  37. Lists, Filters, PivotTables

  38. Lists • Examine Pivot.xls • Provided guidelines follow database theory • Note use of data entry forms • Review Sorting options PgP MIS 202

  39. AutoFilter • Use Pivot.xls, set AutoFilter • Set up Custom AutoFilter PgP MIS 202

  40. List Subtotals • Note levels • Note function subtotals • Sum, Count, Average… PgP MIS 202

  41. Converting Lists to .mdb • Why? Excel has limitations • Simple to do, use Access, File>Open PgP MIS 202

  42. PivotTables, PivotCharts • Data Analysis and Organization • Experiment with the Wizard using Pivot.xls PgP MIS 202

  43. Lists in SharePoint • Create list with Pivot.xls • Save to WSU portal PgP MIS 202

  44. Business Analysis

  45. Goal Seek • Examine GoalSeek.xls PgP MIS 202

  46. Solver • Examine Solver.xls • Make sure to install Solver Add-In • Office Button>Excel Options>Add-Ins…>click Go… • Named Ranges simplifies understanding • Follow book example PgP MIS 202

  47. Scenario Manager for What-If • Examine Scenario.xls • Scenarios for Best Case/Worst Case • Scenario reports-Summary PgP MIS 202

  48. Web Publishing and XML

  49. Excel Web Page • Be aware of feature limitations • Static vs dynamic • .html snapshot • .xls file, requires IE 4.01+ PgP MIS 202

  50. Excel Web Page • Publish Pivot.xls to your web site • Browse to and try to open it • Try to use Solver • Try to use Goal Seek • What are the benefits? PgP MIS 202

More Related