How to deal with missing values in Excel
Handling Missing Data & Data Validation in Excel
In this video, we will learn how to fill in missing values in Excel and set up data validation to keep entries consistent. This tutorial uses a fictional student dataset to rebuild missing email addresses with nested IF, CONCAT, and LOWER formulas; fill blank cells using Go To Special; and populate a missing country column with a lookup table and XLOOKUP (with a look at its optional arguments and a comparison to VLOOKUP/HLOOKUP). It closes with creating drop-down lists and error messages using data validation.
Chapters:
0:00 Introduction
0:54 Rebuilding emails with IF and CONCAT
3:42 Wrapping the formula in LOWER
4:59 Filling blanks with Go To Special
6:06 Fixing the country column with a lookup table
6:33 Building the lookup array with SORT and UNIQUE
7:12 Creating the return data with IF, LEFT, and TEXTAFTER
7:52 Using XLOOKUP
9:00 XLOOKUP optional arguments (if not found, match mode, search mode)
10:40 VLOOKUP and HLOOKUP compared
11:39 Setting up data validation
13:13 Testing the drop-down and error message
13:51 Recap
For more information on basic Excel skills, see my Microsoft Excel Basic Guide at https://lib.unb.ca/guides/microsoft-e...
Intro and outro music from Stereoalex at https://icons8.com/music/track/synthw...