Spreadsheets remain the most flexible interface for manipulating and analyzing data. With 1+ billion users worldwide, spreadsheets allow business users to easily leverage functions, charts, and pivots to generate insights and solve problems.
However, building formulas in spreadsheet platforms such as Google Sheets still requires work. For some users, generating formulas is time-intensive. Other users lack the skills to produce complex formulas.
That’s why an AI formula builder is valuable, whether you’re an expert or a beginner.
An AI formula builder allows users to describe the formula they want to build as text. The formula builder automatically transforms these instructions into spreadsheet formulas.
Our users keep requesting this feature, and we’ve been listening. Recently, we recently announced our Formula Builder — a free AI-powered, text-to-formula creator available in Coefficient.
Read this guide for a full overview of Coefficient’s Formula Builder, including how it works, how to set it up, and key use cases.
Video Walkthrough: How to Use Formula Builder in Google Sheets
What is Formula Builder?
Coefficient’s Formula Builder is an AI-infused, text-box assistant that converts textual descriptions into Google Sheets formulas. The Formula Builder is located on the Coefficient sidebar.
The way it works is simple. Just describe the Google Sheets formula you want to generate in the text box, in plain English. And the Formula Bot will automatically generate the corresponding formula for Google Sheets.
The Formula Builder is designed for lightweight usage; you can seamlessly leverage the tool as you navigate your spreadsheet. Now let’s take a look at how to set up Formula Builder in Google Sheets.
How to Set Up Formula Builder
To use Formula Builder, you need to install Coefficient. Coefficient is a free Google Sheets add-on that allows you to import data from your business systems — such as Salesforce and HubSpot — into Google Sheets.
Here’s a walkthrough on how to install Coefficient and launch Formula Builder.
First, click Extensions from the Google Sheets menu. Choose Add-ons -> Get add-ons. This will display the Google Workspace Marketplace.
Search for “Coefficient”. Click on the Coefficient app in the search results.
Accept the prompts to install.
Once the installation is finished, return to Extensions on the Google Sheets menu. Coefficient will be available as an add-on.
Now launch the app. Coefficient will run on the sidebar of your Google Sheet.
Select GPT Copilot on the Coefficient sidebar.
Then choose Formula Builder.
The Formula Builder will open.
Type a description of a formula into the text box. Then press ‘Build’.
The Google Sheets formula will appear below the prompt box.
Paste the formula into a cell to produce the result.
Formula Builder: Top 5 Use Case Examples
Now let’s examine some practical use cases of Formula Builder using a real dataset. Let’s import a dataset from Salesforce into Google Sheets. We can pull the data using Coefficient.
Coefficient is a Google Sheets add-on that allows you to instantly and automatically pull data from your business systems, such as HubSpot, Tableau, and Looker, into your Google spreadsheet.
Let’s import a sales dataset featuring sales pipeline metrics.
Read our blog on how to connect Salesforce to Google Sheets for a full overview of the process.
Now, without further ado, here are five key use cases for Formula Builder, based on the example Salesforce dataset above.
1. Automatically Extract Email Domains
First, let’s extract the domains from the email addresses in column A into the ‘Email Domain’ column (column I).
The process is super simple. Just open Formula Builder in Coefficient. Now type Get email domain from column A into the text box.
Press ‘Build’ to generate the formula.
Paste the formula under the “Email Domain” column and drag the formula down. This will extract the domains from all the emails in column A.
2. Display Won Amount for Each Account
Now, let’s display the amount of revenue won for each account. You can calculate this by leveraging the TRUE/FALSE values in column G.
Open the Formula Builder and type in this prompt: If column G equals true, return column B value.
Now click ‘Build’. You should receive a formula such as this:
Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.
Now paste the formula into the “Won Amount” column (column J) and drag it down.
The blank cells represent the value of deals that are not closed won (i.e. $0).
3. Calculate Days Since Opp Created
Next, let’s calculate the days since each opp was created. We can do this using column C (Created Date):
Navigate to Formula Builder and type in: Calculate days since date in column C.
Press ‘Build’ and a formula such as this will appear:
Now paste the formula into column K (Days since Opps Created?) and pull it down.
Column K will now display the days since each opportunity was created.
4. Number of Deal Types
For this next example, let’s count the number of deal types across all the accounts. We can do this using the Opportunity Type column.
Launch Formula Builder and enter the following prompt: Count the number of times column H equals cell M7.
Formula Builder will produce a formula like this:
Now paste the formula in your spreadsheet. Note: in this example, cell M7 is equal to “New Business”. We referenced the cell instead of the text string because we want to drag the formula down.
Drag the formula down to count the number of Existing Business deals and Expansion deals as well.
This will give you a complete count of the number of deals by opportunity type.
5. Generate Amount Won by Deal Type
For our final example, let’s calculate the amount won for each deal type. This will require us to add the Closed Won deals for each deal type.
Open Formula Builder and enter this prompt: Add column B if column E equals “Closed Won” and column H equals cell M7.
In other words, add the deal amount to the sum if the stage is Closed Won and the Opportunity Type equals New Business.
Click ‘Build’ and Formula Builder will produce a formula like this:
Paste the formula onto your spreadsheet. Again, we reference the cell M7 instead of the text string “New Business”, because we want to drag the formula down automatically for the rest of the column.
Now drag the formula down to calculate the amount won for each Opportunity Type.
Voila — there are five key sales use cases for Formula Builder! And this is just scratching the surface of Formula Builder’s potential.
AI Formula Builder: Write Complicated Formulas with Simple Text Descriptions
Coefficient’s Formula Builder allows you to harness the power of AI in Google Sheets to write complicated formulas with simple text instructions.
These use cases for our Formula Builder are starting points for your own custom usages. Install Coefficient for free now and start leveraging Formula Builder in your day-to-day spreadsheet workflows.
