Spreadsheet Novel Analysis and Free Download

Spreadsheet analysis of novels allows me to pattern author storytelling and plot devices. But the value in the methodology lies in the columns I create – in other words what I choose to track throughout the book.

After reading HOOKED: WRITE FICTION THAT GRABS READERS AT PAGE ONE AND NEVER LETS THEM GO by Les Edgerton. I created a spreadsheet based on plot elements Edgerton outlines, and used it to study the middle grade novel, HERE TODAY by Ann M. Martin.

The spreadsheet allows me to look for patterns of problems, reactions, goals and actions. It also helps me focus on the elements of that critical beginning – a place where my novels need work.

Just as valuable as the ability to review the spreadsheet afterward, is the process of thinking about each element – words, paragraphs, pages, scenes and chapters – as I read and assign their purpose. The tool is therefore as valuable in its creation as in its end result. Here are a few things I learned – or affirmed – in the process:

In 1963, Ellie’s mother, Doris Dingman, was crowned the Bosetti Beauty at Mr. Bosetti’s supermarket.

Who wouldn’t read on?


Quick hints about the trouble in store began each of HERE TODAY’s four parts. Using this technique, Martin laid out the promise of the plot and allowed me to accept the status quo and lead-in events with intrigue and anticipation. Consider the foreshadowing that continues from that dynamite opening line throughout the first paragraph.

In 1963, Ellie’s mother, Doris Dingman, was crowned the Bosetti Beauty at Mr. Bosetti’s supermarket. President John F. Kennedy was assassinated and the Dingmans began to fall apart. Most of this happened in the second part of the year -a year that had gotten off to a pretty good start, considering they were the Dingmans.


This was tricky for me. I like to think of the inciting incident as the one thing that needs to happen for the story to truly begin. I chose Doris winning the Bosetti drawing as just that incident. But because of this line

…the citizens of Spectacle, nearly always turned out to watch Doris

I had to wonder if Doris’ winning is more of the status quo or what Edgerton calls the “situation.” And perhaps the true inciting incident comes a few pages later when one of the townswomen who watches Doris race through the Bosetti aisles laughs then remarks,

“I just can’t help it. She’s just so … so cheap.”

With that remark Ellie grows so embarrassed she wants to retreat to her private place. This could be the inciting incident instead. But for now, I labeled this second incident THE INITIAL SURFACE PROBLEM.


Martin reveals Doris Dingman’s background halfway through the book in this dialog between Ellie and her grandma:

“We saw the trophies in her room. Are they all for beauty pageants?”

“Mostly. But she won a talent contest when she was in junior high. Won by reciting that poem by Edgar Allan Poe. The one about the talking bird. ‘Quoth the raven, “Nevermore.”‘ I never did understand what that was about. I’m not sure your mother did, either. But she sure said it beautifully.”

“How old was Doris when she met Dad?”

“Oh, goodness. Now let me see. Eighteen, I guess. And they’d up, run off and married in the space of four months. He was your mother’s ticket out of here.”

The reader hears this history at the same time the MC does and the author delivers much information in a brief exchange.

But Martin also uses unique chronology at points in the story when she takes small steps backward, to enlighten the reader, before she moves forward. In this example, Ellie clues us in to incidents that occurred previously, but not before the novel began.

Four times now she had been wakened from a deep sleep to the sound of the phone ringing, then had heard her father’s muffled voice, sometimes raised, sometimes lowered, on the other side of her bedroom wall. But Mr. Dingman hadn’t said anything about these calls, and Ellie hadn’t asked.

I’m not sure if these little backsteps are truly called backstory, but they work. One thing is true of all Martin’s backstory elements? They’re brief. A sentence or two and we’re moving forward again.


The only character who gets time on the page for physical description is Doris – because her looks are essential to the plot. But Martin’s other characters are clearly revealed through actions and dialog. Consider how much we learn about brother Albert from this bit of dialog:

“Wow, where is everybody?” Marie said as Mr. Dingman steered the Buick toward the diner.

“They’re at home,” said Albert, “watching TV and smelling the turkey cooking.”

“Albert,” said Mr. Dingman. “No more of that, please.”

Albert slumped even lower in his seat, then edged to his right until he was pressed against Marie’s side.

“Quit it!” said Marie.

“What’d I do?”

“You touched me.”

“Albert,” said Mr. Dingman again. “What did I just say?”

“Well, what are you going to do to me? Take me home? I don’t want to go to the stupid diner.”

Ah hah. We see very different child from his sister Ellie, the peacekeeper.


As in the above example, many novel segments fell under several plot categories in my spreadsheet. This indicates that Martin accomplishes multiple goals within her scenes. See how in the next few sentences we see not only the development of a surface problem but also Ellie’s reactions to it:

Across the runway Ellie saw two men fasten their eyes on Doris. The eyes slid down her body, up, down again, and then the men grinned in a way that made Ellie think of the boys in the cars riding along Route 27, honking at Doris in her bathing suit. And made the two saleswomen smirk and shake their heads.

She looked around for her father and saw that his own eyes were fastened not on Doris, but on the men. And she looked away quickly, down at the ground, at a button someone had lost.

In that brief scene we see trouble brewing. Ellie’s shame and embarrassment ooze from the page.


After identifying many of the surface problems in HERE TODAY, I rarely found a corresponding action on the part of the main character. In HOOKED, Edgerton discusses MC reactions and actions in response to each surface problem throughout the story. But our MC, Ellie, is much more passive. She doesn’t do much of anything except try to keep the peace now and then. And even those actions are rarely overt. I grew frustrated with my spreadsheet feeling like I had so few actions in relation to Ellie’s many surface problems.

Then – BINGO! – I realized Ellie’s inaction is exactly her reaction to her problems. In fact, it’s so pervasive, it’s a crucial component of her story problem. And when she finally takes action, towards the end of the story – she’s fully-realizing her story problem. Brilliant!! The spreadsheet works!!


Ai yai yai. Another tough one. I went back and forth on this but ultimately concluded that Ellie’s story-worthy problem was that Ellie needed a mother. She fully realizes this during her trip to NYC and she affirms it when Doris returns home one last time.

The resolution of the SWP thus, is Ellie’s realization that a village can raise a kid. She’s got the old ladies and the other families on Witch Tree Lane. She’s got her father, her grandparents and her best friend. And she’s got herself. By the end of the novel she’s grown active and assertive, a true transformation from her passive prior self.


In every novel there are those scenes in which things seem to be moving along just fine. I don’t know what to call them. They aren’t transitions, necessarily, because they’re more than a link. They actually provide mood and setup for the next obstacle or problem. I’d like to know more about those storytelling elements, what they’re called and how they’re used effectively. More on that in another post.


There’s so much more on that silly spreadsheet. But I’ve gone on too long already. Still the study begs the question: How can such a detailed analysis of another author’s book possibly help me?

If we can become better artists by studying the masters, then we can become better novelists by studying the master storytellers. Reading is crucial. But Edgerton’s plot devices coupled with the spreadsheet method helped me think about the storytelling in a more organized fashion. During revisions of my own novel, I plan on putting the spreadsheet to work again.

In the meantime, I’ve added a free download of my Novel Analysis Spreadsheet to my website at www.jeanreidy.com. It’s meant to be used in companion with Edgerton’s book. I’d love to hear your suggestions for improving it or your comments on my study of HERE TODAY.

Phew! That was a long one!

2 Responses to “Spreadsheet Novel Analysis and Free Download”