Audit sampling in Excel: monetary unit sampling step by step

You can do monetary unit sampling in Excel with a cumulative amount column, a sampling interval, a random start fixed as a value, and XLOOKUP to find the item that holds each selection point. This guide gives every formula with its English and Spanish Excel function names, for MUS and for attribute sampling, and shows where spreadsheets usually go wrong.

Updated 10 November 2026

Before you start

  • One row per item, with an ID in column A and the amount in column B, from row 2. The examples use A2:B5000; adjust it to your population.
  • Reconcile the total to the ledger before sampling. If it does not agree, the sample does not represent the balance.
  • Move negative and zero amounts to another sheet: they cannot be selected in MUS and are tested separately.
  • Excel version. XLOOKUP, SEQUENCE, UNIQUE, FILTER, RANDARRAY, SORTBY and LET need Microsoft 365 or Excel 2021; TAKE needs Microsoft 365 or Excel 2024. An INDEX and MATCH alternative is given for older versions.
  • Languages. Spanish Excel uses semicolons between arguments and a decimal comma. Function names translate automatically when a file is opened in another language; only typing them differs.

Step 1. Sample size and sampling interval

Put the parameters in a fixed block, for example column H. With the figures from the monetary unit sampling calculator guide:

CellInputResultFormula (English / Spanish Excel)
H1Book value4,812,350=SUM(B2:B5000)
=SUMA(B2:B5000)
H2Tolerable misstatement120,000typed
H3Expected misstatement15,000typed
H4Confidence0.95typed
H5Confidence factor3.00=ROUNDUP(CHISQ.INV(H4,2)/2,2)
=REDONDEAR.MAS(INV.CHICUAD(H4;2)/2;2)
H6Expansion factor1.6from the AICPA table
H7Sample size151=ROUNDUP(H1*H5/(H2-H3*H6),0)
=REDONDEAR.MAS(H1*H5/(H2-H3*H6);0)
H8Interval31,869.87=H1/H7

The H5 formula reproduces the AICPA Poisson factors: 2.31 at 90%, 3.00 at 95% and 4.61 at 99%. The expansion factor (1.5, 1.6 and 1.9) is not calculated; take it from the table. If you prefer to select with the theoretical interval, H8 becomes =(H2-H3*H6)/H5, which gives 32,000. What matters is that you evaluate with the same interval you selected with.

Step 2. The cumulative amount column

In C2 enter =B2 and in C3 =C2+B3, filled down: the cumulative "to". In D2 enter =C2-B2: the cumulative "from". Each item owns the range of monetary units between D and C. Check that the last cell in C equals H1.

Avoid =SUM($B$2:B2) on large populations: it recalculates the whole range on every row and slows the workbook down.

Step 3. A random start, fixed and recorded

In H9 enter =RAND()*H8 (Spanish =ALEATORIO()*H8). If the interval is a whole number you can use =RANDBETWEEN(1,H8) (=ALEATORIO.ENTRE(1;H8)).

Fix the value as soon as you generate it. RAND and RANDBETWEEN are volatile: they change whenever the sheet recalculates, when you edit any cell or press F9, and the whole sample changes with them. Copy H9 and paste it as a value (Paste Special → Values). Record the number in the working paper with the date. RAND cannot be seeded; if you need a seed, the Analysis ToolPak's Random Number Generation tool accepts one.

Step 4. Selection points

In F2, one formula produces every point: the start and each interval after it up to the total.

=SEQUENCE(INT((H1-H9)/H8)+1,1,H9,H8)
=SECUENCIA(ENTERO((H1-H9)/H8)+1;1;H9;H8)

Without SEQUENCE: F2 =H9, F3 =F2+$H$8, filled down until the value passes H1.

Step 5. Pick the item for each point

The selected item is the first one whose cumulative "to" is equal to or greater than the point. XLOOKUP does this with match mode 1 (exact match or next larger):

=XLOOKUP(F2,$C$2:$C$5000,$A$2:$A$5000,,1)
=BUSCARX(F2;$C$2:$C$5000;$A$2:$A$5000;;1)

To bring back the amount, return $B$2:$B$5000 instead. In versions without XLOOKUP, use the cumulative "from" column and MATCH with approximate match:

=INDEX($A$2:$A$5000,MATCH(F2,$D$2:$D$5000,1))
=INDICE($A$2:$A$5000;COINCIDIR(F2;$D$2:$D$5000;1))

The two only differ when a point lands exactly on the boundary between two items; with a random start that has decimals this practically never happens. Use one and document it.

Example with 12 invoices

A population of 144,600, an interval of 20,000 chosen for the example, and a random start fixed at 7,413. The points are 7,413, 27,413, 47,413 and so on up to 127,413: seven in all.

InvoiceAmount (B)From (D)To (C)Points that land
INV-0014,25004,250–
INV-00218,9004,25023,1507,413
INV-0031,32023,15024,470–
INV-0047,61024,47032,08027,413
INV-00552,30032,08084,38047,413 · 67,413
INV-0062,98084,38087,360–
INV-00711,45087,36098,81087,413
INV-00864098,81099,450–
INV-0099,87099,450109,320107,413
INV-0103,150109,320112,470–
INV-01126,400112,470138,870127,413
INV-0125,730138,870144,600–

Seven points give six invoices, because INV-005 (52,300) takes two.

Step 6. Duplicates

An item larger than the interval can take more than one point. You test it once, but record how many times it was hit. List the distinct items with =UNIQUE(G2:G200) (=UNICOS(G2:G200)), and count the hits per item next to the population with =COUNTIF($G$2:$G$200,A2) (=CONTAR.SI($G$2:$G$200;A2)). That is why the number of items to test is usually a little lower than the calculated sample size.

