Microsoft Excel

2.1 Opening Microsoft Excel 2.2 Microsoft Excel Startup Screen 2.4 Introduction to the Excel Interface 2.5 Customizing the Excel Quick Access Toolbar 2.6 More on the Excel Interface 2.7 Understanding the Structure of an Excel Workbook 2.8 Saving an Excel Document 2.9 Opening an Existing Excel Document 2.10 Common Excel Shortcut Keys 3.1 Entering Text to Create Spreadsheet Titles 3.2 Working with Numeric Data in Excel 3.3 Entering Date Values in Excel 3.4 Working with Cell References 3.5 Creating Basic Formulas in Excel 3.6 Relative Versus Absolute Cell References in Formulas 3.7 Understanding the Order of Operation 4.1 The structure of an Excel Function 4.2 Working with the SUM() Function – Excel 4.3 Working with the MIN() and MAX() Functions 4.4 Working with the AVERAGE() Function 4.5 Working with the COUNT() Function 4.6 Adjacent Cells Error in Excel Calculations 4.7 Using the AutoSum Command 4.8 Excel’s AutoSum Shortcut Key 4.9 Using the AutoFill Command to Copy Formulas 5.1 Moving and Copying Data in an Excel Worksheet 5.2. Inserting and Deleting Rows and Columns 5.3. Changing the Width and Height of Cells 5.4 Hiding and Unhiding Excel Rows and Columns 5.5 Renaming an Excel Worksheet 5.6. Deleting an Excel Worksheet 5.7 Moving and Copying an Excel Worksheet 6.1 Working with Font Formatting Commands 6.2. Changing the Background Color of a Cell 6.3. Adding Borders to Cells 6.4. Formatting Data as Currency Values 6.5. Formatting Percentages 6.6. Using Excel’s Format Painter 6.7. Creating Styles to Format Data 6.8. Merging and Centering Cells 6.9. Using Conditional Formatting 7.1 Inserting Images and Shapes into an Excel Worksheet 7.2 Inserting Shapes In Excel 7.3 Formatting Excel Shapes 7.4. Working with Excel SmartArt 8.1. Creating an Excel Column Chart 8.2. Working with the Excel Chart Ribbon 8.3. Adding and Modifying Data on an Excel Chart 8.4. Formatting an Excel Chart 8.5. Moving a Chart to another Worksheet 8.6. Working with Excel Pie Charts 9.1. Viewing your Document in Print Preview 9.2. Changing the Margins, Scaling and Orientation 9.3 Adding Header and Footer Content 9.4 Printing a Specific Range of Cells 10.1. Intro to Excel Templates 10.3. Opening an Existing Template 10.4. Creating a Custom Template 13.1 Understanding Excel List Structure 13.2 Sorting a List Using Single Level Sort 13.3 Sorting a List Using Multi-Level Sorts 13.4 Using Custom Sorts in an Excel List 13.5 Filter an Excel List Using the AutoFilter Tool 13.6 Creating Subtotals in a List 13.7 Format a List as a Table 13.8 Using Conditional Formatting to Find Duplicates 13.9 Removing Duplicates in Excel 14.1 Excel DSUM Function Single Criteria 14.2 Excel DSUM Function with OR Criteria 14.3 Excel DSUM Function with AND Criteria 14.4 Excel Function: DAVERAGE() 14.5 Excel Function: DCOUNT() 14.6 Excel Function: SUBTOTAL() 15.1 Creating an Excel Data Validation List 15.2 Excel Decimal Data Validation 15.3 Adding a Custom Excel Data Validation Error 15.4 Dynamic Formulas by Using Excel Data Validation Techniques 16.1 Importing Data from Text Files into Excel 16.2 Excel 2019

Excel Basics

15.3 Adding a Custom Excel Data Validation Error

01/02/2021 46 lượt xem

We’ve seen a few different types of data validation instead of Excel.

Little picklists dropdown lists that the users can choose from.

We did that on the make column and we did a decimal range value.

Now we saw that if you put something in there that doesn’t fall within the criteria of the validation, you get an error message.

Just to remind us, I dropped down in the cell B21, I am typing with error spelling “Frd”:

Adding a Custom Excel Data Validation Error 1

The value doesn’t match the validation restrictions the find in the cell.

Little error message that’s generated by the data validation command.

That’s a nice error message but I don’t think it’s very descriptive.

It’s not very user friendly it really doesn’t tell me what I did wrong at least directly little a little more indirect I guess not as intuitive.

What I want to do here is customize the error message that’s presented when you put something that’s incorrect into a column that has data validation on it.

Highlight the cells that I want to modify from B4 to B30, that’s the range that I applied this to before.

You want to select your exact same range. I’m going to go back up to Data tab, down back to data validation data validation. Earlier we were messing with the Settings tab:

Adding a Custom Excel Data Validation Error 2

That’s where you set up your criteria for your data validation.

Right next door we got two other tabs, let’s jump over to the third tab Error alerts.

Here we get to customize the error message. And this is pretty neat.

You can a little bit more descriptive a little more user friendly.

Let the users know what they need to do in order to fix the issue.

This is actually two things that deals with the data validation box that opens up on your screen:

1 – It’s an icon.

Click on the dropdown there’s three options there and you can see that there’s different icons, that present themselves but it’s not only an icon. Once they get the error you’ll recall that defaults.

2 – The Title and messenger

Example: Warning, you must pick a value from the drop down menu.

Adding a Custom Excel Data Validation Error 3

Then Click OK.

Now you can test again the alert:

Adding a Custom Excel Data Validation Error 4

But then they have an option to continue, Yes or No. In this case it’s validating the data but it allows the user just still press forward with that incorrect value. Try this out customizing their message.

We want to be as direct as short and direct to the point let the users know what they did wrong how they can fix it.