Improve Your Writing With The “Punctuate This” Challenge

by Ryan

I got this writing exercise from one of my mentors, Kristine Kathryn Rusch, at a workshop I did a few years back. I found it very instructive. It's simple to explain, and much harder to do.

Punctuate This | Rules

The concept is simple enough.

Open the Excerpt section below. It contains a short excerpt from a book—but only the text. No punctuation. No paragraphing. No capitalization. Nothing except a straight block of text. I did keep the spaces.
Copy the excerpt into your word processor. You can do that either by typing or just select, copy, and paste.
Add the missing punctuation and paragraphing.
After you finish, go ahead and expand the Original section to compare your punctuation and paragraphing with the original work.

I've also included links to the original books in the examples, if you'd like to get a copy. These are affiliate links, so I get a small percentage from purchases, which I really appreciate.

Punctuate This | Questions

As you study the results and compare your work with the original work, consider some of these questions. You may want to write your thoughts down, if that helps.

How do the two different versions sound? What are the differences in the voice of the piece? The character's voice?
What differences are there in the pacing of the two different versions? Is one faster or slower than the other?
In what ways does the punctuation and paragraphing reveal character? Setting?
What else strikes you about the differences between the versions?

Check back. I'll add more examples for you to try in the future. If you want to learn when I've added more, or added other information to the site, sign up for Readinary, my readers' group.

Punctuate This | Exercises

Guilty Pleasures by Laurell K. Hamilton

Exercise

i took his hand his long fingers curved over the back of my hand we began walking, his hand very still in mine i could feel the pulse in my hand against his skin his pulse began to speed up to match mine i could feel his blood flow like a second heart have you fed tonight my voice sounded soft can you not tell i can never tell with you i saw him smile out of the corner of my eye i am flattered you never answered my question no he said no you havent answered me or no you havent fed he turned his head to me as we walked sweat gleamed on his upper lip what do you think ma petite his voice was the softest of whispers

Original

I took his hand. His long fingers curved over the back of my hand. We began walking, his hand very still in mine. I could feel the pulse in my hand against his skin. His pulse began to speed up to match mine. I could feel his blood flow like a second heart.

“Have you fed tonight?” my voice sounded soft.

“Can you not tell?”

“I can never tell with you.”

I saw him smile out of the corner of my eye. “I am flattered.”

“You never answered my question.”

“No,” he said.

“No, you haven't answered me, or no, you haven't fed?”

He turned his head to me, as we walked. Sweat gleamed on his upper lip. “What do you think, ma petite?” His voice was the softest of whispers.

I read Guilty Pleasures for the first time (of many) years ago when it was still an original Ace paperback. I devoured the series, which at the time I discovered it was only the first few books. Since then, I've collected the books in hardcover, ebook, and paperback formats. I've listened to books. And I made it a point to read all of Laurell K. Hamilton's other books.

I have the latest Anita Blake book, Serpentine, on my stack of books to read. I'm not a fan of the hardware covers. It seems like the publishers have always struggled with how to brand the series. Is it horror? Romance? Erotica? They can't seem to figure it out and they keep changing the branding so the hardcovers don't match. Sigh.

A Long Way to a Small, Angry Planet by Becky Chambers

Exercise

as she woke up in the pod she remembered three things first she was traveling through open space second she was about to start a new job one she could not screw up third she had bribed a government official into giving her a new identity file none of this information was new but it wasnt pleasant to wake up to she wasnt supposed to be awake yet not for another day at least but that was what you got for booking cheap transport cheap transport meant a cheap pod flying on cheap fuel and cheap drugs to knock you out she had flickered into consciousness several times since launch surfacing in confusion falling back just as shed gotten a grasp on things the pod was dark and there were no navigational screens there was no way to tell how much time had passed between each waking or how far shed traveled or if shed even been traveling at all the thought made her anxious and sick

Original

