Conditional Highlighting Set Up to Make Status Dots Change Colour

Question

Bardonicloud Author

Level 1

51 points

Conditional Highlighting Set Up to Make Status Dots Change Colour

I am trying to recreate this Status column, dot colour scheme concept:

The Status column would refer to the % of total column.

If "% of Total" is greater than or equal to 75 → Dot Color: Red and text = Critical

If "% of Total" is greater than or equal to 25 → Dot Color: Orange and text = Moderate

If "% of Total" is greater than or equal to 1 → Dot Color: Green and text = Low

If "% of Total" is less than 1 → Dot Color: Grey and text = Minimal

i.e. if values in % of Total column are:

≥75% = 🔴 Red dot

25-74.9% = 🟡 Orange dot

1-24.9% = 🟢 Green dot

<1% = ⚪ Grey dot

My question is how to display the appropriate dot colour and wording text: Critical, or Moderate, or Low, or Minimal based on the above conditions being met:?

Is that possible to change the dot colours to reflect the % of Total column and bring up the correct text wording?

Cheers!

[Edited by Moderator]

MacBook Pro 16″, macOS 14.7

Posted on Aug 16, 2025 05:30 AM

Reply

Answer 1

Badunit

Level 7

29,898 points

Aug 16, 2025 06:23 AM in response to Bardonicloud

With the numbers already highlighted in colors, having this column (or just the dots) highlighted the same color is redundant.

It would be easy to do the whole thing (dot and text together). You would set up four conditional highlighting rules for "text contains" with the four conditions being "critical", "moderate", "low", and "high".

But if you want only the dot colored, one way to do it is to create four cells somewhere else that have your dots & words formatted the way you want and your formula for the status will refer to each of those cells. Let's say those four cells are Table 2::A1:A4.

=IFS(D<1%,Table 2::A1, D<25%, Table 2::A2, D<75%, Table 2::A3, TRUE, Table 2::A4)

I took some liberties with the formula. I assume this what you really meant with your percentages.

I used a unicode dot and set its color but not the text. It is unicode #25CF. You can find it in the Character Viewer. To turn on the character viewer go to System Preferences->Keyboard->Input Sources and select "Show input menu in menu bar". An icon will appear in the top right of the menu bar that lets you see and choose a character. Or you can use this formula to make the character then copy/paste it (the result, not the formula) to the four other cells =CHAR(HEX2DEC("25cf"))

Reply

Answer 2

Bardonicloud Author

Level 1

51 points

Aug 16, 2025 10:51 AM in response to Badunit

This is great, thanks @Badunit. I used the sys pref emoji route for the dots. I coloured the text too but the colours aren't being transferred?

For some reason the alignment is too low despite changing the 3 options for alignment in settings (above the "wrap ext in cell")?

I used a 6pt dot and 14 point text.

Also yours looks better because the dots are centred but mine are "on the line" in Table 2?

[Edited by Moderator]

Reply

Answer 3

Bardonicloud Author

Level 1

51 points

Aug 16, 2025 11:59 AM in response to Badunit

Thanks. Tried all the gear settings but it still looks nothing like yours because the dot will not go to the centre, it's on the line.

Referring to my main table and not the the helper table 2: With default, the baseline setting is not doing anything.

With Superscript, baseline does nothing.

With Subscript the baseline at 1 moves the whole dot and text up but it doesn't move with future incrementation from 1 to 2 or 2 to 3. Also the font now looks tiny even though both the main table and helper table for were se to 14.

Did you use the gear and baseline settings? What font did you use? Did you use =CHAR(HEX2DEC("25cf"

Perplexed!

[Edited by Moderator]

Reply

Answer 4

Badunit

Level 7

29,898 points

Aug 16, 2025 01:16 PM in response to Bardonicloud

My last post was in response to yours where you were using the emoji dots. What worked for me with the emoji dots was the default Helvelica Neue 10pt for the text, same font but 6pt for the dot, and the dot is baseline shifted up one point. Or text at 14 pt, dot at 8pt, dot baseline shifted up 2. Other combinations work good too.

Reply

Answer 5

Bardonicloud Author

Level 1

51 points

Aug 16, 2025 10:36 PM in response to Badunit

The baseline setting isn't doing much after being set to 1 (doesn't do a thing when set to default and the baseline is set to 2, or 3 etc).

I think it's a problem because I am using 14 pt font throughout the sheet and although 10 point looks better that font is too small. (I have 24 pt line height too).

If I carpeted images of the dot and word could those be called up and inserted?

Cheers.

Reply