In this course we discuss how to make data that comes to us in a ‘dirty’ and unusable format useful.  Many times, report writers give us data with subtotals or other information that are not very helpful at all.  In this course we focus on how to make this data useful. 

Finally, we explore new ways to create unique identification formulas that will enhance the relational database aspect of your data (i.e. COUNTIFS, SUMIFS, FIND, SEARCH, VLOOKUP, etc.)

Learning Objectives
  • Identify situations wherein data from typical accounting systems fails to give us what we’re truly looking for (i.e. getting useless subtotals that make tallying and converting data to a tabular format tricky)
  • Discover new ways to create useful management reports from systems that don't quite give us what we exactly are looking for by means of cleaning up this ‘dirty’ data.
  • Explore new ways to create unique identification formulas that will enhance the relational database aspect of your data (i.e. COUNTIFS, SUMIFS, FIND, SEARCH, VLOOKUP, etc.)

 

 

 

Last updated/reviewed: March 22, 2024
21 Reviews (48 ratings)

Reviews

2
Member's Profile
Supporting materials were missing workbooks for Accounts Payable, Accounts Receivable, and "Enhancing your lookup skill set". These workbooks are essential to follow along and for hands on learning. The spreadsheet used as an example in the "Enhancing your lookup skill set" was way too complicated. Something much simpler could have been used and shown better, more practical examples of HLOOKUPs and VLOOKUPs functions
4
Anonymous Author
These course contains great information! I believe that it would be easier to understand it if the files in excel were provided. That way students could download and follow the files for the lectures. Overall, this is a very interesting course. You need to have basic knledge of formulas and Vlookups. I highly recommend it.
5
Member's Profile
This was one of the most practical applications of excel that I've seen in this series. Many of us at SMB need to deal with data that we get from the accounting system. The tricks and tips will go into practice right away.
3
Member's Profile
Presented a number of tools for distilling and cleaning up Excel data derived from ERP software programs. I found the information very useful. There should have been more demonstrations using large data sets.
4
Member's Profile
Useful information on dealing with ERP data where the information is on more than one row, which is unfortunately all too common, and turning it into searchable information.
3
Member's Profile
This course is clear but very basic. It would be great if the course provider could provide more advanced techniques too.
5
Member's Profile
Great course. Very insightful. We need more courses like this specifically designed for "corporate accountants"
5
Anonymous Author
It overall provided a great understanding of the basic excel concepts to help clean up our work papers.
4
Anonymous Author
Usually controller do not manipulate data so good idea for these working with them to learn these hints
4
Member's Profile
I wish instructor took some time to spell out some of conditions in functions he was demonstrating
5
Member's Profile
Helpful to cleaning up data coming from a downloaded report from a system like QuickBooks.
3
Member's Profile
The text in the spreadsheet was too small to follow the instructor on a 17 inch monitor
5
Anonymous Author
Good course to learn some tricks to handle data dumped from an ERP. I would recommend.
4
Member's Profile
Good techniques for sorting large amounts of data to a format usable for accountants.
3
Anonymous Author
Basic functions suited for someone who is not too familiar with Excel functions.
3
Anonymous Author
I like this course with a step by step video so it is clear for me to follow.
3
Anonymous Author
For whatever reason I was unable to expand the presentation to a full screen.
4
Anonymous Author
Seemed a little repetitive in spots, but good intro content for Excel users.
5
Anonymous Author
Excellent course providing a quick way to clean up data out of Sage
5
Member's Profile
Would have been nice to have the Excel files used to follow along.
4
Member's Profile
Good course for begginers. Well presented.
show all reviews
Prerequisites
Course Complexity: Intermediate

No advanced preparation or prerequisites are required for this course but a general working knowledge of Microsoft Excel is preferred. 

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Accounts Payable 12:23
  Accounts Receivable 11:56
  Cleaning Up Your Data Made Easy5:38
  Enhancing Your Lookup Skill Set 10:12
Continuous Play
  Excel for the hands-on Controller:CFO - How to clean up 'dirty' data and enhance your lookup skill set!40:09
SUPPORTING MATERIALS
  Sample General Ledger Detail ReportXLS
  Excel for the hands-on Controller/CFO GlossaryPDF
REVIEW and TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam