1 / 19

J. Carlton Collins carlton@asaresearch

Advanced Excel Dive Deeper into Microsoft Excel with this Spotlight Session on Advance Functionality GSCPA 2008 Fall Decision Makers Conference. J. Carlton Collins carlton@asaresearch.com. Download the detailed Excel file used in this presentation at: www.ASAResearch.com/excel.

maine
Télécharger la présentation

J. Carlton Collins carlton@asaresearch

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. Advanced ExcelDive Deeper into Microsoft Excel with this Spotlight Session on Advance FunctionalityGSCPA 2008 Fall Decision Makers Conference J. Carlton Collins carlton@asaresearch.com

  2. Download the detailed Excel file used in this presentation at: www.ASAResearch.com/excel

  3. Session Objectives The GSCPA Fall Decision Makers Conference Committee has asked me to cover some more advanced features, functionality and concepts in Microsoft Excel to help CPAs who already have a good intermediate understanding of Excel take their knowledge to the next level.

  4. 3 Chances For Free Microsoft Office 2007 Software Attendees of the following GSCPA CPE courses will receive a free copy of Microsoft Office 2007 Small Business Edition ($495 SRP)

  5. Validation • Drop Down List • Dates, Whole Numbers, Decimals • Comments • Also: • Color of Data Input Cells • =TODAY • =VLOOKUP • Macro & Macro Buttons

  6. 2. Macros • Page Setup Macro • Simply turn on macro recording, press keys, turn off macro recording • No Spaces allowed in macro name • Assign macro to icon or object for easy access • Record in workbook vs. personal macro workbook • Absolute vs. relative reference

  7. 3. Protection • Locked Cells • Hidden Cells • Protect Sheet (Review Ribbon) • Protect Sheet Options

  8. 4. Encryption • Save As, Tools, General Options • 40 Bit vs 128 Bit

  9. 5. Formula Auditing • CTRL + ~ • Formula Auditing Tool Bar • Precedents & D • ependents • Links to other worksheets or workbooks

  10. 6. Hyperlinks • Text • Objects • Text Box • Icons • To Web Sites • To E-mail Addresses • To Bookmarks • To Other Files

  11. 7. Gantt Chart

  12. 8. Web Queries • Stock Portfolio Example • Link to Ticker Symbols • Link Results to Portfolio • Refresh • Refresh All

  13. 9. Precision as Displayed • Example • Worst Dialog Box • Auto Rounding and Truncating

  14. 10. Linear Regression Analysis • Simple Example • Linear Regression Explained • More Complex Example

  15. 11. Tabs • Rename • Color • Reorder • Select Multiple • Duplicate with CTRL + Drag

  16. 12. Excel 2007 • Push Pins • Chart Improvements • Animate Excel Charts in PPT by Series • Smart Art • Headers & Footers • Contextual Menus • Quick Access Tool Bar

  17. 13. Set up Options • Always show full menus • Uncheck move on enter • Turn on transition keys so home key takes you home

  18. If Time Permits: 14. Fill in Missing Data a. By copying formula to blank cells b. Simple Example c. QuickBooks Example 15. SUMIF 16. The Subtotal Command 17. Pivot Tables

  19. J. Carlton Collins, CPA Brief BiographyPresident of ASA ResearchAtlanta, Georgia (770) 734-0950 Office (770) 734-0650 Faxcarlton@asaresearch.com ·        2008 & 2009 Chairman of the Southeast Accounting Show - the south's largest CPA event. ·        Named “Top Ten CPA Technologists” by Accounting Technologies Magazine. ·        Named “Top 100 Most Influential CPAs ” by Accounting Technologies Magazine in multiple years. ·        Has personally delivered over 1,500 technology lectures around the world. ·        Has published 80+ pages of accounting software articles in the Journal of Accountancy. ·        Recipient of the AICPA Lifetime Technical Contribution to the CPA Profession Award. ·        Recipient of the Outstanding Discussion Leader Award from the Georgia Society of CPAs. ·        Recipient of the Outstanding Discussion Leader Award from the Alabama Society of CPAs. ·        Selected by Microsoft to develop 24 hour training materials on Microsoft Office Accounting. ·         Lead author for PPC's Guide to Installing Microcomputer Accounting Systems.  ·        Editor of a dozen Accounting Software and Technology web sites. ·        Has installed accounting systems for more than 200 companies. ·        Has assisted thousands with the selection of an appropriate accounting system.

More Related