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:

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?

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:

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.

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.

Finished Cantor Set In Google Sheets

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.

Can I see an example worksheet?

Yes, here you go.

You might enjoy my other mathematical Google Sheet posts:

PI Function in Google Sheets And Other Fun π Facts

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)

In this tutorial, we’ll create a checklist template in Google Sheets.

We’ll use checkboxes, conditional formatting and a sparkline to build a checklist template like this:

Checklist Template Use Case

There are many situations when a checklist comes in handy.

From simple to-do lists to project planners, from teaching lessons to tracking physical goods.

And although this simple spreadsheet checklist isn’t suitable for large, complex projects, or projects that require more robust data trails, it’s a quick and easy way to add some useful flair to your spreadsheet projects.

When I teach live workshops, I often include a front sheet in my Google Sheet that I use as a checklist for the exercise steps.

It ensures I don’t forget anything and gives the audience a visual clue as to where we’re up to in the workshop. And I get almost as many questions about how I built these checklists as for the topic of the actual workshops.

How To Create A Google Sheets Checklist Template

Checklist Template

Feel free to copy this sheet: File > Make a copy

In the meantime, feel free to open it in an incognito window and you should be able to view it.

1. Sheet Set up

Then leave a blank row.

On row 3, we put the word “Progress” in column 1. Leave the other column blank for now.

Row 4 is blank.

On row 5, put the headers for the checklist table: Status and Step

On rows 6 onwards, we put a checkbox in column 1 and the corresponding step in column 2 of that row.

To add a checkbox, go to: Insert > Checkbox

Next, we’ll apply conditional formatting across an entire row to turn the row red when it’s marked as done.

Highlight all the rows with your checkboxes and steps, e.g. A6 to C15 in this example.

Select Conditional Formatting and add this Custom Formula Is rule:

`=\$A6`

Set the formatting rules to: light red background, dark red text and strikethrough.

Now, whenever you check the checkboxes, the entire row will be formatted red to show it’s complete (see the GIF image at the start of this post).

3. Use A Sparkline To Create A Status Bar

Here’s the sparkline function in cell B3 that creates that dynamic status bar:

`=SPARKLINE(COUNTIF(A6:A,TRUE),{"charttype","bar" ; "max",COUNTA(B6:B) ; "color1","red"})`

Let’s break it down to see what’s going on.

Checkbox Count

Firstly, the `COUNTIF(A6:A, TRUE)` function counts how many of the checkboxes in column A have been checked (i.e. have a TRUE value).

The output of this is a single number, between 0 and 10 in this example.

We pass that value into the SPARKLINE function.

Sparkline Bar Chart

Then we set the sparkline to be a bar chat, with the first option: `"charttype","bar"`

Max Value

Next, we need to specify a maximum value for the bar chart, so that it can compare the count of checked checkboxes (e.g. 4) against the maximum possible number (10 in this example) to get the percentage completion.

We could simply type in the max value of 10 as an option, but it’s better practice to set it with a formula so that it will update automatically if your data changes.

To do this we count the number of “steps” in the column next to the checkboxes: `"max",COUNTA(B6:B)`

Custom Color

Finally, we set a custom color for the sparkline with the final custom option: `"color1","red"`

Notes

In some European countries, sparkline formulas have a slightly different syntax and use “\” instead of “;” (read more here about syntax differences based on Google Sheets location).

Pro tip: to select or un-select multiple checkboxes at once, highlight them all and press the space bar!

The final formula goes in cell C3 next to the sparkline to show the actual percentage complete value:

`=COUNTIF(A6:A,TRUE)/COUNTA(B6:B)`

This counts the checkboxes in column 1 and divides by the total count of steps in column 2. Format it as a “%”.

Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).

However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.

We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:

As with the waterfall charts in Google Sheets, they’re not one of the out-the-box charts available to us, so we have to manually create them with a crafty workaround. Thankfully, they’re relatively simple to create, certainly simpler than the waterfall chart.

For all of these examples, we’ll use this fictitious real-estate dataset:

Here, I’m imagining the real estate agency collects data relating to their sales funnel, and they want to display it in a funnel chart format.

Click here to open up the Google Sheet template and make your own copy `(File > Make a copy...)`.

