Use Excel Custom Number Formatting for Data Validation

Use Excel Custom Number Formatting for Data Validation

For more Excel tips and tricks visit me at http://bradedgar.com.

In this video, we take a look at how we can use Excel custom number formatting as a data validation tool for our data set and area/range of cells.

Step #1: Select your entire range in which you want to apply Excel data validation to.

Step #2: Type CTRL +1 once you've highlighted your data set and then this will bring up the format cells screen. Head down to the custom category.

Step # 3: The standard syntax applies to your number format here. [positive numbers; negative numbers; zero; text]. The syntax before the first semi-colon will be the syntax that gets applied to +ve numbers, the syntax prior to the 2nd semi-colon will be applied to negative numbers, the third ";" will be zero and then the syntax after the last semi-colon will be applied if a text value is placed into the cell.

STEP # 4: This is where you can get creative. Because you can use custom number formatting to tell Excel what to display if text is placed in any of the cells that the custom number formatting is applied to, you can put in quotations any statement that you would like. This means that if text is applied to any of the cells, no matter what is placed in the cell will be displayed as Please enter a number.

Here are a few other links and resources for you:

Excel Custom Number Formatting: How To Conditionally Format Text Fields With Icon Sets Using Number Formatting:

http://bradedgar.com/excel-custom-number-formatting-conditionally-format-text-fields-icon-sets-using-number-formatting/

5 Incredible Number Formatting Tricks That Will Impress Your Boss:

http://bradedgar.com/5-incredible-number-formatting-tricks-will-impress-boss/

How to Use Icon Sets in Excel:

http://www.excel-easy.com/examples/icon-sets.html

Excel conditional formatting Icon Sets, Data Bars and Color Scales:

https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/

The Ultimate Guide to Custom Number Formatting:

http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/

-~-~~-~~~-~~-~-
Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard"
https://www.youtube.com/watch?v=MCBmPnaKf_g
-~-~~-~~~-~~-~-