Insert a symbol to a cell with text based upon criteria


I would like to add a symbol to text in a cell based upon criteria in another cell.  For example, I do a lot of diversity reporting, next to a name I would like to insert a symbol if the person is Male or Female.  I tried using an if statement with an ampersand which works but I can only add plain text.  Ideally, I'd like a symbol in a different color, so John Doe * but I'd like the askterisk to be a star and I'd like it to be blue if it's a male and red if it's a female.




Don't put the symbol in the same cell as the name. Even if you manage to get the exact look that you want with this symbol, it will not be easy to maintain in the future or keep exactly how you want.

Instead, use conditional formatting and keep the star in its own column.

  1. Add a new and empty column next to the name.
  2. Input a star into the first cell of the new column.
    1. Insert tab > Symbol button (all the way on the right) > select a start from the list and click Insert once and then close the window.
  3. Make sure the cell with the star is selected and then go to Conditional Formatting - Home tab > Conditional Formatting > New Rule
  4. From the Rule Type box, select Use a formula to determine which cells to format
  5. Enter this formula for the female =$B1="female"
    1. This assumes that B1 is the first cell in the column of data.
  6. Click the Format button and choose the desired formatting for the cell.
  7. Repeat Steps 3-6 for the male but use this formula instead: =$B1="male"
  8. Hit OK to close the window and the copy the cell with the star and paste it down the column for the entire list of data.

This should get you the desired results.


Answer the Question

You must create an account to use the forum. Create an Account or Login