Sparklines are small, lightweight charts, typically without axes, which exist inside a single cell in your spreadsheets. They’re a wonderful, quick way to visualize your data, without needing the complexity of a full-blown chart.

Introduction

Sparklines were first created by interface designer Peter Zelchenko around 1998. The term “sparkline” was coined by statistician and data visualization legend Edward Tufte.

Grab the data and solution file for this tutorial:

10 Techniques for building a Google Sheets Dashboard

You’re probably familiar with using Google Sheets to organize and analyze your data. But did you know you can build a dynamic Google Sheets dashboard to really understand your data?

With a handful of powerful techniques, you can add some pizzazz and dynamism to the presentation of your data. Here are ten tricks to try next time you’re building a Google Sheets dashboard.

1. Collect user inputs through a Google Form into a Google Sheets dashboard

Google Forms are a quick and easy way to collect data. The responses are collected in a Google Sheet which we can then use to power a dashboard. For example, you could run a survey on customer satisfaction, or status reports from your operations team members, and then turn this data into a one page visual summary, giving you instant insight into your data. Let’s run through a super quick and simple example:

Step 1: Create a Google form

Create a Google form in Google Drive (detailed instructions here) by navigating to:

`Drive > New > More > Google Forms`

Step 2: Setup the form

Next, setup your Google Form by giving it a name and adding any questions that you have. In this example, I’ve created a form with one multiple choice question which asks a user which color they prefer (from red, blue or green):

Step 3: Create the Google Sheets dashboard

View your responses and setup the Google Sheets dashboard. You’ll need to submit the form at least once, so that you have some data in your responses which you can use. I then added a new tab and created a new table (a staging table), which uses a countif formula (see section 3 on conditional formulas below) to tally up the votes for each color and show this count in the staging table. Then I added a bar chart and pie chart (see section 6 on charts below) running off this staging table to display the counts visually. These charts will update whenever new votes are submitted.

2. Retrieve data with LOOKUP formulas

Mastering lookup formulas is a key technique for many data projects in Google Sheets (and Excel). It’s at the heart of the Google Sheets dashboard shown at the start of this post and such a useful technique in it’s own right that I’d recommend investing time to practice this technique. There are several methods at your disposal:

VLOOKUP is a vertical lookup formula which searches the first column of a range, and when it finds the first instance of the result (if there is one), it returns the value in that row from the column of the range that you specify with the index value, e.g.:

`=VLOOKUP(F1,A1:D20,4,FALSE)`

This formula takes the search term in cell F1, for example a string “Channel A”, and looks for it in column A. At the first match, if it exists, (e.g. imagine cell A10 contains “Channel A”) it returns the value corresponding to column 4 of that same row (in this case D10, which might be a sales figure for Channel A). Searching through numeric or dates in your lookup column (the first column) requires the data to be sorted to avoid incorrect values being returned.

HLOOKUP is a horizontal lookup implementation of the vlookup formula. I find it’s rarely used but useful to keep in the back pocket for certain specific situations.

INDEX & MATCH are two formulas that combine together to create powerful, flexible lookup solutions. They are superior to vlookups by being more flexible and avoiding some of the pitfalls with vlookups (check out these articles here, here and here – they’re Excel based but still apply to Google Sheets). However, they are a little more complex to implement as they involve two nested formulas.

To create the same implementation as we had above with the vlookup, we could use this formula:

`=INDEX(A1:D20,MATCH(F1,A1:A20,0),4)`

Multi-condition lookup formula: Sometimes a simple lookup formula isn’t enough. For example, you may need to find a result based on two or more parameters (e.g. web traffic from a specific channel in a specific month). In this case, a multi-condition lookup formula can do the trick.

Say we have this table of Google Analytics data and need to retrieve the number of Search results in January 2015 (i.e. our answer is dependent on three criteria):

 A B C D 1 ga:year ga:month ga:channel ga:sessions 2 2015 Jan Search 46,936 3 2015 Jan Email 922 4 2015 Jan Referral 4,973 5 2015 Feb Search 43,302 6 …

Let’s assume we have setup a staging table for our charts below this. To lookup the value we want (in this case Search for Jan 2015):

 9 … 10 Year Month Search 11 2015 Jan Formula? 12 2015 Feb Formula?

