Table of Contents
To calculate commissions in Excel, you can use the formula function to create a formula that calculates the commission based on the sales amount and commission rate. Simply input the sales amount and commission rate into the formula and it will automatically calculate the commission amount. You can also use the SUM function to add together multiple commissions for a total commission amount. Additionally, you can use formatting and conditional formatting to make the calculation visually appealing and easily track commissions over time. By utilizing the features of Excel, you can efficiently and accurately calculate commissions for any sales-related job or business.
Calculate Commissions in Excel (With Example)
Often you may want to calculate commissions for an employee based on their sales numbers.
For example, you might want to use the following table in Excel to determine the commission to pay an employee based on their total sales for the year:
Here is how to interpret this table:
- If an employee has sales between $0 < $5,000, they receive 0% commission.
- If an employee has sales between $5,000 < $10,000, they receive 2% commission.
- If an employee has sales between $10,000 < $20,000, they receive 5% commission.
- If an employee has sales between $20,000 < $40,000, they receive 8% commission.
- If an employee has sales > $40,000, they receive 12% commission.
The following example shows how to use a VLOOKUP function in Excel to determine the sales commission to pay an employee based on their total sales.
Example: How to Calculate Commissions in Excel
Suppose an employee has a total of $11,000 in sales for a given year.
We can type this sales amount into cell E1 and then type the following formula into cell E2 to determine their commission rate:
=VLOOKUP(E1, $A$2:$B$6, 2, TRUE)
The following screenshot shows how to use this formula in practice:
The formula returns a commission rate of 5% since the sales value of $11,000 fell between the range $10,000 and $20,000.
Suppose we change the sales value to $27,000:
The formula now returns a commission rate of 8% since the sales value of $27,000 fell between the range $20,000 and $40,000.
How This Formula Works
Note that the VLOOKUP function uses the following basic syntax:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
where:
- lookup_value: The value to look up
- table_array: The range of cells to look in
- col_index_num: The column number that contains the return value
- range_lookup: TRUE = look for approximate match, FALSE = look for exact match
By using a value of TRUE for the last argument, you can look up a value that falls in a range.
For example, when we looked up the value $11,000 in the commission table this exact value didn’t exist in the Sales column so the VLOOKUP function looked for the next largest value in the Sales column that was less than $11,000.
The formula identified the value of $10,000 and returned the commission rate that corresponded to this value, which was 5%.
Note that the values in the Sales column are already sorted from least to greatest, which is why the VLOOKUP function was able to correctly identify the next largest value that was less than the lookup value.
If the first column in your lookup range is not sorted, the VLOOKUP function could return unexpected results.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: