Mapping out character and faction motivations and relationships with Excel

In the last couple of years, I’ve been experimenting with different ways to brainstorm the narrative structure of my novels, and one approach I’ve been developing is to map out how the characters and factions relate to each other, then look at the various possibilities for thematic development. The other approach is to map out each of their personalities, motivations, philosophies, and methods, and look at what possibilities they could lead to.

The challenge of those two approaches was mainly logistical, since I couldn’t find any software that was designed for those exact tasks. I tried numerous writer’s software–ones like Scrivener, yWriter, Liquid Story Binder, Writer’s Cafe, WriteItNow, and various software for screenwriting, story planning, mind-mapping, and virtual index cards.  But none of them were ideal, since they either forced their own story structures onto the writer, or just don’t have the right kind of GUI, or were very clunky to use, or lacked the necessary features. Finally, after years of searching and testing, I finally came up with the ideal solution, and it’s actually just using Microsoft’s Excel spreadsheet.

It’s not that simple though, because an Excel worksheet’s default layout and behavior is not ideal, and I had to figure out a few tricks in order to get it to behave exactly as I envisioned (I’ll explain those later). Luckily, it’s a software with a huge user-base, so it was very easy to search for answers on the web whenever I ran into problems. After spending a lot of time customizing and perfecting the layout, this is the final result of my “Character & Faction Motivations” and “Character & Faction Relationships”  brainstorming worksheets (the screenshots show a generic example, so you can see how the worksheets are used. In a real project, you would use distinct names for factions and characters):

As you can see, I chose moderately dark green background with bright yellow font for the main text, because it is a combination that is easily readable yet not fatiguing on the eyes (I did a lot of research and experimented with many combination).

Now, I’ll explain how the two worksheets are used.

Character & Faction Motivations – The factions and characters are listed in the leftmost column, while the various personality characteristics and motivations are listed in the topmost row. If there are sub-categories within a column, they will be separated with a green font heading inside the cells.

The motivations worksheet is a great way to get a visual overview of all of your factions and characters and compare their various motivations and personalities, thus make interesting connections that you probably wouldn’t have without this type of visual layout.

In my Scrivener project file, I do have a more detailed character profile template that includes many other information, such as the character’s background story, physical description, mannerisms, how the character sees him/herself versus how others see him/her, and so on. I don’t use those in the Excel worksheet because they aren’t as immediate for quick comparisons and finding connections between factions and characters. But when I want to concentrate deeply on a specific faction or character, I’ll go to the profile sheet in Scrivener.

BTW, you can see that I divide factions into moral, neutral, and evil, which is based on the D&D alignment system. I use that system for character profiles too.

Character & Faction Relationships – All factions and characters are listed in leftmost column and then duplicated in the topmost row. This means, every faction or character will intersect with every other faction and character throughout the worksheet. Wherever they intersect, is the cell that you write what the relationship is between the faction or character. This can be what they think of each other, as well as how they relate to one another, or significant incidents that shape their relationship. I also color-code the background color of the relationships into five types: Very close, on good terms, neutral, dynamic/shifting (meaning there are ups and downs), on bad terms, and enemies. This way, I can see in a glance what type of relationship is between two entities in the story.

Wherever factions or characters intersect, there will be an identical intersection in the other orientation, so you simply copy & paste the cell to the second intersection. This way, you can navigate the factions and characters in any direction you want (horizontally or vertically) and always be able to find any intersection you’re looking for.

The beauty of the relationship worksheet is that once all the factions and characters in your story world are added to the worksheets, you can jump to various intersections where a specific faction or character meet, and it could be a combination you never thought about before, and now that you are thinking about the relationship between them, you’ll unearth possibilities you never would have come up with otherwise. So in a way, it’s like forcing serendipity to happen, but at the same time, you’re still in total control.

There are a lot of interesting ideas that came out of my brainstorming sessions using these two worksheets, and they are far more intuitive to use than any of the other alternatives I’ve tried over the years (and I tried pretty much all of them, including very obscure ones that aren’t even developed anymore). The ideas generated from using the worksheets are added to my Scrivener project file in the plot and character/faction arc outlines, and the worksheets themselves can also function as a quick reference map to all the factions and characters and their various personalities, philosophies, methods, and relationships with each other.

I mentioned previously I had to learn a few tricks to create my ideal worksheets, and I’ll explain them here so you don’t have to go hunt them down all over the web.

-In order to force the leftmost column and topmost row to stay visible while you scroll though the worksheet, you have to freeze the cell that’s just to the right and below them. Once you do, the list of factions and characters will stay in place and never move while you scroll through the worksheet. Here’s the instructions on how to do that: https://support.office.com/en-ca/article/Freeze-or-lock-rows-and-columns-3439cfe6-010c-4d2d-a3c9-d0e8ba62d724

-Excel can’t handle mousewheel scrolling horizontally. There are scripts that you can install to do it, but I couldn’t get any to work, so I use another workaround that gets me close enough. What you do, is to turn on Scroll Lock on your keyboard (usually Shift+NumLock), and then use Control+Arrow left/right keys to jump one screen at a time horizontally. I have bound those two commands to two macro keys on my X-Keys XK-24, so navigating the worksheet horizontally is about as convenient as using mousewheel. In fact, in a story with large ensemble cast and many factions, using these commands will get you to where you want faster than scrolling with the mousewheel.

-Excel can’t do dynamic cell size changes, so if a cell contains more text than can be displayed on the worksheet, the extra text will be hidden. But that’s not a real problem because if you want to read all the text in that cell, simply double-click on it and the cell will expand to show its entire content. There’s also a separate formula bar text window that can be resized to show more than just one row of text (but I don’t do that because it takes up precious screen real estate–I prefer to double-click on the cell).

-You should learn how to quickly swap, insert, delete, copy & paste entire columns and rows or cell ranges, as well as other basic editing, because you’ll need to know them when creating your own worksheets. There are so many tutorials on the web that teach this stuff–you’ll find them easily with a simple search.

These two worksheets are the culmination of years of research and experimentation, and I hope you’ll find them very useful for your own story planning and brainstorming sessions.