we can use this formula in cell C11:

`=INDEX(\$D\$2:\$D\$9,MATCH(\$A11&\$B11&C\$10,\$A\$2:\$A\$9&\$B\$2:\$B\$9&\$C\$2:\$C\$9,0))`

which gives a result of 46,936.

Crazy huh! This formula was inspired by this post from Excel wizard Chandoo, and uses an index/match lookup to compare multiple values across multiple columns in a data table. It concatenates the year, month and channel, to use as the lookup value, then looks for this concatenated value in the raw data across the year, month and channel columns. When it finds the right match it returns the corresponding result.

3. Apply logic with conditional formulas

COUNTIF is a formula which counts items in a range that match the specified criterion. It’s useful for doing things like counting non-blank cells in a range or counting the number of specific items in a range. The formula is:

`=COUNTIF(range, criterion)`

COUNTIFS is similar to the countif formula but returns a result based on multiple criteria. In other words, it counts the number of items in the first range that matches the first criteria AND also match a second criteria in a second range AND a third etc… The formula is slightly different to the basic countif formula, as follows:

`=COUNTIFS(range1, criterion1, [range2, criterion2, ...])`

SUMIF is the same idea as the countif, but returns a sum of the values. It’s possible to match criteria in one range, but sum values in a separate range, which is a really useful feature (e.g. imagine a table with names in column A and sales results in column B, then the sumif formula can sum the sales values for all occurrences of say “Ben” from the list of names). The formula for sumif is:

`=SUMIF(range, criterion, [sum_range])`

SUMIFS is the multi-criteria version of sumif, so it’s the same idea but the sum is calculated when you match multiple criteria in multiple ranges. Again, a very useful formula:

`=SUMIFS(sum_range, range1, criterion1, [range2, criterion2, ...])`

Dashboards often have a date component to them, where a variable changes over time and merits being illustrated visually in the dashboard. There are various formulas/techniques available for automating this process.

The today formula, which gives the current date, will display the date the last time the spreadsheet was recalculated (for example, when you open it or make a change). The formula is:

`=TODAY()`

If you want to also have a current time element in your spreadsheet, then use the now formula, which returns the date and time the spreadsheet was last recalculated. The formula is:

`=NOW()`

Both the today and now functions can be set to update automatically, rather than just when the sheet is recalculated. Go to File > Spreadsheet Settings and then select “On change and every hour” or “On change and every minute”.

Be careful of inserting too many of these formulas in your spreadsheets as they are volatile functions, which means all that recalculating will harm your spreadsheet performance.

An example of using the today formula would be to display the current month in your dashboard, using the following text formula:

`=TEXT(TODAY(),"MMMM")`

For a more complex example, think of setting up start and end dates for a dashboard table, where I could enter formulas using the today function, set it to update automatically, and then base the other dates off that, using formulas.

The eomonth formula comes in handy here, returning the last day of a month which falls a specified number of months before or after another date.

For example, use the following formula to create the first day of the month prior to the current one:

`=EOMONTH( TODAY(), -2 ) + 1`

I could then keep “rolling” the months back, by changing the “-2″ to “-3″ for two months prior, then “-4″, “-5″ all the way back to “-13″, to give the current month plus 12 preceding months in a table, which would automatically update as we move into each new month.

I could also get the first day of the current month but a year earlier, for example to compare current sales metrics against the same period last year, using the following formula:

`=EOMONTH( TODAY(), -13 ) + 1`

There are many possible variations from combining today, date, text and eomoth formulas, to get the correct periods you want in your Google Sheets dashboard and have them update automatically to stay current.

5. Add interactivity with data validation

Use data validation to add interactivity to your dashboards. You can create a nifty drop-down menu from which the user can select a parameter, e.g. a sales channel or specific time, and then change the data based on this choice, so any charts will update automatically. It’s a pretty simple technique but surprisingly powerful.

First, create a list of choices to present to the user, e.g. list of sales channels, and then using the `Data > Validation` feature on the highlighted list of values, create a user input menu for sales channels:

The user then has a drop down menu in your spreadsheet, from which he/she can select the desired parameter:

