Skip to main content

Google Sheets adds pivot tables, suggested formula tools powered by machine learning

Google’s work with machine learning is impressive for a number of reasons, but its most practical uses often come in places you wouldn’t expect. Today, Google is revealing new tools in Sheets made possible by machine learning.

Spreadsheets are very powerful, but you can only access a fraction of that power if you don’t know how to use the right formulas. Now, Google Sheets is making it easier to pick the right formula by using machine learning to give a collection of suggested formulas based on the data being used.

Along with that, Pivot Tables on Sheets are getting new machine learning tools. With a simple click, Sheets can automatically create a pivot table based on the data available, potentially saving a lot of time.

In the Explore pane, you can also ask questions of your data using everyday language (via natural language processing) and have the answer returned as a pivot table. For example, type “what is the sum of revenue by salesperson?” or “how much revenue does each product category generate?” and Sheets can help you find the right pivot table analysis.

In addition, if you want to create a pivot table from scratch, Sheets can suggest a number of relevant tables in the pivot table editor to help you summarize your data faster.

Google Sheets is also adding a few more features and making some tweaks to better suit the spreadsheet tool for data analysis. A brief changelog is below and those features, as well as those mentioned above, will be rolling out in the coming weeks.

  • Check out a refreshed UI for pivot tables in Sheets. We’ve also added new, customizable headings for rows and columns.
  • View your data differently with new pivot table features. Now, when you create a pivot table, you can “show values as a % of totals” to see summarized values as a fraction of grand totals. Once you have a table, you can right-click on a cell to “view details” or even combine pivot table groups to aggregate data the way you need it. We’ve also added new format options, like repeated row labels, to give you more fine-tuned control of how to present your summarized data.
  • Create and edit waterfall charts. Waterfall charts are good for visualizing sequential changes in data, like if you want to see the incremental breakdown of last year’s revenue month-by-month. Select Insert > Chart > Chart type picker and then choose “waterfall.”
  • Quickly import or paste fixed-width formatted data files. Now, Sheets will automatically split up the data into columns for you without needing a delimiter such as commas between data.

Check out 9to5Google on YouTube for more news:

FTC: We use income earning auto affiliate links. More.

You’re reading 9to5Google — experts who break news about Google and its surrounding ecosystem, day after day. Be sure to check out our homepage for all the latest news, and follow 9to5Google on Twitter, Facebook, and LinkedIn to stay in the loop. Don’t know where to start? Check out our exclusive stories, reviews, how-tos, and subscribe to our YouTube channel

Comments

Author

Avatar for Ben Schoon Ben Schoon

Ben is a Senior Editor for 9to5Google.

Find him on Twitter @NexusBen. Send tips to schoon@9to5g.com or encrypted to benschoon@protonmail.com.