Auditing Excel Spreadsheets

On Demand Webinar

Webinar Details $219

  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Software
  • Credit:   ATAAA 1.0, ATATX 1.0, ATAOP 1.0, CPE 2.0

All Access Membership

If you’d like to learn valuable techniques you can use to verify the integrity of even the most complicated Excel spreadsheets, this illuminating course is exactly what you need. Excel expert David Ringstrom, CPA, shows you how to use Excel’s formula auditing and error-checking tools, identify duplicates in a list, monitor the ramifications of even minor changes made to your workbooks, use the Evaluate Formula feature, and more.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.  

Who Would Be Interested in This Course:

Practitioners who review and audit Excel spreadsheets created by others, or those who wish to improve the integrity of their own spreadsheets.


Your Benefits of Attending:

  • Identifying duplicates in a list using Conditional Formatting.
  • Selecting all formulas within a worksheet with just two mouse clicks.
  • Adding a macro to Excel that adds the ability to display any formula in a cell comment.
  • Exploring options for recovering lost passwords for Excel spreadsheets.
  • Protecting sensitive information by marking key worksheets as VeryHidden.
  • Transforming cell references into range names by way of the Apply Range Names to Formulas command.
  • Differentiating between manual and automatic calculation modes in Excel, and when Excel may default into manual calculation mode.
  • Understanding the purpose and nuances of Excel’s Personal Macro Workbook.
  • Bringing Excel’s green error-checking prompts under control by managing the underlying rules.
  • Learning the nuances of circular references in Excel.
  • Adding a macro to Excel that adds the ability to display any formula in a cell comment.

Learning Objectives:

  • Recall how to use the Watch Window to monitor changes made to your spreadsheets. 
  • Identify the ribbon tab in Excel that contains the Show All Comments command.
  • Recall the location of the menu command that allows you to determine categorically if a workbook contains links or not.

Level:

Intermediate

Format:

On-Demand Webcast

Instructional Method:

Self-Study

NASBA Field of Study:

Computer Software & Applications (2 hours)

Program Prerequisites:

Experience Working with Excel Spreadsheets

Advance Preparation:

None


Table of Contents

  1. Introduction
  2. Excel Versions 00:00:34
  3. Recovering Lost Passwords  00:03:13
  4. Unhide Very Hidden Worksheet  00:05:30
    1. ALT+F11 00:06:02, 00:06:57
    2. Visual Basic Editor 00:06:04, 00:06:53, 00:07:13
    3. Unhide Command 00:06:32
    4. Format Command 00: 06:06:36
    5. CTRL+R  00:07:25, 00:08:53
    6. Project Explorer 00:07:28, 00:08:46
    7. Very Hidden 00:08:21, 00:09:30
  5. Confirming if a File Has  Links 00:10:07
    1. Message Bar Prompt 00:10:30
    2. Dialog Box Prompt 00:10:57
    3. Edit Links Command 00:11:24, 00:12:47
    4. Break Link 00:14:45
  6. Automatic vs Manual Calculation 00:15:02
    1. Manual Calculation 00:15:34
      1. Formulas Menu 00:16:03
      2. Calculation Options 00:16:06
  7. Show Formulas Feature 00:18:35
    1. Formulas Menu 00:20:13
    2. Show Formulas 00:20:17
    3. Print Titles 00:21:09
  8. Find & Select Formulas 00:23:24
  9. Find & Select - Constants 00:26:06
    1. Constants 00:26:09
  10. Trace Dependants 00:29:34, 00:30:55
  11. Evaluate Formulas 00:39:40
  12. Use F9 to Alculate Part of a Formula 00:43:23.
  13. Create Range Names from Selection 00:47:40
    1. Assign  00:48:07, 00:48:41
    2. Create from Selection 0050:09
  14. Apply Range Names to Formulas 00:51:08
    1. Define Name 00:52:43
    2. Apply Name 00:52:50
  15. Auditing Contents of Named Cells  00:53.:29
    1. Named Ranges 00:53:33
    2. Indirect Function 00:55:14
  16. Watch Window 00:58:27
  17. N Function 01:03:57
  18. Cell Comments 01:07:06
    1. Insert Comment 01:08:58
    2. Show All Comments 01:09:22
  19. Initiating the Formula Comment Tool 01:13:01
    1. Macros command 01:13:07
  20. Unhide Personal Macro Workbook 01:16:26
  21. Copy/Paste Formula Comment Code 01:18:55
  22. Printing Cell Comments 01:21:41
    1. Print Titles 01:21:53
  23. Manage Error Checkng Prompts 01:24:57
  24. Error Checking Command 01:29:41
    1. Trace Error 01:33:23
  25. Circular References 01:34:20
  26. Locating Circular References 01:36:35
  27. Auditing Pivot Tables 01:40:54
  28. Conditional Formatting - Duplicates 01:44:21
  29. Closing 01:47:42

