Tuesday, August 6, 2013

Random Tables - An Excel Quickie

Someone over on G+ was asking about tools to make random tables for RPGs. I mentioned Excel and the poster said they weren't very good at using the spreadsheet. Since quick and dirty tables are pretty easy with Excel, I thought I'd throw together a quick example, so... here:

This is a very simple table with six entries (A2-A7). A1 is a formula which randomly selects an entry from the table using the INDEX and RANDBETWEEN functions. A8 is a text copy of the function used. Each time you hit the F9 key, RANDBETWEEN recalculates its value, and uses it as an index into the table. Here's what the INDEX function means:

  • A2:A7 is the array to work on (that's six rows by one column).
  • RANDBETWEEN(1,6) chooses a row value between 1 and 6, inclusive.
  • The final 1 parameter chooses the first column.

Points to note:

  • The INDEX function takes an array, a row, and a column as parameters. You can get fancier and use RANDBETWEEN for both row and column to create a two-dimensional table.
  • The RANDBETWEEN function picks a number from the integer values between and including the given low and high.
  • The row and column values in INDEX are values within the given array (A2:A7). The first row/column is always one, and the highest value is equal to the number of rows/columns in the array. Don't use spreadsheet row/column values!
  • Using an array reference inline like that leads to unreadable spreadsheets. If we were doing a more complex example, we'd apply a name to the data range, and use that instead.
  • What works in Excel generally works in Open Office or Google Drive documents. For example you can see the Drive version right here.

OK, that's it, quick and dirty. There's more complex stuff in this post right here though.


  1. Functional, smart, useful. I use the index function so rarely I have to go reread the help menu every damn time. That's a lot clearer.

  2. Reading help in Excel *is* a bit counter-productive in most cases, isn't it? Glad this was useful!

    The biggest weakness of INDEX and this method is that you end up with linear/flat tables. The VLOOKUP/HLOOKUP method is a lot more flexible.


Note: all comments are moderated to block spammers. Please be polite.