Excel guide · 4 min read
How to calculate the book-to-bill ratio in Excel
=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.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order date | Customer | Booked | Invoice date | Billed | Month | Bookings | Billings | Book-to-bill | 3-month B2B | ||
| 2 | Jul 3, 2026 | Arden | 42000 | Jul 10, 2026 | 38000 | Jul 1, 2026 | 60500 | 59000 | 1.0254237288 | |||
| 3 | Jul 21, 2026 | Belmont | 18500 | Jul 30, 2026 | 21000 | Aug 1, 2026 | 107000 | 56500 | 1.8938053097 | |||
| 4 | Aug 8, 2026 | Corvex | 95000 | Aug 14, 2026 | 26500 | Sep 1, 2026 | 52000 | 79000 | 0.6582278481 | 1.1285347044 | ||
| 5 | Aug 26, 2026 | Arden | 12000 | Aug 29, 2026 | 30000 | Oct 1, 2026 | 114500 | 62500 | 1.832 | 1.3813131313 | ||
| 6 | Sep 4, 2026 | Delta | 30000 | Sep 12, 2026 | 44000 | |||||||
| 7 | Sep 19, 2026 | Belmont | 22000 | Sep 27, 2026 | 35000 | |||||||
| 8 | Oct 2, 2026 | Corvex | 64000 | Oct 9, 2026 | 29000 | |||||||
| 9 | Oct 15, 2026 | Evers | 9500 | Oct 24, 2026 | 33500 | |||||||
| 10 | Oct 28, 2026 | Arden | 41000 |
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.
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.
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.
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.
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.
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
SUMIFSAdds with several conditionsEDATESame day, months laterDATEBuilds a dateIFPicks a result from a testSUMAdds numbersAVERAGEMean of numbers
Related guides
- How to build a rolling forecast in Excel
- SUMIF date range in Excel: sum between two dates
- How to calculate year-over-year growth in Excel
- How to make a running total (cumulative sum) in Excel
- How to calculate utilization rate in Excel
- How to calculate revenue per employee in Excel
- How to calculate debt-to-income ratio in Excel
Updated