clock menu more-arrow no yes

Filed under:

Google Sheets’ formula suggestions are like autofill for math

New, 4 comments

It can suggest functions based on your data, and will auto-select your range

Illustration: Alex Castro / The Verge

Google has announced that Google Sheets is getting the ability to intelligently suggest formulas and functions for your spreadsheet, based on the data you're trying to analyze. For example, typing “=” into a cell below a list of numbers will pop up a box that lets you automatically add the numbers together, find their average, and more.

From my admittedly simple tests, it seems to be a pretty smart system. For example, with one column of data, it suggested that I could be looking for either the sum or average of the numbers. After I chose the sum and moved down to the next cell, it only suggested finding the average for the same range of numbers, not including the sum I’d just calculated. It’s a simple thing, keeping track of what’s data and what’s analysis, but it’s easy to imagine a version of this that gets tripped up on that.

Google says that to create these suggestions, it trained a machine learning model using anonymized data from certain spreadsheets. The model doesn’t just take how often certain formulas are used into account, though — it also looks at the context within sheets.

For example, as you can see in the GIF above, I had one row labeled as “Total,” and the system only suggested the sum formula. After I removed the “Total” label, it suggested both sum and average. Google told The Verge that it can also identify headers and look at how data is grouped to get an idea of what suggestions to make.

Spreadsheet programs have long tried to make their users’ lives easier with autocomplete features. For example, both Sheets and Excel feature a sort of series continuation feature, where it’ll try to detect what you’re doing in a selected range and then continuing it. For example, if I’ve got a list that goes 2, 4, 6, I can tell Sheets to continue that for a bunch of cells, and it’ll auto-populate the next cells with 8, 10, 12, etc. While these existing systems are definitely timesavers, it’d be a stretch to call them “intelligent” — you don’t have to use them much before you find something that they misinterpret.

It works with the basics, but falls down with curveballs (or Fibonacci sequences). Still a great life hack, though.

While Google’s intelligent function and formula suggestions aren’t as impressive as, say, Github’s Copilot tool that autocompletes code, they both represent tech’s ability to smooth over the mundane time sucks that take up more time than we’d probably realize. Is it particularly difficult to, say, type out “SUM” then select the range I want? No. Is it great to have the program just do it for me, as it can with email replies and Python functions, letting me get on with whatever it is that I need to use my human brain for? Absolutely.

According to Google’s blog, the feature started rolling out to Workspace, G Suite, and personal Google accounts on Wednesday and will take up to 15 days to show up for everyone. The feature can also be turned off if you’re not fond of the pop-ups. Google also says that the rollout will provide “users with visibility into whether previously compiled formulas need further verification.”