Excel Tips and Tricks - PowerPoint PPT Presentation

excel tips and tricks n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Excel Tips and Tricks PowerPoint Presentation
Download Presentation
Excel Tips and Tricks

play fullscreen
1 / 52
Excel Tips and Tricks
279 Views
Download Presentation
iliana-neal
Download Presentation

Excel Tips and Tricks

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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