How to Create a Sales Commission Calculator

Last Modified: November 7, 2023 - 6 min read

Hannah Recker

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

This example below focuses on the tiered commission structure. But it’s worth noting that Coefficeint offers a range of pre-built (and free) templates and dashboards for different types of sales commission structures like:

  • Base Pay + Commission Calculator
  • Territory Volume Commission Calculator
  • Revenue 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.

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.

Coefficient Excel Google Sheets Connectors
314,000 Pros Sync Live Data from Their Business Systems into Spreadsheet

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

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.

Let’s continue on to discuss other examples of sales commission calculators.


Base Pay + Commission Calculator

The base pay plus commission structure combines a fixed salary with commission based on sales performance.

The base pay plus commission structure combines a fixed salary with commission based on sales performance.

It offers salespeople financial stability with the base pay and incentivizes higher sales with commission.

How to calculate:

Base Pay + (Total Sales * Commission Rate)


A salesperson makes a base salary of $3,000 a month and has a 5% commission rate. If they sold $20,000 in a given month, their commission for the month would be $1,000. 

Territory Volume Commission Calculator

salespeople earn commissions based on total sales within their assigned territory.

In this model, salespeople earn commissions based on total sales within their assigned territory.

Companies can use this structure to maximize sales in a specific region by creating healthy competition among territories. 

How to calculate:

Total Sales in Territory * Commission Rate


A salesperson earns a 5% commission on all sales made within their designated territory. If the total sales in their territory for a month amount to $100,000, their commission would be $5,000 (5% of $100,000).

Revenue Commission Calculator

Common in B2B sales, revenue-based commission awards salespeople a percentage of the revenue from their sales.

Common in B2B sales, revenue-based commission awards salespeople a percentage of the revenue from their sales. It’s a direct share of the company’s earnings from a sale. 

How to calculate:

Total Sales * Commission Rate


Imagine a salesperson earns a 5% commission on the total revenue generated from their sales. If they make $50,000 in sales, their commission would be $2,500 (5% of $50,000).

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. Love this resource? Want more? Coefficient offers libraries of advanced reporting dashboards and simple spreadsheet templates for you to sift through as well.

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

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies