Tip o’ the Week 485 – Excel and the Web

Posted on June 30, 2019 by ewand

For decades, it’s been possible to import data into spreadsheets from elsewhere. Excel supports many data sources, from basic stuff like CSV, ODBC and OLE DB, to more specific and advanced knowledge of particular data sources and types.

A recent tweet from @msexcel showed a simple video on how to grab data from a website – highlighting a capability that’s been in Excel for years but has been refreshed and made a lot easier to use.
Try this as an example:

In a new Excel workbook, go to the Data tab and choose From Web…
Try a web site that has data tables – eg
https://en.wikipedia.org/wiki/World_population
After confirming the authentication method to connect, Excel will retrieve the page and try to make sense of its layout – and let you choose the table you want to retrieve data from.
This is part of a new “Get & Transform Data” set of functionality that’s available to Office 365 subscribers, that uses Power Query to retrieve data from a variety of sources, and can include some advanced transformation and editing of the query too. See here for a more detailed tutorial.

If you want to revert to using the old data import methods, you can either enable the Legacy import wizards in Options (File > Options > Data > Show legacy data import wizards), or just type Legacy into the Search / Tell Me box in Excel, and see the available actions from there.
You can jump to the Search box quickly by pressing ALT+Q and jump straight to any Excel function, or get help on how to do many tasks.

Another cool Office 365 data feature is to use Data Types. Enter your data in a column, select it and choose the appropriate Data Type – let’s use Stocks as an example.

Once you’ve tagged the source data, clicking on the icon to the left of the data point will show a pop up with the background detail, or you can reference the fields within formulae to display or manipulate the data values.

Each data source presents a number of fields that can be discovered and selected through autocomplete in a formula, and the values can be refreshed easily.

See more detail on using the Stock quotes functionality, here.

Tip o’ the Week 484 – Saving Office 365 profile pics

Posted on June 30, 2019 by ewand

Office 365 users will be familiar with the Profile Picture that appears in multiple places, most visibly in Outlook and Teams. Just like their picture on LinkedIn, many users will help people understand what they look like by posting an actual photo of themselves, whereas some will insist on posting a photo of their dog, or their kids, or themselves wearing a hat and shades while standing very far away.

There’s supposedly a lot that your choice of profile picture says about you. There’s a tabloid version (akin to the “What Your Horoscope Says About Your Pet” style nonsense more often to be found on the Edge browser homepage). There are some more scientific resources with views on what people think when they see your picture, and some hints on how to choose the right one. Some fun examples of what not to do could be illuminating.

Facing left-to-right is supposedly best – maybe it makes you look more powerful, or simply, when your photo is on the left side of a load of content (like the details of your LinkedIn profile), then it’s better to be looking toward it rather than away to the left… Similarly, good advice is to stick to a head-and-shoulders shot, or at least waist-up – if your profile pic is your visible brand on LinkedIn and Office 365, then there’s no point in using a photo that shows your face as too small for anyone to recognise you.

How to save photos from Office 365

This tip will probably become obsolete at some future update on O365, such is the march of innovation, but it deals with how you can get to the profile photo that someone else in your organisation has published. The inspiration came from a departmental admin who was trying to build a nice org chart, and had to repeatedly nag members of the team to share a photo of themselves. It can also prove handy when someone has posted a photo of themselves that’s too small to see – if you can open the photo up in a browser, it can show you the original full-resolution image, and you can always use the browser to zoom in, too.

Start by going to the Office home page and sign in; you can then search for someone’s name and click on the People tab for the detailed results.

An even quicker way might be to go to https://www.office.com/search/people?auth=2&q=<name> and follow the q= with the name you want to search for.

When you have the results of the search, hover over the thumbnail of the person’s profile pic, and in the pop-up that appears, right-click on the slightly larger image.

