TECHNICAL FIELD

Aspects of the disclosure are related to the field of software applications, and in particular, to editing formulas and functions in spreadsheets.

TECHNICAL BACKGROUND

Spreadsheet applications are powerful tools for interacting with data of all types. Familiar features of spreadsheet applications include the ability to sort and tabulate data, to generate charts and pivot tables for analyzing data, and to create formulas that automatically calculate values based on the contents of other cells.

Many formulas include functions to expedite programming that would otherwise be tedious, difficult, or both. For example, a range of cells could be added by a formula that specifies each cell as an individual operand of the formula. Alternatively, using the SUM function in a formula provides a much simpler notation whereby the operand is expressed by the range of cells being added together. Other popular but more complex functions include the IF, ROUND, LOOKUP, and MATCH functions.

Unfortunately, many users struggle with using formulas and functions in spreadsheets—especially the more complex ones. At one level, a given user may not know which function to use in a formula, while at another level, the user may lack expertise with a specific function, leading to errors and other frustrations. Most applications provide at least a modicum of help such as basic auto-complete tools and in-line function definitions which, while somewhat useful, only partly reduce the friction associated with formulas and functions.

Overview

Technology is disclosed herein that enhances the user experience with respect to editing formulas and functions in spreadsheets. In at least one implementation, machine learning is leveraged to suggest functions for formulas, while in another implementation, a form allows the user to edit the parameters of a function with ease.

For example, a computing apparatus under the control of software as disclosed herein detects a beginning of a formula in a cell of a spreadsheet. In response to detecting the beginning of the formula, the computing apparatus identifies a vector representation of a context of the cell and supplies the vector representation as input to one or more machine learning models. The one or more machine learning models produce output comprising one or more suggested functions for the formula and one or more suggested operands, which the computing apparatus presents in a user interface to the spreadsheet. In addition, the one or more machine learning modules, examples of which include neural networks and random forest models, may be implemented in the context of or external to the spreadsheet application that hosts the spreadsheet.

In another example, the computing apparatus displays at least a portion of a formula in a user interface to a spreadsheet, wherein the portion of the formula comprises a character string that includes one or more functions expressed therein. The computing apparatus displays a form in the user interface, wherein the form includes fields corresponding to parameters of an active one of the one or more functions expressed in the character string. The computing apparatus populates the character string at positions corresponding to the parameters of the active one, of the one or more functions, with values entered into the fields of the form. As the active one, of the one or more functions, changes to a newly active function, the computing apparatus changes the fields in the form to correspond to parameters of the newly active function.

This Overview is provided to introduce a selection of concepts in a simplified form that are further described below in the Technical Disclosure. It may be understood that this Overview is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

