I’m trying to build a progress tracker in Google Sheets using heart emojis similar to what I’ve seen in other apps. I want to show progress visually with filled and empty hearts.
Right now I have this basic formula that kind of works: =REPT(char(9829),current_value/total_value*10)&REPT(char(9825),(10-current_value/total_value*10))&" "&ROUND(current_value/total_value*100,1)&"%"
In this example, current_value represents completed items and total_value is the maximum. The char(9829) gives me filled hearts and char(9825) gives empty ones.
The problem is the hearts don’t align properly and sometimes I get decimal hearts which looks weird. Is there a better way to make this work smoothly? I want it to show both the visual hearts and the percentage.
Any suggestions for improving this formula or alternative approaches would be great!
Your decimal heart issue happens because you’re not forcing integer values in the REPT functions. I hit this same problem building project trackers for my team. Wrap your calculations with ROUND or INT functions. Try this: =REPT(char(9829),ROUND(current_value/total_value*10,0))&REPT(char(9825),10-ROUND(current_value/total_value*10,0))&" "&ROUND(current_value/total_value*100,1)&"%". For alignment, use a monospace font like Courier New. I’ve also split this into separate columns - one for hearts, one for percentages. Gives you better formatting control and looks cleaner. If hearts don’t align well in your Sheets version, try circles or squares instead.
you can also include max and min functions for edge cases: =REPT(char(9829),MAX(0,MIN(10,INT(current_value/total_value*10))))&REPT(char(9825),MAX(0,10-MIN(10,INT(current_value/total_value*10))))&" "&ROUND(current_value/total_value*100,1)&"%". stops weird stuff from happening when values go over 100% or below zero. learned that one the hard way lol
The alignment issues happen because Google Sheets can’t render emojis consistently across different cell widths. I ran into the same thing building a habit tracker last year. Instead of trying to fix the decimal rounding, try using SPARKLINE: =SPARKLINE({current_value,total_value-current_value},{"charttype","bar";"max",total_value;"color1","green";"color2","lightgray"})&" "&ROUND(current_value/total_value*100,1)&"%". You get a clean horizontal bar that scales right without spacing headaches. If you want to stick with hearts, here’s another trick: use conditional formatting on a range of cells with just the heart symbol, then color them based on your progress value. Gives you exact control over positioning and kills the decimal heart problem completely.