If you’re using classic Edge, then you’ll be able to save the image locally, but if you’re on Chrome or the new Edge Dev browser, then you’ll easily be able to copy a link to it – paste that into a new browser tab, and you’ll get the full-size version of the profile pic so you can zoom in, save it, draw moustaches on it with your Surface Pen and so on.

Tip o’ the Week 483 – mobile OCR and Office

Posted on June 7, 2019 by ewand

Optical Character Recognition is one of those technologies which has gone from being just-about-possible at great expense and hassle, to so mainstream that people just assume it will work flawlessly, all in a relatively few years. Numerous companies offer OCR services or addins to line-of-business systems which help to prepare printed data for easier consumption – scanning invoices for example.

Consumers tend to use OCR in other ways; combined with language translation, you can point your phone at a foreign menu or sign and it may be able to help you understand. In OneNote, if you have captured an image (maybe through the clipper addin from your browser), then it can extract the text from that picture – not always perfectly, and not necessarily well-formatted, but it’s probably quicker than re-typing everything.

Near OCR functionality is also pervading the slew of freely available Office apps for Android tablets, phones and even Chromebooks, and similar versions for iPad and iPhone.

A recent addition to the iOS version of Excel is the ability to scan a table of printed data and use OCR plus a bit of tweaking, to import the data into the spreadsheet. See more here. The same functionality was first made available on Android a couple of months earlier …

Start with the grid capture icon on the toolbar of a new spreadsheet, and then use the camera to highlight the area of a document that you’re interested in – the UI will be familiar to anyone who uses Office Lens, as the same anti-skewing technology is used to prepare the “document” for importing.

Then the OCR goes to work and tries to lay out the data as closely as possible to its source – obviously, your accuracy will be improved by having a well-lit and clear original document, and you’ll get to tweak the contents in context of seeing the OCR’d data and the scan at the same time, before committing to insert it.

Tip o’ the Week 482 – Paste History

Posted on May 31, 2019 by ewand

Back in 2012, three weeks before Super Saturday, ToW #133 talked about the Art of Cut ‘n’ Paste. How the widely-used CTRL-V keyboard shortcut for Paste can trace its roots back to a program co-developed by Butler Lampson, one of the “Dealers of Lightning” as a founder at Xerox PARC, and now a near-25-year Microsoftie and Technical Fellow. QED was a thing before Neil & Buzz set foot on the Moon (which happened on 21^st July, not 20^th: Eagle landed on the 20^th, but it was 21^st before “one small step for a man”… at least it was in UTC).

Did you know that in recent versions of Windows 10, there’s a useful new shortcut – WindowsKey+V?

It shows you the history of the clipboard, so you can quickly access something you’d previously copied; you can sync the clipboard between multiple machines (or phones), too.

There are other controls you can assert when it comes to pasting stuff, too – CTRL+ALT+V in Office apps will let you paste something and decide how to handle it (the equivalent of Paste Special, in most cases) and you can over-ride the default behaviour in Word too, by choosing to Set Default Paste.

In other apps, there may still be different ways of handling Paste actions – Paul Thurrott recently wrote about how to change the options in OneNote for Windows 10 (the UWP app that is replacing traditional OneNote; the one you can start by running onenote-cmd: from the Win+R box).

The “copy & paste” metaphor dates to PARC, too – and yet it’s still evolving, 45 years later.

Tip o’ the Week 478 – O365 and Windows’ Mail and Calendar

Posted on May 8, 2019 by ewand

On the mobile platforms that still survive, the highly-regarded and rightly popular “Outlook” mobile apps have no relation to the Outlook desktop Windows app which first appeared with Office 97, before smartphones were a glint in anyone’s eye. Mobile Outlook has hundreds of millions of downloads on both iOS and Android; quite a feat, as later this year Windows Mobile sinks quietly beneath the waves.

The genesis of Outlook on the phone as we know it today, is perhaps the acquisition of a company called Accompli 5 years ago, and a great deal of refinement and effort since.

