Just kidding, nothing is hotter than Jon Snow. But! Spreadsheets are pretty darn awesome when you think about it. All you need is a cape, tiara, and spreadsheet software and you can pretty much save the world.
My husband even gave me a t-shirt that says “I [heart] spreadsheets” (that’s actually not a joke). I just like spreadsheets so much because I hate math; Excel does all that for me.
I have a spreadsheet for tweets. I used conditional formatting to tell me whether I’m over my character limit (the cell turns red). I even formatted it for bulk uploading to Hootsuite. Do you want to know how I did it or do you just want the spreadsheet?
I’ll give you both. This will totally impress your boss, girlfriend, whatever… (or you can totally just download mine and tell them you did it, I won’t mind).
Conditional Formatting for Tweets (sexy, right?)
Conditional formatting can be your friend once you figure out how to play nice with it. If you’re using a spreadsheet for a budget, you can set up a rule to alert you when your totals reach a certain amount or your cell phone bill goes over $150. The possibilities are endless.
I’m using the Twitter example for it’s simplicity (and so many people that visit this are probably using Twitter and can relate).
Open a new spreadsheet and format your column headers however you want them. In this case I’m going to set-up my tweet column as column D. Click the “Conditional Formatting” button circled in red below and select “new rule.”
Windows users: keep in mind, these screen shots are from Microsoft Excel for Mac. Yours might be in a slightly different location. If you can’t find it, just type “conditional formatting” in the help search and the help file should highlight where it is.
Next, in the formatting window, the first drop down box (Style), you want to select “Classic” for what we’re doing.
The second drop down option, select “Use a formula to determine which cells to format.”
Next, the formula. The magic. Here I entered the formula
I’m telling Excel that I the cell to change its formatting if the length in cell D2 is greater than 116 characters. So, if you plan on having this in a different cell, change D2 to whatever the first cell in our row or column is in your sheet (B3 or whatever).
Tip: If you don’t want to take into account for a URL, just change “116” to “140” in your formula.
Next, select the “format with” drop down with whatever style floats your boat: red, green, … select OK. Now this crazy window pops up. No worries. Now, we’re going to change the “applies to” section. In order to do that, move your cursor behind the window to your cell (in my case, D2).
Select (with one click and hold) the bottom right corder of the cell and drag it all the way down as far as you think your spreadsheet will carry (if you think you’ll have 50-60 tweets listed, then keep going all the way down past that).
When you’re done dragging, just release your cursor. Your “Applies to” in the window should say something like “Sheet1!$D$2:$D$65” Click OK.
Now test it out. Type in a tweet that you know is longer than 116 characters. It should turn whatever color you specified.
Did it work?
Download the completed template here (sample tweets included for reference).
Tip if using template to bulk upload to Hootsuite: you need to save as a .csv file before submitting to Hootsuite. (File -> save as -> change the “xls” drop down to comma separated values (.csv), name the file, then click “save”.