Automatic Writing Tracker
This tracker has a ‘behind the scenes’ post where I walk you through how I was able to build this spreadsheet for an online entrepreneur. Read about it here.
What is it?
It is a writing tracker spreadsheet which allows you to keep track of your writing, provides summaries at various levels and shows you useful things through graphs.
The difference between not tracking and tracking something is huge. When you track it, you’re removing the subjective sense of how things are going, and how they have gone previously. This is because you’re recording actual data rather than just having a general feeling of how you’ve been doing.
And when you have data, that means you can create summaries and analysis based off the data you input which can show you lots of interesting things.
When this is all brought together in an automated spreadsheet which minimises the time you have to spend summarising and analysing, it can be really useful without creating loads of extra work for yourself.
And that’s what I’ve done here, with this writing tracker.
It lets you know exactly how you’re doing at the moment, keeps track of your progress, and helps you optimise your writing habits.
Downloading the Tracker
On mobile? Bookmark this page, make a note of this website or email the link to yourself – so you can return when you’re back on a computer and can try out the spreadsheet.
Click the relevant link below, and it will prompt you to make a copy of the spreadsheet:
- US Version (US Date Format)
- EU Version (EU Date Format)
Some of the formulas on the spreadsheet use your time zone to see what the current date is. The US version of the tracker is set to Eastern Time, and the EU version is set to GMT.
To change the time zone to your own, go to File > Spreadsheet settings > Time zone.
Instructions
There are 9 tabs on the spreadsheet, and it may seem a little complicated at first. But you only need to input your data into one tab, and the other tabs are all just for your reference – and will automatically calculate things for you.
The tabs are:
- Tracker
- Daily Stats
- Weekly Stats
- Monthly Stats
- Document Stats
- Leaderboards
- Totals Graphs
- Analysis Graphs
- Formatting Rules
There are 5 different things you need to record during each writing session:
- Date
- Document name
- Start and end time
- Total amount of words at the start and end
- Your energy and focus
And then let the spreadsheet do everything else for you!
Some important points to mention:
- This tracker won’t work over multiple years, so you will need to have a new tracker for each new year. If you start using the tracker in October, then you can only use that specific tracker until 31st December.
- When you first open it up it might take a few seconds to load, and when you type something you’ll notice the loading bar come up as well. There shouldn’t be too much of a time delay, there’s just a lot of different formulas and things going on in the spreadsheet that it’s something to bear in mind.
- When you first open the spreadsheet and don’t have any data inputted, some of the tabs / graphs can look a bit strange. However it will return to normal and look like the below screenshots when you have some writing data entered.
Right, let’s go through each tab and see what the spreadsheet does!
- Tracker
- Daily, Weekly & Monthly Stats
- Document Stats
- Leaderboards
- Totals Graphs
- Analysis Graphs
- Formatting Rules
This is the tab where you will input all of the data about your writing. On the main section, you will see there are “Start Session Input” and “End Session Input” parts. The columns which have grey headings are automatic calculations, and you don’t need to input anything for them.
When inputting the document name, it’s important to type the exact same name if that name appears on the tracker previously. This is because the name is used by the tracker for various features. When you begin typing, a drop-down box may appear so you could select it… or you could simply copy and paste from a previous session.
Energy is supposed to be “how much energy I have when beginning the session” and focus is “how focused was I during that session”. They are both on a scale of 1 – 5, where 1 is the lowest energy / focus and 5 is the highest.
Above the input section, you can see a summary of the data that’s been completed for the current day. Data will only feed into here if all of the input data has been entered for that session. For example, if you have inputted everything for a session but “Words End” is blank, that session won’t appear in the Today’s Data summary.
In the top right, there is a count of the number of consecutive days you have written for. This will reset to zero if you miss a day of writing.
Pictured above is the Daily Stats tab, and it is very similar to the Weekly Stats and Monthly Stats tabs – but just summarises at day level instead.
All three Stats tabs are auto-generating based off the data that is inputted into the tracker tab. So if all you have entered in your tracker is the whole month of April, you’ll have 30 days in your Daily Stats, 4 weeks in your Weekly Stats and 1 month in your Monthly Stats.
For each day, you can see that it has summarised data inputted into the tracker tab. This allows you to keep track of how you’re doing day-to-day in all of the different data points.
For the key data points (like total word count and total writing time), there are graphs that refer to these stats and shows the data visually rather than being in the above format – which can make it a bit easier to read.
This tab summarises writing data for each document that appears in the tracker tab. The document at the top will be the first document you started, and the document at the bottom will be the most recent document you started.
This lets you know instantly what kind of documents take you the longest to write, which ones you’re the most focused whilst writing, or your largest / smallest documents per word count.
This can be useful by letting you see which types of documents you’re the best at writing, and might affect the things you choose to write about in future.
The leaderboards tab allows you to see your best achievements. Sometimes it’s good to take a step back and see what you’re capable of if you’re looking for some motivation.
The table at the top looks for the best writing session for the Current Week, Current Month and the Year. It judges the best writing session based off 3 different criteria: Word Count, Words per Hour and Session Length. You can see that the data point the line is based on is bold and underlined (for example, word count is bold and underlined on the first line, because the criteria for that line is word count).
The bottom left table shows you your top 5 best documents based off Words per Hour, and Total Word Count.
And to the right of that, it shows you your longest writing streak of all time.
The totals graphs tracks 4 things: Total Word Count, Average Words per Hour, Total Writing Time and Number of Sessions. It shows these things at three different levels: Last 30 days (at day level), all data (week level) and all data (month level).
This is useful to see how different aspects of your writing have been progressing over time. This can let you know if you’ve been doing really well recently, or it might show you that you need to improve.
The good thing about these graphs is that it’s an objective assessment, and isn’t based on how you “feel” you’ve been doing. After all, there is no arguing with data. You might be able to justify why things aren’t going well (“I was on holiday”, or “I’m getting used to my writing habit again”), but at least you can see exactly how you’ve been doing.
The analysis graphs lets you see what effect certain factors have on the Words per hour, total word count, focus and energy. It looks at the day of the week, time of writing, session length, number of sessions, energy and focus.
The headings at the top is what the analysis is based on. For example, the top row of graphs in the above picture are all looking at the day of the week. And as you go across the different columns, it shows you your words per hour each day, your total word count, your focus and your energy. So the first row of graphs would let you see your best / word days to write on.
This tab can be really useful to see what works for you, and lets you improve your writing routine to maximise your output when you write. For example, if 1pm is your best writing time and 7pm is your worst… why would you want to spend time writing at 7pm anymore? Or you might see that one day in particular is your best writing day, and you might block out that day as your main writing day of the week.
However, use this tab with caution. When you don’t have much data inputted, you might think you have identified a better writing practice… when really it’s because of the lack of data. Before you make any changes to your writing based off these graphs, I would recommend having at least 4 – 6 weeks worth of data.
Also, don’t think “my best writing practice IS…”, think “my best writing practice COULD be…” and run an experiment. Maybe you could spend a whole month doing something different with your routine and then see what effect it has had compared to your other months.
You will have noticed throughout the spreadsheet there have been various colours on some of the data points. This is conditional formatting, which means that the colour will automatically change when the number of that cell is within a certain range.
The conditional formatting lets you see at a glance how objectively “good” your data is. For example, if you see loads of light greens and greens then you know you’re doing a really good job. Or if you see lots of reds and oranges, then there’s room for improvement.
The ranges for the different colours are dependent on which tab you are on. For example, the ranges for daily total word count is different to the weekly total word count.
So if you’re ever curious about the colours, you can refer to this tab and see what you need to aim for to achieve certain colours.
Don't want to miss out on future content?
Subscribe to my email list
Have a spreadsheet in mind?
Let me take care of it for you