Things I wish I knew earlier 3. Importing Excel into Outlook appointments

At work I use Outlook as my all-in-one software for emails, appointments, and personal task management.

All my meetings, appointments, and time I’ve chosen to specifically allocate to work on certain tasks – go into the Outlook calendar. Excpet for one very important thing – the desk roster. Until today.

Before I would consult a piece of paper with the roster printed on it which created discordance between my desk duties and other responsibilites which were managed electronically. This meant that:

  • I would occassionally double-book myself into a meeting when I’m meant to be on the desk, as it is harder to detect conflicts.
  • My availablity in Outlook would not represent reality, as colleagues would see me as free when I actually have a desk shift.
  • I would sometimes forget to go out to the desk, being too absorbed in my work and there is no electronic reminder pop up, so I’d either be late or the colleague that’s meant to hand over to me would need to remind me. This is pretty rude on my behalf and I’d like to be punctual to my desk shifts.

I hear you asking – why don’t you just enter your desk shifts into Outlook, like everything else? The answer is: the effort outweighed the benefit. It wasn’t a problem often enough to justify entering all my desk shifts into my Calendar, which happen several times a day and are not neatly recurring, so the data entry is a repetive task without good return on investment.

But – yesterday I had the sudden insight that it would be much better to put all my forseeable desk shifts into a spreadsheet and import them. You can import data via csv into just about any software these days. So I found this how-to and imported a week’s worth of rosters. Adding several individual appointments every day is too cumbersome, but doing it via excel is a 5 min task that I do every few weeks when the roster comes out and that will help my productivity and organisation immensely.

The guide I linked to above gives a quite detailed example, but mine is more simplistic.

2017-10-29 15_38_49-Calendar import.xls - Excel

You need to give your table headings that match the field names used in Outlook.

Subject: name of the “appointment”

Start date: self-explanatory

End date: put in the formula so it just equals whatever is in column B.

Start and end time: self explanatory

Reminder on/off : Entered TRUE to enable reminders

Reminder date: Did the same formula as the end date so whatever I put in start date automatically populates.

Reminder time: What I did was in column I, I entered how many minutes warning I wanted, usually 2 mins except for Refchatter (where I want a bit more time to get ready before I log in). The reminder time column has a formula which minuses that time from the start time. This is simpler than working it out for each row.

Save as an excel file so you can keep the formulas and overall template, so next time you only need to adjsut the dates and times. Then save it as a .csv and import into Outlook.

This approach has less manual data entry and is more customisable. (Outlook only lets you set the reminder time a minimum of 5 minutes prior, while I prefer a very short reminder of just 2 minutes).

Advertisements

Things I wish I knew earlier 2. The “Sort” function in Word

word-sort

SINCE WHEN.

Here’s the long awaited (ha!) second installment of my Things I Wish I Knew Earlier series. It’s for new things I discover that leave me cursing the fact that I didn’t know about them earlier.

So just in case you’re like me and you have somehow missed the following fact most of your life… Microsoft Word allows you to sort stuff in alphabetical, number or date order.

It’s the friendly little A/Z↓ button in the Paragraph section of the Home ribbon.

It’s a good function if you’re compiling a reference list and not using any reference management software (RIP you). Or any list that you want sorted in alphabetical order. It doesn’t matter what order you add items to your list – you can sort the text alphabetically at the end of the process.

Also works for tables. If you need to re-order your rows, alphabetically, numerically or by date, you can do it. It’s very similar to Excel’s sorting options, but in Word. Who knew?

So stop cutting and pasting and turning your document into a big ol’ mess trying to re-order stuff manually. Word knows how to sort! Hooray!

#BlogJune 15 – Things I wish I knew earlier 1. Snipping Tool

I once helped a student put a couple of different screenshots onto a page by using the Print Screen key and pasting them into a word document. They were amazed. “It’s my final year of uni and I only just learned about this!” They said. “I’d been printing things out and gluing them and photocopying them… argh, what a waste of time!”

I’ve started a little series, “Things I wish I knew earlier”, sharing recent discoveries of life hacks, shortcuts, or just normal ways of doing things that I’ve been doing the hard way for no reason…

Snipping Tool

I only learned about Windows’ Snipping Tool last year.

In case you don’t know about it: it’s a really easy way to screen capture. Open Snipping Tool and drag a box over a section of the screen that you want to save. From there you can easily save it as a png, gif or jpg, directly email it to someone, or add highlighting and pen lines before saving.

It’s part of Windows Vista and onwards. Macs have Command-Shift-4, which allows you to draw a box and when you release it the png is saved to your desktop. I think they had that functionality before Microsoft.

snipdemo

The above demo ironically captured in the most complex way possible (screen capture > video > gif) because the one thing Snipping Tool cannot snip is itself.

The long way I used to do it was use the Print Screen key, open Photoshop or Paint, paste as a new document, crop as required, and possibly draw boxes with a yellow border and transparent background colour around important things to highlight them. Then I’d have to save as both a photoshop file and a jpg. NOT ANYMORE!

I learnt this tip from a colleague and proceeded to pass it on to anyone else who would listen. SO convenient.

The only downside is the jpgs are not top quality. If you need high resolution, the photoshop method may be the best. But for most purposes – capturing an error message, demonstrating procedures, etc – it is totally fine.

Let Excel Do Your Searching For You

I thought I would pass on a neat trick I figured out that can makes Excel automatically run searches in a search engine / online catalogue of your choice.

Here’s an example of what your list might look like:

search

You can set up a hyperlink function so that column “B” searches what is in the correpsonding cell in column “A”.

So simply clicking on cell B2 opens up the following webpage in your default browser:

Well played, Old Sport.

Well played, Old Sport.

Step 1: Find the search link

Let’s say I want my spreadsheet to search WorldCat. I go there and search for bunnies.

Look at the url of the results page:

https://www.worldcat.org/search?qt=worldcat_org_all&q=bunnies

Copy that link.

Step 2: Make a hyperlink formula

The excel formula for a hyperlink is:

=HYPERLINK(“url”, “display text”)

So I type that into cell B2 (going by the template below), and copy the worldcat URL, and put “Search Worldcat” as my display text.

hyperlink

Step 3: Hack into the Mainframe!

Now, instead of searching for “bunnies” I want to search for whatever text is in cell A2.

I go into my formula bar and replace the word “bunnies” with the following:

“&A2&”

(I got this technique from this page.)

I work in a university library, and I developed this kind of spreadsheet for the purposes of checking how many copies of course texts we had in our catalogue. But it could be used for any of your mass-search needs, whatever they may be.