Dot Plots In Google Sheets

Dot plots are simple charts consisting of dots plotted on a simple scale, typically used to show small counts or distributions.

Dot plots are one of the simplest statistical charts, only suitable for small-sized data sets. They’re helpful for understanding the “shape” of your data by highlighting clusters, gaps, and outliers. (A histogram is better suited to showing the data distribution of larger datasets, e.g. > 30 datapoints.)

Here’s a table using dot plots to show the hypothetical number of meetings per day for these five employees:

Dot Plot in Google Sheets

How To Create Dot Plots In Google Sheets

You create dot plots in Google Sheets with formulas! 

Suppose we have this data in row 1 of a Google Sheet, in cells A1 to E1:

Data for Dot Plot in Google Sheets

Step 1: 

Create a basic REPT function next to the data, e.g. in cell F1:

=REPT("*",A1)

Step 2:

Next, turn this REPT formula into an array formula:

=ArrayFormula(REPT("*",A1:E1))

Step 3:

Then use the JOIN and CHAR functions to combine the array output. CHAR(10) creates a carriage return, which we use as the delimiter:

=ArrayFormula(JOIN(CHAR(10),REPT("*",A1:E1)))

Step 4 (optional):

Convert the * into circles with the CHAR function:

=ArrayFormula( JOIN(CHAR(10),REPT(CHAR(9679), A1:E1)))

Step 5 (optional):

Rotate the cell up:

Format > Rotation > Rotate up

Here’s an image showing the outputs for these 5 steps in column F:

Dot Plot workings

How To Create Multi-Colored Dot Plots In Google Sheets

Taking this idea one step further, we can add colored symbols to indicate the relative counts.

Here’s an example with green dots for large counts, then orange, and then red dots for the smallest counts:

Multi-Colored Dot Plot in Google Sheets

The formula is more complex and uses the IFS Function to categorize the inputs by relative size:

=ArrayFormula(JOIN(CHAR(10),REPT(IFS(A1:E1/MAX(A1:E1)>0.85,"🟢",A1:E1/MAX(A1:E1)>0.5,"🟠",TRUE,"🔴"),A1:E1)))

How does this formula work?

It’s an array formula that takes an input of the five numbers in columns A to E.

Inside the IFS function, the number (e.g. 7) is divided by the maximum number in the range (10 in this example) and compared to see if it’s bigger than the first threshold (0.85 in this example). If this is true, then the green dot is plotted, otherwise, the threshold is checked (0.5 in this example) If that’s true, then orange dot is used. If that is not true, then the red dot is used as the default.

The REPT function and the JOIN function perform the same way as step 3 above for the simpler single color example.

You can also replace the colored dots in this formula with their CHAR function equivalents, to keep it entirely formula driven:

=ArrayFormula(JOIN(CHAR(10),REPT(IFS(A1:E1/MAX(A1:E1)>0.85,CHAR(128994),A1:E1/MAX(A1:E1)>0.5,CHAR(128992),TRUE,CHAR(128308)),A1:E1)))

As a final step, don’t forget to rotate the cell text up, to get the dots plotted as columns rather than bars.

Notes

This Dot Plot technique first appeared in issue 188 of my weekly Google Sheets Tips newsletter. Signup here if you’d like to receive it!.

Thanks to reader Marcel L. for his sharing his idea for the multi-colored dot plot.

Slow Google Sheets? Here are 27 Ideas to Try Today ?

(Updated Jan 2022 to reflect the increase in the cell limit of Google Sheets to 10 million.)

Slow Google Sheets?

We’ve all been there, stuck watching the little loading bar creep slowly, frustratingly to its conclusion:

Slow Google Sheets loading bar

How can you speed up a slow Google Sheet?

First off, this is a difficult question to answer because there are so many factors that may or may not be causing you to have a slow Google Sheet.

What follows in this article is some suggested optimization strategies and some research into what causes slow Google Sheets.

 


Strategies to speed up Google Sheets

  1. How to recognize slow Google Sheets
  2. Know the size limits of Google Sheets
  3. Measure a Google Sheet’s size
  4. Measure a Google Sheet’s calculation speed
  5. Delete un-used cells
  6. Convert formulas to static values wherever possible
  7. Use closed range references
  8. Remove volatile functions or use with caution
  9. Vlookup strategies
  10. Index-Match strategies
  11. Query function strategies
  12. Array Formula strategies
  13. Import Formula strategies
  14. Google Finance function strategies
  15. Use IF statements to manage formula calls
  16. Manage expensive formulas with a control switch
  17. Use Filter, Unique and Array_Constrain functions to create smaller helper tables
  18. Avoid long calculation chains
  19. Reference data on the same Sheet
  20. Use helper columns
  21. Split your slow Google Sheet into separate Sheets
  22. Use Conditional Formatting sparingly
  23. Leverage the power of Apps Script
  24. Use custom formulas sparingly
  25. Other troubleshooting tips for slow Google Sheets
  26. Understand changes in the cloud can take time to propagate
  27. Know when it’s time to move to a database

