5 Years of Continuous Blogging, Part 3

This article is the third one in my series about having blogged continuously for five years. It describes how I collected the basic data for my five years of continuous blogging, used some elementary Excel calculations, and graphed some results.

The pixstrip shows a truncated Excel tally sheet and a partial Word-paste window. The Excel portion has a red rectangle around the recipe article that I did the word count on. The partial Word paste shows truncation of the recipe text. The lower left of the Word window displays the word count without needing to open menu options or use shortcut keys. How convenient!

Creating a spreadsheet with initial data
I tiled the Word file of my catalog next to a browser view of my blog site to create the following columns in Excel:

1. Article titles ("Article Title")
2. Word count ("# Words")—more on that farther down the article
3. Image, y/n ("Image, 0 or 1")
4. Recipe, y/n ("recipe, 0 or 1")

I differentiated the time periods (September to August over five years) by changing font colors. If you choose to try assessing your own blog over years or other time periods, you can try differentiating by text color, bolding, italics, border looks, etc. (For recipes, I flood-filling rows for quick visual cues.)

In retrospect, the catalog was helpful for quickly confirming recipe status, but not crucial. The most important compilation tool was the index in the blog. It grouped by years and months, and linked to all the articles.

Although I created a column for word count early on, I collected the word counts after creating the rest of the spreadsheet. It seemed more sensible to do word count as a separate process than going through each cell of each row. The word count process follows.

EZ Word Counting
For multiple article word tallying, it was helpful using a wide-screen monitor for tiling my blogsite on one side, the Excel tally sheet on opposite side, and MS Word somewhere else. I performed the following steps:

1. Opened the blog article webpage.
2. Selected the content—excluding title, superfluous info, images, keywords, and reader comments, and pasted it into the clipboard (Ctrl+C).
3. Opened Word, pasted the clipboard contents onto the page (Ctrl+V).
4. Noted the word count at the bottom left of Word window, and typed it into the appropriate Excel cell for word count.

I repeated the process of opening a blog article, copying, pasting into Word, and entering the word count into Excel. However, when bringing Word back to the foreground, I selected the existing text and pasted the new text in its place. I continued with this copy/paste process till finished.

Using Some Very Basic Excel Commands
Using formulas is not necessary in the info gathering, but interesting to calculate totals, averages, highest and lowest word counts (endpoints), and medians (midpoints). Creating graphs require two main layouts of info in Excel—1) calculated values in cells in grid form and 2) and "physical" cell distribution.

The only formula that is necessary for creating the graphs for contrasting numbers of articles over n years is summing. In the Excel window, select the cells, and select the summing icon—
Formula tab > Autosum icon.

Expanding the icon displays options for Sum, Average, Count Numbers, Max, Min, and More Functions. Besides using the summation formula for obtaining the data for creating the graphs in Part 1 of my article series, I also used the percentage formula for creating the table.

Note: I copied some of the data and placed them in another clear area so I could select adjacent cells and use graphing features Insert tab > Chart > 3-D Clustered Column and Chart > Line, respectively. Right-clicking in various areas provided additional options, such as displaying data labels, and modifying titles and other labels.

In case you are a blogger and have not yet tried collecting info on your own articles but might want to try—well, the tools are in the three parts I've written. Click "1" and "2" for the first two articles. For "5 Years Continuous Blogging, Part 4", I will share article links and stats that resulted from my trip down memory lane, my memory lane that I created for myself.

---

October 31, 2014: Links to the series

1. 5 Years of Continuous Blogging, Part 1
   Focus on single- and five-year views for total articles, articles with images, and recipe articles.
2. 5 Years of Continuous Blogging, Part 2
   Focus on numbers of words in articles and graphical representations.
3. 5 Years of Continuous Blogging, Part 3
   More details on collecting the data.
4. 5 Years of Continuous Blogging, Part 4
   Emphasis on data sorting and distribution of word count groupings.