Humble preamble: This method isn’t meant to replace the more considered planning and preparation that goes into creating a hexcrawl campaign. It’s just meant to make the whole endeavor a little easier.
The method involves coming up with a list of items you want to stock your hexes with (settlements, lairs, all that good stuff) and then randomly assigning them to the hexes. It’s the same principle as rolling on a random table for each hex, but the benefit of using a cold soulless machine over the tactile analog pleasure of dice and paper is that you can play around with a much bigger list of items and the whole process goes a lot faster. Score one for the robot uprising.
Step one: open a fresh Google Sheet. This might work on Excel but I don’t know; I can’t try it because the Office plan I didn’t know I had has expired and now I have this shitty little icon sitting on my computer that I keep forgetting to get rid of. Anyway, in Column A write out your list of hex numbers. If you separate hex rows and columns with a period, adjust the decimal place one to the left or else every hex column that ends with zero will be cut off. Frustrating, but we persist.
|I forgot to move the decimal place over in my example images. C'est la vie.|
Step two: In column E, make a big list of all the things you want on your hex map. Don't be too specific—this is the random table that the formula is going to be rolling on, so you should account for the possibility that each result could appear many times. That being said, I find it helpful to include multiple versions of the same thing to get one level of detail deeper. This also helps for weighting the table, which I will get into more later.
So for instance, instead of just having "ruins" four times on the list, I included four different types of ruins: Lizardman, Giant, Cyclopean, and Weird (the useful catch-all for any type of ruin I want to include that isn’t covered by the other categories).
Do this with fortresses, settlements, castles, temples, towers, dungeons, monster lairs, and/or whatever else you want your players to come across over the course of their wondrous adventures. Add in the basic stuff, add in setting-specific stuff, maybe even add in things you don’t have a clear idea of yet and so you can come up with the details later. Consider including placeholder entries like "Special" so you can add in more elaborate and boutique stuff later, if that suits your fancy.
All told, I ended up making 60 different hex items. In order for the below formula to work, you will need 60 items as well.
Step three: Click on the first cell in column B, and then paste this into the formula bar (next to the fx symbol):
Edit: try =INDEX($E$1:$E$120;RANDBETWEEN(1;120)) if the above formula doesn't work
(If you're not using 60 items, your numbers may need to change)
After that, select the cell, click on the little blue box in the bottom right corner, and drag it down to fill in the rest of the cells in column B.
INDEX returns the contents of other cells, and RANDBETWEEN chooses a random number between two parameters. In this case, the formula is choosing a random cell in column E between 1 and 120 and displaying the result in the cells of column B.
Make sure you include the dollar signs or else the function scale will shift up one with every cell, meaning it would go from E1:E120 to E2:E121 and so on. Obviously since you’re copy/pasting this isn’t a concern but this took me way too long to figure out and I want to put it here to make sure NO ONE gets confused by this EVER AGAIN.
Notice the range goes to 120 when I only have 60 hex items. I want some hexes to be empty, so that the map isn’t too cluttered and so I can fill in more stuff later if need be. A matter of personal preference.
Because of the inscrutable workings of Google Sheets every time you update the spreadsheet, all the cells produced by the formula will change. There's probably some way to prevent this but what I do is just copy all the cells and then in a new sheet go Paste special -> Paste values only.
Repeat step three in column C if you want more items per hex, which you should, because one thing per hex is BORING.
Play around with the numbers and proportions. If you want a sparser, more terra incognita hexcrawl, expand the function ranges to make more empty hexes and have a low number of settlements compared to everything else.
Don’t take weighing the table too seriously, unless you’re really into ensuring there is a realistic proportion of castles to settlements or whatever. If you have way more ideas for temples than you do fortresses but you want roughly the same number to be on the map, just condense multiple ideas into the same cell.
This tool is just a jumping off point—play around with it until it spits out something you can work with and then go from there.
Next steps: Adjust, reflavor, and season to taste.
Look at your hex map and your list of semistocked hexes. Get a feel for the ecosystem, what the dialogue between the points of interest and the natural geography may be. Why might things be located where they are.
Take Hex 25.19 in the example image. Within it is both a wizard's tower and a mutant's lair. Perhaps the mutant was the victim of the wizard's foul experimenting, but he escaped the tower and now plots his revenge. Or maybe the wizard and the mutant our allies, and the mutant has agreed to defend the wizard's tower in exchange for magical boons.
This is where you do that DM thing where you iterate on idea fragments until they are fleshed out to your liking. Knowing just the content of a hex, its terrain type, and the nearby locations should develop in you a strong enough creative foundation that can you expand on easily, or even spontaneously at the table if need be.
The same principle of dungeon stocking applies to working out your hex map: you’re obviously allowed to change results if you feel like it would make a better campaign, but also let the weird idiosyncrasies fuel your imagination. Why are there five castles in this tiny stretch of jungle? What the hell is a haunted mansion doing in the middle of a desert? You tell me. Figuring this stuff out and coming upon these “discoveries” is one of the real joys of being a DM, I-M-O.
And that’s just about all there is to it.
*some work will be required to distinguish hexes stocked with the same contents
**"instantly" does not include the time it takes to set up the material required for the method to function.