In Excel, calculate commission by multiplying the sale amount by the commission rate, then format the rate as a percentage and the payout as currency. For example, if sales are in B2 and the rate is in C2, enter =B2*C2 in D2; a $12,500 sale at 6% returns $750.
What commission format means in Excel
Commission format can refer to two related things: the worksheet layout used to calculate commission, and the number formats that make its inputs and results easy to read. A commission rate is usually a percentage, such as 6%, while the commission earned is a money amount, such as $750. Excel stores both as numbers, so the number format changes how a value appears rather than changing the underlying calculation.
For instance, typing 6% stores 0.06. Multiplying a sale of 12,500 by 0.06 produces 750. Applying Percentage format to the sales figure would only display it as a percentage; it would not turn the figure into a commission rate. Keeping the rate and payout in separate, clearly labeled columns helps prevent that kind of mix-up.
Set up a basic commission worksheet
A simple worksheet can use one row per sale or one row per salesperson for a single commission period. For a sales-by-transaction layout, use headings such as Salesperson, Sale Amount, Commission Rate, and Commission Earned. Put the headings in row 1, then enter records from row 2 onward. For example, place the sale amount in column B, the rate in C, and the calculated payout in D.
- Enter sale amounts as numbers, such as 12500, rather than typing a currency symbol into the cell.
- Enter rates as percentages, such as 6%, rather than 6 if the formula expects a percentage.
- Use a consistent period and definition for sales: gross sales, net sales, collected revenue, or another amount specified by the commission plan.
- Keep manual inputs separate from formula results so a later user can tell what is entered and what Excel calculates.
For an ongoing list, convert the range to an Excel table with Insert > Table, or use Ctrl+T on Windows. Tables provide filterable headers and can extend formulas to newly added rows. On Excel for Mac, the Insert tab also provides the Table command; shortcut behavior can depend on the keyboard and Excel settings. A table is convenient, but it does not decide which sales qualify for commission—that rule still needs to be defined.
Calculate a flat-rate commission
With the sale amount in B2, the rate in C2, and the commission result in D2, enter =B2*C2 in D2. If B2 contains 12500 and C2 contains 6%, the result is 750, meaning $750 when the result is formatted as currency. Copy the formula down for the remaining records. If the formula is copied to D3, Excel adjusts the references to B3 and C3 automatically.
This is the usual method when the same percentage applies to the full amount of each eligible sale. If the rate is fixed for every row, you can also put the rate in one cell, such as F1, and use an absolute reference. With sale amounts in B2:B20, the fixed rate in F1, and the result in C2, enter =B2*$F$1 in C2. For a $12,500 sale and a 6% rate in F1, it returns 750. The dollar signs keep the reference on F1 when you copy the formula down.
Format rates and payouts correctly
In Microsoft Excel for Microsoft 365 on Windows, select the rate cells and choose Home > Number > Percent Style to display them as percentages. For more control, open the Number Format dialog from the Number group and choose Percentage, then set the decimal places. A rate entered as 0.06 will display as 6% with Percentage format. A rate entered as 6 will display as 600%, so check the stored value if the result seems unexpectedly large.
For commission amounts, select the result cells and choose Home > Number > Currency or Accounting Number Format. Currency is useful when the currency symbol should sit beside each number; Accounting aligns symbols and decimal points in a column. Choose the currency that matches the worksheet’s purpose, since the symbol alone may not identify the currency in a multinational business. Excel for Mac has comparable number-format controls on its Home tab, though the ribbon arrangement can differ slightly.
Percentage formatting does not change a formula’s result, and currency formatting does not round the stored calculation. A value may display as $750.00 while retaining more decimal places internally. If the commission agreement requires rounding to cents at the transaction level, make that rule explicit in the calculation rather than relying only on a two-decimal display. Rounding each transaction and rounding only the final total can produce different totals.
Worked example with several sales
Suppose the worksheet has three transactions, all paid at 6%. Put the sale amounts 12500, 8000, and 20000 in B2:B4, and put 6% in C2:C4. With the rate values displayed as percentages and D2:D4 formatted as currency, copy the basic commission formula down the column. The individual results are $750, $480, and $1,200, respectively.
To calculate a period total, with commission results in D2:D4, enter =SUM(D2:D4) in D5. The result is 2430, or $2,430.00 when displayed as currency. A total of the sale amounts is a different measure: it would be $40,500 in this example. Keeping a label such as Total Commission beside the result avoids confusing the sales total with the payout total.
A commission report may also need a salesperson name, transaction date, order or invoice number, and eligibility status. Those details make it easier to reconcile the worksheet to source records and explain why a particular transaction was included. Avoid using a single total without retaining the underlying rows when the commission needs to be checked or reviewed later.
Handle tiered commission plans
A tiered plan needs more care than a flat percentage because tiers can be defined in different ways. In a marginal plan, each rate applies only to the part of sales within its band. In a retroactive plan, reaching a threshold can cause the higher rate to apply to the entire eligible amount. The plan document or agreement determines which interpretation is correct; the word tiered by itself does not settle it.
For a marginal example, assume the first $10,000 earns 5%, the next $15,000 earns 7%, and any amount above $25,000 earns 10%. With total eligible sales in B2, enter =MIN(B2,10000)*5%+MAX(MIN(B2-10000,15000),0)*7%+MAX(B2-25000,0)*10% in C2. If B2 is 30000, the result is $2,050: $500 on the first $10,000, $1,050 on the next $15,000, and $500 on the remaining $5,000.
That formula is an illustrative marginal-tier calculation, not a universal commission rule. A retroactive plan with the same thresholds might pay 10% on all $30,000 once the top threshold is met, which would be $3,000. Before building a tier formula, write down whether thresholds apply per transaction, per month, or per year; whether returns or cancellations reduce eligible sales; and whether reaching a tier changes the rate on earlier sales. Test values just below, exactly at, and just above each threshold.
Common commission spreadsheet problems
- A result is 100 times too large: The rate may have been entered as 6 instead of 6%. Enter 6% or 0.06 for a six-percent rate.
- The formula displays as text: Check whether the cell is formatted as Text or whether an apostrophe precedes the equals sign. Set the cell to General, re-enter the formula, and confirm Excel calculates it.
- The payout is zero or blank: Check that the sale amount is numeric, the rate cell is populated, and the formula points to the intended row. Imported amounts containing text characters may need cleaning.
- Copied formulas use the wrong rate cell: Use an absolute reference such as $F$1 when all rows share one rate, or keep individual rates in the corresponding transaction rows.
- The total differs by a few cents: Determine whether each line should be rounded before summing or whether the unrounded calculations should be summed and rounded at the end.
Also check for duplicate transactions and excluded sales. A correct multiplication formula can still produce an incorrect payout if the input list includes a canceled order, counts the same invoice twice, or uses a sales amount that is not the commission plan’s defined basis. Filters can hide rows without removing them from a normal SUM calculation, so confirm what the total is intended to include.
Make the workbook easier to review
Use descriptive headers, consistent date and currency formats, and a separate assumptions area for rates and thresholds. If rates vary by person, keep a documented rate table and record when it applies. Do not silently overwrite historical rates: a rate change can alter a copied formula or make an old period difficult to reproduce if the original rate is no longer recorded.
Before sharing a report, compare a few calculated rows with independent hand calculations and inspect the edge cases in any tier formula. Keep source data and formulas intact, and identify the reporting period and commission basis on the worksheet. Commission calculations can depend on the employer’s written plan and other applicable requirements; use the relevant authoritative documents or qualified professional for questions about actual eligibility, payment, tax, or legal treatment. Excel can calculate the rules entered into it, but it cannot establish which rules apply.
Frequently Asked Questions
What is the Excel formula for commission?
For a flat rate, multiply the eligible sale amount by the rate. If the amount is in B2 and the rate is in C2, use =B2*C2. Format the rate as a percentage and the result as currency.
How do I calculate 10% commission in Excel?
With the sale amount in B2, enter =B2*10%. For a $5,000 eligible sale, the result is $500. If the rate may change, put it in a separate cell instead of embedding it in every formula.
Should commission be formatted as a percentage or currency?
Format the commission rate as a percentage, such as 6%, and the commission earned as currency, such as $750. They are different values: the percentage is the input rate, and the currency amount is the calculated payout.
How do I calculate commission above a sales target?
First confirm whether the plan pays on all sales after a target or only on the portion above it. Those are different rules. A tier formula should reflect the written plan and be tested at the target and on both sides of it before use.
Why does my commission formula show 600%?
The rate may contain the number 6, which Percentage format displays as 600%. For six percent, enter 6% or 0.06. Formatting changes how a value looks, but does not divide the stored value by 100.
Related
- Can You Password Protect an Excel Spreadsheet?
- Can You Print an Excel Sheet? Settings, Pages, and Tips
- Can You Password Protect an Excel File? How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Oracle SQL String Functions: Syntax, Uses, and ExamplesOracle SQL string functions operate on character values to search, extract, combine, change, and format text. Common choices…
- Excel Sort Examples: Rows, Text, Dates, and Custom ListsTo sort a worksheet in Excel for Microsoft 365 on Windows, select a cell in the data, then…
- Excel Data Validation List Formula: Examples and FixesFor an Excel drop-down list, select the cells, choose Data > Data Validation, set Allow to List, and…
- CLEAN in Excel: Formula, Examples, and Common LimitsIn Excel, use =CLEAN(A2) to remove nonprinting ASCII control characters from the text in cell A2. CLEAN can…
- Java String Handling Functions: Methods and ExamplesJava string handling is mainly done with methods on String; use StringBuilder for repeated changes, regular-expression classes for…
- Excel Formula for Date Minus Date: Days BetweenThe core Excel formula for subtracting one date from another is simply =B2-A2, where B2 holds the later…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.