Continue reading Slow Google Sheets? Here are 27 Ideas to Try Today ?

How To Set Default Values For Cells In Google Sheets

In this post, you’ll learn how to set default values for cells in Google Sheets, without using Google Apps Script code.

In the Sheet below, the cells in column B have default values of 100, 25, and 10 respectively. If a user types in a value (e.g. 200) it overwrites the default value. If a user deletes whatever value is in the cell already, then the default value of 100 is displayed again.

Default Values for cell in Google Sheets

Setting Default Values For Cells In Google Sheets

The key to make this technique work is to use Array Literals to create a formula which spills into the adjacent cell. This is a rather abstract concept, so let’s run through an example.

In a blank Sheet, write the value “Input” in cell A1. In cell B1, type this formula:

={"",100}

Your Sheet will look like this:

Default Value Setup Google Sheet

Try typing 200 in cell C1, over the top of the 100.

Cell C1 will show the 200, but cell B1 now displays a #REF! error.

Now, delete the value you just typed in cell C1. The error message disappears and the default value of 100 is displayed again.

Default cell Google Sheets

Finally, hide column B so that the #REF! error is never seen, and you have a default value of 100 set for cell C1.

Hidden column default value Google Sheets

🎩   Hat tip to my friend Scott Ribble for showing me this ingenious solution.

Advanced Default Values Without Hidden Column

The method above suffers from one drawback though: it necessitates a hidden column.

However, we can use a clever circular formula to address this.

In a new blank Sheet, add this formula in cell A1:

=IF(ISBLANK(B1),{"Input",100},"Input")

Initially, you may see this error message about a circular error (i.e. a formula that references itself):

Circular Error in Google Sheets

That is a problem, but we fix it by switching on iterative calculations and restricting them to a single iteration:

File > Settings

Go to “Calculation”.

Set “Iterative calculation” to “On” and the “Max number of iterations” to 1.

(The threshold can be left at 0.05 because it doesn’t apply in this case.)

Iterative Calculation Google Sheets

Now, you can enter any value you want in cell B1 and if you delete it, the default value of 100 will be shown.

How Does This Work?

The IF function checks whether cell B1 is blank.

If it is blank, then it outputs the array literal:

{"Input",100}

which displays “Input” in cell A1 and the value 100 in cell B1.

However, if cell B1 already has a value then the IF function output is just the string “Input” in cell A1.

Note: default values are not limited to numbers. It could be text, an image, or even another formula.

Give this a try and let me know how you use default values in your own projects!

Three Benefits Of Cohort-Based Courses

The world of online education is very different today than it was 10 years ago.

We now live in an age of information abundance, with all of human knowledge only a click away.

And that means we can shift the focus of online courses from information-heavy lectures to a community-driven learning environment with interactive sessions, collaboration, and shared knowledge.

And right now, accelerated by the pandemic, community-driven online courses are having their moment in the sun.

Online creators are seeing incredible demand for their live courses. Top-tier VC firms are investing in companies that facilitate these live, cohort-based courses.

Here are three reasons why these cohort-based courses are the best way to learn:

1. Cohort-Based Courses Hold You Accountable

Cohort based course group

Hands up if you’ve bought an online course with good intentions, but then never got past the introduction video?

Yup! Guilty as charged…

I kid myself that I’ll get around to it one day, but, like all of us, life is busy.

What is there was a different way?

There is!

Join a cohort-based course — like Pro Sheets — and you learn with other people, at scheduled times, with defined and manageable goals.

Turns out that we’re not very good at making and, crucially, keeping promises we make with ourselves. Even if we start a venture with huge enthusiasm, it’s hard to sustain, especially when the journey gets hard or we hit the inevitable hurdles.

Being part of a group offloads some of this willpower burden. Once you become part of something greater than yourself, you don’t want to let the group down by not bringing your best self to the table.

We as humans care deeply about what our fellow humans think of us, even when we’re told not to, or even though we know other people are too busy with their own realities to care much about ours. And yet, this external influence remains very strong.

In other words, when you join a live, group course, you’re giving yourself much better odds at actually sticking through the course and completing it, reaching your goal, and reaping the benefits.

