Social Media Spreadsheet – Conditional Formatting

I’m a big fan of Excel’s conditional formatting feature. I use it a lot in my spreadsheets to check on the quality of data, find errors and many other tasks. Here is the first of a couple of examples of how I’m using conditional formatting in my social media spreadsheet.

Just a bit of background on the spreadsheet. I use this spreadsheet to compose Facebook Posts and Tweets for the Redcliff Library. I also use it to schedule when the posts/tweets will be published. This allows me to sit down and plan a coherent sequence of posts/tweets.

I often take the Facebook posts and cut them down to shorter lengths and reuse them on Twitter. Twitter has a character limit of 140 characters. However, I don’t want to use all 140 characters if I can avoid it. Its’ generally recognized that the ideal tweet length is around 120 characters. This length allows others to retweet and add hashtags without having to edit the tweet.

So I have created 4 conditional formatting rules to help me meet this length limit.

  1. The background of the cell turns bright red [STOP] if the tweet is over 140 characters.
  2.  The background of the cell turns dull red if the tweet is over 135 characters.
  3. The background of the cell turns bright orange [WARNING] if the tweet is over 125 characters.
  4. The background of the cell turns dull orange if the tweet is over 120 characters.

Why four rules? I could use 2 warnings only; at 120 and 140 characters respectively. In fact, that is where I started. But, writing tweets can be a tricky thing and I found I needed a little wiggle room to help me when I compose. The other thing to keep in mind is that the conditional format isn’t applied until I finish editing the cell (by pressing the Enter key or the checkmark). It is possible to have an interactive format applied using VBA, but those functions are memory intensive and slow down the whole spreadsheet. Since my writing process seems to involve a lot of pauses to think, stopping to  apply the conditional format isn’t really a big problem for me.

So what does it look like in action?

Conditional Formatting Results
Conditional Formatting Results

As a result, I can quickly identify which tweets need to be edited. Here are the 4 rules as displayed in the Conditional Formatting Dialog box.Conditional Formatting Dialog

These are formula based conditional formats.

closer look at formula
The formula the conditional format is based on.

 

 

A conditional formatting formula must return a value of TRUE in order to fire. The following formula uses the AND, SEARCH and LEN functions

=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)

If you were reading this formula in something like english it would read: “If the letters TW appear in column B AND the length of text in this cell is more that 140 characters the result equals TRUE”.

Why am I testing for the presence of TW in the subject column? Remember I said that I had both Facebook and Twitter posts in the same spreadsheet. I don’t want the conditional formatting to flag Facebook posts, which by their nature are longer.

Pro Tip:

When you are writing a formula for conditional formatting, do it in a cell in the spreadsheet first. The dialog for conditional formatting is really cramped and you don’t get any help features. After you are sure the formula works, you can then copy/paste it into the dialog using the Ctrl + V keyboard shortcut. Also, because I planned on apply this conditional format to the entire Description column ($H:$H). I had cell H1 selected when I built the conditional formula. That way the formula will adjust relatively to the entire column. Using absolute and relative references properly is another tricky part of building conditional formatting formulas.

applying the coloured background fill
applying the coloured background fill

Once I have my formula built. I can click the format button and select the background colour fill.

 

 

When I’ve built my first format successfully  I can then use it for the basis of the subsequent formulas. Just changing the length of the text in the cell.

  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>135)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>125)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>120)
The order of the rules and the stop if true flags must be set.
The order of the rules and the stop if true flags must be set.

To make this really successful, the rules need to be placed in the proper order, with the Stop If True flags turned on. Now excel will check to see if the text exceeds 140 characters first, then 135, then 125 and finally 120.  The Stop if True flag doesn’t need to be set on the final rule, because no other rules follow it.

Conditional formats can take time to build, but are extremely useful in many ways.

To find another example of using conditional formatting with a social media spreadsheet check out this post.

iOS 9 Wi-Fi Assist

In case you missed the news, there’s a CellurWiFiAssistnew setting in iOS 9, that you might either love or hate. But Wi-Fi Assist could come as a big surprise at the end of the month when your bill comes. When Wi-Fi Assist is turned on (as it is by default) if your wi-fi connection is poor, your cellular data will be called on to fill the gap.  You know, I’ve been some places where the wi-fi connections were so miserable, that I have switched to cellular. But I like knowing that I’ve made the choice, so my cell phone bill isn’t a huge shock.

You’ll find this new setting under Settings>Cellular and scroll down to the very end of the section. Its also a good reminder to go through all your apps and restrict which ones can use your cellular data.