Somewhat interestingly, traces of the same app have come to Windows as well – namely the Mail and Calendar app(s) that are in the box on Windows 10. Look back to ToW 445, and you’ll see that the names for the apps are outlookcal, outlookmail and outlookaccounts. Stick a “:” on the end and you can run them from a prompt.

e.g. Hit WindowsKey+R then enter outlookcal: and you’ll jump straight into the Calendar app.

Both have come a very long way – at first release, they were pretty basic, but they’re now so well featured that most people could use them as their primary email and calendar apps, most of the time.

The Calendar app is functionally pretty similar to the Outlook desktop app, except when it comes to working with other people – there’s no way to view someone else’s calendar, for example, but for a personal diary of appointments it’s really very good. And if you want the best of both worlds, you can connect your Office 365 account to both Outlook – as might be your primary way of working – and to the Mail and Calendar apps, for some side benefits and quicker ways of getting some things done.

Go into the settings on the Calendar app, then Manage accounts, then + Add account… or just Win+R then outlookaccounts: and you’ll be able to add your Office 365 account onto both Mail and Calendar.

If you have multiple calendars connected – like home Office 365, Gmail or Outllook.com accounts as well as your corporate one – you could selectively enable them for display in the app, and the set of calendars that are shown will also appear in the agenda if you click on the clock / date on your taskbar. You can also see your upcoming appointments in a live tile on the Start menu, if you still use such things.

You’ll also see your next appointment on the Windows Lock Screen if you have it enabled under Lock screen settings.

You may want to go into the Notifications & actions settings page (just press Start and begin typing notif…) and turning off Calendar notifications, or you’ll get a blizzard of reminders from desktop Outlook and the Calendar app.

Tip o’ the Week 474 – Parse and Flow

Posted on April 5, 2019 by ewand

Microsoft Flow was introduced a couple of years ago, and covered in ToW #401; it’s basically a glue between different online applications, allowing the exchange of data between them and being driven by events and actions.

Sign up, sign in, and when you go to create a new Flow, you can start with a blank canvas, or by customising a pre-built template, of which there are many.

Learn how to build Flows here.

Many of the templates for Flow are quite esoteric – when a tweet on a particular topic appears, write a log to a Sharepoint site and send a notification to a Teams channel, that kind of thing. But there are plenty of really useful connectors that can be combined in time-saving ways; here’s a really handy way of bringing traditional data sources into the modern era: an email parser, called Parserr.

After signing up with Parserr – free if you only need a few uses per month – you can then crack open mail that is consistent in format and contains some information you’d like to extract and use elsewhere, such as confirmation of an appointment or maybe a travel booking. In practice, you get given an inbox with a unique email address within Parserr and you’d set up a rule in Office365 or Outlook.com to send mails that meet some inbox rule to that address, where it would be parsed for you and key data fields then sent back to Flow.

e.g. if email comes from a specific source address or it has a subject that indicates it’s a particular type of reservation, then forward to your nnnnn@mg.parserr.com inbox address, extract the details of the booking then do something with them within Flow.

Start by sending an example mail to your Parserr inbox, then you can define rules to identify content within it (by looking for set keywords, going to specific line numbers and so on).

Create a rule for each piece of information you want to extract, and it will effectively create a field:

Once set up, you create the Flow by choosing the connectors for Parserr and whatever other applications you need to work on the information.

In this example, we’re using Office 365 to create an appointment that matches a reservation – the arrival and departure dates are provided by the source email, and converted to YYYY-MM-DD format within Parserr, then dragged across in Flow to match the Start & End times of an “event”. We’ll tack on T16:00 to the arrival time and T10:00 to the departure as that’s the check in and check out times, and thus create an ISO8601-compliant date/time such as 2019-04-05T08:00, which Office365 will use as the start or end time of an appointment.

