Numerical Motivation: Spreadsheet Awesomeness Tutorial

Today on Operation Awesome (and in this future post on my blog) I'm sharing the spreadsheet I use to keep track of my writing progress. It calculates based on my daily writing goals and the days of the week I write. I've created four versions: Weekends Only (2 days per week), Mon thru Fri (5 days per week), Mon thru Sat (6 days per week), and Sun thru Sat (7 days per week). I figured these would be the most common writing schedules, but in case your schedule is different, I've created a tutorial below so you can change the formulas to match the days you write, which will give you more accurate calculations.

Changing a spreadsheet might seem overwhelming (especially if you've never worked in Excel before), but it's actually a pretty simple process if you know the formulas to use. And I'll walk you through it step-by-step. :)

First, you'll need to download the spreadsheet that is closest to your schedule (but, really, any of them will work). Links to the spreadsheets and download instructions are in today's post on the Operation Awesome blog or on my blog here.

Once you have the file open and you've enabled editing, you'll need to unprotect all the month pages.

Unprotecting the Sheets:

First, select a sheet by clicking on the tab at the bottom (January, February, etc.), then click the Review menu at the top and click Unprotect Sheet.

You'll need to repeat this step for each of the month sheets. You might want to save the file after you're done unprotecting everything so you don't have to repeat all these steps if something goes amiss later on. :)

Next, you'll need to group the sheets together so you only have to enter the formula once. If you want to do a different schedule every month, skip this step.

Grouping the Sheets:

First, select the January sheet...

And while holding the Shift Key down, select the December sheet.

It might be a little hard to see, but all the month tabs should now be lighter than the other tabs.

Customizing the Writing Days Per Month:

Now that the sheets are grouped, go to cell J1 (next to Writing Days:) in any of the month sheets.



You can see the formula in the box above the cell. Making changes in this box will be easier than making them in the actual cell.

The basic formula is (you can copy and paste into the cell or the box shown above):

=COUNTIF($A$3:$A$33,"M")

It's set to count the days of the week as they're listed in the far left column of the sheet. The above formula will count all the Mondays. To add additional days, you need to add to the formula. Note: you will need to change the = to + for any additions.

=COUNTIF($A$3:$A$33,"M")+COUNTIF($A$3:$A$33,"T")

That will count all the Mondays and Tuesdays. You can continue to add to or subtract from the formula until it fits your schedule. The part you'll need to change with each addition is the letter in quotes at the end (in red).

Monday = "M"

Tuesday = "T"

Wednesday = "W"

Thursday = "Th"

Friday = "F"

Saturday = "Sa"

Sunday = "Su"



Once you're satisfied with the days you have it set to count, hit Enter and it should calculate your total writing days for the month and your Monthly Goal.*

Setting Your Weekly Goal:

To change the weekly formula, click on cell J3 (next to Weekly Goal:).

The formula here is pretty basic. You'll need to determine how many days you'll be writing each week. For example, if in the last step you set it to count Mondays, Wednesdays, Fridays, and Sundays, you'll be writing four days per week.

So the formula for that would be (again, you can copy and paste):

=J2*4

Adjust the last number (in red) to match your schedule. Hit Enter and it should calculate your weekly goal based on your daily goal.*

If you didn't group the sheets before entering the formula, you will need to repeat these steps for each of the month sheets.

Ungrouping the Sheets:

If you did group the sheets, you will now need to ungroup them. This step is VERY IMPORTANT. If you don't ungroup the sheets, then every bit of data you enter will be included on all the month sheets regardless of what is already entered. To ungroup them, right click one of the month tabs and click Ungroup Sheets:

Protecting the Sheets:

If you want the formulas to be protected from being accidentally erased you will need to protect the individual sheets. Do this by clicking each sheet and going back to the Review menu. Click Protect Sheet:

And click OK on the window that pops up. You can add a password if you want, but it's not required.

Repeat this step for all the month sheets. Then all you have to do is save your file and you're all set to start tracking your words. :)

Any questions? If you're confused or if something doesn't make sense, please let me know in the comments. I'll do my best to clarify.

*If you think you've entered something incorrectly, or if you accidentally click on the wrong cell and delete something, don't freak out. Ctrl+z fixes everything. :) If ctrl+z isn't undoing the problem, sometimes you have to hit Enter and then ctrl+z. If all else fails, you can always just close the workbook and start over.