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

Excel    |    Intermediate
  • 13 videos | 1h 29m 30s
  • Includes Assessment
  • Earns a Badge
Rating 4.6 of 538 users Rating 4.6 of 538 users (538)
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.

WHAT YOU WILL LEARN

  • 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

IN THIS COURSE

  • 2m 8s
  • 7m 29s
    Learn how to use absolute and relative cell references in worksheet formulae, lock cell references using the $ symbol, and use the trace error feature to debug formula errors. FREE ACCESS
  • Locked
    3.  Using the Subtotal Feature
    5m 55s
    In this video, learn how to 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. FREE ACCESS
  • Locked
    4.  Using the Filter Feature
    8m 6s
    In this video, you will use the Filter feature from the Data menu to focus on specific subsets of data. You will also illustrate how Filter and Subtotal differ in making changes to a workbook. Additionally, you will identify and avoid circular references using Excel Error Checking. FREE ACCESS
  • Locked
    5.  Checking Conditions Using IF()
    8m 24s
    In this video, find out how to perform conditional analysis using the IF() function, and combine logical conditions using the AND() and OR() functions. FREE ACCESS
  • Locked
    6.  Using Conditional Aggregates
    6m 47s
    In this video, find out how to 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. FREE ACCESS
  • Locked
    7.  One-dimensional Lookups With VLOOKUP() and HLOOKUP()
    11m 26s
    In this video, learn how to 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. FREE ACCESS
  • Locked
    8.  Two-dimensional Lookups Using INDEX() and MATCH()
    12m 1s
    In this video, find out how to 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. FREE ACCESS
  • Locked
    9.  Cleaning Data
    8m 4s
    In this video, you will implement common data-cleaning operations, such as eliminating duplicates via the Data menu, using CLEAN() to remove unwanted whitespace characters, correctly interpreting numbers stored as text, and using CONVERT() for an extensive range of unit conversion operations. FREE ACCESS
  • Locked
    10.  Parsing Strings
    4m 15s
    During this video, you will learn how to 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. FREE ACCESS
  • Locked
    11.  Performing Simple Data Validation
    8m 14s
    Upon completion of this video, you will be able to 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. FREE ACCESS
  • Locked
    12.  Performing Complex Data Validation
    5m 20s
    In this video, learn how to create lists for use in data validation and use named ranges and the INDIRECT() function to create a robust and dynamic data validation mechanism. FREE ACCESS
  • Locked
    13.  Course Summary
    1m 21s

EARN A DIGITAL BADGE WHEN YOU COMPLETE THIS COURSE

Skillsoft is providing you the opportunity to earn a digital badge upon successful completion on some of our courses, which can be shared on any social network or business platform.

Digital badges are yours to keep, forever.

YOU MIGHT ALSO LIKE

Course Solving data problems in Excel 365
Rating 4.4 of 201 users Rating 4.4 of 201 users (201)
Course Managing macros in Excel 365
Rating 4.4 of 163 users Rating 4.4 of 163 users (163)
Course Getting started in Excel 365
Rating 4.4 of 6009 users Rating 4.4 of 6009 users (6009)

PEOPLE WHO VIEWED THIS ALSO VIEWED THESE

Course Complete Guide to Excel 365: What-If Analysis, Solver, & Analysis ToolPak
Rating 4.6 of 213 users Rating 4.6 of 213 users (213)
Course API Clean and Secure Coding: API Secure Coding
Rating 4.2 of 659 users Rating 4.2 of 659 users (659)
Course Ethical Hacker: Hacking Techniques
Rating 4.4 of 53 users Rating 4.4 of 53 users (53)