Dear humans, As there were a lot of questions and hesitancies about how to build the super fancy productivity spreadsheet, the Furred & Frond Management has decided to help out. By that, we’re going to build a brand new spreadsheet with you. The female was going to do this as a video, but the voice portion isn’t working and she’s not feeling up for fixing it. (It is a headache day.) Please note that we are not offering the spreadsheet for download. By doing so, the female would have to troubleshoot it, hers isn’t even finished, and she is an author. If you want your own, please make it–or make use of the Raintree spreadsheet. It’s about ten dollars, and you can hit google for the Raintree writing tracker sheet, and you should find it easy peasy.
The female created hers because the Raintree sheet didn’t do what she needed, and she figured there had to be others in the same boat she is in.
Please don’t ask her to a: make one for you or b: give you hers.
If this sort of thing doesn’t interest you, please have a good day. (Client from Hell is now up for preorder, readers. She’ll be posting about it later today as soon as she confirms Amazon’s servers aren’t hiccuping anymore.)
This is what the spreadsheet will look like when we’re finished.
Click here for a full-sized version of the spreadsheet.
This looks complicated, but most people who have taken the dive and tried to make their own have finished their 100% customized-to-them version in a few hours. Anyone with a spreadsheet program can do this. It’s really easy.
The first step is to set up a Project Master List. This is what you will use to get “Project 1” and “Project Name” populated into the monthly sheets.
Step one: Pick your fields. This is what the female is using:
| Project One | (Project Name Here) |
| Start Date: | |
| Drafting Deadline: | |
| Goal for Year: | (Put # of Words you want to write here) |
| Series: | Series Name here |
| Completed: | 1 for completed, 0 for incompleted |
| Words Written This Year: | (This will be a formula we make later.) |
Honestly, the female isn’t really using her start date and drafting deadline fields yet. She will once she has a few minutes to finalize them.
Without any fancy formatting, you should have something like this:
Not very pretty, right? That’s okay. Here is how to make it pretty. To change the color of the background of the cell, click on the cell in question, drag your cursor across to the other cells you want to be the same color, and then use the paint bucket tool to change the color to whatever you want. Here is a step by step guide, in pictures, of how to change a cell’s background color. Basically, highlight the cells you want to change, pick your colors from the color option, and then you’re done!
This is what the female’s Project Master List chart looks like after she (somewhat) finished with it.
Click here for a full-sized version of this image.
If you want to change the size of your fields, grab them by the edge. In the cause of changing a row’s height, you would put your cursor between the cell numbers and drag to the appropriate height. To make cell 3 bigger, you would grab the BOTTOM of cell 3, click, and drag to adjust the entire row’s height. You can do the same thing with the columns as well. To adjust the width of an entire column, you grab the right-hand side of the column (Where it says A, B, C) and drag it with your cursor until it’s the right size.
The last tool I use is the font modifier tools, which look like this:
The A and smaller A are how you increase and decrease font size easily. The upper row of ‘orientation’ lines are how the text sit in the cell. The one highlighted means the text sits at the bottom of the cell. The one on the left means the text sits at the top of the cell. The row of adjustments below is left justified, center justified, and right justified. The A with the color bar is how you change your text color.
I use all of these tools to ‘make the spreadsheet pretty.’
To make the Project Master List, the female set up one template for my books, and then she copy-pasted it to the appropriate spots. After that, the female manually colored everything in using the paint bucket tool. Honestly, it took her longer deciding what fields to add than it did to color everything in and copy paste. We do recommend you make every project a different color so it’s easier to use your master sheet.
Pick as few or as many projects as you want for the year. We recommend you give yourself some leeway, as you’ll have to adjust all of your formulas if you add stuff in later. In good news, you can totally add stuff in later if you want.
Congrats! You’ve finished part one of your spreadsheet. (You don’t have to color it in if you don’t want to. You just need the data present so you can make your formulas.)
Now, onto making the next spreadsheet, which will be your January 2020 tracker.
To be fair, this does look a bit intimidating, so let’s approach this step by step.
Step one: label the spreadsheet:
If you type “January 2020” in the box, excel will automatically transform it to a date column, which is annoying. So, click the box and type ‘January 2020 (Doing so will prevent excel from transforming it to a date column.)
Well, that’s not really pretty is it? Nope. Female does not like this because it’s not pretty.
Highlight the cells like this and then click this button:
The merge & center tool is your friend. This combines all the highlighted cells into one mega cell.
Now, the female suggests you use the Increase Font button we talked about before to make January 2020 fit into the box nicely, and then use your paint bucket and font color buttons to make it any color you like. The female is going to use a lovely pale yellow for this. She likes yellow. She hit the font increase button 7 times in case you’re curious. It now looks like this:
Next up is setting up the upper statistics bar she is using if you want to know how much you need to be writing a day. Right now, we’re only setting up the formatting. We will populate the formulas later.
Using the same method from Columns A,B,C and Rows 1,2,3, the female is going to repeat this in D,E,F and 1,2,3 to make the next section of her pretty spreadsheet. The female hit the big letters button three times, and she also centered the text in the middle of the cell. It makes it a little prettier. This is what you should have so far.
For the next part of our adventure, you will highlight cells in column G,H and Rows 1,2 to make the spot where you will insert one of your formulas. Just set the box up as you want. The female used merge & center and the paint bucket tool on those cells. The female’s spreadsheet now looks like this:
For the “January Weekday Goal:” section, the female made the cells a little larger to accommodate the text. Honestly, there’ll be a lot of fiddling with the cell sizes later. But, as that is not a functional issue with the spreadsheet, worry about making it look perfect later. You want something that works. You can indulge in artistic rendering of your spreadsheet when you have more time. (The female amuses herself this way often.)
She also made a spot for the formula, just like she did for “Words Written in January.”
Your spreadsheet should now look a little something like this:
Because the female is being persnickety, she has decided to change the color scheme up a little so it looks more like the ‘real’ one. So,
So she highlighted the pesky cells the wrong color and changed them with the paint bucket tool. Find your own style and have fun making something pretty.
We like pretty things.
Next up is setting up the calendar section of the spreadsheet. This is where you will see an ‘overview’ of what you’ve done for the month (by week.)
On Column B and Cell 4, pick the day of the week you want and type them in. The female works Mondays-Sundays, so that is what she is using. It should look something like this when you’re done:
That’s ugly, so we’re going to do some adjustments.
Step one: grab row 4 and make it larger. You want to approximately double the size of the row. To make a row larger, you click between the 4 and the 5 in this case, and drag DOWN. That makes row 4 larger.
That’s a little better but not quite right… so, to fix this, you use the “wrap text” function on the Weekly Total cells. It’s right above Merge & Center.
Now you have something like this! Still… not perfect. Use the text alignment to center all cells we’re working with to make it pretty. Hit bold while you’re at it. Your sheet should now look something like this.
January 1 is a Wednesday, so we’re going to start populating the calendar.
As you want a number to show up beneath the day / number of the week, leave a blank row before continuing numbering the rest of the month. When you’re done, it should look something like this:
When you get to Saturday, the month will change. So, you need to input days that fall outside of the month of January. The female uses ‘(1) and ‘(2) for this.
Uh oh! Excel is mad at us. What’s going on? Why is the formatting skewed? That’s because ‘(1) is not a numeric value and 1, 2, 3, and so on are. They don’t match what excel is expecting. Click the cells with the little green triangle and select “Ignore error.” from the big yellow hazard icon drop down. It’ll go away.
Once you do that, highlight the text like this and hit left justify to make everything uniform.
It is time to color and bold things so they’re pretty and useful–and make it easier for you to see what’s going on.
Your spreadsheet should now look something like this.
In the female’s version, she has used the border tool to make things prettier. The border tool costs time and effort and is really there to make things pretty. You don’t have to do this unless you like the look, but we’re going to walk you through how to make borders anyway.
Step one: highlight what you want to be bordered:
Step two: select the type of border you want. Like to draw them in? You can do that. Want to just use an outside border? You can do that, too. The female is using the “Outside borders” for this demonstration.
This is the result:
Still using the outside borders function, she has gone ahead and blocked in the weekly totals fields.
Still using the outside borders function, she now captures each week of days because she likes how it looks.
Boom! One more little detail to add, and then we’re done with this until it’s time to make the formulas. Add “Week One” “Week Two” and so on to the appropriate boxes under the “Weekly Totals” section and use the outside borders to make them pretty, too.
Hooray! Your calendar is all set up!
The next up is setting up our daily recording chart, which is the main chunk of this spreadsheet.
In Cell A16, the female begins with a “Daily Total” label. This will make populating the pretty calendar we just made a lot easier.
In Cell B17, the female adds a “Project Label” to help keep things organized. Yours should look something like this:
Not very impressive, right? Don’t worry. It’s about to get some backbone to it.
The female uses daily averages as a ‘end of month’ record to see how she did, so she needs a spot for that in her spreadsheet. However, most people don’t need or want this, so decide for yourself if you want that field. We’re including it because that’s how she set hers up.
She is also adding a monthly total label, which is a mandatory field.
Ew. This is ugly. Yeah. It really is ugly. So, we need to make this field a little wider. Just grab it at the top and adjust it so the text fits when bolded. We also need room for book titles, so the row with project title needs to be taller. It should now look something like this:
To start your actual day tracking, type January 1, 2020 below Monthly total. It should look like this:
When you hit enter, it will now look like this:
To populate the days for the entire month, go to the row in green (as shown above) and hit =, select where it says 1-Jan-20 and type +1 before hitting enter. It will then look like this:
Select 2-Jan-20, hit copy, and then highlight the cells beneath it and hit paste. Magic will happen! (click, hold shift, and click again to do the highlight like this.)
Whoops! We selected too many rows. just highlight all the February ones and hit delete.
Now your spreadsheet is starting to have some form, and should look something like this:
Now is when we make use of the Project Master List we discussed before. Click C17 (the one just to the right of Project Title) and hit = before you click over to the tab with your Project Master List. Select the cell with the title of the first book in your list and hit enter. It should look like this:
Ew. That’s ugly. Hit the wrap text button, center it, and adjust the width / height of the cell as needed to make it look okay.
There. That’s better.
Now, do the same for all projects in your list across that row.
As the female has a headache and this is so personal to you, she’s only done a few of her titles for the purpose of this demonstration. Your sheet should now look a little like this:
Now it’s time to work on the monthly total section. We will be using our first formula, which is the SUM() formula. This adds all the cells in the SUM range together and gives you a value.
On cell C19, you want to type =sum( and then click on cell C20, where it is next to Jan-1-2020. You will then want to hold shift and select down to 31-Jan-20 and hit enter. (Excel will automatically close the ) for you. If your program doesn’t, you’ll need to type it.)
Select the field that now says 0, hit copy, and then select all fields in the row beneath projects. Hit paste. This will automatically populate your sum fields in the appropriate spaces for your projects. You can also do it manually as well.
Next up you need to set up your daily total field. In the cell to the left of January 1, do a new sum formula, this time including all columns on that row including your project’s daily totals, which looks a little like what you see below. Do NOT include January 1 in your sum field. Start it in cell C20.
Using the same copy-paste magic, copy the daily total sum formula down the line to the end of the month. Your sheet should now look like this. Oh no! We have a little green triangle again! Dismiss it using “Ignore this error.”
Now we need to populate our calendar above with the daily totals. Select Wednesday the 1st’s cell, hit = and select the daily total column next to jan 1 below. Hit enter. Do this for every day of the month in your calendar section.
Your sheet should now look something like this.
To have a basic functional sheet, you only need a few more adjustments. First, you need to do some sums in week one, etc, in the calendar section. For weekly total (all days) you do a =sum( selecting all days of the week in the calendar preview) For weekly total Jan only, you do a sum of ONLY the days in January. So you would exclude Monday/Tuesday from that sum.
Next up, you need to populate your “Words Written in January” field. Simply do a basic addition formula including all weeks for January using the calendar you just finished making. It should look a little like this:
Here is some random numbers inserted so you can see the sheet in action. Boom! You have a functional tracking spreadsheet. You can make just about any type of widget you want at this point using the skills you just learned. Good luck, and enjoy your new tracking spreadsheet!
The female uses these additional widgets in her spreadsheet:
To make the yearly statistics, she just has an addition formula of all monthly totals. Projects completed is an addition formula of the project completed cells (thus the zeros and ones from the Project Master List) sheet. Her goal for the year is another additional formula drawing from the Project Master List. The % goal achieve is just a division formula formatted to be a percentage. The needed per week and needed in January fields are similar math formulas. Play around with what you need. This guide has given you all of the tools needed to make one of your own.
Hint: Copy-paste January to the other months of the year and modify those sheets to prevent you from having to start from scratch for each month.
We hope this has helped!
Footnote: Please color your sheet however you want and use the outside border/other border function to your heart’s content. The female officially has a migraine and wanted to have this posted rather than wait for random number of days until she can see straight again.
Needless to say… she won’t be hitting her target word count for today.
Leave a Reply