How to Make Random DnD Tables in Excel, Google Sheets, and OpenOffice Calc

A Step-by-Step Guide on How to Make Random DnD Tables in Excel, Google Sheets, & OpenOffice Calc

So, curious into how to make your own RPG random tables?

Whether you play D&D 5e, Pathfinder, Spelljammer, or any other system, random tables are a great tool to keep in your Dungeon Master’s kit. I play D&D 5e, so I’ll often refer to that. But, keep in mind that the methods here apply to pretty much any system.

They aren’t hard to make. Come up with a few ideas and assign a polyhedral die to the table. But, maybe you want a digital alternative.

Since many of us have had to turn to playing on virtual tabletops, it can be nice to have an automated random table or two on-hand.

So, in this post, I’m going to show you how to make your own D&D random tables for a few different spreadsheet programs.

Pick your spreadsheet program to get started.

Google Sheets

Alright. Let’s start with Google Sheets.

The biggest benefits to using this program are 1) it’s free with a Google account, and B) you have access to it anywhere you have an internet connection. So, you always have access to your random D&D table as long as you can connect to the web.

Personally, it’s also the easiest to use.

But, enough with the preamble. Let’s get to creating your random table.

  1. Step 1: Open a New Google Sheets Document

  2. Random Google Sheets Table, Step 1

    We need a space to work in.

    Go to the "New" button and select "Google Sheets".

    A blank spreadsheet should open with a single sheet. You can name your spreadsheet whatever you want. I went with Random Table Test as a generic name. Also, I’d recommend changing the name of the tab from "Sheet1" to something else. But, this isn’t required. We’ll work only in this tab, so keeping track of multiple sheets won’t be a problem.

    Step 1.1 (Optional): Enter Some Text Into the First Cell

    You can add some text to the top-left cell (cell A1) if you like.

    Something like "d6 Random Encounters" or whatever fits depending on the random table you’re making. This is optional and only meant to help you keep track of what you’re creating.

  3. Step 2: Fill Out Your Table

  4. Random Google Sheets Table, Step 2

    Whatever you’re making an RPG random table for, fill it out.

    Keep cell A2 empty. This is where you’ll put the formula that generates a random result.

    Other than that, put your table whereever you want in the sheet. But, you need to keep them all in one column and without empty cells between the results.

    Your table should look something like this:

    I want to make simple 1d6 random encounter. So, I’ll make a short, six entry table with the following cells:

    1. Goblins
    2. Kobolds
    3. Bullywugs
    4. Bandits
    5. Mephits
    6. Skeletons

    This selection of cells is what I’ll call on in cell A2 in the next step.

  5. Step 3: Enter Your Random Generator Formula

  6. Random Google Sheets Table, Step 3

    Now we get technical.

    You need to enter a formula that calls on your table, selects a random one, and outputs that into the cell.

    Luckily, this sounds a lot more complicated than it is.

    Copy paste the following formula into your Google Sheets document in cell A2:

    =INDEX(RANGE, RANDBETWEEN(1, COUNTA(RANGE)))

    That’s about it.

    If you have more or less entries in your table, change the range numbers (in the example, "A4:A9" is the range).

    This formula will return a random result from your table’s range.

  7. Step 4: Switch Our Your Numbers

  8. Random Google Sheets Table, Step 4

    Change out the numbers in the formula for your table. You’ll replace the RANGE parts with your range.

    For example, my formula ends up looking like this: =INDEX(A4:A9, RANDBETWEEN(1, COUNTA(A4:A9))).

  9. Step 5: Refresh to Get New Results

  10. There’s one issue with Google Sheets. There’s no easy way to refresh the result.

    In order to get a different result from your random table, you need to refresh the cell somehow.

    There are a couple ways to do it. But, they’re not all that convenient.

    Your first option is to alter a cell somewhere else in the sheet. Just pick an empy cell, enter a "." and hit Enter. This will give you a different result in A2.

    The problem here is you end up with a bunch of useless cells that you need to clear out when you’re done.

    Now, Google Sheets has a fun little feature that lets you auto-refresh your tab every minute. Go to File, Spreadsheet Settings, Calculation, and choose from the top dropdown under Recalculation. You have the option to recalculate every minute or every hour. This means your random table refreshes at these intervals according to your system’s clock.

    Your last option is to refresh the whole page. Either click the refresh button or use the F5 key.

    This also isn’t ideal because you have to then wait for the page to reload. And, there’s a delay between page load and the cell refreshing. So, it’s even more waiting.

How to Make Random DnD Tables in Excel

