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.
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:
- Name your project table. Just click anywhere in the table, and up on the ribbon replace the generic name with Projects.
- 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.
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.
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).
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.