CPE Packages (Incl. Ethics) for Multiple States and Qualifications Available. Price $4/credit - CLICK HERE to view.

Excel’s Data Validation Tools - keep out bad data from the start

4 (2)

Dennis Taylor

Taylor Associates

Tuesday, January 26, 2021 | 01:00 PM EDT

  • CPA (Canada)
  • CPA (US)
  • CVA
  • Tax Preparer

1 Credit

$10

Subject Area

Computer Software and Applications

Webinar Qualifies For

1 CPE credit of Computer Software & Applications for all CPAs

1 CPD credit (Verifiable) for Canadian CPAs

1 CE credit of Computer Technology for Maryland Tax Preparers

1 General Educational credit for Tax Professionals / Bookkeepers / Accountants

Course Description

Most of Excel’s data management tools focus on the handling of existing data, for example, sort, that let’s you re-arrange rows of data into convenient clusters, or filter, which lets you view just the data you need to see at any given moment. But Data Validation gives you control over data that’s about to be entered into a list – keep out bad data at the start of the data creation process.

 

You need to make sure that salaries fall within a certain range, or that dates (or times of day) have starting and ending limits, or that ID numbers have an explicit number of characters, or that certain kinds of fields can only contain entries available from a master list of set entries.

 

Among the strongest and most efficient validation options is the idea of a pick list - drastically reduce data entry with drop-down clickable lists – no typing (and no typing mistakes) as well as standardizing entries as you accelerate the data entry process. Users of Microsoft 365 (formerly known as Office 365) can take advantage of the UNIQUE function to build dynamic pick lists.

Although Data Validation features concentrate on controlling new data going in to a worksheet, it also lets you identify data that violates new rules that you have set up on existing data.

 

Learning Objectives

  • Control the upper and lower limits of columns containing numeric entries, like salaries, sales, items, etc.
  • Use the Input Message and Error alert options to provide guidance on data entries
  • Keep the entries in a date column within a certain range; or limit either the starting or ending date.
  • Keep the entries in a time column within a certain range; or limit either the starting or ending time.
  • Control the length of entries; or that entries be text; or that entries be numeric
  • Provide an external list, on the same or different worksheet, of entries allowed within a column and provide a drop-down (pick) list of those entries – no typing, accurate entries ensured, and time saved
  • Use the UNIQUE, and possibly the SORT function, to provide a dynamic list as a source for a pick list
  • Create inter-dependent (multi-tiered) drop-down lists – selecting a state, for example, then, from a different list, only cities from that state
  • Locate all worksheet cells that have data validation rules in place; identify all cells that have the same validation rules as a specific cell
  • Encircle all worksheet cells that violate rules set up on existing data

Who Should Attend?

  • Accountant
  • Accounting Firm
  • Accounting Managers
  • Bookkeepers & Accountants & Tax Preparers
  • CPA (Industry)
  • CPA - Mid Size Firm
  • CPA - Small Firm
  • Senior Accountant
  • Staff of Accounting Firm
  • Young CPA

Testimonial

Array
(
    [no_of_record] => 2
    [average] => 4.0000
)
 

4

(2)
50%
0%
50%
0%
0%

TF

Great informative session.