Category Archives: Productivity

Google Timer and Stopwatch

Do you wish that you had a timer on your desktop just like the one on your smartphone?

Just type “timer 5 minutes” (or 3 minutes or 10 or whatever) into your Google search bar. A timer will appear. You can maximize it to fill the screen and there will be an audible beep (which you can mute) at the end of the timer.

You’ll notice a Stopwatch panel on the screen too. To get to the stopwatch directly, type “stopwatch” (unsurprisingly) in the Google search bar.

 

Excel: Frozen

Freezing panes is a basic tool to make a large spreadsheet easier to work with. In my social media spreadsheet I like to freeze the header row into position. Then no matter how far I scroll down the sheet, the columns are labelled.

To turn on frozen panes, select the cell below the row & column you wish to freeze into position. Since I don’t want to freeze any columns, I select cell A2.

Freezing Panes using the Active Cell position
Freezing Panes using the Active Cell position

Select the View Ribbon, Click on the Freeze Panes button, and choose Freeze Panes (or Freeze Top Row in this scenario).

Freezing Panes Results
Freezing Panes Results

Now you can scroll for hundreds of rows, and each column is nicely labelled – no guessing!

Excel: Select Visible (2)

The Select Visible Cells Only button on the Quick Access Toolbar
The Select Visible Cells Only button on the Quick Access Toolbar

The Select Visible Cells Only function is so useful, I like to add it to the Quick Access Toolbar (QAT) in Excel. These instructions are based on Excel 2010, but will be similar in all current versions of Excel.

Quick Access Toolbar Customization
Quick Access Toolbar Customization

The Quick Access Toolbar starts in the top right corner of the Excel window.  The customize button is circled in red. Clicking on that button displays the menu shown below.

Move the QAT under the ribbon
Move the QAT under the ribbon

The first change I like to make is to its’ position. I like to move it under the Ribbon, since there will be more room for buttons there. Over time I tend to fill the QAT up with frequently used tools.

After I move the QAT below the ribbon, I go hunting for useful commands to add.  Click the More Commands… option and the Customize the Quick Access Toolbar dialog opens up.

Customize the Quick Access Toolbar - Popular Commands
Customize the Quick Access Toolbar – Popular Commands

The dialog box defaults to Popular Commands.  Try scrolling through this list and find the Format Painter. Press the Add button, to add it to the Quick Access Toolbar. This is a useful tool to have at hand!

By clicking on the Choose commands from drop-down list, a selection will be displayed.

Drop-down list of source commands.
Drop-down list of source commands.

Select All Commands from this list. Hundreds of Excel commands will display, and this is where it is useful to know the name of the command you are looking for. Scroll until you find Select Visible Cells.

Finding the Select Visible Cells command
Finding the Select Visible Cells command

Select it, Click on the Add button, and click OK.

Simply select the cells you wish to copy and press the Select Visible Cells button. Paste your information and only what you see will be pasted.

Excel: Filtering in action

SortandFilter
Finding the Filtering button

Since Excel 2007, the Filter tool has been on the Home ribbon, under the Sort and Filter drop-down. The Filter tool can be applied to any spreadsheet where every row is a new record. Excels’ guesses about what and how to filter will be more accurate if the data has a header row. Your (human) life will be easier if you give that row a little formatting to make it stand out from the data.

If your data has gaps, select all the data (including the header row) and apply the filter. Once the filter has been applied, little triangles will appear beside each header label.

Filtering Drop-Down panel
Filtering Drop-Down panel

Now you can use each header to filter the data.  Click on the filter drop-down and the panel will open as you can see in the picture above. Clear the check boxes beside the entries you don’t want to see. Then click the OK button. You can spot filtered data, because the row headers will be bright blue (and row numbers will be missing as data is filtered out). The columns where filtering is applied will have a filter icon (circled in red in the picture).

Filtering Applied
Filtering Applied

Once the filters are in place, I can filter out blanks or filter blanks in to find openings in our social media schedule. I can quickly look for Posts and Tweets with images, to ensure the image information is present. I can filter down to a single subject. All of these filters make managing my posting schedule MUCH easier.

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.

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!

 

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.

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.

One-Time Schedules

In my last post I discussed Subscription Calendars. These are a useful way to keep up to date without requiring you to re-enter important dates in your calendar. Today, I’m talking about One-Time Schedules. These are useful when you have a schedule of events, usually of relatively short duration, that you don’t anticipate a lot of changes to. For example;  perhaps you are organizing a soccer league and want to give the schedule to coaches and players in a convenient format. Creating a One-Time Schedule lets you create a file you can quickly email to users, that they can import into their preferred calendar. This is also a technique you can use to bring a work calendar into your personal calendar.

One-Time Schedule

Exporting a One-Time Calendar from iOS Importing a One-Time Calendar into iOS
The iOS app Week Calendar, can easily export a calendar in a number of formats. This is a paid app, but it has a number of useful features.I recommend creating a new calendar to contain the one-time calendar, to avoid accidentally exporting personal appointments. Start by filtering your view to only the calendar you wish to export. Depending on the version of the app (iPad or iPhone) use the Share or Settings button. Look for the Share or Export option. Enter the Start and End dates for the appointments you wish to export.From the Type option select the ICS calendar format (most modern calendars use the ICS format, if you are exporting for someone using an older calendar format, then select CSV).Press the Export option.

Choose Send by e-mail and the ICS file will be automatically attached to an email.

From the email, touch the ICS attachment. Press the Add All button. Select the calendar you wish to import the events into. Press the Done button.

 

Exporting a One-Time Calendar from Google Importing a One-Time Calendar into Google
The Android app iCal Import/Export CalDav (Free and Pro), can export a calendar in ICS format. This is a paid app, but it has a number of useful features. I recommend creating a new calendar to contain the one-time calendar, to avoid accidentally exporting personal appointments. Open the iCal Import/Export app. Start by selecting the calendar you wish to export. Use the Active Calendar link to open the Choose/edit Calendar window.Touch the Choose other calendar button to open a list of your Google calendars. Touch the calendar you wish to export.Use the back button to return to the main screen.

Touch the Export button.

The Export calendar window will open.

If it is not already selected, choose Email as the delivery method.

Enter the filename you want for the calendar in the Filename text box.

Enter the email of the person you will send the calendar to.

Add a subject line to the email.

Press the Start Export button.

The file will be created and the Export finished window will appear.

Click on the OK option.

The Gmail app will open and the email with the attached file will appear.

Send the email.

From the email, touch the ICS attachment. The iCal Import/Export app will launch and open to the Import Calendar screen. Use the Active Calendar link to open the Choose/edit Calendar window. Touch the Choose other calendar button to open a list of your Google calendars. Touch the calendar you wish to import into.Press the Continue button.Press the Import button.

 

I hope this series of posts on Sharing Calendar information has been useful.

One-Time Calendar Postcard Front and Back
One-Time Calendar Postcard Front and Back

Related Posts: