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

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

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

Once the Coefficient sidebar launches, choose Import from…

Then select Salesforce as your data source:

Choose From Objects & Fields.

Under New Import, choose the Opportunity object.

Over 100K pros building reports use Coefficient to automate business systems data into their Google Sheets

Try Now

"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

Click ‘Next’. Then choose Select fields… 

Add the “Full Name” field.

Add the “Amount” field.

Finally, add the “Close Date” field.

Then click “Done Selecting Fields”.

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

Select the “Opportunity Stage” filter.

Under the Picklist section, choose is one of.

Then choose the Closed Won stage.

Make sure to toggle on Pivot Mode.

Now drag Full Name to Rows.

Drag Close Date to Columns.

Lastly, drag Amount to Values.

Under Columns, choose Group by: Quarter.

Under Amount, choose count: sum.

Now name your import. Then click ‘Import’.

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.

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.

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.

Enable the Refresh Schedule.

Configure the Refresh Schedule: Daily at 7am.

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.

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.