10 spreadsheet hacks to step up your blogging game (ft. free spreadsheet templates!)

As of last week, I have decided to occasionally participate in a weekly meme called Top Ten Tuesday, which was originally created by The Broke and the Bookish and is now hosted by Jana @ That Artsy Reader Girl. This week’s prompt is “back to school/learning freebie” – which got me thinking: What wisdom can other bloggers learn from me? And more importantly, what nugget of wisdom do I even have to offer in the first place?

Answer: spreadsheets. More specifically, spreadsheet hacks that you can use to become a more organized and more efficient blogger with, you know, nicer-looking spreadsheets.

If you’re the sort to follow me on Twitter (which I personally recommend you to do), then you’d know that I sort of, kind of, really like spreadsheets (both the Excel and Google kind) and talking about, ahem, spreadsheets.

Um, does that make me a wee bit weird? Yes? Well, in my defense, I enjoy being organized and spreadsheets are pretty darn helpful in that respect. And I’m not the only one who thinks so! Other book bloggers, such as Marie, Kaleena, and May, have shared how spreadsheets have helped them keep track of their reading and book blogging.

Personally, I don’t just track the books or ARCs I have to read and review. I also have Google spreadsheets for keeping track of my ideas for blogging content, taking note of the books that I want, cataloguing physical copies I own, and the list goes on. In fact, I recently made a spreadsheet tracker for Top Ten Tuesday (which I will be sharing with you towards the end of this post).

With all that said, I think having blogging spreadsheets is an excellent way to organize (and make you feel a whole lot more put-together than you actually are), and here are 10 quick and easy hacks you can use to format and design your sheets!

1. Make multiple sheets (and keep things neat).
Coming from personal experience, spreadsheets can get pretty overwhelming to look at, especially when (1) they have too much information on a single sheet, or (2) the information look really crammed together.

A general rule of thumb is: you should be able to acquire the information you need with just a quick glance at the spreadsheet. If your eyes are struggling to smoothly navigate between the rows and columns, then what you have is – to put it crudely – more shit than sheet.

Hence, you should avoid overloading your sheet with text and information. As an alternative, you can distribute the information across multiple spreadsheets. As an example, I keep separate sheets for my tracker and database. Generally, my database keeps a record of everything I’ve finished (e.g. posts published, books reviewed), while my tracker is used to take note of what’s in-progress (e.g. blog post ideas, to be read books, ARCs in need of reviews).

Essentially, I separate my spreadsheets according to their primary function. For example, if my goal is to keep track of the number of reviews I’m able to write and to publish, all the pertinent information in that spreadsheet is directly relevant to that goal. If my goal is to keep track of the books in need of reviewing, I will make an entirely different sheet that’s in line with that goal. I wouldn’t dare put them both in a single sheet because that would make things a lot more complicated on my part.


Check out my #bookishwish tracker template, which contains a sheet for your wish list of books and another sheet for the books you wish to give away.

Related: The Ins & Outs of #bookishwish – learn what it is and how you can join in.

2. Arrange Advanced Reader’s Copies (ARCs) and/or books by release date.
If you want to know which ARCs/books need to be reviewed more immediately based on their publication date, then it is very advantageous to not just arrange them according to date, but also to have them arranged in a way that you get to see the books with the earliest publication dates first. What you want is for ARCs from 2016 at the bottom of the spreadsheet, while your 2018 and 2019 ARCs are at the very top.

Expand to learn how.

HOW:
1. Make a column for the publication date (MM/DD/YYYY).
2. Right click to select that column.
3. Click on Data >> Sort sheet by X, Z to A* (where X is the column you selected).

* Note: Sorting from Z to A goes from newest date to oldest date (e.g. 2018 to 2000), while sorting from A to Z does otherwise (e.g. 2000 to 2018).

ALTERNATIVELY:
1. Make a column for the publication year, and make a separate column next to it for the publication month. You can make a third column for the publication day, if you’d like. As an example, let’s name them Column A, B, and C, respectively.
2. Select all the content of your spreadsheet.
3. Click on Data >> Sort range
4. Select ‘Column A’ in the dropdown list and set it to the Z to A sorting. Make sure to tick off ‘Data has header row’ box.
5. Add another sort column. Column B. Z to A sorting. Yes to ‘data has header row’.
6. Add another sort column. Column C. Z to A sorting. Yes to ‘data has header row’.
7. Click Sort.

