| Tutorials | 4 min read
Spreadsheets and the writer's toolkit
Spreadsheets are a powerful, but often overlooked, addition to the writer's toolkit. Spreadsheet excel (no pun intended) at breaking down data into manageable chunks for easy analysis, manipulation and reporting.
Using a spreadsheet to plan a novel isn't new; the snowflake method for example recommends their use for breaking down scenes in a novel and listing POV.
The key is meta-data
However, using Scrivener partially negates this need because its outline feature functions very much like a spreadsheet, at least visually. By default Scrivener displays the Title and Synopsis, Type and Status but its true power is the ability to add custom meta-data, which can be displayed as extra columns in your outline. Scrivener doesn't give you a POV field by default, but it's trivial to add one.
At the very least you should be tracking your novel scene's POV character...
As note you add anything you want. Some of other the meta-data fields you may want to use for fiction include:
- Other characters
- Event Start/End Date
- Story Arc
Scrivener's outline though visually similar to a spreadsheet, does not offer the ability to manipulate data. However, Scrivener has a feature that allows you to export your novel's meta data as a Comma Separated Values (CSV) file. CSV files can then be imported into your spreadsheet program of choice for analysis.
To export to CSV:
- Go to File -> Export -> Outliner Contents as CSV...
- Select the location in Finder to save the CSV file.
- The file's now ready to import into a spreadsheet program
Using the Spreadsheet
All spreadsheet programs can work with CSV as it's a commonly used format for exchanging data. In the following example, I'm going to use Apple Numbers, it's freely available for Mac and iOS. What I show you will also work with Microsoft Excel and LibreOffice Calc.
To begin, simply import or open the file in your spreadsheet program. Typically, this can be done as simply as double-clicking the file you exported from Scrivener. If however you have multiple programs that can view CSV files, right-click the file and choose to open it in your spreadsheet app. If you want to do this on the iPad version of Numbers, you'll have to transfer the file to your device using iCloud, Dropbox or email it to yourself.
Your spreadsheet will then display the data in a table. In Microsoft Excel, you'll get one table per worksheet. In Numbers, you can have multiple tables per sheet (one of the reasons I like Numbers). Numbers also formats the first row nicely as a header row.
Once your data is loaded, it's time to analyse it. In this example I'm going to count the number of scenes a character has the POV and then display the character POV counts in a pie chart.
To begin with I create another, smaller table with two columns: Character and Scene Count. Under the Character column, I display the characters name and the Scene Count will be the number of scenes that character has POV.
The following table shows the main characters from my book Weaver of Dreams.
Note I include the 1 against each row just to illustrate what will go there.
If you want you can manually count the scenes, but that would be wasting the power that a spreadsheet has to offer. Instead of manually counting the scenes, you can get your spreadsheet to do it for you using a function.
Most spreadsheets have a variation on the COUNT function that allows you to count cells if a certain condition is true. In Numbers, that function is COUNTIF and to use it, all I have to do is enter the following in Scene Count column against each character I want to count:
Your example will differ in two key ways. Firstly POV is the name of the column containing my scene POV. In Excel or other programs, you may instead have to reference the cells by their index, something like E where E is the name of the column.
Secondly, "Lillian Kasparu" is the name of my protagonist and naturally you'll have different character names. Repeat this process for each character row, making sure that you change the character name to be exactly what they are named in your column.
The results of my table look like this:
Visualising POV in a Pie Chart
Once the count is completed, you can then create a chart to break down your POV scenes visually. This lets you see at a glance your story's balance.
Creating a chart in Numbers (and Excel) is as simple as selecting both the Character and Scene Count columns and selecting the Pie Chart option from the list of charts.
For reference here's what my table and chart look like in numbers
As you can see, I have a very clear an easy-to-read visual breakdown of my POV scenes.
This is just the tip of the iceberg and there's no limit to what can be done. In my meta-data, I also track participant characters and locations meaning that I could employ the same technique to chart how often a character appears or indeed where they appear and even at what time (since I track scene dates and times too).
Is this something you'd like to do - or already do? If so, feel free to contact me in Facebook, Twitter or even in the comments below.