“Advanced options” gives you further control (such as adding body text that might contain static text and other fields provided by Parserr, other addresses to forward the invite to, setting if you want it to be free/busy/tentative, reminder duration, time zones etc).

And that’s it: you can test the logic is working within each system – in Parserr, you can continually re-run the processing of your initial sample mail until you know the data is being extracted as you’d like, and within Flow you can keep testing your formatting etc by either triggering a new input or by working with the last set of data that came over from the source.

Once you’re happy just save the Flow, and it will automatically create an appointment in your calendar every time you get a matching email forwarded to the Parserr system – all in a few seconds.

See more on using Parserr with Flow and here’s a worked example.

Tip o’ the Week 472 – Update Store & Office apps

Posted on April 5, 2019 by ewand

We’re all used to Windows Update or other software automatically downloading and installing updates (on phones, TVs, cars…). Sometimes the updates are at more of a leisurely pace than keen users might want though occasionally the recipients demand to hold back the updates until they elect to install.

The Windows 10 October 2018 is now being pushed to (nearly) everyone, though business users will have the option of pausing Windows Updates in case they want to enact a temporary delay for some particular reason – you’re about to go on a trip, for example – though it’s not meant as a centralised policy control: IT departments have other ways to do that. Windows 10 Homes users will soon get the ability to defer updates for up to 35 days, too.

How often applications update themselves is largely down to the publisher, but it’s usually possible to give the apps a prod to see if there are published updates before they get pushed out to you.

In the Microsoft Store app, for example, go to the ellipsis menu on the top right. You’ll see Downloads and updates, which will force the check for updates for all your installed Store apps. Even if you’ve turned on the automatic app update checks, it’s worth taking a look periodically as some of the apps you use most often might have updates pending.

If you find that most of the stuff you’re offered is updates to boring apps that you don’t use, then you could just wait for them to fetch their own in time. If, however, you spot an individual update to an app that you know you want to have the latest version of, then click the down arrow to the right to get it right away, or click the app name to look at its page in the Store and see what’s new.

Of course, non-Store apps may still offer their own updates directly – to check for updates to the Microsoft Office suite, for example, try going into Word (or Excel or PowerPoint if those are your most-used Office apps) and from the start screen that offers a few previously-opened files, templates you’ll never use etc, look to the bottom-left and you’ll see Account.

Click Account to go to the product information page, which will let you check for updates, show you the current installed version number of the the application, and maybe even let you sign up for more updates through the Insider program.

After you’ve installed any pending updates, the What’s New option will be active and will show you a summary of what has changed, in a pane within the app itself.

Or check the View Updates option at any time, and it’ll take you to the web to see what the latest updates contain.

Tip o’ the Week 468 – Get Office, My Office, Office app appears

Posted on March 22, 2019 by ewand

For a while now, new PCs have been installed with an app that “encouraged” users to install and use Office. Even users with Office already installed sometimes complained that Get Office was nagging them to, er, Get Office.

Get Office became “My Office”, which was a lot more useful in the sense that it was showing documents you used etc, but its main aim appeared to still be to help you find and launch Office apps, or buy them if you’re not already using them.

The latest incarnation – simply called “Office” – moves the game on a whole lot more. For one, it’s a portal into all the Office documents you work with on your machine or online, allowing you to search content across not just the docs themselves – so you can search for documents in your most-recently-used lists, something that the File dialog in Word/Excel/PowerPoint annoyingly won’t do.

The search bar also reaches across SharePoint sites you use, OneDrive locations you have and even brings in the global address list so you can get to people details really quickly, including a really fast org chart ability.

The new Office app will be delivered automatically for a lot of people as it will replace the My Office and Get Office apps in due course; if you’d like to check it out sooner, go to the Store.

See more about the app here, or here.

Tip o’ the Week 453 – Outlook Quick Steps

Posted on October 25, 2018 by ewand

