Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets tip 260: Dot plots! 🟥 🟦 🟩
Hi Reader,
Welcome to the Google Sheets Tips newsletter #260, your Monday morning espresso, in spreadsheet form!
➜ News
I.
​We're getting more information on how Google is bringing generative AI to Workspace, including Google Sheets.
The details concerning Sheets are pretty scant at this stage, but it's going to be interesting to see it emerge:
​Learn more about Duet AI and Google Sheets​
_______
A big thank you to this week's sponsors who help keep this newsletter free to read:
If you're interested in reaching this audience through a sponsorship, please see the details in the footer of this email.
_______
➜ Google Sheets Tip #260: Dot plot formulas
In this tip, we look at dot plots!
I covered them before in tip 188, but we do something different with this example.
Let's begin with a quick rundown of the functions that we'll use to build the dot plot.
Required Functions
- The CHAR function converts a number into a special character, in this case the red, blue, or green square.
- The REPT function repeats a text string a set number of times.
- The IFS function is like an IF function with more than one conditional test.
- MIN and MAX functions output the minimum or maximum values respectively.
Step 1: Single color dot plot
Let's begin by constructing this simple dot plot:
The formula uses CHAR to create the green square and REPT to repeat it the correct number of times.
=REPT(CHAR(129001),A2)This same technique can be used to create star charts.
Step 2: Partial multicolor dot plot
We change the number inside the CHAR function to generate blue and red squares, in addition to the green square.
Then we use an IFS function to show green for higher numbers, blue for middle numbers, and red for lower numbers.
=IFS( A2>6 , CHAR(129001) , A2>3 , CHAR(128998) , TRUE , CHAR(128997))Once we have that, we can wrap it with the REPT function to repeat the colored squares the correct number of times:
=REPT( IFS( A2>6 , CHAR(129001) , A2>3 , CHAR(128998) , TRUE , CHAR(128997)), A2)What if we want to combine the colored squares to a single chart though?
Let's see how to do that!
Step 3: Full multicolor dot plot
In this example, we include the color changes inside each chart:
We make use of MIN and MAX to determine how to fill the "buckets":
- 1 to 3 are red
- 4 to 6 are blue
- 7 to 10 are green
For example, 8 is 3 red + 3 blue + 2 green.
However, 5 is just 3 red + 2 blue.
To show the red portion, use this formula which caps the value at 3 (the maximum number of red squares):
=MIN(A2,3)We wrap this with a REPT function to show the correct number of red squares:
=REPT(CHAR(128997),MIN(A2,3))The blue squares are generated by this formula, which caps the max value at 6 and the minimum value at 3, before subtracting 3 (representing the existing red squares):
=REPT(CHAR(128998),MAX(MIN(A2,6),3)-3)And the green squares are generated by this formula, which sets the minimum to 6 and then subtracts 6 (representing the red and blue squares):
=REPT(CHAR(129001),MAX(A2,6)-6)This image shows the breakdown of the values into the "buckets" above the dotted black line, and shows the colored squares below the dotted black line:
Finally, we combine the three REPT formulas with "&":
=REPT(CHAR(128997),MIN(A2,3)) &
REPT(CHAR(128998),MAX(MIN(A2,6),3)-3) &
REPT(CHAR(129001),MAX(A2,6)-6)It looks complex but all it's doing is saying take the value and split it into the three buckets: 1 to 3, 4 to 6, and 7 to 10. Then color the squares accordingly and re-combine.
I'll leave it as an exercise for you to create an array version of this formula that only needs to be entered once at the top of the column.
Even better if you can then turn it into a named function that could be used to easily create dot plots... 🤔
_______
If you enjoyed this newsletter, please forward it to a friend who might enjoy it.
Have a great week!
Cheers,
Ben
Google Sheets Tips
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #370, your Monday morning espresso, in spreadsheet form! This is the last Google Sheets Tips email for 2025. We're taking a break from publishing for the holidays and will return with Tip 371 on 5th January 2026. Thank you so much for reading and replying to these emails. I love learning from all of you when you write back and share your own tips and tricks (and occasional corrections! 😉 ). In the meantime, I wish you...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #369, your Monday morning espresso, in spreadsheet form! We had our first snowfall of the season last Friday, so winter is definitely underway here in the Mid-Atlantic region. It was only a light dusting though, so the sleds are still hanging in the garage. For now. In this issue, we're looking at a clever way to add visual clues to our data tables with indicator arrows ▲🔻. And, if you're a Workspace customer, you must...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #368, your Monday morning espresso, in spreadsheet form! I finished reading a fascinating book last week, Home on the Canal, a long-out-of-print history of the Chesapeake and Ohio Canal and the stories of the people who lived and worked along it. Today, I live beside the canal, now a beautiful National Historic Park, and cycle or walk on the restored towpath most weeks. Riding across the Antietam Aqueduct recently, I...