Using Emojis as Key Values

Key values help you see the current standing for your KPIs, but here’s a tip to add some color and fun with emojis. You can create a dynamic display that shows a different emoji for a specific number or range.

In this example, I am tracking the total sales month-to-date. The monthly goal is $75k, but the stretch goal is $80k (and the sales team gets a party if they reach it). To help drive that stretch goal, let’s add a couple fun key values to show if they hit it or not.

First, add a “Spreadsheet Functions” report. Then add the emojis you want to use, one in each cell. We’ll set this up to show the progression through the month as the sales team gets closer to the goal and the party, so we’ll use these emojis:

First, let’s make a simple formula to show if they have earned a party or not:

=IF(report1!C2, ">=80000", spreadsheet!C2, spreadsheet!C1)

This basically says if the value in C2 of the report (the overall total column) is equal to or greater than 80k, then show the value in C2 of the spreadsheet (the party emoji). If not, then show the value in cell C1 (the shrug).

For the second key value we want to show a different emoji for different ranges as they make more sales to meet their goal. The basic formula is the same, but we will have to nest several IF() statements to make this work:

=IF(report1!C2, "<25000", spreadsheet!C6, IF(report1!C2, "<50000", spreadsheet!C5, IF(report1!C2, "<75000", spreadsheet!C4, IF(report1!C2, "<80000", spreadsheet!C3, spreadsheet!C2))))

This is saying if the overall total (C2 in the report) is under 25k, then show the emoji in C6. If not, then check if the total is under 50k. If it’s not, then check the next value, and so on.

You can test if it’s working by adding or subtracting a number to “report!C2” in an IF statement like this: “reportC2+20000” (without the quotes).

When you select the cells with the formula as the key value, you will need to select the “Treat Value as text” checkbox at the bottom of the key value settings.

And now you have dynamic key values that will display emojis based on the metric performance!