Add tick/ cross into your Data Validation and use it in IFs and Conditional Formatting

To make your drop down lists more visually appealing you may want to use a tick or a cross to indicate acceptance (or not) of an item. Below some tricks to add a tick/ cross into your Excel Data Validation.

So below I want the person to see the tick and cross in the data validation dropdown AND I want the cell in C7 to change depending on whether they use a tick (✓) or a cross (✗)

YouTube Use a tick/ cross in Excel

Add a tick/ cross into your Excel Data Validation

It is surprisingly easy to add a tick into data validation. Firstly you need to find a suitable tick or cross.

So you can copy a tick (highlight the one below and click CTRL and C).

✓

then go into Excel, click on the relevant cell and:

1. Go to the DATA tab
2. Click on DATA VALIDATION
3. Choose the list Option, and
4. paste the tick into the source (CTRL + V)

You need to repeat the same thing with a cross which you put after the tick with a comma before it (bit tedious but can make it a bit faster with clever use of the clipboard). You can copy the cross below to try it out

✗

Now it will look much nicer and you can choose the tick or cross from within the data validation drop down list.

Base Conditional Formatting on the tick/ cross symbols

But what if you want to make the tick turn green and the cross turn red when chosen? It is the same process except you now use the conditional formatting tool. The key is to copy the tick or cross into your clipboard before you activate the Conditional Formatting tool.

So in order to make the colour change depending on whether you choose a tick or a cross you need to:

1. Copy the tick into your clipboard (CTRL + C)
2. Click in the appropriate cell
3. Click on the HOME tab
4. Click on CONDITIONAL FORMATTING
5. Click on HIGHLIGHT CELLS, and click on EQUAL TO as show below

Now:

6. Paste the tick in (CTRL + V)

7. Choose how you want the format to look based on Excel suggestions or click on CUSTOM format to create your exact style.

You need to repeat the same process so that when a cross is chosen, it turns red.

Using a tick/ cross in an IF statement (and other functions)

What if we want to perform a function based on whether a tick or a cross is chosen. So in this case if a cross is chosen (they don’t have a computer) the price of computer hire per day will appear.

Unsurprisingly the process is similar.

• Copy the tick or cross into the clipboard,
• create your function and
• paste in the tick or cross into the appropriate place in your function.

So below the IF function we created was

=IF(B7<>”✓”,342,0)

Want to learn more about Microsoft Excel? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Related

Copy Data Validation to cells that have data in them

How to put m2 in excel cell

Sum cells by colour with the Data Filter

Add the happy face emoji into Excel

Online Excel Course for you