As she woke up in the pod, she remembered three things. First, she was traveling through open space. Second, she was about to start a new job, one she could not screw up. Third, she had bribed a government official into giving her a new identity file. None of this information was new, but it wasn't particularly pleasant to wake up to.

She wasn't supposed to be awake yet, not for another day at least, but that was what you got for booking cheap transport. Cheap transport meant a cheap pod flying on cheap fuel, and cheap drugs to knock you out. She had flickered into consciousness several times since launch—surfacing in confusion, falling back just as she'd gotten a grasp on things. The pod was dark, and there was no way to tell how much time had passed between each waking, or how far she'd traveled, or if she'd even been traveling at all. The thought made her anxious, and sick.

I discovered Becky Chambers' A Long Way to a Small Angry Planet when I read somewhere the description. “Like Firefly — with aliens.” It sounded like something I would like!

I enjoy science fiction with alien species, interplanetary space travel, and interesting characters. This book had all of that and much more. I became an instant fan after reading the first book and picked up each that followed as soon as I could.

A Dangerous Road: A Smokey Dalton Novel by Kris Nelscott

Exercise

the rioting is finally over and the fires have burned out washington dc is a blackened ruin and so are the west and south sides of chicago pittsburgh newark hartford and trenton have all suffered serious damage so have many other cities jimmy and i drive the green oldsmobile that belonged to henrys church and listen to the news we hardly speak to each other any more there isnt much to say martin luther king jr is dead assassinated in our home town in our neighborhood and both jimmy and i played small roles in his death inadvertent roles of course but roles nonetheless

Original

The rioting is finally over, and the fires have burned out. Washington, D.C., is a blackened ruin, and so are the west and south sides of Chicago. Pittsburgh, Newark, Hartford, and Trenton have all suffered serious damage. So have many other major cities.

Jimmy and I drive the green Oldsmobile that belonged to Henry's church and listen to the news. We hardly speak to each other any more. There isn't much to say. Martin Luther King, Jr., is dead, assassinated in our home town, in our neighborhood, and both Jimmy and I played small roles in his death. Inadvertent roles, of course, but roles nonetheless.

I remember reading A Dangerous Road and thinking that, “I wish I could write a book this good.”

Kris Nelscott wrote a mystery set against a time of protest and progress in America, opening following the assassination of Martin Luther King, Jr. It's not the sort of book that I am likely to write. At least right now. I write mysteries, just not historical mysteries. My Moreau Society series is science fiction. My C. Auguste Dupin mysteries are cozy stories with a cat as the detective. I might write historical mysteries someday. If I do, I'd love to write something so amazing.

Project Pope by Clifford D. Simak

Exercise

thomas decker was half an hour from home when whisperer stopped him in his tracks decker said whisperer speaking inside deckers mind decker now ill get you this time i will get you decker swiveled about on the game trail he had been following his rifle raised held away from his body ready to snap to his shoulder against the first sign of danger there was nothing in sight nothing stirring the heavy growth of trees and brush came down close against the trail on either side it all hung motionless there was not the slightest breeze, no flicker of a bird there was absolutely nothing everything was frozen as if eternity had clamped down decker

Original

Thomas Decker was half an hour from home when Whisperer stopped him in his tracks.

—Decker, said Whisperer, speaking inside Decker's mind. Decker, now I'll get you. This time I will get you.

Decker swiveled about on the game trail he had been following, his rifle raised, held away from his body, ready to snap to his shoulder against the first sign of danger.

There was nothing in sight, nothing stirring. The heavy growth of trees and brush came down close against the trail on either side. It all hung motionless. There was not the slightest breeze, no flicker of a bird. There was absolutely nothing. Everything was frozen, as if eternity had clamped down.

—Decker!

“Robot believers at the far end of the galaxy endeavor to create a true religion, but their efforts could be shattered by a shocking revelation.

