Data Validation
A Backdoor To
Master
Excel
Complete course with Downloadable Files
Nabil Mourad
Microsoft MVP
Copyright 2020 Nabil Mourad
All Rights Reserved
No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise without signed permission from the author, except for the use of brief quotations for review purposes.
Limit of Liability/ Disclaimer of Warranty : While the author has used his best efforts in preparing this book, he makes no representations or warranties with respect to the accuracy or completeness of the contents of this book. The author does not guarantee the reader's result will match those of the author. The advice and strategies contained herein may not be suitable for your situation. The author is not engaged in offering financial, tax, legal or other professional services by publishing this book. You should consult a licensed professional where appropriate. The author shall not be liable for any errors or omissions, loss of profit or any other commercial damages including but not limited to special, incidental, consequential, or other damages.
Trademarks : Microsoft and Excel are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
ISBN: 978-1-7774109-1-9
What did fellow MVPs say about this book?
Data Validation A Back Door to Master Excel is the most thorough book about using Data Validation in Excel. The author, Nabil Mourad explains clever uses of Data Validation far beyond simply limiting what can be entered in a cell .
Bill Jelen
Microsoft Excel MVP, Publisher MrExcel.com
Data Validation provides your users with convenience in the form of drop-down lists, but more importantly, it provides you with protection against those unruly users who try to enter any old data into a cell by ensuring the data entered meets criteria you set. As a result, you spend less time fixing messy data that was entered incorrectly in the first place and have more time to create the clever interactive techniques Nabil covers in this comprehensive guide to Data Validation .
Mynda Treacy
Microsoft Excel MVP, MyOnlineTrainingHub.com
It's thrilling to see this topic covered in such great detail. Data inputs and data quality aren't as sexy as AI or data visualization, but data quality is important no matter what's being done with data. And data validation is critical in supporting data quality. I'm so glad to see Nabil take this on. He's a fantastic instructor and an experienced data professional. A true warrior in the battle to keep data clean and useful .
Oz du Soleil
Microsoft Excel MVP, LinkedIn Instructor
Introduction
Dynamic Data Validation is a Game changer in Excel, at the basic level it controls what a user can enter or cannot enter in a cell, you are in control of the user's input.
It also makes data entry easier and ensures the integrity of your worksheet by avoiding typing errors when users can simply select an option from a drop list.
In a more advanced scenario data Validation can make a regular worksheet look like a dynamic dashboard.
In this course, we explore the full potential of the 8 types of data validation.
We start with the basic functionality, then we discuss 2 types in profuse details: Custom and List Data validation. Along the way, we will be tackling almost every functionality in Excel and relate it to our topic. Through Data Validation I will be covering over 60 different functions, Charts, Slicers, Pivot Tables, Conditional Formatting, Macros, Power Query, Data Model, Dynamic Arrays, DAX and VBA.
Downloadable Exercise Files
Because practicing is an integral part of the learning process, I am providing all the exercise files so that you can follow step by step.
You can download the exercise files from the link:
https://officeinstructor.com/books/
Password on last page
Be ready to elevate your Excel Skills and learn lots of Tips and tricks along the way.
Who are my target readers?
My students are Excel users who want to impress their clients and colleagues by creating: Powerful, dynamic, and unique functionalities.
Whether you are an Accountant, Financial Analyst, Business Administrator, project Manager an Educator, if you are working in Human resource, Insurance, sales, or marketing this course is for you.
Excel is the most widely used spreadsheet application, being used by over 750 million people across the globe: This course will ensure making the bottom line of your worksheets look Great.
Who are NOT my target students?
This course is not for beginner Excel users as I do not spend much time on how to navigate in Excel, or any of the foundational principles.
Course Goal
By the end of this course you will be able to create effective and dynamic worksheet functionality cutting the data entry time to just a tiny fraction of what it currently is. You will ensure the data input is only what you want while the output will be impressive to your clients, colleagues, and boss.
The Author
My name is Nabil Mourad.
I am a Microsoft Excel MVP ( Most Valuable Professional ).
A Microsoft Certified Trainer since 2008
A Microsoft Office Specialist - Master in All versions of Office to date.
I work as a professor of Computer applications in Toronto Canada and I deliver Corporate Training at Client Site or remotely. Through my work and Popular YouTube Channel "OfficeInstructor" , I taught Excel to hundreds of thousands of Clients across the Globe.
Dedication
I dedicate this book
To my wife, Abeer, who has been my partner in life, since 1990.
To my sons Karim and Ramy, who provide a constant source of joy and pride.
Table of Contents
Chapter One- The Basics of Data Validation
01- Types of Data Validation
File: 01- Types of Data Validation.xlsx
Data Validation is a functionality in Excel that enables you to:
1- Set what a user can input in a cell or what a user cannot enter.
2- Make data entry easier.
3- Ensure the consistency of your worksheet by avoiding errors.
4- Guide the user upfront to what should be entered in a cell.
5- Deliver a customized message explaining what the reason is of rejecting your entry.
6- Create a dynamic setup by changing your selection from a drop list.
You do not apply Data Validation to cells and ranges, you are just changing the default type of Data Validation which is "Any Value".
There are eight types of Data Validation that we will explore in this section, then, in next sections we will dive deeper into 2 of them: "List" and "Custom".
Finally, we will be combining the functionality of Data Validation with almost everything else in Excel.