Step 7. The top stratum

Every item equal to or larger than the interval is always selected. List them with =FILTER(A2:B5000,B2:B5000>=H8) (=FILTRAR(A2:B5000;B2:B5000>=H8)) and flag them: in the evaluation their difference counts at its actual amount, not projected. In the example they are INV-005 and INV-011. You can also take them out first, test them 100% and sample the rest with a recalculated interval; either works as long as the evaluation matches the choice.

Attribute sampling random selection in Excel

A test of controls needs n items at random without replacement. In Microsoft 365 or Excel 2024, for 4,200 invoices and a sample of 93:

=TAKE(SORTBY(A2:A4201,RANDARRAY(ROWS(A2:A4201))),93)
=TOMAR(ORDENARPOR(A2:A4201;MATRIZALEAT(FILAS(A2:A4201)));93)

Paste the result as values straight away, for the same reason as in step 3. In older versions, add a column with =RAND(), paste it as values, sort by it and take the first 93 rows.

The audit sample size can be calculated in the sheet too, with confidence in K1, the tolerable deviation rate in K2 and the expected rate in K3. The formula finds the smallest n that meets the binomial condition the AICPA tables are built on:

=LET(n,SEQUENCE(2000),k,ROUNDUP(ROUND(n*K3,9),0),MIN(FILTER(n,BINOM.DIST(k,n,K2,TRUE)<=1-K1)))
=LET(n;SECUENCIA(2000);k;REDONDEAR.MAS(REDONDEAR(n*K3;9);0);MIN(FILTRAR(n;DISTR.BINOM.N(k;n;K2;VERDADERO)<=1-K1)))

With 95%, 5% and 1% it returns 93, as in the table. It assumes a large population: if the sample is more than a tenth of the population, the exact size is a little smaller. To evaluate, the upper deviation limit for k deviations in n items is =BETA.INV(K1,k+1,n-k) (=INV.BETA.N(K1;k+1;n-k)): 60 items with one deviation at 95% gives 7.66%. See the attribute sampling guide.

The evaluation in Excel

To evaluate a MUS sample, add the audited value and work out, per item, the difference, the tainting (with the amount in B and the audited value in E, =(B2-E2)/B2) and the projection (tainting × interval, or the actual difference for a top-stratum item). Then rank the overstatement taintings from largest to smallest with RANK.EQ (JERARQUIA.EQV) and apply the confidence factor increment to each; the factor for k misstatements is =ROUNDUP(CHISQ.INV($H$4,2*(k+1))/2,2) (=REDONDEAR.MAS(INV.CHICUAD($H$4;2*(k+1))/2;2)). The full calculation, with an example, is in evaluating MUS results.

Where a sampling spreadsheet breaks

  • Reproducibility. If the start was not pasted as a value, opening the file and editing one cell changes the sample. Without the number recorded, nobody can re-perform the selection.
  • Review trail. The workbook does not record who generated the random number, when, or whether it was regenerated until a convenient sample came up. The working paper has to say so.
  • Evaluation formulas. Ranking taintings, keeping the top stratum out of the incremental allowance and handling understatements separately are easy to get wrong and hard to spot in a cell.
  • Versions and ranges. Templates copied from year to year, fixed ranges like A2:B5000 that miss a population that has grown, and functions missing from a colleague's older Excel. Check every time that the cumulative total agrees with H1.

If you'd rather not build the sheet, download the free monetary unit sampling Excel template: it runs these steps, keeps the start as a value and works out the evaluation.

AuditSampler Pro does these steps from the same Excel or CSV population file: it works out the size, selects while keeping the random start or seed, evaluates with the AICPA factors and produces the working paper as a PDF for MUS and attribute samples. If you are weighing other tools, the audit sampling software comparison covers IDEA, ACL, TeamMate Analytics, ForSampling and the free options.

Frequently asked questions

How do you do monetary unit sampling in Excel?

Build a cumulative amount column, work out the interval, generate a random start and fix it as a value, then use XLOOKUP (or INDEX and MATCH) to find the item that holds each selection point. The evaluation can be built too, with more care.

How do I stop Excel random selection from changing?

Copy the cell with RAND or RANDBETWEEN and paste it as a value as soon as you generate it, and record the number in the working paper. They are volatile functions: they recalculate whenever anything in the sheet changes.

Which Excel formula selects the items in monetary unit sampling?

=XLOOKUP(point,cumulative_to,ids,,1), in Spanish Excel =BUSCARX(punto;acumulado;ids;;1). It returns the first item whose cumulative total is equal to or greater than the selection point.

How do I build an audit sample size calculator in Excel?

For MUS, =ROUNDUP(BV*CF/(TM-EM*EF),0), with the confidence factor =ROUNDUP(CHISQ.INV(confidence,2)/2,2). With 4,812,350, 120,000 tolerable, 15,000 expected and 95% it gives 151. For attribute sampling, use the binomial LET formula in this guide.

Is there an audit sampling Excel template?

Yes: auditsampler.com has a free monetary unit sampling Excel template that runs these same steps and adds the evaluation with the upper limit. If you build your own with this guide's layout, check the ranges and that the cumulative total agrees every time.

  • AICPA, Audit Guide: Audit Sampling
  • IAASB, ISA 530 Audit Sampling
  • Microsoft, Excel function reference for XLOOKUP, SEQUENCE, RAND and BINOM.DIST

No random numbers that change on their own

AuditSampler Pro reads the population from Excel or CSV, keeps the random start of every selection and produces the working paper as a PDF for monetary unit and attribute sampling.