How to audit your Excel files to identify errors or mistakes
4.0 (20 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
231 students enrolled

How to audit your Excel files to identify errors or mistakes

Perform Foresnic Auditing of Excel files to ensure that calculation are error free & not manipulated with hidden tools
4.0 (20 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
231 students enrolled
Created by Abhay Gadiya
Last updated 5/2020
English
Current price: $11.99 Original price: $19.99 Discount: 40% off
2 days left at this price!
30-Day Money-Back Guarantee
This course includes
  • 5 hours on-demand video
  • 1 article
  • 32 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Protect against formula and operational errors
  • Be able to demonstrate management of material spreadsheet risks
  • Reduce compliance costs for business in regulated sectors
  • Design and implement robust internal controls over spreadsheet based financial reporting
Requirements
  • You should have used Excel earlier
  • You should know Excel basics like - entering formulas, pivot tables, etc.
  • You should have Excel 2010 onwards and preferably professional plus editions
Description

Are your spreadsheets important? 

If yes, then this course is MUST for you. It describes good practices that all spreadsheet users should follow.

Use this course to learn how to avoid the most common errors and to make future development easier. If you are a software tester or a manager of end user-computing, it gives you techniques in checking spreadsheets for accuracy and soundness. If you are an auditor looking for evidence of fraud, such as deliberately concealed data or functionality, you will also benefit from knowing the many ways in which data can be hidden or calculation methods subverted.  

This course was created in response to experssions of concerns by managers or auditors about risk to the business from a pervasive dependence on spreadsheets. This course can be considered as a guide to developing high quality spreadsheets, steps to perform for auditing or reviewing spreadsheets created by someone else to ensure accuracy. This course is of interest to anyone who relies on data analysis performed in spreadsheets or models built in it for decision making. The techniques described include areas such as ensuring that the objectives of the models are clear, defining the calculations, good design practice, testing and understanding and presenting the results from spreadsheet.

From this course your will learn how to:

  • Increase efficiency by avoiding rework

  • Discover Powerful formula auditing techniques

  • Foil attempts to conceal data and formulas from you

  • Reduce worry about costly and embarrassing mistakes

  • Create spreadsheets faster by avoiding wasted time from lack of specification

  • Present results with more confidence knowing that you have checked for errors.

In this course we will first understand various frequently used keyboard shortcuts. I have provided a cheat of all keyboard shortcut combination available in excel. You can simply take its printout and stick it on your workstation.  

Then we will have a look at various inbuilt feature available in Excel which are underutilized. These features can be extremely useful when we are reviewing or auditing or even building any complex models inside Excel file. Some of the features are like watch window, comparing same file side by side on single monitor, options under go window special, how to select cells based on their formatting and various printing setting like keep row heading on each page or printing cells comments, etc.  

Then next section is fully dedicated to a new add-in called as Inquire Add-in. If you are reviewer or auditor then you MUST learn how to use this add-in. Let me repeat you must learn this. This add-in will help you analyze your entire workbook for formulas, errors, hidden sheets, macros and many more. This will help you get pictorial presentation of relationship between workbooks, worksheets and individual cells. Further, if you want to compare multiple version of files then this add-in will do it for you at cell level even for formatting changes and also changes to macro. Further, if you are struggling with excess formatting in workbook then it can help you clean that. So this section is almost more than one hour long which gives detailed explanation of various feature of this add-in. I can assure you that you would not get so much explanation about this add-in even at Microsoft site also. So this is one of the highlight of this course.  

Now moving on auditing the formulas. Most of the people are aware about blue arrows displayed using formula auditing toolbar but unaware about the approaches to be followed to understand complex formulas. There have been many instances when formula entered had parentheses at wrong places and hence results were incorrect. I will explain you the order of operations followed by excel while calculating the results. We will also look at various referencing available in Excel like relative / absolute / structured and circular referencing. You must have a solid understanding of these referencing if you want to audit any formulas.  

In the next section we will look at pivot tables and various settings. Pivot Table are one of the USP of Excel because it gives flexibility to users to perform the data analysis very quickly. With recent versions of Excel there have been lot of changes made to this pivot table. Current Excel now contains data models and power pivot. In this section I will provide a brief introduction about these new tools. Further I will show you various unexplored setting related to filtering, sorting and grouping data inside the pivot tables. Also, if you believe that the grand totals displayed in Pivot Table are always correct then I will show you settings through which this can be incorrect. Many fraudsters have used this setting to overstate or understate certain items used in financial statements. I also have provided solutions to FAQ on pivot table.  

Now in next section titled overall governance around spreadsheets I will show various design level controls should be in place and implemented to avoid errors from spreadsheets. I will provide you a copy of sample spreadsheet policy which can be used to implement relevant procedures at your organisation. I will also show how can you create a inventory of files stored in a folder and also create a index sheet within excel file using Power Query. I have two other full fledged courses on Power Query.  

So currently these are areas I am covering as part of this course. In future I will also update more videos to this course and you do not have to pay anything extra for it. So don’t wait and enroll into this course immediately.

Who this course is for:
  • Auditor looking for evidence of fraud or errors in spreadsheets
  • Manager or reviewer who needs to ensure spreadsheet accuracy
  • Regular Excel user who wants to increase efficiency, data quality and accuracy
Course content
Expand all 39 lectures 05:06:09
+ Get started with Excel
3 lectures 18:41

This video gives details about all keyboard shortcuts available in Excel.

Preview 03:35
Ribbon and Quick access toolbar
07:38
Selecting Data in Excel and Navigating through file
07:28
+ Features that will speed up your review and auditing process
5 lectures 01:01:14
Watch Window - keep selected cells results displayed at all times
05:12
Compare multiple files side by side on single monitor
05:21
Go To Window and Special - detailed explanation of all items
18:00
Find & select with formatting of selected cell or as per selected format setting
14:39
Printing set up in Excel explained in detail with various settings
18:02
+ Inquire tool add in to perform through analysis of any Excel file automatically
7 lectures 54:03
Introduction to Workbook Analysis Report
04:38
Workbook Analysis Report - Part 1
11:13
Workbook Analysis Report - Part 2
09:21
Workbook, Worksheet and Cell relationships
12:01
Compare files
07:52
Clean Excess Cell Formatting and Workbook Passwords
03:25
+ Formula Auditing
7 lectures 55:19
Operators and Order of operations used in Excel for calculations
06:15
Precedents / dependent cells, Evaluate partial or full formula stepwise
10:14
Formula Auditing - Identifying Errors
09:40
Checking the Formula Text to locate errors and deceptions in spreadsheets
06:00
Referencing - Relative & Absolute, Structured, Named Ranges / cells - Part 1
12:40
Referencing - Relative & Absolute, Structured, Named Ranges / cells - Part 2
06:35
Precision as displayed
03:55
+ Pivot Tables
11 lectures 01:27:43
Legacy Pivot Table - Data Source - Single Sheets
11:11
Other Data Sources - Multiple Sheets, External Data and Existing pivot table
09:03
Simple Data Model and Power Pivot
09:22
Filtering the data - Report Level, Slicers, Timelines - Label & Value filters
14:48
Sort the data in Pivot Table - Ascending, Descending, Manual or Custom Sort
09:05
Grouping Records and rename fields or groups
03:54
Different options for summarize values by
04:57
Conditional Formatting - Heat Map, Data Bars, Sparklines
04:21
Issues with usage of calculated fields and items
07:00
Solution for FAQ for pivot table related issues
06:58
+ Overall governance around spreadsheets
3 lectures 15:39
Overall Governance around spreadsheets
03:16
SIPOC Model - suggested approach to create, maintain and monitor spreadsheets
06:29
+ Bonus Lecture
1 lecture 00:06
Bonus Lecture - discount on my other courses
00:06