Cash Denomination Calculator in Excel: Count Bills and Coins

Counting a till at the end of a shift means answering one question: how many of each note and coin make up this total? A short spreadsheet does it exactly, with no manual trial and error, and shows the leftover amount after every denomination so you can see the maths is right.

Cash Denomination Calculator in Excel: Count Bills and Coins

Set Up the Denomination Table

Lay the sheet out so each denomination gets its own column heading, from the largest note down to the smallest coin.

1. In row 3, type the denominations in descending order: 100, 50, 20, 10, 5, 1.

2. In column A, put a label such as Amount on row 4 and the cash total in cells A5 downward.

3. Leave the cells under each denomination empty — the formula fills them with counts.

4. Add a final Total column that adds the value of every count back together.

   

   Denomination table layout in a spreadsheet

Use Word, Excel, and PPT for FREE

Free Download

Count Each Denomination with INT and SUM

The trick is to subtract the value you have already allocated before moving to the next, smaller denomination.

1. In the first denomination cell (B4, under 100) use =INT($A4/B$3). INT drops the decimals, so 286 divided by 100 returns 2.

2. In the next cell (C4, under 50) use =INT(($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3). This subtracts the 200 already counted, then divides the remaining 86.

3. Drag that formula across to the last denomination. Each column looks at the value left over after the bigger notes.

4. In the check cell use =SUMPRODUCT($B$3:$G$3,$B4:$G4); it must equal the amount in A4, or a count is wrong.

   

   INT and SUM formula counts each denomination

Extend the Calculator to Coins and Fix Rounding

Notes are whole numbers; coins are not, and that is where results start drifting by a cent.

1. Add coin columns to the right: 0.25, 0.10, 0.05, 0.01, and extend the drag handle so the formulas cover them.

2. Wrap the calculation in ROUND — for example =ROUND(INT(($A4-SUMPRODUCT($B$3:F$3,$B4:F4))/G$3),0) — so floating point noise does not turn a 0.01 count into 0.

3. If your business only handles whole dollars, hide the coin columns instead of deleting them, and keep them ready for the day a customer pays in change.

   

   Coin columns and a reconciliation check

Use Word, Excel, and PPT for FREE

Free Download

The Best Free Alternative: WPS Office

INT, SUMPRODUCT, and ROUND behave identically in WPS Spreadsheet and Microsoft Excel, so the calculator you build today keeps working whichever app opens it.

• Works on the counter: the same sheet opens on the shop tablet and the back-office desktop.

• Free templates: start from an existing cash-log or float-count layout instead of a blank grid.

• No conversion step: paste counts from a POS export and the formulas update instantly.

• Offline by default: till totals stay on your device rather than syncing to a cloud service.

WPS Spreadsheet for cash denomination counting

FAQs

Why does my denomination calculator leave one unit behind?

Almost always floating point rounding. Wrap the count formula in ROUND so values such as 4.999999 become 5 instead of dropping to 4.

Can I use this for a currency without a 1 unit coin?

Yes. Replace the denomination row with your own values — for example 200, 100, 50, 20, 10, 5, 2 — and the same formula logic still works.

How do I show the leftover after each denomination?

Add a helper row with =A4-SUMPRODUCT($B$3:B$3,$B4:B4) under each column, so you can watch the running remainder fall to zero.

Is there a dynamic array version that returns all counts at once?

Yes, if your app supports LET and SCAN: let the array walk the denominations while carrying the remaining amount, then return all counts with HSTACK.

Can I count a mixed till with rolled coins?

Enter each roll as its face value in a separate row, then sum the rows into one Amount cell before the breakdown runs.

Free Office Download

   

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

Free Download

100% secure

Algirdas Jasaitis