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:58
    • 2. Validating the the Number of Characters

      2:21
    • 3. Creating Date Validation Rules

      2:21
    • 4. Drop Down Lists for Easy Data Entry

      4:41
    • 5. Dependent Lists to Break Up Large Lists

      5:23
    • 6. Custom Data Validation Messages

      6:44
    • 7. Encrypt a File from Unauthorised Access

      2:46
    • 8. Protect the Structure of a Workbook

      5:44
    • 9. Protect Elements of a Worksheet

      7:48

Project Description

For this class project I have two exercises for you to work through. Download the class-project.xlsx file.

Exercise 1 - Validating Date Entries

  1. Open the class-project.xlsx spreadsheet.
  2. Apply a data validation rule to cells E2:E11. The rule should ensure that the date entered is between the date invoiced in column C and today's date.

    Create your own error alert so that the user knows they have entered a date outside of this range.

Exercise 2 - Setting a Warning Message

  1. Open the class-project.xlsx file.
  2. Apply a Data Validation rule to cells D2:D11 that prevents a value greater than 10,00 from being entered.
  3. Set an error alert using Warning style so that it does not completely prevent this entry, but instead warns the user. Write an appropriate message.

Student Projects