Luckily, Excel works in much the same way at Google Sheets. So, the steps are mostly the same. But, there are a couple differences.

  1. Step 1: Open a New Excel Workbook

  2. How to Make Random DnD Tables in Excel, Step 1

    Do any tab renaming and organizing you like to keep track of things.

    Step 1.1 (Optional): Enter Some Text Into the First Cell

    Optional but it helps me remember where to put my formula. And, it just looks nicer.

  3. Step 2: Fill Out Your Table

  4. Random Tables in Excel, Step 2

    Let’s switch is up and make a random table for a tavern keeper’s race. Your players are gonna go to taverns so you’re gonna need to know vaguely what the owner/proprietor looks like.

  5. Step 3: Enter Your Random Generator Formula

  6. Random Tables in Excel, Step 3

    Put this in the cell under your table’s title. Or, in A1. Basically, wherever you plan on making your random DnD table.

    Formula:

    =INDEX([RANGE],RANDBETWEEN(1,COUNTA([RANGE])),1)

  7. Step 4: Switch Out Your Called Cells

  8. Random Tables in Excel, Step 4

    Change [RANGE] to match the range of cells you want to call on.

    So, in my example, my formula looks like: =INDEX(A4:A12,RANDBETWEEN(1,COUNTA(A4:A12)),1).

  9. Step 5: Refresh to Get New Results

  10. In my opinion, this is the major pro over using Google Sheets.

    Excel makes it much easier to refresh a cell or tab. So, your D&D random table becomes a lot easier to use if you make it in Excel.

    The two ways to refresh your result are:

    • Refresh the cell with F2 + Enter
    • This option highlights the cell like you’re editing it. When you hit Enter it’s like you’re updating the cell without making any changes. Not ideal because you need to then navigate back to the cell and do it again anytime you want to get a new result.

    • Refresh the tab with Shift + F9
    • This option is best because it refreshes the entire tab. So, you don’t need to go back to the specific cell. You can refresh over and over without the extra work.

OpenOffice Calc

Now, for our last options (but another free one), things get a little weird.

At the time of writing this, the version of OpenOffice I was using didn’t really have a random cell option. At the very least, not an easy way to randomize a selection from a range of given cells. So, the formula is…messy.

But, let’s start from the top.

  1. Step 1: Open a New Calc Document

  2. Random Tables in OpenOffice Calc, Step 1

    Ya need to have a blank sheet to start somewhere.

  3. Step 2: Build Out Your Table

  4. Random Tables in OpenOffice Calc, Step 2

    Again, let’s switch it up.

    I’m gonna build a random, 1d6 table of loot found in a kobold mine.

  5. Step 3: Enter Your Random Formula

  6. Random Tables in OpenOffice Calc, Step 3

    Here’s the weird one:

    =CHOOSE(ROUNDUP(#*RAND()+0.00001);Each;Individual;Cell;Etc)

    So, yeah. To make a random table in OpenOffice Calc, you need to specifically call each individual cell in your range.

    In my example, my formula ends up looking like this: =CHOOSE(ROUNDUP(6*RAND()+0.00001);A4;A5;A6;A7;A8;A9).

    It’s definitely not perfect. While your table is basically random, the the percentage chances are a little skewed due to that "0.00001" number. But, if you want to use Calc, this is what you get.

  7. Step 4: Change the Numbers

  8. Random Tables in OpenOffice Calc, Step 4

    Let’s make it even more complicated.

    So first off, put in the number of called cells in place of the &quot#" symbol in the formula. This tells Calc how many cells/options are in the range. In my example, I’d change it to a &quot:6."

    Then, call on each individual cell separated with a semi-colon following the semi-colon. You need to specify each cell to tell the formula that, yes, you would like to choose a random option from that specific range.

    So, my formula ends up like this: =CHOOSE(ROUNDUP(6*RAND()+0.00001);A4;A5;A6;A7;A8;A9).

    Personally, I think it’s kind of a mess.

    Even better (worse?), you could forgo using cells altogether with this method. Just plug in your in place of the cells you want to call. So, like, in my example the formula would then look like: =CHOOSE(ROUNDUP(6*RAND()+0.00001);"Rusty Dagger";"Broken Handaxe";"Jammed Crossbow";"Cleaved Spear";"Snapped Bow";"Holy Avenger").

  9. Step 5: Refresh Your Tab to Get a Random Option

  10. At the very least, OpenOffice Cale makes it pretty easy to refresh the tab your random DnD table is in.

    All you need to do is press Ctrl + Shift + F9 and you’ll get a new result from your random table.

You can do a lot of really cool stuff for your D&D random tables in spreadsheet programs.

If you want to take it a step further, you can create two separate tables and use the TEXTJOIN function to combine the result. This is great for making your own, random fantasy surnames, tavern names, or ship names.

But, I hope this tutorial helps you when you want to make your own random tables for your D&D, Pathfinder, or any other role-playing game.

What’s your favorite way to use random tables? Leave a comment with your favorite table use.

6 thoughts on “A Step-by-Step Guide on How to Make Random DnD Tables in Excel, Google Sheets, & OpenOffice Calc”

  1. To quickly refresh the table without having to enter a value into a cell, just select an empty cell and press the “Delete” key. It will cause the spreadsheet to refresh automatically without moving to a different cell or having to remove extra data! I use this all the time for my formulas that use the “RAND()” function.

    The best part is, you can press and HOLD the delete key to constantly refresh it! Works as a great visual this way, too!

    1. Oh, that’s a neat way of doing it. I like that your table lets you remove used results.

      I do wish there was a faster way of resetting the table in either case (refreshing or re-filtering).

      I found out Google Sheets has an auto-recalculate feature you can set for every minute or hour. Which is kind of nice.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.