Data Validation with Wingdings

Did you know you can indicate whether the user of your file is putting in the right information or not? And that too without using ‘Data Validation’ tool or ‘Conditional Formatting’?

Sounds unbelievable, but it could definitely be done. And the advantages are straight-forward as well. Firstly, you do not have to use volatile conditional formatting anymore. Secondly, you can make your spreadsheet more user-friendly. And we have Jordan “Jlookup” Goldmeier here to teach us all this.

So, let’s get kicking.

1 – The Setup

The picture below shows what you could potentially end up with. Yes, this is in Excel, and the check and cross marks are dynamic.

The first step is very simple. Enter a formula based on your input cells that will return TRUE or FALSE. For example, the green tick actually sees if the ‘Project Name’ is four characters long or not. Similarly, the cross checks if the ‘Project Budget’ is a number above $100,000.1

You guessed it right! We will convert those TRUE and FALSE into green checks and red crosses, respectively.

2 – Trick 1

This trick is extremely simple, yet very people know about it. If a formula in a cell yields TRUE or FALSE, just wrap it in brackets and put “–“ outside. Yes, that’s a double minus. What it does is that it will convert TRUE to 1 and FALSE to 0. For example, observe the image below. 2

Alternatively, you can simply write an IF function which returns a 1 or a 0 instead if this trick doesn’t seem to stick with you.

3 – Trick 2

‘Custom’ format these cells and type the following:


An illustration is shown in the picture.3

You might be wondering what these “P” and “Д have to do with everything. The answer is very simple: once you have applied this format, select the cells and choose their font to be ‘Wingdings 2’. Yes, these two letters represent a check and a cross, respectively, in this font.

4 – Some Explanation

There might be two more questions lingering around still. We will answer those here.

What are these “Color50” and “Color3” doing?
It is a little known fact that one can specify color the text within a cell should take within ‘Custom’ format. This is exactly what “Color50” (green) and “Color3” (red) are doing. Note that this is also why we do not need to use Conditional Formatting for your checks and crosses.

How did we get “P” and “Д?
These symbols under ‘Wingdings 2’ translate to a check and a cross. To use other symbols, the procedure is very simple:

Just go under ‘Insert’ tab and select ‘Symbol’. Now select ‘Wingdings 2’ from the dropdown menu and browse through symbols you like. When you find them, select them and click on ‘Insert’. Once you are done, just select the cell where you inserted these symbols and set a regular font (such as Calibri).

Now you can use the regular version of the symbols you liked instead of P or Ð.

What’s next?

What Jordan taught us is actually very cool. Practice these out! If you add your own personal touches to it, be sure to share it with us in the comments section below.

And do not forget to share this “awesomeness” with your friends and colleagues.

Jamani Arsalan


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!