Excel 2024: Shuffling and Dealing a Deck of Cards

Take a look at the figure below. The 13 playing card values are in A5:A17. The four playing card suits are in B4:E4. A simple =A5:A17&B4:E4 in cell B5 generates a 13-row by 4-column array of all 52 playing cards.

To rearrange those into a single column, use =TOCOL(B5#) in cell G5.

To sort the 52 cards randomly, =SORTBY(G5#,RANDARRAY(52)). Every time that the worksheet recalculates, you will get a new shuffle of the deck of cards.

Now that you have the 52 cards arranged in a random sequence, you can deal them using WRAPCOLS or WRAPROWS. In the figure below, cards are dealt to 6 players. Avoid an #N/A error for the last two cards by using "" for Pad_With argument.

Typically, in a card game, you would be dealing one card to each player and then a second card to each player. If you would want to deal the first 8 cards into a single pile, use WRAPROWS as shown above.

The next figure shows some interesting alternatives. The formula shown in L22 uses TOCOL, SORTBY, RANDARRAY and WRAPCOLS to perform the shuffle and deal in a single formula.

The formula shown in L24 and the results in L25:P28 use TAKE to make sure that each player only receives 5 cards.