SkillSoft Explore Course – Lambers, Inc.

BUY NOW! STARTING AT ONLY $299!

IT Professional Curricula Enterprise Database Systems Solution Area Data Visualization A Complete and Comprehensive Guide to Excel

Excel is not only used for aggregating and analyzing data, but also for data cleansing. There are several data cleaning, validation, and checking techniques available, some of which are among Excel's most well-known and widely-used functions.

In this course, you'll start by using various string and data manipulation functions to clean data and fill in missing values.

Next, you'll perform simple data validation based on specific numeric thresholds and text lengths, before moving on to validation using lists. You'll then combine data validation to implement a formidable, dynamic data validation mechanism using named ranges and the INDIRECT formula.

Finally, you'll use one-dimensional lookups - the classic HLOOKUP and VLOOKUP worksheet functions - as well as more complex, two-dimensional lookups utilizing a combination of INDEX and MATCH.Â

Asset ID	it_dacgexdj_05_enus
Course Type	Video Course
Course Category	Enterprise Database Systems Solution Area

Objectives
Complete Guide to Excel 365: Validating, Cleaning, & Performing Lookups on Data discover the key concepts covered in this course Use absolute and relative cell references in worksheet formulae, lock cell references using the $ symbol and named ranges, and use the trace error feature to debug formula errors Â analyze data using the Subtotal feature, specify when the subtotal function should be applied, compute group summaries and grand totals, and remove changes to the workbook made by the Subtotal menuÂ use the Filter feature from the Data menu to focus on specific subsets of data, illustrate how Filter and Subtotal differ in making changes to a workbook; identify and avoid circular references using Excel Error Checking Â perform conditional analysis using the IF() function, and combine logical conditions using the AND() and OR() functions use conditional aggregate functions, such as COUNTIF() and COUNTIFS(), to perform aggregate operations based on specific conditions and recognize the semantics of predicates created from strings for use within these functions Â utilize VLOOKUP() and HLOOKUP() functions for one-dimensional lookups that join data ranges based on a common column, then use relative cell references for the lookup value and absolute cell references for the lookup rangeÂ use the INDEX() and MATCH() functions to perform two-dimensional lookups and nest two calls to MATCH() within a single call to INDEX() to match on both row and column Â implement common data-cleaning operations, such as eliminating duplicates via the Data menu, use CLEAN() to remove unwanted whitespace characters, correctly interpret numbers stored as text, and use CONVERT() for an extensive range of unit conversion operations Â work with external hyperlinks and parse them using string functions, such as RIGHT(), LEFT(), MID(), SUBSTITUTE() and LEN(), and fill in blank/missing values using the Find & Select menu option and the Go To Special feature Â specify data validation constraints, configure bounds on acceptable values, create user-friendly error messages, and validate data based on decimal, whole number, text length, and date/time values Â create lists for use in data validation and use named ranges and the INDIRECT() function to create a robust and dynamic data validation mechanism Â summarize the key concepts covered in this course

Objectives

Complete Guide to Excel 365: Validating, Cleaning, & Performing Lookups on Data

discover the key concepts covered in this course
Use absolute and relative cell references in worksheet formulae, lock cell references using the $ symbol and named ranges, and use the trace error feature to debug formula errors Â
analyze data using the Subtotal feature, specify when the subtotal function should be applied, compute group summaries and grand totals, and remove changes to the workbook made by the Subtotal menuÂ
use the Filter feature from the Data menu to focus on specific subsets of data, illustrate how Filter and Subtotal differ in making changes to a workbook; identify and avoid circular references using Excel Error Checking Â
perform conditional analysis using the IF() function, and combine logical conditions using the AND() and OR() functions
use conditional aggregate functions, such as COUNTIF() and COUNTIFS(), to perform aggregate operations based on specific conditions and recognize the semantics of predicates created from strings for use within these functions Â
utilize VLOOKUP() and HLOOKUP() functions for one-dimensional lookups that join data ranges based on a common column, then use relative cell references for the lookup value and absolute cell references for the lookup rangeÂ
use the INDEX() and MATCH() functions to perform two-dimensional lookups and nest two calls to MATCH() within a single call to INDEX() to match on both row and column Â
implement common data-cleaning operations, such as eliminating duplicates via the Data menu, use CLEAN() to remove unwanted whitespace characters, correctly interpret numbers stored as text, and use CONVERT() for an extensive range of unit conversion operations Â
work with external hyperlinks and parse them using string functions, such as RIGHT(), LEFT(), MID(), SUBSTITUTE() and LEN(), and fill in blank/missing values using the Find & Select menu option and the Go To Special feature Â
specify data validation constraints, configure bounds on acceptable values, create user-friendly error messages, and validate data based on decimal, whole number, text length, and date/time values Â
create lists for use in data validation and use named ranges and the INDIRECT() function to create a robust and dynamic data validation mechanism Â
summarize the key concepts covered in this course