Project Pope highlights many of the themes and concepts found in Simak's work. I reread this book not all that long ago and enjoyed revisiting the characters and the questions raised by this story.

Cujo by Stephen King

Exercise

he heard its purring growl he smelled its sweet carrion breath tad trenton clapped his hands to his eyes hitched in breath and screamed a muttered exclamation in another room his father a scared cry of what was that from the same room his mother their footfalls running as they came in he peered through his fingers and saw it there in the closet snarling promising dreadfully that they might come but they would surely go and that when they did the light went on vic and donna trenton came to his bed exchanging a look of concern over his chalky face and his staring eyes and his mother said no snapped i told you three hot dogs was too many vic and then his daddy was on the bed daddys arm around his back asking him what was wrong tad dared to look into the mouth of his closet again the monster was gone instead of whatever hungry beast he had seen there were two uneven piles of blankets winter bedclothes which donna had not yet gotten around to taking up to the cut off third floor

Original

He heard its purring growl; he smelled its sweet carrion breath.

Tad Trenton clapped his hands to his eyes, hitched in breath, and screamed.

A muttered exclamation in another room—his father.

A scared cry of “What was that?” from the same room—his mother.

Their footfalls, running. As they came in, he peered through his fingers and saw it there in the closet, snarling, promising dreadfully that they might come, but they would surely go, and that when they did—

The light went on. Vic and Donna Trenton came to his bed, exchanging a look of concern over his chalky face and his staring eyes, and his mother said—no, snapped, “I told you three hot dogs was too many, Vic!”

And then his daddy was on the bed, Daddy's arm around his back, asking him what was wrong.

Tad dared to look into the mouth of the closet again.

The monster was gone. Instead of whatever hungry beast he had seen, there were two uneven piles of blankets, winter bedclothes which Donna had not yet gotten around to taking up to the cut-off third floor.

I love Stephen King's work. I've gone back and reread his books. In Cujo, King creates a character, a monster, that sticks in popular culture alongside other horror movie creatures. The book shows off King's talents for creating characters and intense situations. It isn't the easiest book to read, it hits a bunch of triggers for me as a parent and as a pet-owner. It's also hard to put down, hard to get away from once you've started reading.

Beat the Blank Page With These Weird Writing Prompts

by Ryan

Welcome! I've written some of my favorite stories with simple writing prompts that have encouraged my imagination to come up with things I probably wouldn't have come up with otherwise. Check back each week for new writing prompts, or sign up for Readinary to receive my weekly emails and get the prompts in your inbox along with my latest posts. I've also set up a simple submission page you can use to suggest writing prompts.

Latest Writing Prompt

Prompt courtesy Ryan M. Williams, author of Europan Holiday. Imagine if Lovecraft and Heinlein wrote a Christmas story.

Suggest Prompts

Suggest a writing prompt using this form. If picked, I'll feature it on the site and link to your website.

First NameWriting PromptWebsiteSend Writing Prompt

Previous Writing Prompts

Check here for previous prompts you might have missed.

Prompt courtesy Ryan M. Williams, author of The Task of Auntie Dido.

Prompt courtesy Ryan M. Williams, author of The Murders in the Reed Moore Library.

Prompt courtesy Ryan M. Williams, author of Invasion of the Book Snatchers.

Prompt courtesy Ryan M. Williams, author of The Gingerbread House.

Prompt courtesy Ryan M. Williams, author of Dark Matters.

Prompt courtesy Ryan M. Williams, author of Stowaway to Eternity.

How to Write Amazing Characters Like Stephen King

by Ryan

I love Stephen King's characters. I once sat on the floor reading Dolores Claiborne instead of packing for a move. I hadn't planned to sit and read the entire book. I picked it up, flipped to the first page and started reading.

“What did you ask, Andy Bisette?
Do I “understand these rights as you've explained em to me”?
Gorry! What makes some men so numb?
No, you never mind—still your jawin and listen to me for awhile. I got an idear you're gonna be listenin to me most of the night, so you might as well get used to it.” (King, 1993)