With dedicated time slots and friendly faces waiting to greet you, you’ll feel inspired to attend as many live sessions as possible.

And by simply turning up, again and again, you’ll see the results you’re after.

I was a student in a cohort-based course earlier this year, and I was pumped every time a live class rolled around. I couldn’t wait to join and catch up with everyone. There’s no way I would have stuck with a video-only version of this course on my own.

2. Learning Together Is Faster

Remember the game minesweeper?

It was a classic strategy game, with a very strong 90s PC desktop vibe, requiring players to make calculated decisions and educated guesses on where the mines lay on the board.

Minesweeper makes for a nice analogy with learning a new skill or deepening an existing skill.

You have your existing knowledge, represented in Minesweeper by the portion of the board that is uncovered.

Now, suppose you’ve reached an impasse with your learning. You don’t know how to proceed.

This is like one of those vexing 2-3-3-4-1 combinations on the Minesweeper board that eventually require an educated guess.

Continuing alone is possible, but it will be slow going and frustrating, and you will make mistakes — step on mines — along the way.

Minesweeper

If you learn with others though, whether they’re experts or just slightly ahead of you on their journey, they can show you the next move.

They can unlock the board for you, so whole new regions of knowledge open up in front of you.

Minesweeper

Your journey will be dramatically quicker with a guide, simply because you make fewer mistakes and better decisions.

3. It’s More Fun

Look at all these beautiful smiles in the final live session of Pro Sheets Accelerator earlier this year:

Pro Sheets cohort-based course community

When I launched Pro Sheets Accelerator, I expected students to say their favorite part was learning new formulas, scripts, or frameworks.

But by the end of the course, it wasn’t the formulas or scripts that students most appreciated, it was the community.

They loved learning with other spreadsheet enthusiasts!

One of the students, Jen, an educator from Massachusetts, summed it up well:

“It was really just nice to have other nerdy people to talk to and to share stories and ask questions to, who just really cared about talking through it in a high-quality conversation.”

And I was chatting recently with another student, Jim, a financial advisor from California, who said:

“Getting together with like-minded people who were really passionate about spreadsheets was just magic.”

By learning in a group, you meet like-minded people to share the highs and lows of the journey. You’ll laugh and have fun along the way, and your best learning happens when you’re happy and having fun.


Pro Sheets Cohort 2 Now Enrolling

37 students joined the first cohort earlier this year to learn advanced Google Sheets formulas, workflow automation, frameworks, and much more, whilst also honing their data analysis and visualization skills.

The first edition had an amazing group of people who came together for high-quality conversations about how they use Google Sheets in their own work. We had educators, digital marketers, finance professionals, and entrepreneurs, who shared their passion and expertise with each other.

I only teach Pro Sheets Accelerator twice a year, so don’t miss this chance to join us.

How To Draw The Cantor Set In Google Sheets

The Cantor set is a special set of numbers lying between 0 and 1, with some fascinating properties.

It’s created by removing the middle third of a line segment and repeating ad infinitum with the remaining segments, as shown in this gif of the first 7 iterations:

Cantor Set Iterations

The formulas used to create the data for the Cantor set in Google Sheets are interesting, so it’s worth exploring for that reason alone, even if you’re not interested in the underlying mathematical concepts.

But let’s begin by understanding the set in more detail…

What Is The Cantor Set?

Cantor Set in Google Sheets

The Cantor set was discovered in 1874 by Henry John Stephen Smith and subsequently named after German mathematician Georg Cantor.

The construction shown in this post is called the Cantor ternary set, built by removing the middle third of a line segment and repeating ad infinitum with the remaining segments.

It is sometimes known as Cantor dust on account of the dust of points that remain after repeatedly removing the middle thirds. (Cantor dust also refers to the multi-dimensional version of the Cantor set.)

The set has some fascinating, counter-intuitive properties:

  • It is uncountable. That is, there are as many points left behind as there were to begin with.
  • It’s self-similar, meaning each subset looks like the whole set.
  • It’s fractal with a dimension that is not an integer.
  • It has an infinite number of points but a total length of 0.

Wow!

How To Draw The Cantor Set In Google Sheets

To be clear, the Cantor set is the set of numbers that remain after removing the middle third an infinite number of times. That’s hard to comprehend, let alone do in a Google Sheet 😉

But we can create a picture representation of the Cantor set by repeating the algorithm ten times, as shown in this tutorial:

Create The Data

Step 1:

In a blank sheet called “Data”, type the number “1” into cell A1.

Step 2:

In cell B1, type this formula:

