Cellwise Start free

Excel guide · 4 min read

How to calculate the book-to-bill ratio in Excel

Quick answerBook-to-bill is orders booked divided by revenue billed for the same period. With monthly totals from SUMIFS, it's =I2/J2. Above 1 means you took in more new orders than you invoiced, so the backlog grew; below 1 means it shrank. Lumpy orders make single months jump around, so also track a trailing 3-month ratio: =SUM(I2:I4)/SUM(J2:J4).

Sample data

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

RowABCDEFGHIJKL
1Order dateCustomerBookedInvoice dateBilledMonthBookingsBillingsBook-to-bill3-month B2B
2Jul 3, 2026Arden42000Jul 10, 202638000Jul 1, 202660500590001.0254237288
3Jul 21, 2026Belmont18500Jul 30, 202621000Aug 1, 2026107000565001.8938053097
4Aug 8, 2026Corvex95000Aug 14, 202626500Sep 1, 202652000790000.65822784811.1285347044
5Aug 26, 2026Arden12000Aug 29, 202630000Oct 1, 2026114500625001.8321.3813131313
6Sep 4, 2026Delta30000Sep 12, 202644000
7Sep 19, 2026Belmont22000Sep 27, 202635000
8Oct 2, 2026Corvex64000Oct 9, 202629000
9Oct 15, 2026Evers9500Oct 24, 202633500
10Oct 28, 2026Arden41000

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

Monthly bookings and billings with SUMIFS

Keep two plain logs: orders booked (date, customer, value) in A:C and invoices (date, amount) in E:F. Don't type monthly totals by hand; build them so they update when someone adds a late order.

In H2:H5, enter the first day of each month, for example =DATE(2026,7,1). Bookings for the month in I2: =SUMIFS($C$2:$C$10,$A$2:$A$10,">="&H2,$A$2:$A$10,"<"&EDATE(H2,1)). Billings in J2 is the same pattern on the invoice log. Using "less than the 1st of next month" instead of "up to the last day" also catches invoices stamped with a time on the 31st, which a "<="&EOMONTH(H2,0) test silently drops.

=I3

Result: 107000

107000: August bookings, two orders from Corvex and Arden.

The ratio, and the month with no invoices

Book-to-bill in K2 is =IF(J2=0,"",I2/J2). The IF matters in a new business line or a shutdown month: with no billings, a plain division gives #DIV/0!, and that error spreads into every chart and average that touches the column. Format the column as 0.00, since the ratio is usually read as "1.89" rather than as a percentage.

July is close to even at 1.03. August is 1.89, September drops to 0.66 and October is back up at 1.83. Three months above 1 out of four means the order book is growing faster than the team is delivering.

=K3

Result: 1.8938053097

1.8938...: in August the company booked about 1.89 of new orders for every 1 it billed.

A trailing 3-month ratio that smooths lumpy orders

One big contract can swing a month from 0.7 to 1.9 and back. A trailing ratio sums the last three months of each before dividing. In L4 (the first month with three months of history), type =SUM(I2:I4)/SUM(J2:J4) and fill down; the range slides one month each row.

Sum, then divide. Averaging the three monthly ratios gives a different, wrong answer, because a small month counts as much as a big one. For September the trailing ratio is 1.13; by October it's 1.38.

=L5

Result: 1.3813131313

1.3813...: August to October, 273,500 booked against 198,000 billed.

Roll the backlog forward

Backlog is the value of orders booked but not yet billed. Each month: closing backlog = opening backlog + bookings - billings. Type the opening backlog for the first month as a number (150,000 in M2), then N2 is =M2+I2-J2 and M3 is =N2, so each month opens where the last one closed. Fill both down.

This is the check that makes book-to-bill meaningful: every month with a ratio above 1 adds to the backlog, every month below 1 takes from it. Over the four months, 334,000 was booked and 257,000 billed, so the backlog grew by 77,000. If your closing backlog doesn't match the open-orders report from your system, look for cancelled orders, which need their own column subtracted in the roll-forward.

=N5

Result: 227000

227000: the backlog at the end of October, up from 150,000 at the start of July.

How many months of work is in the backlog?

Divide the closing backlog by average monthly billings to see how long it would take to deliver at the current pace: =N5/AVERAGE(J3:J5). Here it's about 3.4 months. A rising ratio with a backlog that grows faster than capacity is the early sign that lead times are about to stretch.

Definitions differ between companies and industries: some count only signed orders, some exclude service renewals, and "billed" may mean invoiced or recognized revenue. Use whatever your finance team uses and write it in the sheet's header so the ratio means the same thing every month.

=N5/AVERAGE(J3:J5)

Result: 3.4393939394

3.4393...: about 3.4 months of billings sitting in the backlog.

Common mistakes

Averaging monthly ratios for a quarter

Sum bookings and billings for the quarter first, then divide once.

Testing dates with "<="&EOMONTH(...)

Use "<"&EDATE(first_of_month,1) so timestamps on the last day are counted.

Letting #DIV/0! spread from a month with no billings

Wrap the ratio in IF(J2=0,"",I2/J2).

A backlog that drifts from the system's open orders

Subtract cancellations in the roll-forward, and restart from the system figure each year.

FAQ

How do you calculate the book-to-bill ratio in Excel?

Total the orders booked and the amounts billed for the same period with SUMIFS, then divide bookings by billings: =I2/J2.

What does a book-to-bill ratio above 1 mean?

You booked more new orders than you billed in the period, so the backlog of unbilled orders grew. Below 1, the backlog shrank.

How do I calculate a trailing 3-month book-to-bill?

Sum the last three months of bookings and divide by the sum of the same three months of billings, like =SUM(I2:I4)/SUM(J2:J4), then fill down.

How do I calculate backlog in Excel?

Closing backlog = opening backlog + bookings - billings. Link each month's opening backlog to the previous month's closing figure.

Functions used

Related guides

Updated