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.
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.
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.
Let's create a quick summary by our project titles.
- 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.
- 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.
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)!