Home > Blog

Use Excel custom formats to show emoji and text

If you want to show a status based on a number, for instance:

formatting example

You could use an IF statement such the one below with some conditional formatting:

=IF(A1>0,"?"&"ahead",IF(A1=0,"?"&"on track","?"&"behind"))

To enter emojis in Excel, use the Windows key and full stop or period: <WIN><.>

However, there is a much easier way assuming your “trigger” values are positive OR negative OR zero, and this is to use the Custom Formatting functionality in Excel.

To access this, access the Cell Format dialogue by either clicking on the Home Ribbon and then Cells / Format or using the keyboard shortcut <ALT><O>,<E>.

Then choose the custom format at the bottom of the list and type into the “Type” box on the right.

Cell formats in Excel can be set based on if the cell is positive, negative, zero or text. A semicolon separates these four sections “;”. This Microsoft page explains this quite well:

custom format setup

The custom format code is:

[Green]?"ahead";[Red]?"behind";[Color45]?"on track"

The anatomy of each section is:

  • colour for the cell]
  • The emoji entered using <win><.>
  • “the words which will replace the numerical value”
  • ; separating the sections

As you can see, the colour can be a name for a small number of colours; however, you can access 56 different colours by using [colorx]. Note the US spelling of color. The complete list of colours available is shown here

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.