•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Custom conditional formatting icon set, using XLOOKUP to return a Picture in Cell
Our take
Unlock the potential of your spreadsheets with custom conditional formatting icon sets using XLOOKUP. While traditional icons offer limited choices, this innovative approach allows you to define personalized icons or any pictures that fit within a cell. By creating a lookup table with scores and corresponding images, you can seamlessly integrate custom visuals into your dashboard. With XLOOKUP, you can easily return the appropriate icon based on score ranges, enhancing clarity and engagement in your data presentations.
I just gave this solution as an answer to another question, but thought it deserved its own post.
Conditional formatting icons already exist, but you have to choose from a small selection. With this technique, you can define your own custom icons (or indeed, any picture that will fit in a cell).
- Create a lookup table, with a column for each score (or score range) and a corresponding icon
- In the Icon column, use the Insert > Picture > Place in Cell feature, to fill in each cell
- Where you want to use the icons in your dashboard, use XLOOKUP to return the icon corresponding the the score:
=XLOOKUP(score,Table[score],Table[icon])
By making use of XLOOKUP's match_mode, you could have the same icon correspond to a range of scores. To handle if_not_found, use an absolute reference to a dedicated icon cell outside of your lookup table.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#no-code spreadsheet solutions#Excel alternatives for data analysis#financial modeling with spreadsheets#rows.com#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#Excel alternatives#XLOOKUP#conditional formatting#lookup table#custom icons#picture in cell#icon column#corresponding icon#score range#returned icon#insert picture#if_not_found#match_mode