I recently set up a character database in Excel, and when I posted about it on Twitter/Facebook several people contacted me and asked “What’s a character database?” Sensing that this subject might be interesting to others, I decided to share the details. First, let me say that I’m not an Excel whiz kid, so trust me when I say that this file set up is really straightforward.
This type of database is especially useful if you write a series, and I finally set it up because I got tired of having to look back to see how I had described a character in a previous novel or to search endlessly for the name of a street. I started the file in a Word document, but that was too messy and didn’t allow nifty sorting features.
First, I established the column headers across the top. I’m still tweaking as I go, but for now I have:
- First name
- Last name
- Category
- Role/Function
- Description
- Car, address, phone
- Other details
- Book title 1 (The Sex Club)
- Book title 2 (Secrets to Die For)
- Book title 3 (Thrilled to Death)
- Book title 4 (The Baby Thief)
Most of these headers are self-explanatory, but the Category column is where I assign the character’s level: 1=main character/recurring, 2=main character/specific to novel, 3=villain, 4=secondary character/recurring, 5=throwaway characters.
Next I listed the characters by row and inserted relevant information. I still have to go back into The Sex Club and find/input all the secondary characters, but with my new novel, I’m adding to the database every time I add important details to the manuscript. (For example, if my character dyes her hair, buys a speed boat, or adopts a pet monkey.)
What’s great about this file is that each column can be sorted individually. I separated out the first and last names so I could alphabetize/sort each list individually. So if I come up with the name Kirstin, I can quickly sort first names and check the middle of that column and see how many characters have first names that start with K. Yikes! Better come up with a different name.
The purpose of the book title columns is to be able to sort by title. I simply put an X in each column title that the character is present in. Then if I’m working in book 3, I can sort by that column and have all the book 3 characters come to the top of the spreadsheet, allowing me easy access to their information. And if I have one of those moments when I’m wondering, Was Officer Chang in my first story or just my second?— it’s easy to find out.
Important reminder: Even if you’re sorting by a single column, be sure to highlight all your data so the information for each row/character stays together. I hope you find this idea useful (and comprehensible). Feel free to ask questions and make suggestions. It’s not perfect by any means.
If you read my blog regularly, thank you. And, it would be great if signed on as a follower and/or linked to my blog from yours.
I’m glad to see that you suggest using this as a tool, L.J.! 🙂 I also use a character database to keep track of my detectives, since they have different powers, different backgrounds. I use it to keep up with their families, who’s married, who has kids, where they live etc.
Lj,
This is a great idea. I have my notes in word – and a separate file for each book. Your spreadsheet makes more sense.
I’d emphasize, though, the importance of selecting ALL data in the spreadsheet before sorting. Otherwise, you scramble your data. I take two precautions to cover myself. First I SAVE the file before sorting. That way, if I mess up step 2, I can recover by simply closing the messed up version – without saving – and reopening the saved version.
Step 2 is to select the data by highlighting all the rows – selecting the rows by clicking on the row numbers to the far left.
Then I sort.
As a tech purist, I’d like to point out that Excel cannot create a true database. Access is for databases.
I have had your blog in my blogroll for a long time. Just now signed up as a “follower” also. As usual, I appreciate your post. I also have character databases. Although I am just now writing a first “series” (Owen Fiddler) – a database to keep track of all the characters’ pertinent info is invaluable and so time-saving when writing a full-length novel. But I also include color of hair, eyes, skin, religious/spiritual bent, personality type, off-the wall quirks, ethnic background, all that. Not at the beginning, but as the ms unfolds and I write, I always go to the character Database and add in what I’ve written about him/her so I can be consistent the rest of the way through the book.
Incredibly simple. A duh moment. Thanks for sharing.
As a writer myself, I thought about using Excel for this sort of thing. But I ended up using OneNote and I cannot imagine working without it.
Unlike Excel, you can include video, audio, photos – just about any attachment you can think of. Plus you can hyperlink anything to anything else, which is huge.
You don’t have the automatic sorting like Excel, but in my case I don’t really need that. You can always whip up something in Excel and then paste it into OneNote.
Each novel gets its own notebook. Each notebook has section groups (in my case there’s General Notes, Places/Things, etc.) Each section group has pages for characters, settings, scene/dialogue snippets, chapter notes, literary devices (irony, imagery, blah, blah, blah.) Some of the pages have related subpages. For example, I’ve got several characters who work for the U.S. gov’t. I’ve got some people who are college professors, and so on. I keep everybody in their respective cabal.
Again, all this junk is not only grouped together, it can be hyperlinked to anything in the notebook or on the web. So if I’ve got, say, a OneNote page about a town in Spain, I can link that page to a character who has a connection with that town, instead of writing about it again in the character’s page. I link characters to characters, characters to photos, notes to notes, notes to web pages (although I always try to copy the necessary info. from the site into OneNote in case someone pulls the plug on the site down the road.)
Sorry for the lengthy comment, but there is no end to what you can do with OneNote. I will admit that over the years I haven’t been the biggest Microsoft fan, but I swear if I ever saw Bill Gates in person I would kiss him on the lips for this thing.
OneNote sounds amazing. Thanks for sharing. I’ll look into it.
I use OneNote for book notes, also–it’s really good for that, much better than Excel. Although, you CAN make links in Excel, too.
You don’t need OneNote. Each novel could have an Excel file and then you could various tabs for your various information (your town in Spain) with links between tabs within Excel. It works just as well… and you can still sort alphabetically.
Don’t know if this would help you with your sorting process but give it a try:
In Excel 2003, select the cells that have all the information (according to your blog, select from the header that says "First Name" to the bottom-right cell under "Book 4" — do not select by clicking on the column letters or the row numbers) and go to Data>List>Create List… on the little pop-up, turn ON the "My list has headers" box, and hit OK. You will now see a drop-down button to the right of every header column that offer you various sorting options. The drop-down button will turn blue if that column is being sorted, so viewing your whole table again is as easy as clicking on those blue drop-down buttons and choosing to view "(All)". One more thing to mention about this List feature is that you can keep on adding to the database: at the bottom of the table there will be a blue asterisk – click on that cell and start typing and it will automatically add a row that is included in your sorting List (and the blue asterisk will move down one row).
In Excel 2007 this same feature is called Table. Select your data as mentioned above and under the Home tab, in the "Styles" section there's a "Format as Table" button, which allows you to add some formatting to your data (i.e., color of cells/fonts) and presents enhanced sorting capabilities (such as sorting by more than one criteria at a time). While you won't see the "blue asterisk" as in 2003 to add a new row, if you start typing at the bottom of the Table, the row will automatically be formatted to be included in the Table above.
Sorry for the long post. I am by no means an Excel expert, but hope that this little nugget of info can help you track your characters a little better. Good luck!
–vico
This is an interesting post – thanks! (You may be interested to know that I found it because someone posted about it in the WORD-PC e-mail list.) I have two comments:
– As someone says above, MS Access is really the program for databases. It might take a bit of learning, but using that would solve all the problems with messing up your data by performing incomplete sorts, etc! Maybe that’s not worth the effort now that you’re set up in Excel, though.
– I know a lot of other people who don’t use Access but do use Excel, and so I also use it myself for this kind of thing when I’m working on something collaborative. The safest solution that I have found for the sorting difficulties is to have two copies of each worksheet that you wish to sort. One is like the ‘master’, and is updated but never sorted etc; the other one is identical, and updated in the same way, but is for sorting. That way, even if you do do something like sorting on a column when not all columns are selected, and therefore mess your data up, you have a virgin copy and nothing is lost. It’s really easy to make mistakes like that (he said with chagrin). When I do this, I name the two identical worksheets, for example, Characters and Characters_sort.
I use Scrivener (on Mac) for this kind of “project-oriented” function (Journlar and MacJournal are some other similar options). You can do all kinds of storage and write in it as well. Very flexible structure, including outline and notecard views.
Barbara