3. Freeze rows AND columns.
For easier viewing, I freeze specific rows and columns on my spreadsheets. Generally, I freeze the topmost row containing the column headers. As for the columns, I make sure the most important information is located at the leftmost section of the spreadsheet, and depending on the situation, I freeze one to three columns.

Expand to learn how.

HOW:
1. Select the last row/column you want to freeze.
2. Click on View >> Freeze >> Up to current row/column

4. Add filters to your column titles/headers.
Not to exaggerate, but I owe much of my efficiency to filters. They’re a good way to filter out information you don’t need at the moment. For instance, if my spreadsheet contains ARCs from 2015 to 2019, but I’m only interested in seeing my 2018 ARCs, then I can easily use filters to isolate them from the rest of the ARCs.

Expand to learn how.

HOW:
1. Select all the column headers on the spreadsheet.
2. Click on Data >> Create filter

5. Use checkboxes.
I’m pretty sure that at some point in our lives, we have all written a to-do list, a shopping checklist, or any type of list that involved checkboxes at least once. Now, correct me if I’m wrong (I highly doubt that I am), but didn’t it feel absolutely fulfilling whenever you could tick off a checkbox because you finished a task, bought an item, or accomplished something you were supposed to do? Didn’t you feel a wee bit accomplished afterwards?

If you answered yes to any of those questions, then here’s a tip I think you’ll appreciate: use checkboxes in your spreadsheet instead of boring yes/no columns. Turn your spreadsheet into a virtual to-do list!


Check out the Top Ten Tuesday tracker template I made recently.

Have you prepared your content for this week’s Top Ten Tuesday? Check.
Has your TTT post been scheduled for publication? Check.
Has your TTT post already been posted? Check.
Have you linked up your TTT post? Check!

Do you want to step up your blogging spreadsheet game with checkboxes?

Expand to learn how.

HOW:
1. Select the column you want to add checkboxes to (e.g. Column X).
2. Click on Data >> Data validation
3. Define the cell range as ‘X2:X’.
4. For Criteria, select the ‘Checkbox’ option from the dropdown list.
5. Make sure the ‘Show warning’ feature has been selected.
6. Click Save.

6. Specify your desired inputs for every column, if possible.
Manually typing down the same information over and over again is not only unnecessarily exhausting by time-inefficient as well. Minimize the hassle by specifying a range of desired inputs for a particular column. More specifically, set a range of labels or categories for columns asking for generic or categorical information (e.g. year, format, genre, source). By doing this, you just have to select from a dropdown list of preset information instead of manually typing anything.


Check out the owned book database template I created for physically owned copies.

For instance, you can classify where your owned books came from using the following general categories: Author, Gift, Giveaway, Publisher, Purchased, Trade, and Miscellaneous. Additionally, you can select from a preset range of labels to describe the condition of your copy (i.e. Excellent, Decent, Worn/Aged, Damaged).

Expand to learn how.

HOW:
1. Select the column you want to add preset information to (e.g. Column X).
2. Click on Data >> Data validation
3. Define the cell range as ‘X2:X’.
4. For Criteria, select the ‘List of items’ option from the dropdown list.
5. Enter the items or labels you want individually, and ensure that you separate each of them using a comma. (Personal tip: Enter the items in alphabetical order.)
6. Make sure the ‘Show warning’ feature has been selected.
7. Click Save.

7. Keep track of your progress with a ‘status’ column.
A ‘status’ column is one of my absolute must-haves in spreadsheets, especially sheets that are designed to track certain things such as progress and current condition. It’s a highly efficient way to know what’s up and what’s next.

Tips on ‘status’ columns? Expand this.

7.1. Place your ‘status’ column as your foremost column in the spreadsheet. That way it’s the first thing that you see, and you’ll instantly have an idea of what you’re supposed to work on.

7.2. Color-code your spreadsheet according to its status. For instance, in my owned book database, books that I lent (or were borrowed by other people) are colored in green, books that are to be given away are colored in blue, and books to be sold are colored in red. Even without looking at the status column, I immediately associate the colors I see to their respective statuses.

7.3. Create a preset range of status options. Using Hack #6, you can make a dropdown list of preset status options in order to avoid manually typing them over and over again.

7.4. When making a preset range, make sure to enumerate the status options in a particular order, preferably alphabetically (e.g. Borrowed, Giveaway, Selling), chronologically (e.g. Idea, Outline, Draft-in-progress, Finished Draft, Scheduled Post), or based on intensity (e.g. Low Priority, Moderate Priority, High Priority).

