Track Your Load Rates by Lane and Broker in Google Sheets

What This Does

A Google Sheet rate tracker gives you a history of every load — broker, lane, rate per mile, and total pay — so you can see which brokers actually pay well, which lanes are most profitable, and whether your rates are trending up or down. Google Sheets can auto-generate charts and summary tables so the analysis happens automatically as you add data.

Before You Start

• You have a free Google account (Gmail)
• Google Sheets is available on your phone or computer
• Time needed: 15 minutes initial setup; 2 minutes per load to enter data
• Cost: Free

Steps

1. Create Your Rate Tracker Sheet

Open a new Google Sheet and name it "Load Rate Tracker 2025." Create these column headers in row 1:

• A: Date
• B: Broker Name
• C: Origin (City, State)
• D: Destination (City, State)
• E: Miles
• F: Total Rate ($)
• G: Rate/Mile
• H: Load Type (Dry Van / Reefer / Flatbed)
• I: Notes

2. Set Up the Rate/Mile Formula

In cell G2, enter this formula: =F2/E2 This automatically calculates your rate per mile for each row. Copy this formula down for all future rows by selecting G2, then dragging the fill handle (bottom right corner) down the column.

What you should see: The rate per mile auto-calculates whenever you enter a total rate and mileage.

3. Add a Summary Section

Below your data (say, starting in row 100), create a summary table. Ask Sheets AI (or paste into ChatGPT) to write formulas for:

• Average rate per mile by broker: =AVERAGEIF(B:B,"[Broker Name]",G:G)
• Total revenue by broker
• Average rate per mile by origin state

Tip: Type your question directly in a free chatbot: "Write a Google Sheets formula to calculate the average rate per mile from column G for all rows where column B equals 'Coyote Logistics'."

4. Create a Chart

Select your data and click Insert → Chart. Choose a "Bar Chart" and set:

• X-axis: Broker Name
• Y-axis: Rate/Mile

Google Sheets generates a visual chart showing which brokers pay you the most per mile.

What you should see: A bar chart where you can immediately see which brokers are above and below your average.

5. Use the Data to Make Better Decisions

After 20-30 loads of data, review your chart. You may discover:

• One broker consistently pays 15% below your average
• A particular lane (e.g., TX to IL) always gets strong rates
• Rates from the same broker vary widely — time to negotiate a contract lane

Real Example

Scenario: After 3 months of tracking, you discover that Broker A averages $2.45/mile while Broker B averages $1.95/mile on similar lanes. You've been treating both brokers equally.

What you do: Prioritize Broker A for new loads; tell Broker B your minimum rate is $2.20 or you'll pass.

Impact: Even a $0.25/mile increase on 8,000 miles/month = $2,000/month additional revenue.

Tips

• Update this sheet after every delivery — takes 90 seconds while you're waiting at the dock
• Share it with your accountant at year-end for clean gross revenue reporting
• After 6 months, you'll have the data to confidently tell any broker "I don't haul that lane for under $X"
• Add a "Would Work Again" column (Yes/No) to track broker relationship quality beyond just rates

Tool interfaces change — if the chart or formula AI assistant has moved, you can get the same formulas by asking ChatGPT to write them for you.