I started reading and I did sit and listen to Dolores for awhile. A long while, the sort of thing I haven't done many times, just sit and read a book through front to back in a single rush. Yes, the pages are a bit narrower than some, and the lines are generously spaced, but it still comes in at 305 pages in the hardcover edition I read. King started writing the book the year I finished high school and it came out the year I finished college—and I still don't understand how he creates such compelling characters.

Studying the Masters

With this series of posts, we're going on a journey to study and learn characterization from the masters of fiction writing. I've written over twenty novels. I have degrees in writing fiction and in library and information science. I'm still learning about writing. I hope I never stop learning about writing. I want to do better. I always give it my best—I want to push that to higher levels.

I'm starting with a personal favorite. I love Stephen King's work and have collected his books for my private library. This is an initial foray into a longer journey of exploration and discovery. We're going to start figuring out a plan to tackle these questions, to uncover new questions, and figure out an approach.

This series isn't going to stop with Stephen King. I plan to bring in other authors' works. Compare genres, different time periods, and also look at non-fiction works on writing amazing characters.

I hope you'll join me in this process and share your experiences in the comments. I'd love to hear from you as we dig into this topic.

As we go through this post, I recommend that you grab copies of the books in print or e-book to follow along. I do use affiliate links to titles in this series, which provides a bit of support for this blog if you go through the links included. Don't want to buy copies? Visit your local library, or check out their options to check out e-books online.

It's not required, of course. I'll include quotes as we look at each, but you'll need access to copies if you want to do the exercises.

[Subscribe now to receive Readinary, my private email each week and follow along with this series and receive news, instruction, and inspiration. Stephen King magnet, A Year of King, a reading challenge with titles organized by when they happen. Yes, and an e-book version of this post.]

Introducing Stephen King's Characters

Here's the question: how does King introduce us to his characters? In those first few pages of a book or a story, what does it look like? What does King do?

Let's start at the beginning, with the book that launched King. I'd like you to meet Carrie.

Opens Book

Okay, this book starts off with a news article about a rain of stones. It introduces two characters (the widow Margaret White and her 3-year-old daughter Carietta).

After the article, as the novel opens, it isn't a single character, but a group of characters that are referenced.

“Nobody was really surprised when it happened, not really, not at the subconscious level where savage things grow. On the surface, all the girls in the shower room were shocked, thrilled, ashamed, or simply glad that the White bitch had take it in the mouth again.

Carrie had been going to school with some of them since the first grade, and this had been building since that time,—

What none of them knew of course, was that Carrie White was telekinetic.”

What's Going On Here?

[two_third_first]

The novel has multiple openings. The newspaper, the opening that references what happened and flat out states that Carrie is telekinetic, and then the novel moves into the locker room to show what happened.

It's an opening rich with sensory details as King takes us into the shower room. And its when we get our first look at Carrie.

“Carrie stood among them stolidly, a frog among swans. She was a chunky girls with pimples on her neck and back and buttocks, her wet hair completely without color.”

And it goes on. The description picks up steam and moves. King takes us from the establishing shot of the shower room into a description of Carrie and deeper until we're into Carrie's thoughts and sensor details. Until we're right there with Carrie, the details washing over us until another character appears.

“Miss Desjardin, their slim, non-breasted gym teacher, stepped in—”

Okay, let's stick with Carrie and figure out what's going on.

[/two_third_first][one_third_last]

Exercise One

Type the opening of Carrie from “Nobody was really surprised…” all the way to “…they all saw the blood running down her leg.”

Why? So you get what it feels like to type the opening, how it looks when you type it into Scrivener or Word, or whatever you use. If you hand-write first drafts, hand-write the opening. You'll learn so much more than just reading it. We're not trying to copy King, but we are figuring out how he breathes life into characters. It's only a bit over 400 words, easily done in 10-15 minutes.

