Blog /

How to Create a Sales Commission Calculator in Google Sheets (Template Included)

By Kevin Bartley

Sales commissions are a key payment stream for sales reps. Commissions incentivize sales reps to perform at a superior level, lead to more closed deals, and generate higher revenue for the company.

However, calculating sales commissions is often a challenge for sales orgs. Although the process seems straightforward, there are many roadblocks to creating a flexible, transparent sales commission calculator.

Since our customers routinely face this problem, we’ve designed our own sales commission calculator for Google Sheets. And now we’re sharing it with you!  

The following blog will show you how to build our sales commission calculator in Google Sheets.  

Alternatively, you can simply download our free template for the sales commission calculator to skip the building part.  

Important note: the sales commission calculator is based on your Salesforce CRM data.

Now let’s get started!

Sales Commission Calculator: Powered by Live Salesforce Data from Coefficient

Building a sales commission calculator is simple if you follow the steps in this guide. But the calculator isn’t helpful if you must copy-and-paste new Salesforce data to update the commissions every day.

What you actually need is live data — data that’s synced with your Salesforce CRM — so your sales commission calculator always stays up to date.

That’s where Coefficient comes in. Coefficient enables you to import live Salesforce data into Google Sheets on an automatic schedule. That way, the sales commission calculator is refreshed daily.

Coefficient empowers you to pull data from any company system — such as HubSpot, Tableau, and Snowflake — into Google Sheets and automatically update the data.

With fresh data from Coefficient, you can make daily calculations as to how much you owe salespeople, and share the calculator among your team at all times.

Here’s how you can combine Google Sheets and Coefficient to build a sales commission calculator that updates automatically.

How to Build a Google Sheets Sales Commission Calculator

Before you undertake any of the steps in this guide, you must install Coefficient.

Our blog on how to connect Salesforce to Google Sheets offers a full walkthrough of both the install process and pulling Salesforce data into Sheets.

Once you install Coefficient, follow the steps below to build the sales commissions calculator (or copy our free template).

First, open a new spreadsheet and create a set of tables similar to the ones below. They will contain the following columns:

Table 1: Quarterly Sales Commission Rates

  • Tier
  • Start Tier
  • End Tier
  • Commission

Table 2: Sales Commission – To Be Paid

  • Sale Rep
  • Q1 2022
  • Q2 2022
  • Q3 2022
  • Q4 2022
quarterly sales commission rates

Once you have your tables set up, launch the Coefficient app. Go to Extensions -> Coefficient -> Launch.

launch coefficient data connector

Once the Coefficient sidebar launches, choose Import from…

import data from salesforce google sheets

Then select Salesforce as your data source:

salesforce connector google sheets

Choose From Objects & Fields.

import objects and fields salesforce google sheets

Under New Import, choose the Opportunity object.

Over 100K pros building reports use Coefficient to automate business systems data into their Google Sheets
"What an amazing app! Coefficient makes integrating some of the most complex systems unbelievably simple! Set up your integrations in less than a minute."
Yonatan Schvimer
salesforce opportunity import google sheets

Click ‘Next’. Then choose Select fields… 

import salesforce fields google sheets

Add the “Full Name” field.

full name salesforce google sheets

Add the “Amount” field.

salesforce amount import google sheets

Finally, add the “Close Date” field.

salesforce close date google sheets

Then click “Done Selecting Fields”.

opportunity amount salesforce google sheets

You will return to the New Import screen. Click “Add filter” to add a filter to your import.

add filter google sheets coefficient

Select the “Opportunity Stage” filter.

filters opportunity stages coefficient import

Under the Picklist section, choose is one of.

picklist salesforce google sheets

Then choose the Closed Won stage.

closed won deal salesforce google sheets

Make sure to toggle on Pivot Mode.

pivot salesforce data google sheets

Now drag Full Name to Rows.

add fields salesforce google sheets

Drag Close Date to Columns.

columns google sheets salesforce

Lastly, drag Amount to Values.

values salesforce google sheets

Under Columns, choose Group by: Quarter.

values salesforce by quarter spreadsheet

Under Amount, choose count: sum.

row sum salesforce google sheets

Now name your import. Then click ‘Import’.

import live salesforce data

After you finish your import, fill in your Quarterly Sales Commission Rates table with your desired commission structure.

Now navigate to the Q1 2022 cell under Michael Brown (cell C12).

Copy and paste the following formula into cell C12:

=xlookup($B12,’Salesforce Import’!$A$4:$A$7,’Salesforce Import’!B$4:B$7)*vlookup(xlookup($B12,’Salesforce Import’!$A$4:$A$7,’Salesforce Import’!B$4:B$7),$C$6:$E$8,3,true)

Note: Replace ‘Salesforce Import’ with the name of your Coefficient import in the above formula.

Otherwise, it won’t work.

The formula will calculate the Q1 2022 sales commission for Michael Brown.

edit salesforce import google sheets

Now drag the formula across the rest of the table. This will give you the sales commission calculations for all of your sales reps across Q1, Q2, Q3, Q4.

quarterly sales commission calculator google sheets

As a last step, set up automatic data refreshes.

This ensures that the Salesforce data in your spreadsheet will update on a daily basis. Q2, Q3, Q4 will gradually fill in as new deals come in throughout the year.

To set up automatic refreshes, open your Salesforce import on the Coefficient sidebar. Select ‘Edit’ on the dropdown menu.

edit salesforce import google sheets

Enable the Refresh Schedule.

refresh data google sheets

Configure the Refresh Schedule: Daily at 7am.

refresh salesforce data daily google sheets

The Salesforce data will update every day at 7am. At the same time, the sales commission calculator will recalculate how much you owe each sales rep.

quarterly sales commissions google sheets

This makes paying out commissions easy and straightforward. As the year goes on, your sales commissions will fill in for each quarter automatically.

Automate Sales Commissions: Combine Spreadsheets with Live Salesforce Data

With Coefficient, you can import real-time Salesforce data into Google Sheets, and build a sales commission calculator that updates automatically.

Follow the steps in this guide to build a Google Sheets sales commission calculator. Or download our free template to access the sales commission calculator, already built for you.

Get started for free with Coefficient so you can create a flexible sales commission calculator with fresh Salesforce data.

Exported with Wordable

Wait, there's more!

Connect any system to Google Sheets in just seconds.
Get Started Free
100,000+ users on
Google Marketplace
Trusted by thousands
of companies