Home On The Range

Sometimes, when you’re teaching, its’ not about the complexity of the subject. Sometimes its a very simple piece of information that students get the most “mileage” out of.

When I’m teaching students MS Excel, the simplest thing that I teach them about is Named Ranges. Its the  simplest thing to talk about, but the uses for ranges go on and on.

Excel Spreadsheet Example
A plain spreadsheet.

Above you see a standard Excel spreadsheet. Adding a range name or two (or ten) can help make it much easier to work with.

Adding a Range name to the Cell Address box.
Type the range name into the Cell Address box. Press Enter when done.

A range name can refer to a single cell or a group of cells, here I’ve selected the cell containing the total for the six month period (H11).

Click into the Cell address box (circled in red) and type in the desired name. There are some simple rules about naming ranges; the name can’t start with a number, can’t look like a cell reference (imagine how confusing that would be) and can’t use spaces and special characters (notice I’ve used an underscore to separate words). But after that it is up to you, to make your range name meaningful.

Adding a range name to a group of cells
Adding a range name to a group of cells.

If you are going to add a range name to a group of cells, select them and type the name into the cell address box. The most frequent mistake students make at this point, is that they forget to press the Enter key to confirm the range name.

Now, how do you use these range names?

Navigating your spreadsheet using range names.
Navigating your spreadsheet using range names.

First, you can quickly jump to your named ranges by using the drop-down menu. When you click on the drop-down menu in the cell address box, you’ll see a list of all the ranges you’ve added to your spreadsheet. Regardless of what sheet they are on. So you can use this to quickly jump to those cells that you work with again and again.

Range Names can replace cell references in formulas
Range Names can replace cell references in formulas.

Second, you can replace cell references in a formula with range names. Does =SUM(January) seem easier to read and understand than =SUM(B2:B10)? Then a formula that uses range names will make your spreadsheets easier to read.

Third, you can use range names in conjunction with all sorts of other Excel tools. As an example, try using range names with the Data Validation tool.

A Named Range provides the source for this data validation list.
A Named Range provides the source for this data validation list.

In the sample above, a range name provides the source list for a drop-down list.

Data Validation Result
Data Validation Result

Resulting in this drop-down list. The list will update as the list of animals changes on Sheet1.

This is a more elegant solution for using drop-down lists, since it means your source lists can be kept on another sheet, and not clutter up the working area. This is something that is impossible to do, without using a range name.

So faster navigation, easy to read formulas and access to more powerful features in Excel. What’s not to love about range names?

A suggestion for improvement

One of my great interests is in the interactions between Seniors and technology. As a rule; seniors are not well served by technology, they are a forgotten group. And yet, I believe that tech has a great potential to help in so many ways. In particular, adaptive technologies can do so much to help people with their daily lives, and those technologies can move out to the wider population.

It’s revealing when you find an app or function that would be perfect, if only the designers had kept seniors in mind. Because it shows how much we all don’t want to face growing old and the inevitable changes that time brings.

The app I’m thinking about today, is Day One. Day One has been a well reviewed diary/journal app on iOS for a few years. Yet it misses a really basic feature because its’ designers can’t envision anyone other than their intended audience using their product.

I found Day One for one of my senior clients. One of the things you might have heard about getting older is that sometimes you are a little forgetful. Oh, and you also might have some medical issues. Which might result in more than a few doctors office visits. Which would mean that a nice journaling app could prove useful. Especially one that could support multiple tags on a single entry. Which would mean that you could filter your entries by the name of your specialists, or medications, or illness or test results. It would be excellent if you could export information from the app (Day One allows for PDF exports of entries) and add photographs. So far, so fantastic; Day One does all these things and has beautiful clean interface.

What it doesn’t do, is take advantage of iOS’s accessibility features.

Day One Screen: Magnifiying Glasses Required
Day One Screen: Magnifying Glasses Required

While you can change the size of the typeface in your entries. Day One ignores any preference you have set for Larger Text, Bold Text or Increased Contrast. Which means that people with vision issues are going to struggle with using Day One, if they don’t give up on it altogether.

What I would love to see in this app is a high contrast setting; that would allow the tag icons to resize, adjust the labels and menu items to a typeface that is bolder/larger and that would allow the user to select a high contrast colour scheme.

I also want to see Apple lay down the law with developers and require them to respect user accessibility settings. Designers should look at designing for accessibility as a challenge that will improve the functionality of their apps for everyone. We don’t always interact with our devices under the best visibility circumstances. We may not be facing (yet) the vision problems caused by illness, but we are all going to get older.

Do you have an app that is useful for seniors? Tell me about it.

Well, this is weird …

I’ve had some oddball posts recently on my Twitter account. But I really have to step back for a moment and explain how they got there. I use IFTT to add pins I make to my Pinterest boards to my Twitter account. I enjoy looking at fantastic Steampunk art and costuming and hope that other Twitter users share my enthusiasm.

Steampunk rabbit: made of gears

I noticed a couple of months ago, that items I had not pinned were being posted under my #SteampunkFun hashtag. I kept an eye out for it happening again and it did. What I believe is happening, is that Pinterest is  jumbling up their RSS feeds. In this case, someone else’s humour pins are being attributed to my Steampunk board. But only for short periods of time. But during that time period I suddenly tweet jokes labelled #SteampunkFun – it certainly looks weird.

But what if I’ve been hacked? Wouldn’t I feel silly if I had done nothing to protect myself. This is where a Password Manager comes in handy. I recommend using password managers all the time. There are a number of popular ones out on the market. I use one called PWSafe2 (Apple). I chose PWSafe because I could use it on my Windows, Apple and Android devices. And also because it was developed by Bruce Schneier. PWSafe has a lot of nice features; including the ability generate complex passwords and a notes area.

I use the notes area to write down the lies I tell when asked for security questions. You know, the ones that ask you where you where born or what your mother’s maiden name was. Since this is all public information, I figure a lie is actually more secure than the truth. The problem is remembering what the lie is. Fortunately, the notes section is very helpful with this.

But the feature I made use of this time was the search feature. You see; if I’d been hacked, the problem would be determining which of my many many accounts had been compromised. So I used the search feature to see if I had reused my Pinterest username, email or password on any other accounts. This allowed me to target the most likely accounts and update their information. And because I have everything recorded in PWSafe, updating my accounts was no big deal.

So a password manager not only helps record your passwords and account information, but it can help you quickly change and update your accounts .

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


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.


#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.


* 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.