Introduction
In the world of sales, accurately calculating and tracking commissions is crucial for both the success of your sales team and the overall growth of your business. A well-designed commission system not only motivates your sales representatives but also ensures fairness and transparency in rewarding their efforts. While there are several other tools available for commission management, Microsoft Excel stands out as a versatile and accessible option that provides a wide range of functionalities to streamline and automate the process.
In this blog post, I’ll walk you through the process of calculating and tracking commissions for your sales team using Microsoft Excel based on my experience helping hundreds of other businesses. Whether you’re a small business owner, a sales manager, or an aspiring entrepreneur, understanding how to leverage Excel for commission management can save you time and money while greasing the wheels of your sales machine.
Commission Tracking Components
Before you go to town building your own rock star commission spreadsheet, take a few minutes to do some preliminary planning. It will pay dividends, I promise. This section is designed to help you understand the essential components of your commission tracking system before you get started in Excel.
Below are some questions you’ll want to get clarity around as you proceed. If you don’t have answers to all of these just yet, don’t worry— the beauty of Excel is its flexibility. Even so, the more clarity you have at the outset, the better.
- What is the ideal commission structure that you want to use for your sales team?
- What are all of the rules that make up that commission structure? Think exceptions, thresholds, special conditions based on salesperson position, etc.
- Where are you currently recording or processing sales transactions? Will these be manually entered in the spreadsheet, or automatically imported?
- Will more than one person need edit-access to the spreadsheet (e.g., to input data)?
- Do you allow refunds and if so, how do these impact commission calculations?
Create Commission Spreadsheet
This article assumes you have some how-to Excel knowledge and are familiar with basic terminology and formulas. If that is not the case for you, you may want to reach out to us for customized help instead.
Commission Structure Tab
Open a new workbook in Microsoft Excel and be sure to go ahead and do a Save As to save the file on your computer or network. Then create a Commission Structure tab. This tab should contain any table(s) that are necessary to define the rules of your incentive structure for sales commissions and bonuses. I recommend keeping the information in a single table if possible.
The commission incentive structure you select will largely determine what columns are needed. Below are some common ones I have seen working with clients who use a variety of different incentive structures.
Employee Name – Unique First & Last Name.
Position – If different commission rules apply for different positions, this can provide a clean way to keep track of that.
Base Pay – Amount salesperson gets independent of sales activity (if they are not straight commission)
Commission Rate – Standard commission rate to be applied to sales totals
Override Rate – Percentage of sales payable to a Head of Sales or Sales Manager
Volume Bonus Tier – Threshold where an additional bonus kicks in.
Volume Bonus Basis – What is the basis for this bonus. For example: cash collected; sales closed; contracts executed
Volume Bonus Amount – This can be either a fixed value (e.g., $1,000) or an additional percentage above and beyond the standard commission rate which gets applied to sales beyond the bonus tier.
PIF Bonus Tier – If you want to reward salespeople for deals on which payment is made in full upfront, enter the amount of deals that need to be closed with ‘paid in full’ (PIF) status in order for this bonus to kick in.
PIF Bonus Amount – This can be either a fixed value (e.g., $1,000) or an additional percentage above and beyond the standard commission rate which gets applied to sales beyond the bonus tier.
Tip: Once you have your columns created, enter one row of example data in the table (doesn’t need to be real, you can overwrite it later), then format the range as a table. To do this, highlight the range of the table then go to the Home ribbon in Excel and select Format as Table and pick your color scheme.
Sales Data Tab
Next you will need a tab to record sales data. One approach to consider is temporarily housing new sales data that will be used for the current payroll cycle, then once that commission is processed, this data can be archived to another tab which contains historical sales data.
Another approach is to have a single tab which will contain all sales data in perpetuity. Lastly, if you don’t need or want historical data, you can have a single tab and clear out all sales data after every payroll cycle. Regardless of the approach you take, here is how to set up this critical tab in the spreadsheet.
Again, you will need to create the columns that make the most sense for your unique needs, but below you will find some of the common columns I see and how they are used to help you get started.
Payment Date – Date payment was received.
Customer ID – If you assign unique identifiers to your customers, it can be recorded here.
Customer Email – Email address of customer (if applicable).
Customer Name – Customer first and last name.
Transaction Amount – Amount of the transaction (actual amount paid, which may differ from total sale).
Total Sale Amount – Amount of the deal/sale that was closed, which this payment applies to.
Description – Description of the product or service that was sold.
Payment Source – Stripe, PayPal, Check, etc.
Payment # – If you allow multiple payments, this is where you record whether this is the 1st, 2nd, 3rd, etc. payment being made.
Payment # / Total – If this customer was set up on 3 payments and this is the 2nd of those, this column would display 2 / 3.
Appt Setter Name – The name of the appointment setter associated with this deal/sale (if applicable).
Salesperson Name – The name of the person to be credited with the sale and primary sale commission.
Appt Setter Commission – This will be a formula that calculates the base commission amount generated by this sale for the appointment setter.
Salesperson Commission – This will be a formula that calculates the base commission amount generated by this sale for the salesperson.
Override Commission – This will be a formula that calculates the override commission that will go to the head of sales/ sales manager for this transaction.
We’ll work out the formulas for the table above later. For now the goal is to get the structure of your sales commission spreadsheet built out in Excel.
Tip: Once you have your columns created, enter one row of example data in the table (doesn’t need to be real, you can overwrite it later), then format the range as a table. To do this, highlight the range of the table then go to the Home ribbon in Excel and select Format as Table and pick your color scheme.
Commission Calculation (Payroll Report) Tab
Now it’s time to set up the Commission Calculation sheet (aka payroll report tab). This is where you will be able to select a member from your sales team and see a rollup report, as well as a detailed breakdown of where their commissions are coming from. This provides you with the bottom-line summary information you need for Payroll, as well as details for improved transparency.
Create a new tab and rename it Payroll Report (or whatever makes sense to you). How you lay this sheet out will depend on how you plan to use it and what information is important to you and your team. Below is an example calculator report which shows some common information I like to include.
The main sections are:
Employee Selection Cell – this is where you select the specific employee you want to view or process commission payroll for. You can also set it up with an “all” option to see the entire team’s performance.
Date Range Cells – this is where you enter the from and to dates for the current payroll cycle.
Summary of Commission Payment – this section will contain the different amounts that make up the employee’s total payment amount. For simple single-rate, straight-commission employees, this would just be one cell. But often it includes volume bonuses, base pay, standard commission and/or override commission.
Details Table – this section includes a list of all sales that were attributed to the salesperson for the current payroll cycle. I like this because it adds a level of transparency and the opportunity to cross-check to make sure nothing was missed.
See the screenshot below for an example of what this might look like, but feel free to be creative and design a layout that makes sense to you.
Add Excel Formulas
With the structure of the workbook mostly built-out, you can now begin adding in the formulas and features that will make the sheet functional. Since the Commission Structure tab is just a static table(s), we will skip that one and begin with the Sales Data tab.
SALES DATA TAB
While more advanced automation features can be implemented, to avoid getting too complex at this stage, we are going to allow for manual entry in all but 4 columns of this tab.
All of these formulas will use an INDEX / MATCH nested formula approach. However, if you have Microsoft 365 Excel, you will also have access to XLOOKUP which can do the same thing. I do not recommend using VLOOKUP as it may cause issues with the sheet down the road as you make further adjustments.
Appt Setter Commission
This will be a formula that calculates the base commission amount generated by this sale for the appointment setter. Enter this formula on the first row of the Sales data table, in the Appt Setter Commission column. In my example, this is row 2 of the spreadsheet.
#1 – the column in the Commission Structure table that lists the % commission rate for each employee.
#2 – the cell containing the Appointment setter’s name on the Sales Data tab.
#3 – the column in the Commission Structure table that lists the employee names.
#4 – the cell containing the Transaction Amount on the Sales Data tab.
Salesperson Commission
This will be a formula that calculates the base commission amount generated by this sale for the salesperson. Enter this formula on the first row of the Sales data table, in the Appt Setter Commission column. In my example, this is row 2 of the spreadsheet.
#1 – the column in the Commission Structure table that lists the % commission rate for each employee.
#2 – the cell containing the Salesperson’s name on the Sales Data tab.
#3 – the column in the Commission Structure table that lists the employee names.
#4 – the cell containing the Transaction Amount on the Sales Data tab.
Override Commission
This will be a formula that calculates the override commission that will go to the head of sales/ sales manager for this transaction.
#1 – the column in the Commission Structure table that lists the % override rate that each employee generates for the sales manager/head of sales.
#2 – the cell containing the Salesperson’s name on the Sales Data tab.
#3 – the column in the Commission Structure table that lists the employee names.
#4 – the cell containing the Transaction Amount on the Sales Data tab.
PAYROLL REPORT TAB
Now for the really fun part. This tab will be highly-customized to your business and preferences which will dictate the layout and what to include on it.
Step # 1 – Set up the Staff Member drop-down menu.
1. On the Commission Structure tab, select the data (not the header) in the Employee Name column of your table.
2. On the Formulas ribbon, click “Define Name”
3. In the pop-up window, in the Name field, enter “EmployeeName” without any spaces and click “OK.”
4. On the Payroll Report tab, select the cell where you plan to enter the Staff Member’s name to view their payroll report. Then go to the Data ribbon and click “Data Validation.”
5. From the “Allow” drop-down menu, select “List.” Then in the Source field, enter =EmployeeName and click OK. The selected cell should now contain a drop-down menu which will show any employee names you have entered in the Commission Structure table.
Step # 2 – Set up the date range cells.
1. Create two cells where you will enter the start and end date for the payroll report.
2. Enter example dates in each cell which will prompt Excel to format these cells as dates.
Step #3 – Create formulas to summarize payroll information such as payments collected, and commissions earned. Below are some examples that will get you headed in the right direction.
We’ll be using the SUMIFS formula a lot in this section. If you aren’t familiar with how it works, check out my quick tutorial video here, which will teach you everything you need to know about what SUMIFS is and how it works.
Cash Collected Formula: the amount of cash the company collected through this person’s sales or appointment setting for the selected date range.
Use a formula like the following by entering it in the cell where you want the Cash Collected amount to be displayed.
=SUMIFS(Table2[Transaction Amount],Table2[Salesperson],C3,Table2[Payment Date],”>=”&C5,Table2[Payment Date],”<=”&E5)+SUMIFS(Table2[Transaction Amount],Table2[Appt Setter],C3,Table2[Payment Date],”>=”&C5,Table2[Payment Date],”<=”&E5)
Formula Key:
Table2 – Sales Data table
C3 – Replace this with the cell where you select the staff member
C5 – Replace this with the cell where you enter the start date for the payroll cycle.
E5 – Replace this with the cell where you enter the end date for the payroll cycle.
Also note that if you don’t use appointment setters, you won’t need to include the “+” symbol or anything after it in this formula.
Standard Commission Formula – the amount of commission the employee earned based on their standard percent-of-sales commission rate.
=SUMIFS(Table2[Salesperson Commission],Table2[Salesperson],C3,Table2[Payment Date],”>=”&C5,Table2[Payment Date],”<=”&E5)
The formula key from the previous section is exactly the same for this one!
Note: If you are using appointment setters you should duplicate this formula for appointment setters and change the two salesperson fields in the formula to the corresponding appointment setter fields. See the Cash Collected formula above to follow as an example.
Volume Bonus Formula – the fixed bonus amount for crossing the volume bonus threshold on cash received.
=IF(D9>INDEX(Table1[Volume Bonus Tier],MATCH(C3,Table1[Employee Name],0)),INDEX(Table1[Volume Bonus Amount],MATCH(C3,Table1[Employee Name],0)),0)
Formula Key:
C3 – Replace this with the cell where you select the staff member
D9 – Replace this with the cell where you entered the total cash collected formula
Table1 = Commission Structure table
Paid in Full Bonus Formula – the fixed bonus amount for crossing the PIF threshold on cash received for deals paid in full upfront.
=IF(SUMIFS(Table2[Transaction Amount],Table2[Payment ‘# / Total],”1 / 1″,Table2[Salesperson],C3)>INDEX(Table1[PIF Bonus Tier],MATCH(C3,Table1[Employee Name],0)),INDEX(Table1[PIF Bonus Amount],MATCH(C3,Table1[Employee Name],0)),0)
For this formula, the Formula Keys provided above will apply. The only additional thing to note is that the “1 / 1” must match the designation used on the Sales Data tab for payments that are made in full upfront. In this case “1 / 1” means it is payment 1 of 1 which indicates there are no future payment and therefore this is a PIF transaction.
Step #4 – The last step here would be to add a variety of formulas to automatically pull in a list of transactions on which the employee earned commission this pay cycle. These formulas will not be included in this blog post as their complexity would make effective communication via a blog article prohibitive.
Upgrade Your Spreadsheet
The goal of this article is to help you get a relatively simple commission calculator and commission tracker in place for your business using Excel. While this is a good starting point, there are quite a few other features and improvements I would recommend.
Implementing these will make your system more bullet proof, will save you even more time on data entry and will give you access to additional insights and capabilities.
Just a few examples would include:
- Automating a customized PDF commission report that gets emailed to each person on your sales team.
- Incorporating the ability to handle chargebacks/refunds.
- Automatic archiving of old sales data.
- Adding a visualization dashboard with graphs and charts to bring your sales and commission data to life and help you understand positive and negative trends that are important for your business.
If you get stuck building out your commission system, need help taking it to the next level, or would prefer to wash your hands of it and have someone else build out a fully customized system that does exactly what you need, you can book a free consultation with someone from our team today.
Concluding Thoughts
Excel can be the perfect tool for streamlining and automating your commission management system. When it comes to getting your hands on a fully-customized solution with powerful automation capabilities without breaking the bank, Excel is hard to beat.
One of the benefits of using Excel for your commission is that you don’t have to start out with a full solution with all the bells and whistles. Especially if you have a small business and a sales team with a relatively simple commission structure, you can start simple and build on it from there.
