Outline

How Bestselling Writers Outline (or Not) Their Novels

Few things divide writers more than the question of whether or not an outline is used in writing a novel. The spectrum of views on outlining shows that the best answer is the one that works for you. These books describe different approaches used by different authors to craft bestselling books.

By the Seat of Your Pants (No Outline)

Write by the seat of your pants, no plan, just dive in and tell a story. For some writers (sometimes referred to by the unflattering ‘pantsers'), this is the only way to write. Outlines feel restrictive and confining. An outline comes from the critical, rather than creative side of the writer's brain. 

Dean Wesley Smith is an enormously prolific bestselling author who advocates this approach. His book Writing into the Dark: How to Write a Novel Without an Outline covers the reasons why writers might choose to take this approach.

There are very few articles and books on how to just type in the first word and head off into the dark writing a novel with no plan, no character sketch, nothing but pure exploration.

This isn't a long book. Smith covers common myths about writing with an outline, the difference between creative and critical voice, the benefits for long-term writers, and hints on how to tackle a novel without an outline.

Whether you've written with or without outlines, this is a good place to start looking at other approaches.

Outline You Crazy Fools

As often as the debate comes up, there are always writers that fall into both sides. Typically writers are pretty mellow about the whole outline vs. no outline approach. Whatever works for you is fine. At the same time, each group is still quick to tout the perceived advantage of their method (or disadvantage of the other). 

Usually both sides lay claim to the same advantage. Case in point: In Smith's book, he makes the point that long-term novel writers get past the need for outlines, and are more productive as a result. Libbie Hawker, in Take Off Your Pants!, makes the same argument in favor of outlines saying that it is superior if your goal is a full-time writing career because it will increase your speed and volume of production. 

Hawker isn't alone in the outline space:

And many more! 

Can't We All Just Outline, or Not?

For the most part, writers recognize that different methods work for other writers. Some authors also write outline books that sit somewhere in the middle between no outline and a detailed outline. 

James Scott Bell, author of many books on writing and otherwise, introduces us to the “tweeners” that fall between plotters and pantsers. His solution to the conversation? Start in the middle! His take on the topic is Write Your Novel From The Middle: A New Approach for Plotters, Pantsers and Everyone in Between by James Scott Bell.

Another approach is detailed in Randy Ingermanson's How to Write a Novel Using the Snowflake Method. Although the snowflake method calls for what sounds like a very detailed outline, it's shorter than a 100-page detailed synopsis, so I guess it falls into this in between category? The Snowflake Method has been a popular feature on Ingermanson's website for many years. (You can also get a 50% discount on his Snowflake Pro software for buying the book).

Do I Outline? Do You?

I love reading books like these (and other writing books). It's fascinating to see how other authors approach writing. Over the years and many novels, I've written outlines for some books and I've written into the dark. For my current work-in-progress, I wrote a short 2-3 paragraph synopsis for each of the first three books in the series. I don't have detailed outlines. I am creating Bibliogalactica, an encyclopedia of characters, places, and all the other nifty bits from my novels. Some of that material is what people include in outlines. Even Dean Wesley Smith says that he sometimes outlines after he writes so he can keep track of the details. That's useful. I want to be able to pull up details about a character, a world, or a timeline when I start writing a new book.

What about you? Do you outline or not? Do you fall in between, or do you use different methods depending on the project? Share in the comments.

For the latest information, instruction, and thoughts, sign up for my newsletter and I'll email it to you when I get each issue out. It's always easy to unsubscribe.

Creating Graphs Dashboard

Creating Graphs for Word Count Tracking

Creating graphs is one way to visualize your writing progress. It can provide quick insights, particularly over time as you record more information about your word counts in your spreadsheet. If you're unfamiliar with using Excel, you may want to start with the previous posts in this series, starting with the benefits of writing streaks, demystifying spreadsheets, improving the tracker, pivoting for more information, adding project details, and writing a formula to track streaks.

Creating Graphs 101

If you've already worked through the previous posts in this series, you've already set everything up for creating graphs of your writing progress. Excel does make it easy to create really bad graphs, fortunately it is also easy to create good graphs. First rule in creating graphs, keep it simple

Forget 3D styles, colorful backgrounds, and all of that sort of thing. Most of the time those sorts of features obscures the data and makes it more difficult to gain useful insight from the visualization. Try to use less as often as you can when you create graphs. Though the field of data visualization is deep and can be complex, for our purposes we'll look at a few visualizations of the data in the word count tracker.

Dashboard

The dashboard is nothing more than a tab we'll use to show visualizations and information from our data. To get started, I'll add a new blank worksheet and double-click on it to name it Dashboard.

Think of this as a sandbox. We can add features, change things, and make this look however we like. I'm going to start adding features. I might move them around later, but for now I want to see a few basic details.

Quick Formulas

First off, I want to know how long my longest streak was. I'm going to type “Longest Streak” in the first column. Next to it, I want to add a formula that gets the highest number from my streak count column.

=MAX(Words[Streak])Excel Max Formula<img class=”tve_image wp-image-1124″ alt=”Excel Max Formula” width=”409″ height=”349″ title=”Excel Max Formula” data-id=”1124″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/max-formula.jpg” style=”width: 100%;”>

That's an easy formula. It uses the MAX function to get the highest number from the Words table and the Streak column. We can use the same formula to get the last day I wrote simply by changing the column referenced. As you type the formula, press TAB to accept the autocomplete suggestions (if you want). After entering the formula for the last day, it returns a number rather than the date. That's simply that the cell isn't formatted for the date.

With the number selected, choose the format you like from the dropdown list in the number section on the Home tab of the ribbon.

Creating Graphs With PivotCharts

Let's add a graph to the dashboard. On the ribbon's Insert tab, click the PivotChart button in the Charts section. It will open a dialog very much like what we saw previously when creating a PivotTable. The defaults should be fine, so click OK.

The PivotChart Fields panel works just like the PivotTable Fields panel. Since it's already using the data models, we have our tables available, and there's a box on the dashboard where it will draw our graphs.

I'm interested in seeing words written over time, so I'm going to drag the Date (Month) to the Axis, Words to the Values quadrant. I also want to add the Date (Year) to the Filters so that I can just set it to show this year.

Creating Graphs Dialog<img class=”tve_image wp-image-1126″ alt=”Creating Graphs Dialog” width=”387″ height=”346″ title=”Creating Graphs Dialog” data-id=”1126″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/Creating-Graphs-Dialog.jpg” style=”width: 100%;”>Creating Graphs PivotChart<img class=”tve_image wp-image-1128″ alt=”Creating Graphs PivotChart” width=”1448″ height=”886″ title=”Creating Graphs PivotChart” data-id=”1128″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/Creating-Graphs-PivotChart.jpg” style=”width: 100%;”>

Modifying the PivotChart

Now that I've created the PivotChart, I'm going to modify it, cleaning up some things so that it shows what I want. First, there are some things I'll turn off.

  • Field Buttons. Those are the ugly gray buttons on the chart. On the plus side, they can be used to change what is being displayed. I just don't like them (and there are other ways to change things). I'll right-click one and choose to turn them all off from the menu.
  • Legend. I'm only showing one thing—words. I don't need a legend. I'll turn it off. If I want to change it later, I can. Up on the ribbon, under PivotChart Tools, I'll click on Design. And then I'll click the down arrow under the Add Chart Element button (on the far left) to turn off the legend. This is also one place where I can turn it back on later if I want.
  • Chart Gridlines. Under the Add Chart Element button, I'll go to the gridlines section and deselect the horizontal gridlines.
  • Vertical Axis. The vertical axis displays the word counts. In the Axes section under Add Chart Element, I'm going to turn that off as well. Instead, I'll turn on Data Labels and choose outside end.
  • Gridlines. This isn't the chart, but the dashboard worksheet. By default, Excel shows gridlines around each cell. They don't print, but they make it easier to see each cell. I don't need them for my dashboard so I'll turn them off by clicking on the View tab in the ribbon and uncheck the gridlines box. While here, I'm going to uncheck the formula bar and the headings as well so that I have a very clean dashboard.
  • Border. The PivotChart has a border around it. I'll turn that off by right-clicking the chart and click the Format Chart Area command. That opens a panel full of formatting options. Right now, with Chart Options selected in the dropdown, I want to expand the Border option and change it to No line. Then I'll click the x for this panel to close it.

That's looking much better. I want to move the chart up on my dashboard, I have a few other tweaks to make.

I want to change the title from “Total” to “Words.” Click, click again and then replace the title. In the PivotChart Fields panel, next to Date (Year) I want to click the down arrow on the right (doesn't appear until the item is selected). That will open the filter panel. I'll select 2018 and click ok. The chart changes to show the months from this year only.

I only have data for this month right now, so that's the only month the chart shows. As I track more writing sessions I can refresh the chart to see the updated information. I have one more change to make. I want the value to use a comma. In the values quadrant, where it says Sum of Words, I'm going to click the down arrow and choose Value Field Settings.

In the dialog that opens, I'll click the Number Format button, I'll choose Number as the format, check the Use 1000 separator box and set the decimal count to zero. Then ok and ok again. Click off the chart to deselect it and hide the PivotChart Fields panel.

Dark Matters Cover<img class=”tve_image wp-image-699″ alt=”Writer T-Shirt” width=”478″ height=”457″ title=”Have Fun Write Now” data-id=”699″ src=”//ryanmwilliams.com/wp-content/uploads/2018/03/havefun.jpg” style=”width: 100%;”>

Creating Graphs by Duplicating the PivotChart

I spent time with that PivotChart to illustrate a few of the possibilities available. I also wanted to emphasize simplicity when creating graphs. This monthly graph of word count won't have very many columns. It's easier to put the exact count above each bar than to look at the vertical axis.

Creating Graphs Simplicity<img class=”tve_image wp-image-1132″ alt=”Creating Graphs Simplicity” width=”836″ height=”359″ title=”Creating Graphs Simplicity” data-id=”1132″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/Creating-Graphs-Simplicity.jpg” style=”width: 100%;”>

An easy way to create additional graphs is simply to select one that you already have and copy and paste a duplicate that you can modify. Use the PivotChart Tools section of the ribbon and modify the copy how you like. You can change the type of chart, the fields used in the chart, whatever you want. I created a new graph for the dashboard that show word count by the type of project.

On my dashboard, I put the pie chart right beneath the monthly word count. It gives me a quick view of the relative amount I've written on each type of project. If I wanted, I could have added a column to the monthly word count for each type. That would complicate that graph. Or, I could use a stacked column instead. That would show the same total words for the month, but the column would show the relative proportions for that time period.

If I don't stack them, I do want to change the color so that the blue of the “Novel” group isn't the same color as the total group.

Creating Graphs Pie<img class=”tve_image wp-image-1134″ alt=”Creating Graphs Pie” width=”442″ height=”302″ title=”Creating Graphs Pie” data-id=”1134″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/Creating-Graphs-Pie.jpg” style=”width: 100%;”>

Avoiding Unnecessary Graphs

The dashboard should provide useful information. It should make sense at a glance. Ultimately, it's for your use. Track what you want on the dashboard, but avoid unnecessary graphs. Pick those key pieces that you want and show those. Change it as your needs change. Experiment and have fun.

I think I've reached the end of this series. I could keep tweaking and adding additional functionality to the word count tracker but that's more time that I'm not spending writing! I plan to use the tracking spreadsheet to get back on track with my own writing streak.

Share questions and thoughts in the comments. If you'd like to get emails from me with new content, please sign up for my newsletter.

Writing Streak Formula

How to Calculate a Writing Streak in Excel

I talked about writing streaks at the beginning of this series, then went on to describe how to set up a basic word count tracking spreadsheet, added improvements to the spreadsheet, created a pivot table summary, and expanded the spreadsheet with project details. In this post, I calculate a writing streak in the word count tracking spreadsheet. I'll walk you through each step of creating the formula necessary.

Write a Formula to Calculate Your Writing Streak

You can use formulas in Excel to do all sorts of calculations. It's easier than you might think. I'll walk you through creating the formula to calculate a writing streak each step. It isn't all that complex and we'll take it one step at a time. First, let's look at what the result will look like so you see what we're working to create.

Calculate Writing Streak Result<img class=”tve_image wp-image-1094″ alt=”Calculate Writing Streak Result” width=”498″ height=”234″ title=”Calculate Writing Streak Result” data-id=”1094″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/calculate-writing-streak-result.jpg” style=”width: 100%;”>

If you've worked through this series of posts, you've seen this table. It's our Words table in the word count tracking spreadsheet—with an additional Streak column. You can see the results. With each day the streak count increases by one until it encounters a gap (e.g. 4/12 – 4/14), then it resets back to one. It isn't obvious, but if you have several writing sessions in the same day, the formula won't increase the streak count.

You could, of course, fill in the streak count manually by entering it yourself each time you added new word counts. But where is the fun in that? We might as well let the spreadsheet do the calculations for us. It's what spreadsheets do best.

Thinking It Through

We want the spreadsheet to calculate our writing streak for us, so we need to think through how that will work. Let's work out the concept.

  • If the current word count entry is the only entry, then the streak count is one.
  • If the current word count entry isn't the only entry, then we want to know if it is the next day or not. If it is, then we want to take the previous streak count and add one. If not, then the streak count is one—unless the previous entry was the same day. In that case the streak count is unchanged.

The assumption here is that we're counting any word count, and we're basing our streak on writing every day.

Dark Matters Cover<img class=”tve_image wp-image-751″ alt=”Write Faster T-Shirt” width=”466″ height=”460″ title=”writefaster” data-id=”751″ src=”//ryanmwilliams.com/wp-content/uploads/2018/03/writefaster.jpg” style=”width: 100%;”>

What IF? Calculating a Writing Streak

We need to compare the date of the current entry to the previous entry. Fortunately, Excel has a function that will help us. It's the IF function. 

=IF(Compare something, Show if TRUE, Show if FALSE)

This is the basis of our formula. The IF function has three parts, separated by commas. The first part compares something, in this case our dates. The next part is what Excel should show if the comparison is true, and the last part is what Excel shows if the comparison is false. Let's go through each part.

[@Date]-1=A26

The comparison takes the date from our word count entry, subtracts one day, and compares it to the previous row (A26 in this case). If the dates match, then the comparison is TRUE. If not, FALSE.

+F26+1

When the comparison is TRUE, Excel uses this simple calculation. It takes the previous streak count and adds one. That's it! Not too complicated. 

In the case that the comparison is FALSE, there are two cases we have to consider that came up when we thought through the calculation earlier. The previous entry date could be the same as this entry, e.g., multiple sessions in the same day. Or, it could just be a different day because there's a gap. We'll take advantage of another property of the IF function and nest a second IF function within our first. The calculation for the third part, the FALSE case, can also use other functions like IF.

IF([@Date]=A26,F26,1)

In this case, we're comparing the date of our entry to the date of the previous entry. We're just not subtracting one. If it's true, that is the dates match, then our streak count is the previous streak count. We don't add one. If the dates don't match there has been a gap and we restart the streak count at one for this entry.

The completed formula is:

=IF([@Date]-1=A26,+F26+1,IF([@Date]=A26,F26,1))

Creating the Streak Formula

Assuming you're following along in your own spreadsheet, let's look at how to add this formula. On your Data tab, go to the top of the Words table. If you have any filters selected, go ahead and clear them first.

Now click in cell F1 in your heading row and type Streak and hit ENTER. Excel expands the table to include the new column. Skip the first row and in the second row, start writing the formula by typing: =IF( at that point, use the arrow keys or the mouse to select the date for this entry. It will fill in the [@Date] part for you. Continue typing the formula with -1= and use the arrow keys or mouse to select the date cell in the first row.

Calculate Writing Streak Formula<img class=”tve_image wp-image-1100″ alt=”Calculate Writing Streak Formula” width=”691″ height=”137″ title=”Calculate Writing Streak Formula” data-id=”1100″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/streak-formula.jpg” style=”width: 100%;”>

Excel fills in the cell value. Type a comma, then use the up arrow or mouse to select the streak count in the first row and then type +1,IF( and select the date for this row again. Type = and select the date again for the first row, type a comma and select the streak count for the first row, another comma and then 1)).

Press ENTER.

When you hit ENTER, Excel defaults to creating a calculated column. It automatically adds the formula to all of the cells in the streak column, including new rows when you add additional entries. You don't have to write the formula again! Now, each time you add an entry Excel will calculate the streak count for you. You can keep your table filtered to the current week, month, and it will still calculate the streak count. As the number increases it gathers increasing weight.

How many days can you keep your streak going?

I want to start a new writing streak, so this is a helpful feature for me to add to the tracker. I mentioned in the previous post that there are still other features to add to the tracker, including visualizations and a dashboard. I'll work on those in upcoming posts. If you want to receive emails from me about new posts and other useful information for a writer with a day job (or not), go ahead and sign up for my newsletter. I'm not going to spam your inbox, my goal is simply to share useful information. You can always unsubscribe easily at any time. It's also a great way to show your interest or to ask questions.

Project Details

How to Add Project Details to the Word Count Tracker

I started this series on creating a word count tracking spreadsheet by talking about the benefits of writing streaks, how to set up a basic spreadsheet, added enhancements, and created a pivot table summary. If you haven't read those posts you may want to go back and take a look unless you're already comfortable with Excel. As a next step, we'll add project details in this post, which gives us additional information about our writing.

Deciding on Project Details

When you think about project details, consider what you want to know about each project. A basic list might include:

  • Title. We already have this on the Data tab, but we'll need to reference it for each project.
  • Genre. What genre is this project? If you only write in one, that may not be much of an issue. Or you may want to get specific and think about what two BISAC codes you want to use for this project.
  • Type. Is this a novel? Short Story? Screenplay?
  • Status. Where are you at with this project? Editing? Writing? Idea?

That's enough to start with—you can always add additional fields later if you decide it's necessary. We're going to create a project inventory list that we can use in our summary (or future summaries).

Add a Project Details Tab

Let's add a project details tab to our spreadsheet. Click the ‘+' sign in the tab row to insert a new tab. Double-click the tab and rename it Projects. Now we're going to create a new table (just like we did on the Data sheet). Go ahead and enter your detail headings in the first row, starting with “Title.” After entering each heading, go ahead and enter your titles in the Title column. 

Now that you have some information and the headings, you can click on any one of the titles and click the “Format as Table” button to create the new table. Pick the style, make sure to check the box “My table has headers” and then click OK.

Go ahead and fill in the details for each project. If you've added other fields, you'll have additional columns. And again, you can always come back and add more details as needed. When you start a new project, fill in the relevant details.

Project Details TableProject Details Table<img class=”tve_image wp-image-1074″ alt=”Project Details Table” width=”390″ height=”317″ title=”Project Details Table” data-id=”1074″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/project-details.jpg” style=”width: 100%;”>Project Details FilledProject Details Filled<img class=”tve_image wp-image-1075″ alt=”Project Details Filled” width=”420″ height=”138″ title=”Project Details Filled” data-id=”1075″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/project-details-filled.jpg” style=”width: 100%;”>

Linking the Project Details

Okay, now that we have project details for each project, we can link those over to our summary tab to add additional information about our writing. Two quick edits before we do that:

  1. Name your project table. Just click anywhere in the table, and up on the ribbon replace the generic name with Projects.
  2. Change Column B on the Data Tab. If you started with the earlier posts, on your Data table column B is labeled “Projects.” To make it easier for Excel to detect relationships between our tables, let's change that to Title. Since each table will have a field named “Title” Excel can detect that connection. We could link the tables without that, but it's an easy change to make.

Now click on the Summary tab.

Adding the Project Details Table

If your PivotTable Fields panel isn't showing, click anywhere on your summary pivot table. If it still isn't showing, click on the PivotTable Tools Analyze tab in the ribbon and click the Field List button on the right side of the ribbon.

By default, the Active section shows our Words table, but not the Projects table we just created. Right under the panel heading are two links, “Active | All.” Click the All link. You'll see the Projects table listed above a line and the Words table. Right-click the Projects table and click “Show in Active Tab.” Nothing changes visibly, but when you click the Active link above you'll see that you have access to your Projects fields as well.

Project Details PivotProject Details Pivot<img class=”tve_image wp-image-1079″ alt=”Project Details Pivot” width=”274″ height=”336″ title=”Project Details Pivot” data-id=”1079″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/project-details-pivot.jpg” style=”width: 100%;”>

Summarizing With Project Details

Let's change our summary and link our tables all in one operation. Drag type down to the Rows quadrant. You can put it above or below Title. Put it above the titles to summarize first by type.

In the pivot summary, all of the titles appear under each type. Not what we want! We want each type to filter the title list so only those that match that type show up. 

Over in the PivotTable Fields panel is a prompt about relationships between tables. This is why we made sure that the Words table and the Projects table both use the same heading Title.

Click the Auto-Detect button.

Excel shows a prompt that it may take several minutes to detect the relationships. Given the simplicity of our data, it only takes a moment. When it finishes, click the close button.

Project Details RelationshipsProject Details Relationships<img class=”tve_image wp-image-1080″ alt=”Project Details Relationships” width=”254″ height=”339″ title=”Project Details Relationships” data-id=”1080″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/project-details-relationship.jpg” style=”width: 100%;”>Dark Matters CoverWrite Faster T-Shirt<img class=”tve_image wp-image-751 tve_evt_manager_listen tve_et_mouseover” alt=”Write Faster T-Shirt” width=”466″ height=”460″ title=”writefaster” data-id=”751″ src=”//ryanmwilliams.com/wp-content/uploads/2018/03/writefaster.jpg” style=”width: 100%;” data-tcb-events=”__TCB_EVENT_[{&quot;t&quot;:&quot;mouseover&quot;,&quot;config&quot;:{&quot;anim&quot;:&quot;grow&quot;,&quot;loop&quot;:0},&quot;a&quot;:&quot;thrive_animation&quot;}]_TNEVE_BCT__”>

Using the Project Details

Now we can use the project details to change our summary. With type in the row's quadrant, we can see which titles match each type, and the related word counts. If you added the date (year) to the columns quadrant, you can see that information by year.

If you click the little -/+ symbol next to each type label, you can collapse the display to hide the titles. Or simply uncheck titles from the Words table in the field list. Or drag it from the Rows quadrant (there are usually several ways to do things). 

Project Details SummaryProject Details Summary<img class=”tve_image wp-image-1082″ alt=”Project Details Summary” width=”272″ height=”229″ title=”Project Details Summary” data-id=”1082″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/project-details-summary.jpg” style=”width: 100%;”>

Experiment!

Try different fields. You can add fields to the Filters quadrant to add a filter to your pivot summary. You can stack fields in the rows quadrant so that you can drill down. Want to see things by status? Add that. 

Now that we have the Projects table, we can add additional columns if there's something else we want to track, and we can use that information in our summary to gain additional insights into our writing.

You're also free to create additional pivot tables if you have some standard views that you want to see and don't want to have to keep changing things to see those views. Quick tip: Right-click the Summary tab, click on “Move or copy” and check the box, “Make a copy”. It'll duplicate the summary tab and then you can customize that view, change the name of the tab, whatever you want.

Next Steps With the Word Count Tracker

What's next? At this point, we can track word counts and project information, summarize it in different ways, expand the information (e.g., add Series to the Project details tab), and filter our information. I've got the date filter set on my Data tab to show “This week” so it automatically updates and only shows me the current week. If I don't write it won't show any data!

The tracker still doesn't report on writing streaks! That's something to fix. We might also want to add some visualizations in the form of Pivot Charts to view our data in a different way. And we might want to pull several different pieces together to create a writing dashboard. Things to tackle in the future!

If you want the latest news and information, please sign up for my newsletter. 

Pivot Tables

Writers Pivot Tables Too, Further Word Tracking Enhancements

This series started by talking about streaks, went on to spreadsheet basics in creating a tracking spreadsheet, and then leveled up the spreadsheet with an Excel table. In this post, I'll show you how to use pivot tables for greater insights into your writing. Yes, writers pivot tables too—it's an easy way to discover different ways to look at your word counts.

Discovering Pivot Tables

Pivot tables in Excel summarize information. In the last post, I talked about using the filters to narrow what is displayed in the tracking spreadsheet and to change the total formula from a total word count to an average. By changing filters you could check on totals or average word counts for different time periods or projects. With pivot tables you can do that easily across multiple projects and date ranges.

Creating Pivot Tables

I've gone ahead and pulled some word count data from some past projects so that I have more data to work with for this demonstration. I have some word counts tracked at different times, and various projects.

To create the pivot table, I'll click anywhere in my tracking table.

Tip: On the Table tab, there's a field to give the table a name. By default, Excel names it something like “Table1” but you can change the name. It's optional, but I'm going to rename mine “Words.”

Right next to the name field, is a command, “Summarize with PivotTable.” Click that button to start the process.

Excel Tracking Pivot Tables<img class=”tve_image wp-image-1053″ alt=”Excel Tracking Pivot Tables” width=”416″ height=”658″ title=”Excel Tracking Pivot Tables” data-id=”1053″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-tracking.jpg” style=”width: 100%;”>

Defining the Pivot Table

A dialog box appears with options to set up your pivot table. The default options are just fine. You'll see that it already has your table listed in the Table/Range field, and the section below that has “New Worksheet” set as the location. In the final section is a check box, “Add this data to the Data Model.” Though not necessary right now, go ahead and check that box. We won't use it in this post, but in a future post it will help with analyzing multiple tables.

A new worksheet will be created. We'll use this worksheet to summarize the data. If you like, you can change the names of your worksheets from “Sheet1” and “Sheet2” to something else, e.g. “Data” and “Summary,” respectively. 

Create Pivot Tables<img class=”tve_image wp-image-1055″ alt=”Create Pivot Tables” width=”387″ height=”346″ title=”Create Pivot Tables” data-id=”1055″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/create-pivot-table.jpg” style=”width: 100%;”>Dark Matters Cover<img class=”tve_image wp-image-699 tve_evt_manager_listen tve_et_mouseover” alt=”Writer T-Shirt” width=”478″ height=”457″ title=”Have Fun Write Now” data-id=”699″ src=”//ryanmwilliams.com/wp-content/uploads/2018/03/havefun.jpg” style=”width: 100%;” data-tcb-events=”__TCB_EVENT_[{&quot;t&quot;:&quot;mouseover&quot;,&quot;config&quot;:{&quot;anim&quot;:&quot;grow&quot;,&quot;loop&quot;:0},&quot;a&quot;:&quot;thrive_animation&quot;}]_TNEVE_BCT__”>

Analyzing the Data with Pivot Tables

On the left side of the sheet is a section labeled “PivotTable1” and on the right side is a panel labeled “PivotTable Fields.” The PivotTable1 section will display our summary information, and we can used the Fields panel to change what is displayed.

Pivot Tables in Excel<img class=”tve_image wp-image-1057″ alt=”Pivot Tables in Excel” width=”1024″ height=”627″ title=”Pivot Tables in Excel” data-id=”1057″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/pivot-table-excel-1024×627.jpg” style=”width: 100%;”>

Let's create a quick summary by our project titles.

  1. Click and drag the Project field down to the Rows quadrant. As soon as you do, the PivotTable1 section changes to show the titles in our tracking spreadsheet, one per row.
  2. Click and drag the Words field to the Values quadrant. The summary changes to show the total words for each project.

We can improve the summary further by changing the column headings. To do so, click first on “Row Labels” and type “Titles.” Then click on “Sum of Words” and simply type “Words.” You may want to right-align the “Words” heading using the alignment icon on the Home tab. Lastly, I like a comma in the thousandths place, so over in the Values quadrant I'll click the down arrow and choose “Value Field Settings.” This dialog lets us change the formula (just like we did with the total formula), and it also let's us format the numbers.

I'll click the Number Format button, choose Number for the format, check the box for the comma separator and change the decimal places to 0. Click Ok twice, and now we have a summary by title with word counts.

Pivot Tables Summary<img class=”tve_image wp-image-1062″ alt=”Pivot Tables Summary” width=”336″ height=”228″ title=”Pivot Tables Summary” data-id=”1062″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/pivot-tables-summary.jpg” style=”width: 100%;”>Pivot Tables Fields<img class=”tve_image wp-image-1061″ alt=”Pivot Tables Fields” width=”263″ height=”575″ title=”Pivot Tables Fields” data-id=”1061″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/pivot-tables-fields.jpg” style=”width: 100%;”>

Exploring Pivot Tables For Greater Insights

Pivot tables in Excel is an incredibly powerful feature with many more features than this brief look can cover. Try dragging the date field to the Columns quadrant. What happens? Explore. There are also powerful filter options that can be used with pivot tables, custom fields, analyzing multiple tables, and so much more! At the very least, this summary offers a quick way to show your titles, their word counts, yearly counts (did you try dragging the date field to the columns quadrant?), and a grand total.

Refreshing Pivot Tables

One more quick point. What happens when you've added more rows to your data tracking table? Nothing—at least when you first add the row. To refresh the summary, go up to the PivotTable Tools tab on the ribbon and click Analyze, then click the Refresh button right in the middle. That will update the pivot table with the new information.

With that, I'm going to wrap up this segment. We've taken the word count spreadsheet from a basic list to a formatted table, to a powerful pivot table summary. Experiment with different ways to display the information and have fun! In future posts, we'll start adding even more features to our word count tracking spreadsheet, such as project information, visualizations and much more!

If you're enjoying this series, or have questions, please let me know in the comments or sign up for my email newsletter for more productivity, tech, craft, and other insights for the writer with a day job (or without)!

Word Count Spreadsheet

Improving Our Word Count Spreadsheet

In the last post, I went over the basics of creating a simple word count spreadsheet. If you're not familiar with spreadsheets, it's a good place to start. As simple as the spreadsheet is, it provides a foundation for other improvements. Recording the data is only the first step. With the improvements to the word count spreadsheet in this post, we'll start to see how it can be used to provide more information about our writing.

Level Up the Word Count Spreadsheet

I'm going to pick up with the word count spreadsheet created in the last post. Right now it makes it easy to enter the ending word count after each session and it calculates the finished words written. If you wrote 250 words and deleted 200 of them, it will only show 50 words written because that was your net gain. It could even be negative if you went back and deleted words previously written! I'm focused on progress each day, so that's fine.

Still, the spreadsheet doesn't show me more information. If I have multiple projects, I can't see totals for each without going through the list. It doesn't tell me how many words are written on average. It won't track streaks. And it doesn't have any information except the title of the project. If I want to use the word count spreadsheet to gain insights about my productivity, then I need to make some changes.

I'm using the current version of Excel to create this word count spreadsheet. You can do many of the same things in other products, but the commands will be different.

Format as Table

Here's a tip to spruce up your word count spreadsheet—and add great functionality at the same time!

First, click on one of the cells where you've put in information, such as the title or word count. Next, up on the Home tab of the ribbon (the bar with the icons at the top of Excel), in the Styles section, click the Format as Table button.

A drop-down panel will give you options to pick for formatting your word count spreadsheet. Pick the scheme you like by clicking it. Excel will automatically select the information you've entered. Make sure the box is checked that says “My table has headers” and click OK.

Just like that, Excel formats your spreadsheet! This is called a table in Excel.

Even better, when you add the next entry in the row underneath, it will automatically extend the table to include the new information. You can keep adding rows simply by typing in new entries—just don't skip rows! It'll also automatically copy formulas to the new row.

Excel Format as Table&amp;lt;img class=”tve_image wp-image-1027 jetpack-lazy-image–handled” alt=”Excel Format as Table Word Count Spreadsheet” width=”189″ height=”130″ title=”Excel Format as Table” data-id=”1027″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-format-table.jpg” data-lazy-loaded=”1″&amp;gt;&amp;amp;amp;lt;img class=”tve_image wp-image-1027″ alt=”Excel Format as Table Word Count Spreadsheet” width=”189″ height=”130″ title=”Excel Format as Table” data-id=”1027″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-format-table.jpg”&amp;amp;amp;gt;Excel Formatted Word Count Spreadsheet&amp;lt;img class=”tve_image wp-image-1028 jetpack-lazy-image–handled” alt=”Excel Formatted Word Count Spreadsheet” width=”435″ height=”342″ title=”Excel Formatted Word Count Spreadsheet” data-id=”1028″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-formatted.jpg” data-lazy-loaded=”1″&amp;gt;&amp;amp;amp;lt;img class=”tve_image wp-image-1028″ alt=”Excel Formatted Word Count Spreadsheet” width=”435″ height=”342″ title=”Excel Formatted Word Count Spreadsheet” data-id=”1028″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-formatted.jpg”&amp;amp;amp;gt;

Totally Your Table

Okay, now we have a table in our word count spreadsheet. Other than adding some formatting, what good is this?

To start, we can add some additional features to make the table more interesting. Let's start by adding a total at the bottom of the word count column. All we have to do is click on the Table Tools Design tab on the ribbon. Right in the middle is a section called “Table Style Options.” Check the Total Row box.

Excel automatically adds a total row to the bottom of the table and adds a formula totaling the Words column for us. If we don't want to see it anymore (say we want to add a new entry), we can uncheck the box to hide the total row. After we add our entry we can check the box again to bring it back.

Hint

If you don't see the tab for the table on the ribbon, click on any of the cells in the table and the tab should show up.

Dark Matters Cover&amp;lt;img class=”tve_image wp-image-751 jetpack-lazy-image–handled” alt=”Write Faster T-Shirt” width=”466″ height=”460″ title=”writefaster” data-id=”751″ src=”//ryanmwilliams.com/wp-content/uploads/2018/03/writefaster.jpg” style=”width: 100%;” data-lazy-loaded=”1″&amp;gt;&amp;amp;amp;lt;img class=”tve_image wp-image-751″ alt=”Write Faster T-Shirt” width=”466″ height=”460″ title=”writefaster” data-id=”751″ src=”//ryanmwilliams.com/wp-content/uploads/2018/03/writefaster.jpg” style=”width: 100%;”&amp;amp;amp;gt;

Filtering Your Word Count Table

You may have noticed the arrow buttons in your top row with the headings. Those buttons allow you to filter what shows in your table. You can use them to show only the current month or other date ranges. You can filter to just the project that you want to see. Combined with the total row, this gives you an easy way to see how many words you've written within a given time period or on different projects.

The filters give you different options. If Excel detects that the column contains dates, you'll see that it has a variety of predefined filters, e.g., choose “This Week” to see only items from the current week. Or “Last Week” to see entries from the previous week.

When you filter a column the other items are hidden from view—but you can clear the filter or change it at any point to see other items. That means you never have to see a big list of entries. You could set the date filter to this week and add items as you write. When the week passes it will show the current week as if there are no entries until you add more, but the whole time all of your previous entries are safe.

If you turn on the total row, it will show you the total for only those items showing.

Just like the date column, the Project column filter allows you to filter by the contents of that column. Instead of Date filters it will show Text filters. Either way there is also a search box in the dropdown and a treeview you can use to display and uncheck or check different entries. 

Excel Table Filters&amp;lt;img class=”tve_image wp-image-1034 jetpack-lazy-image–handled” alt=”Excel Table Filters” width=”450″ height=”846″ title=”Excel Table Filters” data-id=”1034″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/Screenshot-2018-04-11-16.00.02.png” style=”width: 100%;” data-lazy-loaded=”1″&amp;gt;&amp;amp;amp;lt;img class=”tve_image wp-image-1034″ alt=”Excel Table Filters” width=”450″ height=”846″ title=”Excel Table Filters” data-id=”1034″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/Screenshot-2018-04-11-16.00.02.png” style=”width: 100%;”&amp;amp;amp;gt;

Average Word Counts

I want to show one more thing for this post. There's still many more ways we can enhance the word count spreadsheet, but I'll do another post for the next step. First, I want to revisit that total line at the bottom of the table. Turn it on and click the that adds up the total words written (for the displayed entries). In the formula bar above the column headings, you can see the formula used to calculate the total.

=SUBTOTAL(109,[Words])

The formula uses the subtotal function. A function allows Excel to perform various types of calculations and other operations on the data entered into the cells. Subtotal can perform different actions depending on the function number, e.g. 109 in this case, which is a SUM function, meaning it will add up the items in the [Words] column. We didn't have to write the formula because Excel automatically created it when we turned on the total row the first time.

You may notice that the total cell also has a dropdown arrow button on the right side. If you click that it will open a menu of other functions you can use instead. I'm going to click “Average.”

Excel Table Average&amp;lt;img class=”tve_image wp-image-1037 jetpack-lazy-image–handled” alt=”Excel Table Average” width=”482″ height=”398″ title=”Excel Table Average” data-id=”1037″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-table-avg.jpg” style=”width: 100%;” data-lazy-loaded=”1″&amp;gt;&amp;amp;amp;lt;img class=”tve_image wp-image-1037″ alt=”Excel Table Average” width=”482″ height=”398″ title=”Excel Table Average” data-id=”1037″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-table-avg.jpg” style=”width: 100%;”&amp;amp;amp;gt;

Now, instead of showing the total word count, it shows the average word count which tells me that I've written 505 words on average per day. By clicking the dropdown arrow button again and picking SUM, it will change back to the total. You'll see that you have other functions available. You can also write your own formulas to use in the total row. 

One more tip: If you want the name of the row to be something other than “Total,” just click in that cell and type what you want it to say. Since I want my word count spreadsheet to show the average words written, I'll change that to Average. Excel remembers your changes, so you can still turn the total row on and off.

Next Level Word Count Spreadsheet

You can think of each of these posts as quests. This series of quests started with a look at streaks, moved on to spreadsheet basics, and now we've added formatting, filtering, and totals or averages to our tracker. It's very functional right now. We haven't maxed out the level yet! We can add even more functionality to the spreadsheet to give us greater insight into our writing and productivity. Check back for the next post. You're also welcome to sign up for my newsletter to find out when I post more content to help you in your publishing journey as a writer with a day job (or even if you don't have a day job).

Tracking Word Counts

Tracking Word Counts Even If You’re Not Familiar With Spreadsheets

I love data and data tools. Some of my fellow librarians find this amusing—and useful! I find data very useful. This isn't usually considered odd in athletic pursuits. People routinely track and discuss data about their favorite sports teams. If you're a runner you probably know how far, how long, and at what pace you ran. There's a good chance you track that information. Wearable tech has made it easier than ever to track our athletic efforts, visualize the data, and inform our attempts to improve. In my last post, I talked about the power of streaks. Today, I want to share some tips on using simple spreadsheets for tracking your word counts.

Tracking Word Counts Simply

I'll start simply. You can use whatever spreadsheet program you prefer. If you haven't used any, Microsoft Excel and Google's Sheets are both good options. I'm going to use the desktop version of Excel and keep this simple to start. I'll walk through creating a simple spreadsheet and then mention some ways that it could be enhanced.

Spreadsheet Basics

Don't be intimidated by a spreadsheet. It can do so much work for you and can be used in lots of different ways to help you with your writing. A spreadsheet can help improve productivity. It has numerous business uses. And you can use it as tool in your creative process as well. I'm going to focus on tracking word counts, but many of the points covered can be used in other ways.

Here's a blank spreadsheet in Excel (Office 365, current version):

Blank Excel Spreadsheet<img class=”tve_image wp-image-1004 jetpack-lazy-image–handled” alt=”Blank Excel Spreadsheet” width=”1024″ height=”627″ title=”Blank Excel Spreadsheet” data-id=”1004″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel_blank-1024×627.jpg” data-lazy-loaded=”1″>&amp;lt;img class=”tve_image wp-image-1004″ alt=”Blank Excel Spreadsheet” width=”1024″ height=”627″ title=”Blank Excel Spreadsheet” data-id=”1004″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel_blank-1024×627.jpg”&amp;gt;

The spreadsheet contains a bunch of boxes known as cells, defined by the rows (numbered down the left side), and the columns (letters across the top). You can click in any cell and type text or numbers. The spreadsheet has a bunch of ways to format the cells, to change the size, and do math based on the cell contents. We're going to start small and add functionality.

What should we track?

Okay, so we want to track word counts. What do we need to know in order to track word counts? It's up to you, but I'd want to know a few details.

  • Date. When did I write the words?
  • Project. What project was I working on? I usually have several projects going at once between stories and novels.
  • Words. How many words did I write? This one could be done in a few different ways. We could track just the number of words written and figure that out myself. I usually like the spreadsheet to calculate that for me.

That's enough for now! Let's get started on creating the spreadsheet. I'm going to click in the top left cell, in row one, column A. The address of this cell is A1. I'm going to type some headings, one per column for each of the things I want to track. The end result should look something like the picture on the right.

Excel Headings<img class=”tve_image wp-image-1006 jetpack-lazy-image–handled” alt=”Excel Headings” width=”463″ height=”280″ title=”Excel Headings” data-id=”1006″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel_headings.jpg” data-lazy-loaded=”1″>&amp;lt;img class=”tve_image wp-image-1006″ alt=”Excel Headings” width=”463″ height=”280″ title=”Excel Headings” data-id=”1006″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel_headings.jpg”&amp;gt;

Entering word counts

Now all I have to do to track my word counts is type the details about each writing session in the cells below the headings.

Excel automatically formats information you type. So when I typed “4/5” in cell A2 and pressed the TAB key (moves the selection box to the next cell, ENTER will move it down one cell), Excel reformatted the date. 

Under Project, I typed “Untitled Short Story” and it looks like it is cut off. It's all in the cell still, but because the word count cell has a number in it, the full project title doesn't show. 

We can change the formatting in different ways to change the way Excel displays the information. There are different ways to change the formats. I'm not going to go into them all (and they may differ depending on what you are using). 

I do want to change the date format. To do that, I'm going to click in the first cell, hold down the mouse button and drag down to the last cell.

On the ribbon (the bar at the top with the icons and commands), I'll click in the dropdown list that currently says “Custom” and choose “Short Date” instead. There are many other formats available, but I like this one.

Next, I'll format the word counts. I want a comma in the thousandth place. I'll select the word counts and right under the dropdown list, click the icon with a comma. Then, with the entries still selected, I'll click the decrease decimal icon (to the right of the comma) twice. I don't want to show any decimal places in my word counts.

All of this is just setup. Once we have our spreadsheet ready we won't have to mess with the format again unless we want to change things.

I do want to see the entire title. To do that, I want to make column B wider. All I need to do is a double-click on the line between “B” and “C” and the column will automatically fit the length of the title. I can repeat that later if I have a longer title.

At a very basic level, that's all that is needed! I can track my word counts now. There's much more that I could do with the spreadsheet to improve how it works.

Excel Data Entry Tracking Word Counts<img class=”tve_image wp-image-1008 jetpack-lazy-image–handled” alt=”Excel Data Entry Tracking Word Counts” width=”376″ height=”328″ title=”Excel Data Entry” data-id=”1008″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel_data.jpg” data-lazy-loaded=”1″>&amp;lt;img class=”tve_image wp-image-1008″ alt=”Excel Data Entry Tracking Word Counts” width=”376″ height=”328″ title=”Excel Data Entry” data-id=”1008″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel_data.jpg”&amp;gt;Excel Date Format<img class=”tve_image wp-image-1009 jetpack-lazy-image–handled” alt=”Excel Date Format” width=”382″ height=”630″ title=”Excel Date Format” data-id=”1009″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-date-format.jpg” data-lazy-loaded=”1″>&amp;lt;img class=”tve_image wp-image-1009″ alt=”Excel Date Format” width=”382″ height=”630″ title=”Excel Date Format” data-id=”1009″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-date-format.jpg”&amp;gt;

Improving Our Tracking Spreadsheet

The first thing I want to do is have the spreadsheet calculate the number of words written. I'm going to do this by replacing the word count column with a starting count, an ending count, and let the spreadsheet figure the words written. I'll right-click on the column letter “C” and choose Insert to create a new column “C.” My column with the heading “Words” will move over and become “D.” I'll repeat this to add one more column.

That gives me two columns without headings, so I'll type “Start” and “End” on row one in the new “C” and “D” columns (“Words” is now column “E”).

As I did the last time, I'll format those cells with the comma icon and remove the decimals (I don't have to have anything typed to set the format). Now, instead of entering the total words, I'll enter the starting word count and the total word count when I stop writing—which I can easily find out in whatever program I'm using to write.

Creating Our First Formula

Let's create a formula so that Excel calculates the difference between the start and end counts in each cell. In cell E2, I'll type +D2-C2 and press ENTER. As I do, Excel highlights the cells included in the formula. When I press ENTER the formula disappears, replaced by the answer!

If you look in the address bar just above the column headings, you'll see the formula as we typed it except it starts with an “=” equal sign. You can start a formula by typing “=” and for some formulas that's necessary. In this case Excel understood we were writing a formula from the “+” symbol and added the equal sign on its own.

Now that I have the formula in one cell, all I have to do is click and drag down on the square box in the lower right corner of E2. The mouse pointer will change to a cross symbol and when you let go after selecting all the rows, Excel fills the cells with the formula! Except it has adjusted the formula addresses to match each row number. 

Excel Tracking Word Counts Formula<img class=”tve_image wp-image-1013 jetpack-lazy-image–handled” alt=”Excel Tracking Word Counts Formula” width=”470″ height=”329″ title=”Excel Tracking Word Counts Formula” data-id=”1013″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-formula.jpg” data-lazy-loaded=”1″>&amp;lt;img class=”tve_image wp-image-1013″ alt=”Excel Tracking Word Counts Formula” width=”470″ height=”329″ title=”Excel Tracking Word Counts Formula” data-id=”1013″ src=”//ryanmwilliams.com/wp-content/uploads/2018/04/excel-formula.jpg”&amp;gt;

Next Steps in Tracking Word Counts

This simple spreadsheet is only the start for tracking word counts. We can add to it so that Excel automatically formats our spreadsheet, calculates totals, average word counts, writing streaks, and much more! That's for another post. If you have questions, ask in the comments.

I'll also point out (being a librarian), that many libraries offer courses such as Lynda.com and Microsoft Imagine Academy which can help you achieve mastery of Excel and other programs. In our library we also offer the opportunity for people to take the Microsoft certification tests for free! 

If you want to receive updates, sign up for my newsletter.