Hi - Dave here.
Happy Friday!
Last week, we looked at
combinations and permutations, and how Excel can count them with functions like PERMUTATIONA, COMBIN, and PERMUT. These functions answer one question well: how many?
Take a classic rotary dial padlock: 3 numbers, each selected on a dial with 40 numbers, 0-39. PERMUTATIONA gives you the count easily:
=PERMUTATIONA(40,3) // returns 64,000
But counting is not the same as listing. How can you actually generate all possible codes?
The trick is to think like a car odometer. You count from the very first code to the very last, and each wheel only rolls forward when the wheel to its right finishes a full turn. To read any single code, you "decode" that count back into its separate positions. The worksheet below shows the result:
[
Download the workbook and read the full explanation]
A nice feature of this approach is that one pattern scales in different ways. It handles a basic 4-digit padlock (all 10,000 codes from 0000 to 9999), a 40-number rotary dial, codes of any length, and even locks built from letters or other symbols, with your original values kept intact. The full article walks through each example step by step, with formulas you can copy. Download the workbook to follow along.
Note: These formulas require Excel 2021 or later.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.