Index

ALT+F11 00:06:02, 00:06:57
Constants 00:26:09
CTRL+R 00:07:25, 00:08:53
Dialog Box Prompt 00:10:57
Edit Links Command 00:11:24, 00:12:47
Find & Select Formulas 00:23:24
Format Command 00: 06:06:36
Formulas Menu 00:20:13
Hide Command 00:05:50, 00:06:21, 01:17:51
Indirect Function 00:55:14
Macros command 01:13:07
Message Bar Prompt 00:10:30
Name Box 00:50:42
N Function 01:03:57
Password 00:03:22
Password Tool 00:04:33
Project Explorer 00:07:28, 00:08:46
Properties Window (F4) 00:09:20
Range Names 00:47:45
Trace Dependants 00:29:34
Trace Error 01:33:23
Unhide Command 00:05:40, 00:06:21, 00:06:32, 01:26:32, 01:17:00
Visual Basic Editor 00:06:04, 00:06:53, 00:07:13

Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.

Constants: A constant is a value that doesn't change (or rarely changes).

Error Checking Prompts : Error checking prompts are little green triangles that appear in the left-hand corner of cells where Excel perceives that formulas may have one or more errors. An exclamation mark icon appears when you click on a cell containing such a prompt, which you can click on to display a set of error handling options.

Evaluate Formulas : A feature available on the Formulas tab of Excel 2007 and later and the Tools/Formula Auditing menu in Excel 2003 and earlier that allows you to step through a formula in slow motion.

Formula Menu: The Formula tab is used to insert functions, define the name, create the name range, review the formula, etc. Located in the ribbon, the Formulas tab has very important and most useful functions to make dynamic reports.

Indirect Function: The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

N Function: A worksheet function that enables you to store notes in the formula bar. Anything contained within the N function typically evaluates to zero.

Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.

Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.

Range Names: A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.

Trace Dependents: A formula auditing feature that enables you to identify which cell or cells at are linked to the active cell.

Trace Precedents: A formula auditing feature that enables you to identify which cell or cells that the active cell is linked to elsewhere in the workbook.

Watch Window: A formula auditing and workbook navigation feature that enables users to monitor the value and formulas within key worksheet cells as well as easily navigate to said cells.

Workbook Links: A technique by which one or more cells in one workbook can reference one or more other workbooks. Such links pose data-integrity risks and should be used sparingly.

Worksheet Tab: A sheet, sheet tab, or worksheet tab is used to display the worksheet that a user is currently editing. By clicking a worksheet tab (located at the bottom of the window), users may move between the various worksheets. Every Excel file may have multiple worksheets, but the default number is three.

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

Guest Speaker

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees use [...]

    View Full Profile

Webinar Survey Overall Rating

This webinar received a total of 0 survey responses. Attendees have given an average rating of 0 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

0.0 / 5
Webinar Presentation
How many of the objectives of the event were met?
0.0 Stars
How useful was the information presented at this event?
0.0 Stars
Overall, how satisfied were you with this event?
0.0 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
0.0 Stars
How closely did the presenter follow the schedule?
0.0 Stars