Excel: Multiple Cell entry

Here’s a quick keyboard tip, instead of copying and pasting the same information in multiple cells (too many steps!), try the following.

  1. Pre selecting the cells you want to enter data in.
  2. Enter the data – BUT instead use the Ctrl + Enter keyboard shortcut to confirm the data. This will duplicate your data entry to every cell you have selected.

The toughest part of this shortcut is remembering to use it!

A new course: Social Media Nuts & Bolts

I’m teaching a new class. Check it out!

Course Description:

Social Media Nuts & Bolts

Saturday, January 30
1-4 pm
Redcliff Library
Free

Go beyond Facebook, Twitter and other social media for yourself, community groups or business.
Topics:

  • Not just Facebook, Twitter and Pinterest – the other free services you need to sign up for.
  • Whose account is it anyway? Making sure that your organization retains ownership of your accounts when volunteers change.
  • Not all social media channels are the same. Takin advantage of the strengths of Facebook, Twitter and Pinterest.
  • The right message at the right time. Setting up scheduled posts, tweets and pins.
  • Developing your presence; means developing your content. Ideas and brainstorming for your messages.

Bring your tablet or laptop and connect to the library’s free Wi-Fi during the class.

Call 403-548-3232 to register by January 22.

Excel: Adding time

Addingtime1
Automatically adding 30 minutes to the Start Time

In my Social Media spreadsheet I want to add 30 minutes to the starting time for the post.  Why 30 minutes? That’s the default scheduling time in Google Calendar.

To do this, I use the TIME function. The TIME function has 3 arguments; hour, minute and seconds – all three arguments are required. So my formula would look something like this:

=F2+TIME(0,30,0)

So, I’m adding 30 minutes to the value from cell F2.  Easy!

Excel: Find the Weekend

In a previous post I showed how I entered a column of repeating dates when building my Social Media spreadsheet. The next thing I like to do, is colour code those dates so that I can see at a glance when the weekend dates are. For this I use the WEEKDAY function in Excel.

Weekday function example1
Weekday Function Example

Point the WEEKDAY function at a date and it will return a number from 1 thru 7 indicating what day of the week the date is.  In this case the formula reads =weekday(A2,2)

The 2 in the above formula is the return type, and here indicates that the week starts on Monday. This means that Saturday and Sunday will return values of 6 & 7.

This is perfect for using with conditional formatting.

If I plug the following formula into the conditional formatting dialog box
=(WEEKDAY(A2,2))>5

I am testing for values above 5, namely the weekend. So I can use this to put a colour fill in those dates so that they stand out.

Weekday function with Conditional Formatting
Weekday function with Conditional Formatting

Obviously, the Results column isn’t needed because the formula is actually residing in the Edit Formatting Rule dialog box.

This is the second post discussing using Conditional formatting with a Social Media spreadsheet.  Check out this previous post for another example of using conditional formatting.

 

Excel: Sequential Dates in Multiples

When I’m setting up my Social Media spreadsheet in Excel, I like to limit the number of scheduled Facebook entries per day. Over time, I’ve come to think that 4 Facebook entries per day is a reasonable maximum.  This lets the librarian post “live” when things are happening in the library without clogging up our follower’s feeds.

So I want to create a column of dates that looks like this:

Each date is repeated 4 times
Each date is repeated 4 times

 

The quickest way to do this with minimal typing is to use the Fill Series dialog box. Since Excel 2007, you can find it under the Fill menu on the Home tab.

Finding the Fill Series Dialog
Finding the Fill Series Dialog

To use the Fill Series dialog, select the range of cells you want your dates to be entered in. Make sure the first cell in the range has the starting date. Then select the Fill button and choose Series.

The Fill Series Dialog
The Fill Series Dialog

Enter a Step value. In this case, because I want 4 repeats of each date I’m using .25 as the Step value.  If I wanted 5 repeats, I’d use .20 (and so on).

If you don’t feel like calculating how many cells to select when doing this for a date range that spans a couple of months; try using a Stop value. With a Stop Value, the series will stop at the first instance of the date entered into the field. Otherwise, the series will fill the entire selected range.  ( In the picture above the full date is not displayed in the field, it was actually 06/01/2016.)  Using a Stop Value allows you to make a rough selection (say 500 cells) and Excel will stop when the series runs its’ course.

 

 

Email Aliases

I don’t know about you, but I have a number of email addresses.  For example:

  • I have my main work email address.
  • The email address I use for volunteering, because the variant spelling of my first name confuses people.
  • The email address I use for shopping on line when I think the company is going to spam me

These are all forwarding accounts which means mail sent to them is all forwarded into one email account. Personally, since IMAP supports folders, I use folders to organize that email account. This lets me move between my desktop and mobile world with minimal confusion.

But using these email addresses from my iOS devices has previously required that I create multiple email accounts on my iOS device. Which I find confusing; my unread mail counts are wrong because items are counted multiple times, moving mail into folders may or may not work depending on what account I’m using to view the mail, etc.  It just hasn’t been a very satisfactory solution.

Its’ been made more frustrating, since for years in Outlook I’ve been able to send mail using different email addresses.

But yesterday, I tripped across I feature I think is new in iOS 9.1. The ability to add email aliases to my main account.

Some caveats: I have only tested this with an IMAP account, I’m using iOS 9.1 and iOS 9.2 has just been released. And using an email alias does not change the internet headers attached to the email. In other words, anyone who goes under the hood to examine my email can still see that it was handled by my main email account.  Which is fine, I’m not interested in spoofing mail accounts, rather I just don’t want to confuse people by continually responding from a different email address than the one I gave them.

So to set this up go to Settings>Mail, Contacts, Calendars and pick your email account. Open your Account Information:

iOS EMail Account Settings
iOS EMail Account Settings

You’ll notice the > beside the email address, which indicates another dialogue box.

adding a new addressWhen you move to this Email dialogue, you will find the Add Another Email … option which allows you to type in another email address. This can be any email address, as it is not verified. So make sure you spell your email address correctly.

And when you are done (and its embarrassing how long it took me to figure this out) press the RETURN button on the keyboard to confirm the email address you’ve added. Or it will be lost.

The edit button allows you to choose which email address from your list will be the default.

The From line
The From line appears

When you create a new message you’ll find the From: line appears, using your default address. When you want to change the email address the message is from, touch that line in the message ,

Picking your email address
Picking your email address

and a spin button will appear showing the list of alternate email addresses you’ve created.

Easy to use!

 

On with the show

Normally, people save their PowerPoint presentations in the default format. However, once you are on the final version of you presentation consider using the PowerPoint Show format. Saving your PowerPoint presentation as a show is easy.  Use the Save As command and use the Save As Type list to show all the possibilities. Select PowerPoint Show and save as normal.

PowerPoint 2010 Save as Dialog. Not many changes here.
PowerPoint 2010 Save as Dialog. Not many changes here.

The show will be saved in a different file format, using the .ppsx or .pps file extension.

The result is a change in behaviour when the file is opened. Double-click on the file and it will launch immediately into Slide Show view. Much slicker than starting the presentation, allowing the audience to view your notes, finding the slide show icon and starting the presentation. If you have a presentation that uses timed transitions and you are worried about the presentation running away on you, remove the timing from the first slide. Use a mouse click to advance to the rest of your timed slides once you are ready to start.

If you need to edit your presentation, start PowerPoint and use it to open the show. You can edit the file as you would normally. If you wish to convert it back to a regular presentation, use the Save As command and save it in the normal file format.

iOS App Recommendation: Due

I’m recommending an iOS app called Due. I’ve had it on my iPhone, almost from the beginning. The reason is that the iOS reminder functions and calendar alerts are too polite. For things I really need to be reminded of I use Due. You can set the reminder sound to be loud, obnoxiously loud, can hear it in a busy shopping mall loud. Sometimes I need that 😉 . You can also set it to endlessly repeat and snooze reminders as necessary.

Its’ a paid app, but it has been worth it.

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.