Somewhat improbably, one fairly prominent feature of Outlook has never been discussed in detail on a previous ToW – Quick Steps. Hiding in plain sight on the Home tab, it’s likely that every Outlook user has clicked on Quick Steps at some point, but do you use them regularly?

Put simply, Quick Steps make some repetitive tasks easy with a single click or even a shortcut key combo – start by selecting a message you’d like to apply some action to (such as moving or categorizing it), or if you’d like to start some new item based on the contents of the message – like create a task or an appointment, including the body of the original mail.

Quick Steps can be applied to individual messages or multiples (hold CTRL key while selecting more than one), including selecting the whole conversation if you’re viewing in that mode. Click on Create New Quick Step (or click the little expand icon in the bottom right, for the Manage Quick Steps dialog, and create one from there).

You’ll see there are plenty of options available for actions that you can take on messages, some already combined if you kick off the New step from within the Manage Quick Steps dialog box – though you can add multiple actions to any one after the initial creation. The Categorize and Move option is particularly handy if you want to file all your mails for a given customer or a specific topic, into a subfolder.

For more on Quick Steps, see the tutorial here. If you ever think about backing up and restoring your defined Quick Steps, see here (very much not for the faint-hearted), or here.

If you haven’t played much with Quick Steps before, have a go – they’re fab-u-lous!

Tip o’ the Week 451 – OneNote auto-listing

Posted on October 25, 2018 (Updated January 7, 2022) by ewand

Here’s a quick tip in OneNote – both the full-fat desktop client and the modern app version – which was inspired either through PEBKAC type unexpected clickery or maybe an Office update that inadvertently switched something off. A common feature stopped working, and it caused a serious dent in productivity…

Despite the two parallel PC versions of OneNote – which have been covered previously in ToWs #441, #427, #386, #320, et al – offering a good chunk of similar functionality to each other, there are still quite a few areas where the old desktop x86 version wins through.

Add-in support is available in the 2016 variant, for example, so you can run OneTastic (and in particular, OneCalendar, which is immensely helpful if you use many notebooks and take a lot of notes throughout your week).

A simpler and more useful feature for many is the ability to grab the contents and context of a meeting request from your Outlook calendar – so you can take notes during a phone call or a meeting, with all the text in the invite, names and email addresses of attendees etc. Can’t do that with

Search in the desktop OneNote is more powerful, too – CTRL-F takes you to the search box, CTRL-E expands your search, but the most powerful and probably least used is to press ALT-O when you have search results from the CTRL-E dialog; it will order them by the date of the page update… helping to filter out current vs obsolete info.

Bullet lists & indentations (sounds like a Muse song)

Did you know that, in both OneNote versions, if you’re typing notes and press the asterisk or dash key at the start of a new line, and then the space bar, it automatically turns your text into a bulleted list? Asterisks in the middle of a text block are ignored; it’s only seen as an auto-correct function if on a new line.

Just hit enter after you’ve started typing to add another new bullet or hit enter at the beginning of new bullet to finish the list. TAB and SHIFT-TAB lets you indent and un-indent a bulleted line. It doesn’t sound all that revolutionary, but if you’re typing notes during a phone call, it can make all the difference between keeping up or missing discussion points as you fish around with the mouse looking to click the toolbar. If you’re used to it and it gets switched off, it’s a real pain.

The same kind of functionality exists in Word and Outlook too, but now and again it does get in the way – if you’re marking a block of text* that you then want to expand on later without auto-bulleting, for example.

* The simplest way to get an asterisk or dash at the start of a new line is to quickly press Undo – CTRL-Z – as soon as the indentation with the bullet happens, and you’ll be reverted to simply having the character at the start of the line.

To enable or disable the automatic list features on desktop OneNote, go into Options and look under Advanced…

There doesn’t appear to be any way of disabling the feature on the Modern App (which you can start by running onenote-cmd: at the Win+R box, if you read ToW #445 and #443) – maybe that’s a good thing, preventing the user from harming their own productivity…