8. Use gradient color-coding to emphasize order.
As someone who’s literally made a handful of rainbow-colored spreadsheets in the past (the last one being from two weeks ago, just so you know), let me be the first to say: there is definitely such a thing as having too colorful of a spreadsheet. Let’s not blind ourselves, honey.

All the colors in your spreadsheet should have a deliberate purpose and should be strategically chosen. While no one’s stopping you from picking out shades that you find aesthetically pleasing, an excessive number of colors is a huge no-no in my book. Every picked out shade should indicate something specific, be it a status, condition, mark of progress, or level of priority.

One of my go-to strategies in color-coding is to use one-color gradients, specifically ones that progress from lighter shades of a specific color to darker ones.

Personally, I typically make use of gradients for any of the following purposes: (1) to indicate levels of intensity/priority or (2) to show progression towards completion. I often associate darker shades to higher levels or greater intensities. I also like doing this because the spreadsheet ends up looking much neater compared to spreadsheets with a bunch of random color assignments.

9. Don’t just color code!
Yep, you read that right. Don’t just color code! That is the amateur’s way to spreadsheet designing, to be frank. Instead, make use of italics and bold typefaces to emphasize distinctions. I occasionally use the strikethrough, too.

When working with a limited set of colors, especially one-color gradients, making combinations with limited shades and different typefaces is a smart and strategic thing to do. Solely relying on colors will run the risk of choosing more colors once you run out of combinations. Plus, typefaces could indicate several meanings as well. For example, italicized items on the spreadsheet could pertain to things that are in-progress or are currently being worked on, while bolded items could refer to finished outputs.

10. Keep things simple, straightforward, and strategic.
There’s not a lot of right or wrong when it comes to designing spreadsheets for your blog, but in my opinion, it is good to keep in mind that your primary goal is to make things easier for yourself, and spreadsheets are your means to achieving that.

With that said, if you’re struggling to understand the information displayed on the sheets, if you’re frequently failing the quick-glance test (that I briefly mentioned earlier), or if seeing your spreadsheets causes you to feel a tad too overwhelmed and panicked, then that’s probably a little red flag telling you that maybe you should reassess how you structured them.

By the end of the day, the main targets are ease of use and efficiency. So keep things simple, straightforward, and strategic.

What happens now?

Well, that’s totally up to you, really. I can only hope that the 10 things I shared were somewhat helpful to you! I’m not the best at explaining things, but I tried to anyhow.

Here are a few suggestions on what you can do next:

💜 Take advantage of my generosity and download my spreadsheet templates! Okay, kidding aside, you are more than welcome to use any of the templates I featured on this post:

1. #Bookishwish Tracker Template
2. Top Ten Tuesday Tracker Template
3. Owned Book Database Template

In order to have your own copy, simply click on File >> Make a Copy, and then save the sheet in your personal Google drive account.

💜 Start designing your own blogging spreadsheets! It might seem tedious at first, but once you get into it, it can be quite addictive, if I do say so myself. When you’re finished, show them to me! I’d love to take a look.

💜 Suggest more blogging spreadsheet ideas! Who knows? Maybe one of these days I’ll be bored enough to play around and make more templates. (That’s actually a pretty likely scenario.)

💜 Leave links to your Top Ten Tuesday post for this week. I’d definitely be happy to check them out during my free time.

💜 Consider supporting me and my work! You can easily do this by sharing this blog post on social media platforms (see buttons at the bottom of this post!) if you think my tips could help out someone else. If you’d like to help out a financially struggling college student (who lost her opportunity to free higher tertiary education this year), then maybe consider sending a cup of coffee my way or using affiliate links for Amazon and Book Depository (which won’t affect your purchase at all). Thank you very much!

Twitter: @bookshelfbitchTumblrInstagramGoodreadsBloglovin’

Advertisements

Published by

Shealea

First of her name. Queen of lists and spreadsheets. Protector of books. Breaker of norms. Iskolar ng bayan.

51 thoughts on “10 spreadsheet hacks to step up your blogging game (ft. free spreadsheet templates!)”

  1. I absolutely adore your take on a TTT topic! Brilliant! I need to save your post forever – I’m really rotten at spreadsheets and organization in general – thanks so much for the simplified, step-by-step instructions! 😀

    Like

Your two cents?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s