Using Excel to Summarize, Display, and Test the Integrity of Quantitative Data

Contemporary auditing, monitoring, investigation, and evaluation invariably entail the acquisition, use, summarization, and display of data sets. The size of these data sets has progressively increased to the point that data “miners” can no longer “eyeball” or manually “clean” the thousands (if not millions) of entries in the typical body of data. Data stakeholders must employ automation to assess the integrity of, summarize, and display quantitative information.

Instructor: Bruce Truitt

Objectives:

Upon completion of this course, participants will be able to

• Find and eliminate random errors
• Find and determine how best to deal with blanks, including data replacement and data sensitivity strategies
• Find and eliminate embedded yet invisible character codes
• Find and deal with entries that appear to be but do not behave like numbers
• Determine if transferred data are identical
• Eliminate unnecessary spaces in data sets
• Parse data strings into multiple columns
• Combine multiple columns into one data string
• Format numbers and dates
• Define and use custom, user-specific data formats
• Find and deal with duplicate entries
• Use data filters
• Use subtotals
• Develop pivot tables
• Use basic Excel formulas to query a data set
• Find and deal with unusual items in the data set
• Summarize a data set in numbers
• Summarize a data set in graphs
• Extract a small probe sample from a data set

Who should attend: Auditors, Investigators, Evaluators, Reviewers, and Analysts

Program level: Beginning

Prerequisites: A working knowledge of Excel or similar spreadsheet software

Delivery method: Group live

Recommended CPE credit: 8 hours

Click here for more information about Bruce Truitt.