In MS Excel, the conditional formatting feature allows users to include visual elements on Excel performance reports to use colors and shapes to quickly differentiate good performance from sub-optimal performance. The popular conditional formatting styles are color scales, color bars, and icons.

But what if you wanted to explore other visual elements i.e. a more diverse set of icons? Well, with the combination of the IF function, formula-based conditional formatting, and the Insert Symbol feature (under the Segoe UI Symbol Font group), this is possible.

You can set up your KPI icons in this way in three simple steps.

First, define your conditions for positive vs negative performance. For instance, positive performance (or target met) could be 100%, while negative performance could be any value less than 100%. For my KPI examples below, positive performance is defined by attaining specific Target values, as shown below:

Next, locate the two icons you want to use (using Insert Symbol) and place them in any cell in the worksheet. Close the Insert Symbol dialog box and copy (Ctrl+C) the icons from the formula bar.

(NB: The thumbs up and thumbs down icon are easier to access for me because I have used them before, but when you do this the first time, you might need to search for them in the Dingbat subset)

Now, set up an IF formula that checks the Actual value (for whether it meets the +ve performance criteria or that of the -ve performance) and assigns the requisite KPI icon to it. The formula is as follows:

=IF([@Actual]>=[@Target],"👍",IF([@Actual]<[@Target],"👎"))

A short clip explaining how to set this up is below:

Finally, set up two conditional formatting rules – one for the +ve KPI icon and the other for the -ve KPI icon – to color the positive performance green and the negative performance red.

With this set up, you have a dynamic KPI report that updates icons and icon color to reflect whether the Actual value meets the positive performance criteria, or the negative performance criteria