Hi everyone! I’m trying to build a cute progress tracker in Google Sheets using heart emojis. I want to show both filled and empty hearts to represent completion status.
Right now I have this formula that kind of works: =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)&"%"
In this example, current_value would be like 5 and total_value would be 12. The char(9829) gives me filled hearts and char(9825) gives me empty ones.
But I’m having trouble making it look really polished. The hearts don’t always line up nicely and sometimes the percentage calculation gets weird.
Has anyone created something similar? I’d love to see other creative ways people have made visual progress bars in spreadsheets. Any tips on making the formatting cleaner would be awesome!
I had the same formatting headaches when building a progress tracker for my project sheet. CHAR codes are a pain because fonts render them at different sizes, so everything looks misaligned.
Block characters work way better than hearts. I use CHAR(9608) for filled blocks and CHAR(9617) for empty ones - they stay consistent across different fonts. Your formula structure stays the same, but it looks much cleaner.
For the wonky percentage calculation, wrap your percentage formula with MAX(0,MIN(100,)). This stops negative values and prevents it from going over 100% when current_value is higher than total_value. Also try ROUNDUP instead of ROUND if you want partial progress to show something instead of zero.
One more tip: format the cell with Courier New or another monospace font. Every character takes up the same width, so your progress bar looks way more professional.
Aligning emojis can be tricky due to varying rendering across platforms. I recommend switching to asterisks or dashes for a consistent appearance. It might also help to split your progress bar and percentage into different cells for easier troubleshooting. Regarding odd percentage calculations, ensure your current_value isn’t pulling in empty cells or text, as ROUND can misinterpret these data types. Using ISNUMBER checks can prevent unexpected results. Additionally, consider applying conditional formatting to visualize progress with color changes in cells. Creating a row of ten cells and highlighting them according to your progress can provide a cleaner look than using characters.
Honestly, just use colored squares ■ ■ ■ instead of hearts. Way fewer alignment headaches. Your formula’s doing the same calculation twice - store ROUND(current_value/total_value*10,0) in a helper cell first, then reference it. Saves processing and makes debugging way easier.