[/one_third_last]

Point of View (POV)

King doesn't stick to a fixed point of view (check out this Wikipedia article for definitions of narrative point of view). King moves all the way from the newspaper to the locker room, deep into Carrie's thoughts, and then back up and out until he references “they all saw” and back up to an excerpt from a scholarly piece about Carrie. We've gone for a ride with King all the way down and back up the other side. It's a third-person mode, subjective, omniscient POV.

King relishes in details, in opinions, calling Carrie “a frog among swans” and “the sacrificial goat” and more. Ouch.

The other girls are described as a group. Through the sensory details, we feel it from Carrie's perspective surrounded by the pack.

Miss Desjardin, the third character named (after Carrie's mother and herself), warrants a description of her own—and it's not Carrie's view of the teacher. We've come back up enough that we learn that Miss Desjardin won the whistle she wears in an archery contest.

One other character is named, Sue Snell, getting a single line. Enough to indicate that we'll see more of Sue later (King drops back down into Sue's thoughts after the break for the article).

Thoughts on Carrie

In On Writing, King talks about writing Carrie, and the challenges that led him to throw it away. Fortunately, Tabitha King rescued it from the bin. Stephen King reflects on lessons learned from writing the book.

“The most important is that the writer's original perception of a character or characters may be as erroneous as the reader's. Running a close second was the realization that stopping a piece of work just because it's hard, either emotionally or imaginatively, is a bad idea.” (King, 2000)

The point of view technique King uses in Carrie—third person, subjective, omniscient—has a cinematic feel to it, don't you think? It starts from a place of commentary, drops down to show the shower room, moves into Carrie's thoughts and feelings, then pulls back again as Miss Desjardin shows up and the incident develops.

The omniscient narrator isn't an objective camera. King writes with attitude, with opinions about the characters and the situation. King includes sensory details in the environment and it all brings the scene to life as the POV tightens in closer and closer to Carrie, down into her perspective. This is a point of view that moves from one character to another and isn't limited third person. The narrator tells the reader right away that Carrie is telekinetic, stating that none of the girls who have gone to school with her know.

The whole thing, the article to the break, is only about 600 words, maybe two and a half pages. King manages to create suspense, tension, and a vivid sense of Carrie in that space. King has given us a description of the teacher, Miss Desjardin, and clearly conveys her own dislike of Carrie. The section also introduces Sue Snell, and it's Sue's perspective that we return to after the next article.

Summary of Carrie's Introduction

Short, 600 words
Third person viewpoint
- Subjective
- Omniscient
Describes physical details of two characters, Carrie and Miss Desjardin
Moves from a wide establishing shot down to Carrie's thoughts and feelings, and back up to the wide shot again.

Meet Paul Sheldon in Misery

Next, let's take a look at an all-time favorite Stephen King novel, Misery, and meet Paul Sheldon (we'll also meet Annie Wilkes). We're going to look at the first two chapters and see how King introduces and brings these characters to life.

“1

umber whunnnn

yerrrnnn umber whunnnn

fayunnnn

These sounds: even in the haze.”

Short chapter, huh?

What's Going On Here?

Let's take a look at the next few lines.

“2

But sometimes the sounds—like the pain—faded, and then there was only the haze. He remembered darkness: solid darkness had come before the haze. Did that mean he was making progress?”

