Smileys and icons can bring life to your Spreadsheet if you use them cleverly. You can insert smileys and icons based on values in Google Sheets.
There are two methods to insert emoticons within cells (not above cells or floating) in Sheets.
- Using Image function (requires image URLs).
- Using the menu Insert > Image > Image in cell feature (directly search on Google from within Docs Sheets and insert or directly upload from your desktop).
I am following the second method that uses the built-in menu command.
If you use the Image function, it might affect the performance of your Sheets. It requires Image URLs, and I don’t recommend you to use image URLs from third-party websites.
If you have icon sets or smileys, upload them into any free image hosting website and get the URLs from there to use in the Image function.
As mentioned above, I am following the second method here, which doesn’t involve the Image function and the URLs.
I am using Google Sheets’ relatively new Insert Image in cell feature. Here are the steps.
Steps to Insert Smileys and Icons Based on Values
Download an icon pack (required icons/smileys) on your desktop.
There are plenty of sites offering free as well as paid icon packs. If you can’t find one, don’t worry! Just read on. I will guide you to get one.
Step # 1: Upload Image/Smileys into Cells in Google Sheets
I have one Google Sheets file, which contains two tabs – Sheet1 and Sheet2. Sheet1 contains my demo data, and Sheet2 is blank.
Click on cell B1 in Sheet2. Then go to Insert > Image > Image in Cell.
Click on the tab “Search” and search for smileys. If you have Smileys or icon packs already downloaded on your desktop, use the “Upload” button instead.
Click on the Smiley that you want to insert. In Cell B2, B3… follow the above steps. Name the smiley in cells A1, A2, A3… as shown below.
Our topic is how to get Smileys and Icons based on values in Google Sheets. So far, we have only inserted a few of the most common smileys.
If you want a home icon, use the search term “home icon” as the search key on the image search field above.
Step # 2: Prepare the Example Sheet in Which You Want to Insert the Emoticons
The above images (icons) are in Sheet2.
In Sheet1, we can prepare the sample data to test. I have very basic data as below in Sheet1.
| A | B | C | |
| 1 | Employee | Sales Volume | Status |
| 2 | Employee 1 | 4500 | |
| 3 | Employee 2 | 11000 | |
| 4 | Employee 3 | 7500 |
Step # 3: Vlookup Smileys or Icons Based on Values
Conditions to make the smileys appear:
If sale volume is <5000, “Unhappy”, sale volume is >=5000 and <7500, “Happy” and >=7500, “Love”.
The below IF formula (cell C2) adopts the above conditions to return the texts unhappy, happy, or love based on sales volume.
=ArrayFormula(if(B2:B4<5000,"Unhappy",if(B2:B4<7500,"Happy","Love")))Don’t forget to use the same keyword presented in Sheet2 column A in this IF formula.
The above formula only includes the finite/closed range B2:B4. You can use the infinite/open range B2:B to include future values as below.
=ArrayFormula(if(B2:B<>"",(if(B2:B<5000,"Unhappy",if(B2:B<7500,"Happy","Love"))),))Use this formula as search keys in a Vlookup as below.
Generic Formula:
Vlookup(search_keys[If formula above],range to lookup [Sheet2 column A and B],output[Sheet2 Column 2])Rewrite the IF formula in Sheet1 cell C2 as below (based on the generic formula above).
=ArrayFormula(IFERROR(Vlookup(if(B2:B<>"",(if(B2:B<5000,"Unhappy",if(B2:B<7500,"Happy","Love"))),),Sheet2!A1:B,2,0)))
This way you can get Smileys and Icons based on values in Google Sheets. Enjoy!
Resources:
Hi, I have tried the formula for the emoji icons to reflect.
However, only the first row reflects the emoji icons and not the following row. Not too sure where the error occurs.
Hi, Deanna,
I could help if you share an example Sheet.
Is there a way to achieve the same result by adding a condition to match the text in the cell?
I’m trying to add an image based on text in the previous cell.
Hi, Paola Soto,
I don’t know what exactly you are trying to achieve.
Try using the image lookup or share a sample sheet.
Thank you very much!
I tried using image lookup. But since I’m starting to learn to work with Google Sheets, I think I wrote it wrong. It keeps saying ERROR.
Here is the sheet: Link removed by admin.
Hi, Paola Soto,
Please see the tabs “kvp test” and “kvp test 2” added to your sample sheet
Hi! so I’ve done this manual conditional formatting to my chart and it looks great!
My only problem is when I try to copy-paste it to a google slide. It does not paste it, even if I don’t link it to the original source. Have you found a solution for this?
Thanks!!