What I learned on my summer vacation

5 Things I Learned On My Summer Vacation

I volunteer as a Board Member for the Redcliff Public Library.  We are a small library in a small town, but we’ve agreed that one of the important things that we need to do is to promote our library  on Social Media. Of course, there is no extra money to hire additional staff to perform this responsibility. And so, one of the tasks I’ve taken on, is handling the library’s social media campaigns. It has been a good experience, partnering with our Library Manager to promote our library through various social media channels*.

This summer I worked on 3 different campaigns promoting the library. While I’m rediscovering the wheel here; I think the wheel is the same shape regardless of the size of your audience/campaign. So, here are 5 things I’ve learned about using social media effectively.

#1 Organization is Key

If you’re not prepared to be organized about your social media you might as well not start any kind of social media campaign. The problem – many people (including myself at the start of this project) think of social media in terms of hanging out on Facebook and Twitter and playing on Pinterest. They don’t think of it as real work. If you’re going to do it right – it is real work and you need to get organized to do it. After running three social media campaigns for our library, I’m building a workflow and the tools to support that workflow. I also think about the workload differently now. Various campaigns may represent peak effort, but in order to keep the connection with our audience alive (see #3), we need to plan to stay active all the time.

#2 Know Your Audience

It seems so simple, of course you’ve got to know your audience. But really, who are you talking to? To get the best results for your effort on social media, be prepared to re-evaluate who your audience is. For example; initially on Twitter our follows were of other libraries and and book oriented accounts. But the longer this social media project goes on, the more our definition of our audience and our purpose for talking to them evolves. Currently, we see all social media channels as methods for connecting with our patrons. And by demonstrating our strong connections with our patrons, we are also using social media to influence our funding agencies.

#3 Silence is Golden – Except When it Isn’t

Don’t wait for your announcement/programs/promotion before you start to speak to your audience. The social media space is a busy place and people aren’t going to hang around watching to see if you’re about to say something.

Instead

#4 Give First

Bring value to the conversation with information that is useful and/or interesting to your conversation partners. This is good manners and good conversation in real life, and it works online too. Good value can be kitten pictures or local information. Targeting your audience will help you decide what information serves them best. I  won’t say the worst thing you can do is post random stuff, but random does make you appear less focused. It makes it harder for your desired audience to understand what you can do for them.  Take advantage of the analytics provided by the various social media channels. What posts are liked and shared? What tweets are viewed most often?

#5 Make Connections

Make connections with complementary organizations. Linking to partners also helps with the ever present problem (demand) for more content. It wasn’t until I started including information from the Town of Redcliff’s Programs and Events calendar; that I felt like our information stream started to achieve a natural flow. That is; we’re publishing lots of useful posts/tweets, without always harping on our particular program reminders and solicitations. Instead, “our stuff” is placed in an attractive context of useful information.  Connections have helped with content and those connections extend our reach by sharing and reposting  our content.

Have I figured everything out yet? Of course not, social media is a work in progress and so is how we use it.


Footnotes

* What do I mean by Social Media Channels. For now, for us, it is Facebook, Twitter and Pinterest.
†By workflow, I mean creating a series of steps that will take an idea and create a series of posts, tweets etc to promote it. Very little of this is automated. My goal is to create a workflow that any volunteer could follow.
My tools include at this point: an Excel spreadsheet, an IFTTT account, Google Calendar, and a bit of Hootsuite. This article from Hootsuite started me thinking about how I wanted to design my own spreadsheet.


 

Never be the first

Never be the first to  upgrade  software or buy new hardware. It’s a good rule of thumb that’s helped me avoid problems like this. Personally, I usually wait a week, watch the tech websites and then decide if it’s the right time. I’m sure I will upgrade to iOS 9. It will just be a matter of timing.

Also, back up your device before upgrading, so if the worst happens you don’t lose everything.  If you can, perform your upgrade via iTunes – not wifi.  In my experience, choppy or poor wifi connections can lead to upgrade grief.

Has there been an update that I regret? Well yes, upgrading my mini2 to iOS 8 was really a backwards step in terms of performance.  And subsequent updates to 8.4 etc. didn’t solve the performance problems. But I knew it was risky when I did it. But I wanted to see the accessibility features (which were an improvement) in action.

In fact, my mini might be the first device that I do upgrade to iOS 9. Since I don’t have much to lose at this point, it might be a good way to check out the backwards compatibility of iOS 9. In theory with iOS 9 Apple is doing more to ensure that older devices will continue to perform well. I’ll let you know.

Apple 9/9/2015

Did I get what I want from the Apple Event today?   Yeah, pretty much. Although we still don’t know if any podcast improvements are happening.   🙁

But in the main I’m pretty happy with what I saw today. Apple is pretty clearly positioning the iPad Pro  for business users and as serious competition for gaming laptops.   The mini 4  gets a much-needed hardware update.  And there certainly is enough  improvement in the iPhone 6S category to warrant an upgrade from a 5S.

What I’m hoping for from Apple

Well it looks like September 9th is the date when we find out what’s coming out this fall from Apple. As usual, there’s all sorts of speculation, although things are kind of quiet on the iPhone end. I think because of last year’s physical changes to the phone people can’t quite imagine what they’ll do to the phone next.

The iPad however is a different question. Some of the changes coming with iOS 9 that were previewed in June have pundits predicting a “business sized” iPad to be called the iPad Pro. In particular, the ability to tile multiple apps on screen.

One of the wonderful things about the iPad is its’ portability. However, it really wouldn’t take much of a size increase to bump up the display to the rumoured 12.9″ dimension.  Increasing the physical form to roughly the size of a standard 8 1/2 by 11 piece of paper would do it. The other aspect of the Pro size is that it would have more space available for accessibility options, much like the iPhone 6 Plus offers larger icons and viewing options. For a certain demographic this will have real appeal.

Of course, this kind of leaves the iPad mini in the dust. And people have been predicting the death of the mini since the new larger phones came out. I regretfully put my mini aside for a newer Air 2 a few months ago, and while I love the power of the Air 2, I miss the portability of the mini. It was great for travel and reading. But my version the mini 2, simply couldn’t cope with iOS 8.

I’d love to see a better processor in the mini. An improved camera would be great too. While taking pictures with an iPad remains kind of dorky looking; people do it all the time. Why not give them a better camera?

Design-wise, I hope that the move to ever smaller touch points comes to an end.  I’m not sure if the entire iOS design team has run their fingers through pencil sharpeners in order to use their devices. But regular humans aren’t about to do that.  I assume that the improved text selection in iOS 9 is an attempt to make life easier for people with normally sized fingers.  I’d also really, really like to see the Podcast app get some love. Since iOS 8, the podcast app has become a real battery hog, and running it noticeably heats up my phone.

What are you hoping for from Apple on Wednesday?

 

Smart Playlists

I have an intense dislike of iTunes. I used to think that  it was deliberately lousy on Windows machines. Then we got a Mac and I found it was just as terrible on it as well.

But there is one thing that I like about iTunes,  and that is the ability to create a Smart Playlist.   A smart playlist is one that adapts; automatically adding songs based on the criteria that you set. Right now, smart playlists can only be created through iTunes but I have hope that soon I’ll be able to create them through the music app on my iPhone.

iTunes Smart Playlist
iTunes Smart Playlist

To make a smart playlist  select the  File, New,  Smart Playlist menu choice

from within iTunes.

My favorite smart playlist is one that finds songs that I haven’t listened to in the last  month  and plays them for me.   It’s a great way to keep from listening to the same music over and over again.  As songs are played, they are removed from the playlist and new unplayed songs are added to the playlist.

Smart playlist settings
Smart playlist settings

Here are the settings that I use to make my  Not Recently Played  playlist. The  most important setting is to ensure that song has not been played in the last month. The rest of the filters remove holiday theme music, videos, audiobooks, podcasts  and any music I’ve given a 1 star rating to.  Right now I’m limiting the  length of this playlist to  30  songs.  The random selection option, is acting more like an alphabetical selection right now. Previously it was truly random *shrug* (the oddball behaviors of iTunes are nothing new).   I’m hoping that this will repair itself with the next version of iTunes.

When you sync your iPhone with iTunes be sure to  select  your Not Recently Played playlist,  so the playlist is pulled over to the phone.

List of smart playlists
Smart playlists have a different icon than regular playlists

In the new music app, this is what your playlist will look like.  These playlists have a different icon, and of course you can’t add songs to them manually. You can see from my list  that I also have a smart playlist looking for the word  “happy” in the song title  and playlists looking for recently added music on the basis of  musical genre .

Those playlists are a little less successful because genre tags are not always applied consistently. Or at least music is not always classified the way I would classify it.  Again,  it would be great to be able to add metadata to songs from within the  music app on the phone. But that type of editing has to be done from within  iTunes on the computer. So I usually don’t bother.  Nevertheless, I really like my Not Recently Played  playlist since it does  find gems that I would otherwise forget are in my music library.