Udemy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Development
Web Development Data Science Mobile Development Programming Languages Game Development Database Design & Development Software Testing Software Engineering Development Tools No-Code Development
Business
Entrepreneurship Communications Management Sales Business Strategy Operations Project Management Business Law Business Analytics & Intelligence Human Resources Industry E-Commerce Media Real Estate Other Business
Finance & Accounting
Accounting & Bookkeeping Compliance Cryptocurrency & Blockchain Economics Finance Finance Cert & Exam Prep Financial Modeling & Analysis Investing & Trading Money Management Tools Taxes Other Finance & Accounting
IT & Software
IT Certification Network & Security Hardware Operating Systems Other IT & Software
Office Productivity
Microsoft Apple Google SAP Oracle Other Office Productivity
Personal Development
Personal Transformation Personal Productivity Leadership Career Development Parenting & Relationships Happiness Esoteric Practices Religion & Spirituality Personal Brand Building Creativity Influence Self Esteem & Confidence Stress Management Memory & Study Skills Motivation Other Personal Development
Design
Web Design Graphic Design & Illustration Design Tools User Experience Design Game Design Design Thinking 3D & Animation Fashion Design Architectural Design Interior Design Other Design
Marketing
Digital Marketing Search Engine Optimization Social Media Marketing Branding Marketing Fundamentals Marketing Analytics & Automation Public Relations Advertising Video & Mobile Marketing Content Marketing Growth Hacking Affiliate Marketing Product Marketing Other Marketing
Lifestyle
Arts & Crafts Beauty & Makeup Esoteric Practices Food & Beverage Gaming Home Improvement Pet Care & Training Travel Other Lifestyle
Photography & Video
Digital Photography Photography Portrait Photography Photography Tools Commercial Photography Video Design Other Photography & Video
Health & Fitness
Fitness General Health Sports Nutrition Yoga Mental Health Dieting Self Defense Safety & First Aid Dance Meditation Other Health & Fitness
Music
Instruments Music Production Music Fundamentals Vocal Music Techniques Music Software Other Music
Teaching & Academics
Engineering Humanities Math Science Online Education Social Science Language Teacher Training Test Prep Other Teaching & Academics
AWS Certification Microsoft Certification AWS Certified Solutions Architect - Associate AWS Certified Cloud Practitioner CompTIA A+ Cisco CCNA Amazon AWS CompTIA Security+ Microsoft AZ-900
Graphic Design Photoshop Adobe Illustrator Drawing Digital Painting InDesign Character Design Canva Figure Drawing
Life Coach Training Neuro-Linguistic Programming Personal Development Personal Transformation Mindfulness Life Purpose Meditation CBT Emotional Intelligence
Web Development JavaScript React CSS Angular PHP Node.Js WordPress Vue JS
Google Flutter Android Development iOS Development React Native Swift Dart Programming Language Mobile Development Kotlin SwiftUI
Digital Marketing Google Ads (Adwords) Social Media Marketing Google Ads (AdWords) Certification Marketing Strategy Internet Marketing YouTube Marketing Email Marketing Retargeting
Microsoft Power BI SQL Tableau Business Analysis Data Modeling Business Intelligence MySQL Data Analysis Blockchain
Business Fundamentals Entrepreneurship Fundamentals Business Strategy Business Plan Startup Freelancing Online Business Blogging Home Business
Unity Game Development Fundamentals Unreal Engine C# 3D Game Development C++ 2D Game Development Unreal Engine Blueprints Blender
30-Day Money-Back Guarantee
Business Business Analytics & Intelligence Excel

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
Rating: 4.2 out of 54.2 (22 ratings)
246 students
Created by Abhay Gadiya
Last updated 5/2020
English
30-Day Money-Back Guarantee

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

Featured review

Bhaskar Joshi
Bhaskar Joshi
21 courses
11 reviews
Rating: 5.0 out of 55 months ago
It is easy to apply functions in the spreadsheet but very tricky for users to find out the reason why it is not working. I learned through this course really some new tips & tricks to audit Excel files and identify errors & mistakes. I applied these tricks in my work-life and solved those errors which were faced before I joined. Thank You! Abhay Gadiya Sir for making this stuff. Highly recommend for Accounts & Finance and Business Professionals.

Course content

8 sections • 39 lectures • 5h 6m total length

  • Preview07:55
  • Download and Install - Power Query and ASAP Utilities
    05:29

  • Preview03:35
  • Ribbon and Quick access toolbar
    07:38
  • Selecting Data in Excel and Navigating through file
    07:28

  • 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

  • Preview05:33
  • 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

  • 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

  • Preview07:04
  • 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
    03:16
  • SIPOC Model - suggested approach to create, maintain and monitor spreadsheets
    06:29
  • Preview05:54

  • Bonus Lecture - discount on my other courses
    00:06

Instructor

Abhay Gadiya
Best seller instructor at Udemy for Power BI and Power Excel
Abhay Gadiya
  • 4.3 Instructor Rating
  • 513 Reviews
  • 2,487 Students
  • 7 Courses


I am Chartered Accountant, Certified Fraud Examiner and have completed Diploma in Information System Audit (Indian equivalent exam for CISA).

I have circa 16+ years of experience into Data Analytics, Internal Audits, External (Statutory) Audits, SOX Compliance, Risk Assessment and Regulatory Compliance.  I have audited various entities under BFSI, Automotive Manufacturing, ITES, Infrastructure and Pharmaceuticals sectors. I have worked circa 9 years with Big4 consultancy firms – Deloitte, Price Waterhouse and other with Bank of New York Mellon.

I have conducted various workshops at Institute of Chartered Accountants of India. Approximately 600+ people have attended these workshops and have given extremely positive feedback.

  • Udemy for Business
  • Teach on Udemy
  • Get the app
  • About us
  • Contact us
  • Careers
  • Blog
  • Help and Support
  • Affiliate
  • Impressum Kontakt
  • Terms
  • Privacy policy
  • Cookie settings
  • Sitemap
  • Featured courses
Udemy
© 2021 Udemy, Inc.