Brought to you by:

Hi Reader,

Welcome to the Google Sheets Tips newsletter #374, your Monday morning espresso, in spreadsheet form!

Coming to you from the cold and snowy East Coast of the US. We didn't quite get the 20 inches of snow forecast earlier in the week, but we did end up getting around 8 - 10 inches of very dry, powdery snow. And with the temps rarely getting above 20 F (-7 C) this week, it's going to stick around for a while yet.

School is closed so you'll probably find me and my family and neighbors making the most of the snow:

➜ News

I.
​Worth a watch: The Day After AGI, an interesting discussion with the head of Google AI's DeepMind, Demis Hassabis, and CEO of Anthropic, Dario Amodei, about the current pathway to AGI (Artificial General Intelligence, aka equal to or exceeding human intelligence).

​Watch it here >>​

II.
​If you still need convincing of AI's impact, Ryan Dahl, founder of Node.js and one of the most influential voices in software, recently declared that “the era of humans writing code is over.”

​Read the full comment here >>​

III.
​My friend and fellow GDE, Martin Hawksey, recently published this guide to different ways to generating Excel file outputs from Google Workspace projects.

​Read more here >>​

Brought to you by: BudgetSheet Track & Analyze Your Spending With BudgetSheet Automate the most tedious part of tracking your spending and preparing for taxes. BudgetSheet can automatically categorize and import all your bank transactions and track account balances in Google Sheets. No more manual copying & pasting from CSV exports for bank data! It's quick, secure, and free to try. Join over 15,000 satisfied users today! Try BudgetSheet For Free

➜ Sheets Tip #374: Task list with status bar

Consider this simple task list that uses checkboxes to indicate when a task has been completed:

Let's spruce it up a bit!

First, let's add a conditional formatting rule to make it easier to see the items that have been completed.

Format > Conditional formatting

Then select "Custom formula is" as the format rule and set it to

I set the formatting to strikethrough and grey cell background, which gives us this effect:

Now we'll add a status indicator to show how far through completing the list we are.

Under the checkboxes, add this formula:

If your checkboxes are in a different range, you'll need to adjust both the range (B2:B9) and the denominator (the number we divide by, in this case 8). The denominator should equal the total number of checkboxes.

Format the cell as a % to show what percent of the checkboxes are checked.

Taking it further, we could add a sparkline in the cell under the percent to visually show this:

In this example B11 contains the percent formula.

In our Sheet, it looks like this:

Let's use HSTACK to tidy it up a bit. Replace the percent and sparkline formulas with this single formula:

This is a rather ghastly formula where the COUNTIF is repeated twice, so let's clear it up with a LET function:

In our Sheet, it now looks like this:

Finally, we can get really fancy and overlay the percent on the sparkline bar chart:

The formula now sits in column A (in cell A11).

It's a modified version of the HSTACK above that "pushes" the percent value out of its cell (A11) into the adjacent cell (B11), which contains the sparkline.

The value "17+p*14" is the amount of the offset, i.e. how many characters to "push" the percent value across. Feel free to experiment with the values to move it closer or further out, especially if you change the width of your columns.

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. When you use ChatGPT for everything...​