Brought to you by:

Hi Reader,

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

I read something online last week (source) that really resonated with me:

The author gave the example of the US figure skater Alysa Liu, who rocked a huge smile on her way to a gold medal at the recent Winter Olympics.

As someone who has a tendency to take things too seriously, this was a good reminder for me to smile more and enjoy the journey. To seek joy in the process and focus less on the outcome. When we find the joy, the outcome will naturally follow.

I think we can all relate to doing our best work (or best anything in life) when we find that joyful flow state.

Whilst you ponder that, below you'll find the usual news, sponsor message, and Sheets tip (it's a 🔥 today!).

Have a great week ahead!

➜ News

I.
​Developers take note: Google engineers launched Google Workspace CLI, a command-line tool for Drive, Gmail, Calendar, Sheets, Docs, and more, with built-in AI Agent skills.

This is an open-source project, not an official launch from Google.

​Check it out here >>​

II.
​Google Workspace admins can now enable users share their Gemini chat conversations by creating public links to share and publish.

This has been possible with personal accounts for a while, so it's good to see this feature making it into Workspace.

​Read more here >>​

Hopefully, we'll get custom instructions in Workspace (it's available in personal accounts)...

Brought to you by: Lorka AI Lorka: GPT, Gemini, DeepSeek in One Access the world’s leading AI models in one place with Lorka. Combine GPT, Gemini, DeepSeek, and more under a single subscription: fast, flexible, and easy to use. Stop switching tools and unlock smarter productivity with an all-in-one AI platform. Try Lorka now

➜ Sheets Tip #380: Make your Sheets feel more like apps

When a Sheet gets too large or complex, it can be hard to focus.

You can "scale up and down" your level of detail in a Sheet by using the grouping feature to group rows or columns. This lets users show/hide data with a simple toggle switch.

But that's not all!

You can use this grouping feature to create interactive elements that make your Sheets feel more like apps.

Below, you'll see how to add sidebars to your Sheets and "smart buttons" for toggling grouped rows.

Enjoy!

1. Group Rows or Columns (Basic)

The basic way to tidy up a Sheet is to highlight columns or rows you don’t need to see all the time, such as intermediary calculation columns, and group them.

Highlight the columns or rows you want to group. Then either:

• Go to the menu View > Group​
  OR
• Right-click, and select View more row actions > Group rows (same for columns)

Google Sheets adds a [+] or [-] button in the margins of your Sheet to toggle visibility instantly.

You can right click on these "group bars" to bring up the group menu, where you can remove the group. Note, you can also move the position of the +/- button, which is helpful if you want to incorporate this technique into dashboards.

2. Give your Sheet a Sidebar (Intermediate)

To make this professional, you can add a visual "handle" to your grouped section to nudge users about what’s inside.

Here's how to do this:

• Add a narrow column next to your grouped columns.
• Type a label like "Click [+] for details"
• Use the Text Rotation tool in the menu bar (or the in the menu Format > Rotation) to make it vertical
• Merge that cell with several rows below to show the full text
• Give the merged cell a distinct background color to act like a "dockable" navigation panel

A great use case for this technique is to add a sidebar to a dashboard with details about the datasets or other metadata.

It stays hidden so the dashboard looks clean, but then it's available for anyone who wants to look.

Here's an example dashboard of Covid counts (back when that was a daily obsession for us all in 2019/20) that shows the sidebar technique in action:

3. “Smart Button” for Row (Advanced)

If you want your Sheet to feel more like an app, the SUBTOTAL function is your best friend.

SUBTOTAL can ignore data in hidden rows. We can use this feature to create a label that changes based on whether the group is open or closed.

Suppose you want to group rows 6 through 8. Use this formula in row 5 to add a "smart button":

It requires at least one cell in the grouped rows (rows 6 - 8) to contain data to work.

In the SUBTOTAL function, the code 103 counts values but ignores hidden rows. So when the rows are collapsed, the count is 0, and the formula tells the user to "Show Details."

Note, this method only works with grouped rows (because the subtotal function only works with rows).

To really make it "pop", add conditional formatting to the formula cell.

Apply a rule so that when the cell contains "Show", it turns a bright color (e.g. green). When it says "Hide", apply a rule that turns the cell red.

This provides your users an unmistakable visual cue of where the hidden data lives.

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

Have a great week!

Cheers,
Ben

P.S. In case you needed another reason to eat healthy...​