Data in the table which underpins a chart is changed based on the user’s choice from the drop-down menu above, by using one of the lookup formulas from step 2.

Google has a whole suite of charts available to use with your data. Some of the most well known are the plain old bar/column chart, the much-maligned pie chart (for and against arguments. Personally, I think judicious use is ok), line charts and scatter plots. In addition though, Google Sheets has the ability to create map charts, interactive time series charts, gauges (can be useful if used judiciously) or combined “combo” charts, which allow you to combine different data series visualizations.

Examples

The humble bar chart can be tweaked into a stacked bar chart, which can be used to visualize two related metrics, for example how many sales have been made so far, versus how many are still required to hit the target.

An area chart can be used to show comparisons of data, as shown in this example of the cumulative sales during a digital flash sale, showing 2014 data against 2015 data:

7. Show trends with sparklines

Sparklines were first created by statistician and data visualization legend Edward Tufte. They’re small, simple charts without axes, which exist inside a single cell. They’re a wonderful, quick way for visually showing a result, without needing the complexity of a full-blown chart. They work well for datasets based on a timescale.

A sparkline looks like this:

The formula for sparklines in Google Sheets is:

`=sparkline(data,[options])`

where data refers to a range of values to plot the sparkline. The optional options argument is used to specify things like chart type (line, bar, column or winloss), color and other specific settings.

8. Apply conditional formatting to show changes

Hidden in the Custom Number Format menu is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0 or less than zero.

It’s a great tool to apply to tables in your Google Sheets dashboards for example, where the data is changing. By changing the color of a table cell’s text as the data changes, you can bring it to the attention of your user.

Consider the following sales table which has a % change column:

Now take a look at the same table with colors and arrows added to call out the % change column:

It’s significantly easier/quicker to read and absorb that information.

How to add this custom formatting

1. Somewhere in your Sheet, or a new blank Sheet, copy these three CHAR formulas (you can delete them later):

`=char(A1)`

`=char(A2)`

`=char(A3)`

Now, copy and paste them as values in your Sheet so they look like column C and are not formulas any longer.

(You copy as values by copying, then right clicking into a cell and select Paste special > Paste values only…)

You’ll need to copy these to your clipboard so you can paste them into the custom number format tool.

2. Highlight the % column and go to the custom number formatting menu:

3. Change the 0.00% in the Custom number formats input box to this:

`[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬`

as shown in this image:

What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.

Copy in the symbols from step 1 (you’ll have to do this separately for each one).

Use the square brackets to specify the color you want e.g. [color50] for green.

(Yes, it’s an Excel article, but the rules are the same.)

9. Format like a pro!

After all that effort to tease out the real stories hidden in your data, and make them accessible in charts and tables, it’s worth a little effort to spruce up the final version. Consider some of these ideas:

• Give all the tables a consistent format, e.g. light gray borders, a bold header row with white text and alternate gray/white shaded rows
• Remove the gridlines. Find this option in the View menu: `View > Gridlines`
• Hide all working tabs except the dashboard tab (does not affect the functionality of the dashboard)
• Use freeze panes, to lock specific rows or columns, so that if a user scrolls the header row(s) will be locked in place for example, and the title and user input options will always be visible. It’s found in the View menu: `View > Freeze`
• View the dashboard in full screen mode

10. Share and publish your Google Sheets dashboard for the world to see

It’s quite likely you’ll want to share your dashboard with colleagues, clients and/or the world. There are a couple of ways of doing this.

Firstly, you can click the Share button in the top right corner of the screen, which opens up the sharing options pane:

From here, you can enter email addresses to share directly with colleagues, or you can grab the sharing url and email that to people you want to share with, or paste into social media channels.

You have control over the access rights and whether recipients of the link can view, comment on or edit the dashboard. More information on the sharing settings in Google sheets here.

Secondly, you can publish your Google Sheets dashboard as a web page, or embed it as a component in another page, by clicking on:

`File > Publish to the web...`

which brings up this pane of options:

Publishing this way makes the Google Sheets dashboard visible to the public. For example, here is the Color Picker Form dashboard example from section 1 of this post, published online as a web app:

Grab the Google Sheet template for this Google Sheets Dashboard tutorial, with all 10 examples!!