={ FILTER(A1:A,A1:A<>"") ;
SUM(FILTER(A1:A,A1:A<>"")) ;
FILTER(A1:A,A1:A<>"") }

Step 3:

Drag this across your sheet up to column J, which creates the data for the first 10 iterations.

Each formula references the column to the left. For example, the formula in cell D will reference column C.

Your data will look like this:

Data For Cantor Dust in Google Sheets

How does this formula work?

It combines array literals and the FILTER function.

Let’s break it down, using the onion framework.

The innermost formula is:

=FILTER(A1:A,A1:A<>"")

This formula grabs all the data from column A and returns any non-blank entries, in this case just the value “1”.

Now we combine two of these together with array literals:

={ FILTER(A1:A,A1:A<>"") ;
FILTER(A1:A,A1:A<>"") }

Here the array literals { ... ; ... } stack these two ranges.

In this first example, it puts the number “1” with another “1” beneath it in column B.

Then we add a third FILTER and also SUM the middle FILTER range to create our final Cantor set algorithm:

={ FILTER(A1:A,A1:A<>"") ;
SUM(FILTER(A1:A,A1:A<>"")) ;
FILTER(A1:A,A1:A<>"") }

As we drag this formula to adjacent columns, the relative column references will change so that it always references the preceding column.

In column B, the output is:

1,1,1

Then in column C, we get:

1,1,1,3,1,1,1

And in column D:

1,1,1,3,1,1,1,9,1,1,1,3,1,1,1

etc.

This data is used in the sparkline to generate the correct gaps for the Cantor set.

Draw The Cantor Set

We’ll use sparklines to draw the Cantor set in Google Sheets.

Cantor Dust In Google Sheet
Click to enlarge

Step 4:

Create a new blank sheet and call it “Cantor Set”.

Step 5:

Next, create a label in column A to show what iteration we’re on.

Put this formula in cell A1 and copy down the column to row 10:

="Cantor Set "&ROW()

This creates a string, e.g. “Cantor Set 1”, where the number is equal to the row number we’re on.

Step 6:

The next step is to dynamically generate the range reference. As we drag our formula down column B, we want this formula to travel across the row in the “Data” tab to get the correct data for this iteration of the Cantor set.

Start by generating the row number for each row with this formula in cell B1 and copy down the column:

=ROW()

(I set up my sheet with the data in columns because it’s easier to create and read that way. But then I want the Cantor set in a column too, hence why I need to do this step.)

Step 7:

Use the row number to generate the corresponding column letter with this formula in cell C1 and copy down the column:

=ADDRESS(1,ROW(),4)

This uses the ADDRESS function to return the cell reference as a string.

Step 8:

Remove the row number with this formula in cell D1 and copy down the column:

=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

Step 9:

Combine these two references to create an open-ended range reference for the correct column of data in the “Data” sheet.

Put this formula in cell E1 and copy down the column:

="'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

This returns range references e.g. 'Data'!A1:A

Step 10:

Put this formula in cell F1 and copy down the column:

=INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1",""))

This will show #REF! errors: “Array result was not expanded because it would overwrite data in…”

However, don’t worry, these are only temporary as we’ll dump this data into the sparkline formula next.

Step 11:

In column G, create a default sparkline formula:

=SPARKLINE(INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")))

This shows the default line chart (except for the first row where it shows a #N/A error).

Step 12:

In column H, convert the line chart sparkline to a bar chart sparkline by specifying the charttype in custom options:

=SPARKLINE(INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")),{"charttype","bar"})

Step 13 (optional):

Finally, in column I, change the colors to a simple black and white scheme, by specifying color1 and color2 inside the sparkline:

=SPARKLINE(INDIRECT("'Data'!"&ADDRESS(1,ROW(),4)&":"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")),{"charttype","bar";"color1","black";"color2","white"})
Cantor Set Data Formulas
Click to enlarge

Feel free to delete any working columns once you have finished the formula showing the Cantor set.

Finished Cantor Set In Google Sheets

Here are the first 10 iterations of the algorithm to create the Cantor set:

Cantor Set In Google Sheet
Click to enlarge

Of course, this is a simplified representation of the Cantor set. It’s impossible to create the actual set in a Google Sheet since we can’t perform an infinite number of iterations.

Can I see an example worksheet?

Yes, here you go.

See Also

You might enjoy my other mathematical Google Sheet posts:

PI Function in Google Sheets And Other Fun π Facts

Complex Numbers In Google Sheets

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas

The FACT Function in Google Sheets (And Why A Shuffled Deck of Cards Is Unique)