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.

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 (✗)

add tick cross into your data validation and use it in ifs and conditional formatting

Add a tick into your Data Validation

It is surprisingly easy. 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)

add-tick-cross-into-your-data-validation-and-use-it-in-ifs-and-conditional-formatting

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 or cross symbol

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

add-tick-cross-into-your-data-validation-and-use-it-in-ifs-and-conditional-formatting

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.

add-tick-cross-into-your-data-validation-and-use-it-in-ifs-and-conditional-formatting

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

Using a tick or 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)

add-tick-cross-into-your-data-validation-and-use-it-in-ifs-and-conditional-formatting