Use Google Sheets AI to Build a Commission Tracker

What This Does

Google Sheets can suggest formulas and structure a commission tracking spreadsheet for you — so instead of manually building totals and calculations, you describe what you want and Sheets handles the formula logic. This helps you catch missed commission payments across multiple carriers.

Before You Start

• You have a Google account (gmail.com or Google Workspace)
• Open Google Sheets (sheets.google.com)
• Have a list of your recent cases: carrier name, product, premium amount, expected commission rate, and payment received date

Steps

1. Open a blank Google Sheet and set up your columns

Create a new spreadsheet. In Row 1, type these column headers across columns A through G:

• A: Carrier
• B: Client Name
• C: Product
• D: Premium ($)
• E: Commission Rate (%)
• F: Expected Commission ($)
• G: Received Date
• H: Amount Received ($)
• I: Outstanding ($)

What you should see: A clean header row with your column labels.

2. Use the AI formula suggestion for Expected Commission

Click on cell F2 (under "Expected Commission"). Type: = and then click on cell D2. Then type * and click E2. Press Enter.

What you should see: =D2*E2 — this calculates expected commission as Premium × Rate.

Now click F2, grab the small blue square in the bottom-right corner, and drag it down to fill the formula for all your rows.

3. Calculate Outstanding Commission

Click on cell I2. Type the formula: =F2-H2 and press Enter. Drag down to fill all rows.

What you should see: Each row now shows the difference between what you expected and what you received. Positive numbers = still owed to you.

4. Add a total row

Click on the row below your last entry. In column F, type =SUM(F2:F50) (adjust the range for your number of rows). Do the same for H and I columns.

Troubleshooting: If cells show #VALUE! errors, check that your commission rate column (E) contains numbers only, not text like "7%" — it should be 0.07 or just 7.

5. Ask Google Sheets AI to improve your layout

Click Extensions in the menu bar → Sheets AI (if you have Google Workspace) or use the Explore button (bottom-right lightning bolt icon) in any version.

In the Explore panel, type: "Show me a summary of total expected commissions, total received, and total outstanding." Sheets will generate a suggested pivot or summary.

What you should see: A summary panel or chart showing your commission totals at a glance.

Real Example

Scenario: You've closed 6 cases this month across 4 carriers. You're not sure if all commission payments came through.

What you type: In the Explore box: "Which rows in column I have a value greater than zero?"

What you get: Sheets highlights the rows where you're still owed money — so you can follow up with those specific carriers.

Tips

• Keep commission rates as decimals (0.065 for 6.5%) or the formula will give wrong results
• Add a "Notes" column for tracking when you called the carrier about a late payment
• Sort column I (Outstanding) descending monthly to see your biggest unpaid amounts first

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.