For Authors: Building a Productivity Spreadsheet

Dear humans,

The female started posting in-progress pictures of her new spreadsheet today, and as a result I have taken it upon myself to show off her hard work, expose all of her secrets, and teach you how you can build your own productivity worksheet.

Time Investment: 2-10 hours depending on Excel skill level, patience, and tendency to make everything nitpicky perfect. The less pitpicky you are, the faster it’ll go.

The female is developing some nitpicky tendencies, so she’s about three hours into her spreadsheet and has several months of data arranging to go.

To begin, I will show you the spreadsheet for February, which is fully populated and showcases the female getting her act together and doing some serious work.

Please click here for a full-sized version of this image.

The female initially used a spreadsheet she had purchased from another author, which included revision tracking and similar things, but honestly? She used only one portion of the sheet, and it didn’t do what she needed. The female borrowed a few elements from that sheet, particularly in terms of the weekly/daily tracking overview at the top of the spreadsheet, as she found this was particularly useful for her.

The female needs to track daily, as it reminds her she needs to work. If she doesn’t track daily, she gets lazy, and lazy authors don’t write books.

Key elements of the spreadsheet:

1: Project title/projected length: to make this spreadsheet happen, there is an entire sheet dedicated to a legend, which includes the titles, the estimated word counts, and other factors, including how much was written on that project the entire year. She’s still working on this spreadsheet, so some fields haven’t been populated yet.

Please click here for a full-sized version of this image.

For the most part, the female is using manual formulas to gather the data. So, for where it says “Words Written This Year” the formula will look like this:

“=Jan!C19+Feb!C19+March!C21+April!C19+May!C19+June!C19+July!C19+Aug!C19+Sept!C19+Oct!C19+Nov!C19+Dec!C19”

What the hell does that mean, you may ask? It just means that it is adding the values from January Spreadsheet Cell C19, February Spreadsheet Cell C19, etc, etc, etc to come up with a grand total, which then looks like this:

You can create your own by clicking the cell you want the formula in, hitting =, and then clicking to the tab in the spreadsheet you want to reference, then hit + after you have selected the cell, and then pick a new cell, hitting + after every selection until you’re done. Then you hit enter, and you have your functional formula.

This is fairly basic excel work, just make sure you take your time and get the formulas correct the first time.

If you’ve never used excel before, these little buttons are how you change the background color and the text color. If you click the little down arrows, you have a decent selection of basic colors to work with. That is all she is using to make pretty colors on the spreadsheet.

She is also using the merge cell function, which lets her combine several cells. She only does this on fields she doesn’t need to reference generally later. You can run into “Split” errors if you try to reference a merged cell in an unmerged cell. In layman’s terms: Only do it in fields that are purely decoration unless you know what you’re doing.

To fix your numbers so they look pretty, you have several choices. The female makes use of the Format Cells tool to adjust text and numbers as needed. It’s handy.

Beyond that, you will need the sum function, which I’ll explain in a few moments.

Onto the statistics overview pane, which is the female’s favorite part of this tool. She is picky. She likes her numbers organized. In this case, it means knowing how much she wrote in a Monday-Sunday week and how much was written in the specific month.

Please click here for a full-sized version of this image.

The original sheet she used didn’t offer her the ability to distinguish the two, so she made her own version of it. (#) indicates that it happened outside of the spreadsheet’s month.

Please click here for a full-sized version of this image.

If you look in the fx bar (fx being function), you will see that cell B6 uses =Jan!B14 as the formula. This means it is pulling data out of the January spreadsheet in cell B14 and displaying it in the Monday, January 27th spot on the female’s February tab of the spreadsheet.

Every single day of every month calls a cell from somewhere on the same sheet or a different sheet, depending on where it’s being sourced. In the case of Saturday, February 1, it looks a little like this:

Please click here for a full-sized version of this image.

The function bar states it is calling cell A20, which is the Daily Total cell associated with February 1, 2020. The Daily Total cell is a sum cell, which looks a little like this:

Please click here for a full-sized version of this image.

As you can see, the function line for cell A20 sums together all cells between C20 and V20. C20 represents the day’s total for Blood Bound. V20 is the last active column in the project tracking list. Sum is simply that: it adds together all values in the selected cells.

Next up is the first week sum column, which includes all days of the week regardless of which month they’re in. This is a simple sum formula ranging from B6 (Monday) to H6 (Sunday.)

The month only sum formula is only a hair more complicated. Only the cells used for the month are used. In this case, the formula reads =SUM(G6:H6) and adds those two numbers together.

The Words Written in February section uses an addition formula, which looks a little like this: =J6+J8+J10+J12+J14

That adds the numeric cell values of J6 (Weekly Total (Feb Only)) and other subsequent weeks in the sheet.

At this point, everything else is basic math formulas calling on other cells in whatever fashion you need. The female picked this method because it works great for her. You may very well need a different method to organize yourself. Do what works best for you, human… but an organizational spreadsheet is now out of your grasp. You can do just about the entire thing with the simple formulas presented in this post.

The female needs the accountability, and the act of filling in the spreadsheet daily keeps her on task and gives her a visual idea of how productive she’s being. She uses the average column at the end of the month to judge past performance. Unless she’s front-loading a month of work, or she has a target average per day she must reach, it’s a ‘skewed’ number until the month is over. It’s more of a statistic for year-end review to see what her various averages are depending on type of project she’s working on. If you don’t like skewed numbers, you don’t even need to have it in your specific spreadsheet!

You do want to keep the “Monthly Total” column that sums up everything in the month, as that’s the field you call when you’re adjusting your Project Master List. (And it’s a good idea to know how much you wrote per project each month and for the entire year.)

Last but not least, the most complicated field in the spreadsheet: Total Words Written. This uses a merged cell, which I talked about before, so it can be persnickety.

To avoid problems with the merged cell, which is used to make the pretty header, you need to select the upper left cell of the merged cells, in this case, “G1” — “G1” is the same across all spreadsheets, so it’s a very simple SpreadsheetName!G1 reference with an addition sign between the values. That will add up your entire monthly totals accurately without having ‘overlap’ from your weekly structure.

Projects Completed is a very similar formula, but it always calls from the Project Master List tab. The female used 1 to indicate the project is completed (AKA published or submitted for publication) and 0 to indicate it is still a work-in-progress.

We of the Furred & Frond Management how you found this spreadsheet tutorial useful in managing your own writing projects, authors!

It’s easy. It really is. Don’t be scared of excel. It doesn’t bite… often.