In this post, we’ll look at how to create miniature formula pie charts in Google Sheets. Formula pie charts are miniature pie charts that exist inside a single cell of a Google Sheet.
We’ll even create a Named Function to make it super easy to use these miniature pie charts. We’ll name this new function SPARKPIE, in honor of the eponymous SPARKLINE function.
This post was born from my ongoing experimentation/obsession with the SPARKLINE function in Google Sheets.
We’ll see how to create a Join-The-Dots game in Google Sheets using only the built-in formulas, no code.
In this post, we’ll build a named function that creates a miniature bullet chart in Google Sheets, as shown in this GIF:
Bullet charts are variations on bar charts that show a primary measure compared to some target value. They’re highly effective because they capture a lot of information in a small, neat design.
To begin with โ a warm-up if you like โ let’s create a simple version of a bullet chart using a standard sparkline. (This formula featured as Tip 237 in my weekly Google Sheets newsletter. Sign up to get a weekly actionable Google Sheets tip!)
โก A template is available at the end of this post.
Continue reading Bullet Chart in Google Sheets with Sparklines and Named Functions
I was playing with my children the other day when one of them grabbed our Etch A Sketch toy and started drawing a treasure map with it.
Sitting in my office later that day I had a crazy thought “Could I build a working Etch A Sketch in Google Sheets?”
Two days later and boom! Here it is:
๐ Grab your own copy of the template at the bottom of this article.
The game works using four techniques:
It doesn’t use any code. In fact, it’s created entirely with the native built-in functions of Google Sheets.
Before I dive in though, I want to acknowledge a fellow Google Sheets aficionado…
Tyler Robertson is a Google Sheets wizard who’s built an amazing portfolio of spreadsheet games (described by some as the Sistine Chapel for spreadsheets) using only built-in formulas.
Thankfully, he hasn’t built an Etch A Sketch clone yet ๐
This Etch A Sheet game uses Tyler’s checkboxes as a button technique, and has similar logic to his moving-a-character-around-a-Sheet game.
So thank you, Tyler, for your amazing work!
Just like the real Etch A Sketch game, there are controls to move the stylus left or right and up or down, to create lineographic images.
Since you can’t “shake” a Google Sheet (although I bet you wish you could sometimes!), there’s an additional reset checkbox to clear out the image and put the stylus back to the bottom left corner.
There’s a button to open up the right side of the Sheet and see the formulas that generate coordinates for the sparkline function:
The buttons are regular checkboxes, which toggle a TRUE/FALSE value in the cell.
The checkbox in cell H30 is the reset checkbox and I’ve called it “reset” in the named ranges box.
In cell V3:
=IF(F30<>V3,F30,V3)In cell U3:
=F30<>V3I also named U3 “right”.
Then in cell W3:
=IF(reset,0,IF(right,W3+1,W3))With iterative calculation switched on (see File > Settings > Calculation) and set to a maximum of 1 iteration, these formulas let the checkboxes function as buttons (see Tyler Robertson’s post for more details of how this works).
I put the value 1 in cell U16 and named it “startX”.
Similarly, I put a 1 in cell U19 and named it “startY”.
The main IFS function, which controls the horizontal X coordinate, is in cell V16:
=IFS(reset, startX, AND(right,currentX<50), currentX+1, AND(left,currentX>1), currentX-1, TRUE, currentX)This formula cell is labeled as a named range “currentX”.
Firstly, it checks if the reset button is checked and if it is, resets the value of the cell to the starting X value.
If the reset button is unchecked, i.e. FALSE, then it checks if the right button is pressed and the current X is less than 50, using an AND function, and if it is, adds 1 to the current value.
Otherwise, if the left button is pressed and the current X is greater than 1, it subtracts 1 from the current value.
Finally, there is a TRUE condition to act as a catch-all when the previous conditions fail. It sets the value back to the start value.
This self-referencing formula is put into the adjacent cell, W16, and called “xPath”.
It appends each new current value to itself to create a string of x values as the buttons are pressed, i.e. “1”, “1,2”, “1,2,3”, “1,2,3,4” etc.
=IF(reset,,xPath & "," & currentX)For this to work, the spreadsheet needs to have iterative calculations enabled with a max of 1 iteration.
The settings are found under the File > Settings > Calculation:
This formula turns the xPath string of values into a column of numbers, which is fed into the sparkline function in the next step.
=IFERROR(TRANSPOSE(SPLIT(xPath,",")),"")The SPLIT function breaks up the previous string of data, by the comma separator.
The TRANSPOSE function turns the array from a row vector to a column vector.
The IFERROR function wrapper hides the error message when the xPath variable is empty.
The same formulas are replicated to create a column of Y coordinates.
=IFERROR(SPARKLINE(Q3:R,{"linewidth",2 ; "xmin",1 ; "xmax",50 ; "ymin",1 ; "ymax",50}),"")Here, the sparkline formula takes the X Number and Y Number coordinates (two columns of numbers) and simply plots them as a line.
In the sparkline options, I’ve set the linewidth to 2 so it stands out more. I also set min and max values for the canvas, so that the drawing always starts from the bottom left.
This is another interesting technique, used here to show or hide content that doesn’t need to be on display all the time. I’ve used the same technique for the “Formulas” section, shown in the GIF above.
The grouped row button below the Etch A Sheet board shows and hides the instructions section when it is toggled:
There are a few other steps to complete the Etch A Sheet:
And there you have it!
To stay true to the original game, I put the horizontal checkbox buttons on the left side and the vertical controls on the right side.
However, these are awkward to press because you have to jump back and forth between them with your cursor. Of course, it doesn’t matter with the physical Etch A Sketch because the dials are positioned for each hand and can be operated simultaneously.
Perhaps a better approach in the Sheet version is to put the checkbox buttons close together so that the cursor movement is minimized.
Every Etch A Sheet game restarts from (1,1).
However, when you turn a real Etch A Sketch upside down, shake it, and then restart, the line is drawn from wherever it last finished. It does NOT revert back to the bottom left.
So I’ll leave this as a challenge for you! Can you modify the formulas to match this behavior?
If you look really closely at the GIFs in this post, you’ll notice that the line is one step behind the button press. I.e. when I press a button, it has to draw the previous step still before registering the new button action. Obviously, this is not ideal.
Again, I leave this as a challenge for you to explore…
Unfortunately, I have to get back to my actual work now, so I’m going to leave this fix for another day. This project exceeded my expectations (It was a lot of fun! It was intellectually challenging! I learned some new techniques!) so I feel satisfied with this outcome. I don’t feel the need to make it perfect.
Open the Etch A Sheet template here.
Make your own copy: File > Make a copy
(Note: If you are unable to open this file, it’s probably because it’s from an outside organization and my Google Workspace domain is not whitelisted at your organization. You may be able to ask your Google Workspace administrator about this. In the meantime, feel free to open it in an incognito window and you should be able to view it.)
If it does not appear to work, check you have the iterative calculations enabled.
Go to File > Settings > Calculation
Make sure Iterative Calculations is ON and set to 1 iteration. See the image under “X Path Formula” for more details.
If you do make a copy, I’d love to see what you draw with it!
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:
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…
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:
Wow!
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:
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:
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 to generate the correct gaps for the Cantor set.
We’ll use sparklines to draw the Cantor set in Google Sheets.
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"})
Feel free to delete any working columns once you have finished the formula showing the Cantor set.
Here are the first 10 iterations of the algorithm to create the Cantor set:
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.
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)
How To Draw The Sierpiลski Triangle In Google Sheets
Exploring Population Growth And Chaos Theory With The Logistic Map, In Google Sheets