Cellwise Try it free

Excel guide · 3 min read

Calculate percent of goal in Excel

Quick answerDivide the actual by the goal: =B2/C2, then format the cell as a percentage (Ctrl+Shift+%). 46,800 against a 60,000 goal shows as 78%. To stop at 100% for a progress bar, use =MIN(1,B2/C2).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1RepSales to dateQuarter goalQuarter startQuarter endAs of
2Amara4680060000Oct 1, 2026Dec 31, 2026Nov 20, 2026
3Diego6350055000
4Hana2190050000
5Liam3825045000
6Noor (new)120000
7Team182450210000

Download this sheet as an Excel practice file (.xlsx)

Percent of goal: actual divided by goal

The result is a fraction, 0.78, and the percent format displays it as 78%. Do not multiply by 100 and then format as a percentage, or you get 7800%.

Fill the formula down for each rep. Point at the goal cell on each row, or lock it with $ if every row shares one goal, such as =B2/$H$1 when the goal sits in H1.

=B2/C2

Result: 0.78 shows as 78% with Percent format

Amara has reached 78% of her quarter goal.

Cap it at 100%

Diego is past his goal at 115%. That is useful in a table, but a progress bar or a bonus calculation that pays up to 100% needs a cap. MIN returns the smaller of 1 and the ratio, so nothing goes above 100%.

Keep the uncapped figure somewhere too. Managers usually want to see who overshot.

=MIN(1,B3/C3)

Result: 1 shows as 100% with Percent format

100% for Diego, whose real figure is 115%.

How much is left to reach the goal

Goal minus actual gives the amount still needed, and MAX(0, ...) stops it going negative for anyone already past the goal. Divide by the days left to get the daily pace needed.

With the quarter ending December 31 and the report dated November 20, there are 41 days left, so Amara needs about 322 a day. =ROUND((C2-B2)/NETWORKDAYS(G2+1,F2),0) gives the figure per working day instead: 455 over 29 weekdays.

=MAX(0,C2-B2)

Result: 13200

13,200 still needed by Amara. Diego's version returns 0, not -8,500.

Are they on track? Compare with time elapsed

78% sounds good, but it depends on how far into the quarter you are. Compare percent of goal with percent of time gone: (as-of date - start + 1) / (end - start + 1). On November 20, 51 of the quarter's 92 days have passed, which is 55%.

Hana is at 44% of her goal with 55% of the time gone, so she is behind pace. At her current rate she finishes near 39,506, which you can project with =ROUND(B4/(G2-E2+1)*(F2-E2+1),0).

=IFS(B4/C4>=1,"Goal met",B4/C4>=(G2-E2+1)/(F2-E2+1),"On track",TRUE,"Behind")

Result: Behind

Behind: 44% of goal against 55% of the quarter elapsed.

Zero or missing goals

Noor is new and has no goal yet, so B6/C6 returns #DIV/0!. Test the goal first and show a note instead. This is better than IFERROR, which would also hide a typo in the formula.

=IF(C6=0,"No goal set",B6/C6)

Result: No goal set

No goal set, instead of #DIV/0!.

Team percent of goal: divide the totals

For the team figure, divide total sales by total goal, as in row 7: 182,450 / 210,000 is 87%. Averaging each rep's percentage gives a different number, because a small goal counts as much as a large one.

For the four reps with goals, =SUM(B2:B5)/SUM(C2:C5) gives 81.2%, while averaging their four percentages gives 80.6%. Use the totals unless you deliberately want each person to count equally.

=B7/C7

Result: 0.86880952381 shows as 86.9% with Percent format

86.9% of the team goal, including Noor's 12,000 in sales.

Common mistakes

Getting 7800% instead of 78%

Do not multiply by 100 before applying the percent format. =B2/C2 formatted as % is enough.

#DIV/0! for a missing goal

Use =IF(C2=0,"No goal set",B2/C2) so blank goals do not break the sheet.

Averaging percentages for a team total

Divide total actual by total goal instead, so bigger goals carry their real weight.

Calling 60% "on track" without context

Compare with the share of the period that has passed. 60% on day 80 of 90 is behind.

FAQ

What is the formula for percent of goal in Excel?

=actual/goal, formatted as a percentage. For example =B2/C2 with 46,800 and 60,000 shows 78%.

How do I cap a percentage at 100% in Excel?

Wrap it in MIN: =MIN(1,B2/C2). Values over the goal show as 100%.

How do I make a progress bar for percent of goal?

Select the percent cells and choose Home > Conditional Formatting > Data Bars. In Manage Rules, edit the rule and set Minimum to Number 0 and Maximum to Number 1, so a full bar means 100%.

How do I calculate the remaining amount to reach a goal?

=MAX(0,goal-actual). The MAX stops it from showing a negative number once the goal is passed.

Functions used

Related guides

Updated