Unlike the opening of Carrie, this time King starts down deep. Right down in the unnamed (at this point, because he doesn't even remember his own name) character's perspective. We're with this character, just as clueless, not knowing what's happened, except there are sounds, haze, darkness, and pain. And “he.”

[Typing Exercise Two Sidebar

Type the first two chapters of Misery from “umber whunnnn..” all the way to “…they all saw the blood running down her leg.”

This one is longer than the last exercise, about five pages, but it's worth it to see how the characterization builds as Paul becomes more aware.]

Point-of-View

The POV here is again in the third person. It's subjective, and it limited. Very much limited, just as Paul's mind and senses are limited in the beginning. The chapter sticks to that viewpoint the entire way through. Unlike Carrie, King hasn't pulled back to describe where Paul is, Annie, what has happened, or made any mention of what is coming for Paul like a swinging—

Well, I don't need to go there right now.

Thoughts on Misery

The form and structure of King's writing in this introduction to Paul is driven by Paul's own experiences. The first chapter, short, confused sounds and little else. The text drifts, long blocks of text without paragraphs as Paul drifts in the haze. As Paul's awareness increases, other moments intrude. King's descriptions of Paul's resuscitation, the stink of it, “she raped him full of her air again,” characterizes Annie before we actually see her for the first time, and leads up to the chilling end of the second chapter.

We've also learned quite a bit about Paul. Not physically, but we get a sense of who he is, and some history and background. It's tied into what he is experiencing, memory informing the sense of the constant pain.

Summary of Misery's Introduction

Not too long, 1,224 words
Third person viewpoint
- Subjective
- Limited
Introduces Paul Sheldon and his number one fan, Annie Wilkes.
The form and structure reflect the limitations of Paul's experience.

Now: From a Buick 8

Let's take a look at one more example from Stephen King before wrapping up this initial dip into introducing characters. In this case, Sandy Dearborn.

“Curt Wilcox's boy came around the barracks a lot the year after his father died, I mean a lot, but nobody ever told him get out the way or asked him what in hail he was doing there again. We understood what he was doing: trying to hold onto the memory of his father. Cops know a lot about the psychology of grief; most of us know more about it than we want to.” (King, 2002)

What's Going On Here?

At first, it might seem as if this opening is similar to the opening of Carrie, but that isn't the case. It also isn't down deep within a character like Misery. The first paragraph tells us something about Curt Wilcox's son (but not his name), tells us that the narrator is a cop, and sets a voice for the story.

[Typing Exercise Three Sidebar

Type in this opening, just the first couple pages and change, right up to the scene break on page three. That's from “Curt Wilcox's boy came around…” to “…Michelle Wilcox was short a husband.” Clocking in at 874 words, it shouldn't take too long to type that in.]

Point-of-View (POV)

The section starts with Now: Sandy at the top of the page, giving us both the time and the name of the character narrating the story. Unlike the other two examples, this is told in a first-person POV. Sandy tells us what happened, relating the events and background, with subjective opinion and knowledge that comes from looking back at the events described.

In Misery, the POV is limited to Paul and it rides his confusion and pain. It's right there and happening. The POV in Carrie moves about, free to dip in and out, to describe and tell the reader things that the title character doesn't know. Here, we only get what Sandy chooses to share, and that's at a distance. We're not living the events, as descriptive as they are told. Sandy tells us what happened to Curt, basing part of the story on court testimony. The opening goes on to page 24 when Sandy starts to tell Ned about the Buick.

Thoughts on From a Buick 8

The fact that Sandy is sitting there, telling the story about telling the story of the Buick, adds distance to the events. It frames the story. How could it be otherwise? It seems right the way King wrote it, as if there isn't any other way to tell the story. Maybe there isn't. If we imagined it in a limited third-person POV, whose POV would we use? Curt, destined to become roadkill? Sandy, or the other cops? The story does move between different POVs as Sandy tells the story, bouncing between now and then like a time traveler. King wrote it the way he wrote it because that is how he wrote it. Nothing more than that, I believe.

It's a question, though, when writing. If you hitch along figuring it out as you go, it most likely won't even be a conscious answer. The words and voice will come spilling out of your fingers in the most satisfying way they can. If you're more of a planning sort, your accountant brain might weigh and measure the advantages of different approaches. When it comes to first person POV stories, there's a finger on the scales that keep the story a bit at a distance. It isn't as deep as the third-person limited POV, and not as free-wheeling as the third-person omniscient POV.

Summary of From a Buick 8′s Introduction

Short, 874 words
First-person viewpoint
- Subjective
- Limited
Introduces Ned and Curt Wilcox, the narrator Sandy Dearborn and mentions other characters.
The POV distances the readers from the story.

It's Nice to Meet You

I know we've hardly exchanged a few words in this brief introduction to Stephen King's characters. With so many books and characters to pick from, I could have gone on. If you haven't done the exercises yet, I encourage you to do so. Your fingers provide a two-way access to your subconscious. By typing in work by masters like Stephen King, you're teaching your inner neural network. You'll pick up techniques that you're not conscious of–only to see them emerge in your own writing, with your own words later on.

I plan to continue this series, looking at how characters are introduced and developed by other writers. I want to keep exploring and learning from writers with more miles on the road than me. I hope you'll join me on this journey of discovery.

Get Your Code On: Generating Novel Titles With Python

by Ryan

Writing doesn’t require learning to code. But you can have fun, save time, and improve creativity by learning at least some coding. Even something as simple as creating a word count tracker can help with productivity. Picking up knowledge around HTML and CSS can help writers with websites. Discovering languages like JavaScript or Python unlocks potential for many other options.

I faced this recently with a problem I faced around titles for novels in a series. Don’t worry if you haven’t coded anything before—this is a short example of using Python to generate a list of possible titles for my books. The techniques used can apply to any programming language—or even to doing it entirely by hand.

Branding Series Titles

For the sake of series branding, I wanted to follow the same title format that I used for the first books in the series, in this case Pierce, County, a series set in a fictionalized Washington state with vampires, werewolves, and all sorts of other monsters. Familiar? Think again. In this series the girl turns the tables on the vampires, reacting to them as the pedophiles they are rather than falling in love with them.

The first novel in the series, Dirty Old Vampires, came out a few years back under a pen name. I’ll be releasing an updated version later this year. Then I’ll follow it with Naughty Young Werewolves, and the third book, Pretty Dead Ghouls.

Get the pattern with the titles?

Three words, two adjectives, and a plural noun. The first word is suggestive with a “-y” ending, the second descriptive, and the last refers to a type of monster. I wanted to generate a list of possible titles from combinations of different words.

Creating the Word List

Before starting any coding, I created three lists of possible words, a list for each category. This step requires thinking up words. The lists don’t need to be the same length, but I kept at it until I had lists each with 24 words. I didn’t plan to use all of those words, I only wanted to see what combinations turned up.

The lists can be any words you like, with more lists, or fewer. The more lists, the more words in the lists, the greater number of possible combinations. And that is where coding comes in. The number of possible combinations for my lists exceeded 13,000. Although I could generate them by hand, it’d take a while to even list all of them, much less consider them. It's faster to use the code to generate the list.

After I review the code used for generating word combinations, I'll talk about how I went about narrowing that list down. Fortunately, the process didn't take too long.

Random Titles

You can skip the coding part and use word lists like this to generate title ideas. Pick a word from the first column, one from the second, and one from the third. If you’re using Excel and want to create random titles, use a formula like:

=INDEX(Table1[First],RANDBETWEEN(1,ROWS(Table1[First])))

The formula refers to the column of a table containing your words. You'd use the same formula in adjacent cells, except change the reference to the other columns of your table. Press F9 to refresh the sheet and generate a new random title.

Example Word List

Here's a portion of the word list I used in generating titles. I'll use these lists in the example code.

First	Second	Third
Crazy	Innocent	Reapers
Curvy	Bold	Gargoyles
Fleshy	Live	Sirens
Flirty	Cute	Zombies
Kinky	Brunette	Ghosts
Lusty	Wild	Demons
Steamy	Ancient	Angels
Sultry	Blonde	Mummies

Here's another way to try title combinations: in a table like this one, sort by the different columns to quickly see different combinations.

Introducing Python With TitleGen.py

Here's the code I used for this simple title generator. I'm not a Python expert. I like experimenting and trying things out, but I'm still learning.

titlegen.py Python

The first lines with the hash mark # are comments.

“import itertools” adds a module with code to help with combinations.

An array is a list. These are the word lists that I'm using for this example. You can see that they are inside single quotes and the comma goes outside the quote. If you want a possessive word use double-quotes instead (just be consistent).

The titles array is empty at the start.

def gen_titles creates a function, a piece of code, that actually generates the titles, adding each to the previous empty titles array.

Next is another function, show_titles, that sends the titles to the Python shell.

Finally, the code runs the two functions created, generating and showing the titles.

Python Generated Titles

The example list of eight words in each group generated 512 possible titles as it iterated through the combinations. The text output can be copied and pasted into other programs.

Python output

This isn't the only way this could work. For example, if I only wanted to look at possible titles for zombies, I could run the program with only zombies in the third list and generate a list of titles that used every combination of the other words. That's similar to what I actually did to narrow down a list of over 13,000 items.

Reducing the Title List

Even with a long list of titles, some titles will catch your attention, e.g., Flirty Live Gargoyles. With this series, I don't want to use any of those words again, so I delete them from the arrays and run the program again. In that way, I can quickly reduce the number of titles by removing words used that I don't want to repeat and running the program once more. If you're using the random title method, the same technique works. Take out the words used and run it again. You can repeat that as many times as you like until you run out of possible combinations that interest you.

Adapting TitleGen.py

Nothing requires the program to remain structured the way I have created it. Don't want a three-word title? Leave an array empty or remove it entirely. If you remove the array, you'll need to update the for loop in the gen_titles function. The key part is the itertools.product(first, second, third). If you removed an array, removed the reference from the product function, e.g. itertools.product(first, second). Likewise, if you want to add an array, create it like the others in the program and add a reference to the product function, e.g., itertools.product(first, second, third, fourth).

Your arrays also don't need to be single words. You could have an array with prepositions, prepositional phrases, articles. Or one with verbs. Maybe you want words ending in “-ing.” You can create word lists from whatever you want and an entry in the array can include multiple words as a single entry, e.g., “of the” might be an entry. Customize it however you like.

Resources for Learning Python

Get started learning Python at your local library. There's a pretty good chance that they will have books to teach Python. My local library offers Lynda.com ($30/month value) which has courses on learning Python. You can also check out free resources online such as W3Schools and the Python.org website. Whatever resources you use, you'll want to visit the Python.org website to get started and install Python on your system.

It isn't necessary for a writer to learn Python, but its usefulness isn't limited to creating a quick title-generating program. There are many other different ways that Python can be used to help you in your creative efforts. Or any other programming language that you choose to learn. Writing is all about discovery. Learning Python is also about discovery. Go on an adventure. And while you're at it, go ahead and sign up for Readinary, my weekly email below.

Introducing Your New Writing Tool – Novlr

by Ryan

How to Get Started With Kindle Ads

by Ryan

Creating Graphs for Word Count Tracking

by Ryan

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])&lt;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%;”&gt;

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.

&lt;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%;”&gt;&lt;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%;”&gt;

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 &lt;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%;”&gt;

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.

&lt;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%;”&gt;

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.

&lt;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%;”&gt;

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.

How to Calculate a Writing Streak in Excel

by Ryan

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.

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.

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.

How to Add Project Details to the Word Count Tracker

by Ryan

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.

<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 Cover <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_[{"t":"mouseover","config":{"anim":"grow","loop":0},"a":"thrive_animation"}]_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).

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.

Writers Pivot Tables Too, Further Word Tracking Enhancements

by Ryan

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.

<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_[{"t":"mouseover","config":{"anim":"grow","loop":0},"a":"thrive_animation"}]_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.

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)!