Ultimate Excel Course #6: Validating and Protecting Excel Data

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
9 Lessons (41m)
    • 1. Introduction

    • 2. Validating the the Number of Characters

    • 3. Creating Date Validation Rules

    • 4. Drop Down Lists for Easy Data Entry

    • 5. Dependent Lists to Break Up Large Lists

    • 6. Custom Data Validation Messages

    • 7. Encrypt a File from Unauthorised Access

    • 8. Protect the Structure of a Workbook

    • 9. Protect Elements of a Worksheet


About This Class


This class is #6 in a series called the Ultimate Excel Course.

In this class you will learn how to effectively validate data entry, and to protect important data on a spreadsheet in Excel.

These Excel skills are often overlooked as users want to learn how to create charts, PivotTables and powerful formulas. Yet without, clean and accurate data none of those Excel features will work correctly.

Although it is the responsibility of the user to correctly enter data and to avoid editing the wrong cell, mistakes happen. So if we can limit, or possibly prevent them from happening. It is important.

In this class we will look at some Data Validation rules that you can apply right away. We will also cover a clever technique to create dependent drop down lists (a commonly asked question on my training).

We then look at protecting parts of a spreadsheet from accidental editing. We lock down specific Excel features, but ensure the user is able to do what they need to.

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enroll and let's do this.

Please note that this is class #6 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Functions and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your Data Ranges as Tables for Superior Management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot