Set Sales Targets in Google Sheets: A How-To

This guide shows you how to build a clear, flexible sales target tracker in Google Sheets. You’ll set monthly goals, enter actuals, and see progress automaticallyâ€”no formulas beyond basic arithmetic and a couple of functions. You’ll need a Google account and a sheet with your sales data (or a blank one to start fresh).

What You’ll Need

A Google account (free)
Google Sheets (accessed via sheets.google.com)
Your sales data: at minimum, the periods (months/quarters) and the target amounts for each
Optional: actual sales figures if you want to track progress against targets

Step-by-Step

Open a new Google Sheet. Go to sheets.google.com and click the blank spreadsheet icon (or use a template if you prefer).
Set up your columns. In row 1, create headers: Period (A1), Target (B1), Actual (C1), Variance (D1), and % Achieved (E1). Adjust the labels to match your business (e.g., “Month”, “Quarter”, “Rep Name”).
Enter your periods. In column A, list the time frames you’re trackingâ€”e.g., January, February, March, or Q1, Q2, Q3, Q4. You can also use dates like 01/01/2025.
Input target amounts. In column B, type the sales target for each period. Use plain numbers (e.g., 50000 for $50,000) or format the cells as currency by selecting the range and clicking Format â†’ Number â†’ Currency.
Add actual sales (if you have them). In column C, enter the actual sales figures for each period. If you don’t have them yet, leave blankâ€”the formulas will still work.
Calculate variance. Click on D2 and enter the formula: =C2-B2. This gives the difference between actual and target. A positive number means you exceeded the target; negative means you fell short.
Calculate percentage achieved. Click on E2 and enter: =IF(B2=0, "", C2/B2). This divides actual by target. Format the cell as a percentage (Format â†’ Number â†’ Percent). The IF statement prevents a divide-by-zero error if a target is 0.
Copy formulas down. Select D2 and E2, then drag the fill handle (the small blue square at the bottom-right of the selection) down to cover all your periods. The formulas will auto-adjust for each row.
Add a summary row (optional but useful). Below your last period, add a row labeled “Total”. In the Target column, use =SUM(B2:B13) (adjust the range to match your data). Do the same for Actual with =SUM(C2:C13). For variance, use =SUM(D2:D13) or simply =C14-B14. For % achieved, use =C14/B14.
Visualize with a chart (optional). Select your Period, Target, and Actual columns (A1:C13), then click Insert â†’ Chart. Choose a column or line chart. This gives a quick visual of performance.
Format for clarity. Bold headers, freeze the top row (View â†’ Freeze â†’ 1 row), and use conditional formatting to highlight cells where variance is negative (e.g., red) or positive (green). Select D2:D13, then Format â†’ Conditional formatting â†’ Add rule â†’ Format cells if â†’ Less than â†’ 0 â†’ Choose red fill.

Common Mistakes

Mixing units: Entering targets in thousands (e.g., 50) but actuals in full numbers (50000) will skew everything. Pick one unit and stick to it.
Forgetting to format percentages: If you enter =C2/B2 and leave the cell as a number, you’ll see 0.9 instead of 90%. Always format as percent.
Using wrong cell references: Copying formulas without adjusting ranges (e.g., using B2:B13 in a row 3 formula) will give errors or wrong sums. Double-check after dragging.
Ignoring zero targets: Dividing by zero gives #DIV/0!. Always wrap division in an IF statement as shown above.

Tips

Use data validation for periods: To avoid typos, select the Period column, go to Data â†’ Data validation, and add a list of allowed periods (e.g., Jan, Feb, Mar). This keeps entries consistent.
Add a “Notes” column: Track why a target was missed or exceededâ€”helpful for reviews.
Use named ranges: If your data grows, name the target range (e.g., Targets) via Data â†’ Named ranges. Then formulas like =SUM(Targets) are easier to read.
Set targets as formulas: Instead of hardcoding, you can set targets as a percentage growth over last year. For example, in B2: =F2*1.1 where F2 has last year’s sales.
Share with your team: Use the Share button to give view/edit access. Set permissions to “Viewer” for most, “Editor” for managers.

Troubleshooting

If your formulas aren’t working as expected, here are common fixes:

#DIV/0! error: This means a target is zero. Use the IF formula provided in step 7, or ensure all targets are greater than zero.
#VALUE! error: This usually means a cell contains text instead of a number. Check that all target and actual cells are numeric (no currency symbols or commas). Re-enter them as plain numbers.
#REF! error: This occurs when a formula references a cell that was deleted. Undo (Ctrl+Z) or re-enter the formula with correct references.
Percentages showing as decimals: Select the cells, go to Format â†’ Number â†’ Percent. If that doesn’t work, check that the cell isn’t formatted as plain text.
Formulas not copying correctly: If you drag the fill handle and the results are off, check that you used relative references (no $ signs) for rows that should change per period. For summary totals, use absolute references like $B$2:$B$13 if needed.

Frequently Asked Questions

How do I set targets for different sales reps?

Add a “Rep” column before Period, then use the same structure. You can filter by rep using Data â†’ Create a filter, or use SUMIF to sum targets per rep.

Can I track daily targets?

Yes, simply change the Period column to dates (e.g., 01/01/2025). The formulas work the same. For a month’s worth of daily targets, you’ll have many rowsâ€”use the summary row to see monthly totals.

How do I handle targets that change mid-month?

Keep a “Revised Target” column and use that in your calculations instead of the original. Or overwrite the target cell and note the change in a Notes column.

Can I import targets from another sheet?

Yes, use =IMPORTRANGE(“spreadsheet_url”, “Sheet1!A1:B10”) to pull data from another Google Sheet. You’ll need to allow access the first time.

What if I have no actuals yet?

Leave the Actual column blank. The variance and % achieved cells will show 0 or blank until you enter numbers. You can also set them to show “â€”” with =IF(C2=””,”â€””,C2-B2).

How do I make a dashboard with multiple metrics?

Create separate sheets for each metric (e.g., revenue, units, new customers) and link them with formulas. Use a main dashboard sheet with charts that reference those sheets.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →