Excel: Validate!

Data Validation dropdown
Data Validation drop-down

When building my Social Media spreadsheet, I want to enter my subject keywords and trigger keywords consistently. Minor typos can make it difficult to find all the relevant posts and worse; prevent scheduled posts, tweets and pins from being published on time. This is why I find the Data Validation feature in Excel so useful. As you can see in the picture above, once Data Validation is in action, my data entry is restricted to a preset list of options.

DataValidationRibbon
Find the Data Validation tool on the Data ribbon

Since Excel 2007, the Data Validation tool has been on the Data Ribbon. Simply select the cells you want to apply Data Validation to and press the Data Validation button and select Data Validation. Then the Data Validation Settings dialogue box will appear.

Data Validation Settings
Data Validation Settings

To keep the active sheet “clean”, I use a named range on another sheet as my data source (I’ve talked about that previously). Here you can see it’s called PostTypes. But you can enter short lists directly into the Source box:

DataValidationSettings2
The list entered into the source box, each item separated by a comma.

However, I find in the long run (especially for long lists) keeping the list source on another sheet makes maintenance easier.

Excel: Concatenation for the Social Nation

Concatenation results
Concatenation results

As I’ve worked more with scheduling posts, tweets and pins, I’m trying to make the most of the Subject line used by Google Calendar.

Google Calendar Subject line
Google Calendar Subject line

I’ve found that if I combine a meaningful keyword describing the post(or tweet, or pin) plus the phrase that triggers the IFTTT action, then managing the scheduled posts once they are uploaded into Google Calendar is a bit easier. It also makes it easier when I’m filtering and managing the spreadsheet too.

In my spreadsheet I use a separate column each for subject keyword and for subject trigger phrases (actually I’m paring those down to keywords too). But I want them joined together to create the actual subjects. To do this, I use the Excel CONCATENATION function. Which is most simply represented by the & symbol. In the example at the beginning of the post you can see the formula:

=B103& ” ” &C103

In this case I’m using the & symbol to join the values of cells B103 and C103 together with the string ” ” in the middle to create a nice space between words. This allows the subject phrase to be created automatically once I’ve selected the subject and trigger keywords.

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.