|
Be honest. Do you know how to use the VLOOKUP function in Excel? It has its roots in @LOOKUP from VisiCalc, which goes back well over 30 years – see here for a demo (and, wipe a tear, you missed “VLOOKUP WEEK 2012”). It’s one of the more useful functions, where you can use tables of text to cross reference one another – leading some to create spreadsheets to manipulate data that might be achieved elsewhere by a database join or an IF…THEN…ELSE statement. VLOOKUP (and her friends, HLOOKUP, LOOKUP and the other reference functions) is all very well if you have nicely constructed and controlled data – but what if you have messy text that has been entered by end users? How do you go about normalising that without boring brute force (ie ploughing through it all yourself)? Imagine, if you will, that you have a list of a few hundred company names exported from your CRM system – let’s call them “Partners”. What if you also had many thousands of unique names from people who’ve registered at a conference? (Let’s call that “Partner Conference”). Wouldn’t it be nice to run a report which shows the team that works with each partner, who has registered and where they’re from? If the registration tool allowed anyone to enter free text fields for the name of their company, you’ll get any number of variations, mis-spellings etc – maybe even the odd deliberate spanner. (On the McXFace front, once again, El Reg excelled itself with this headline, though has a way to go to top the best so far… or the subheading of this one, which reads like a line from a DC Thomson cartoon). These names won’t allow VLOOKUPs as they’ll show up as all different, and therefore cross-referencing one source with the other will be difficult. So even telling Jane Smith, who manages the ACME Inc account, that these 10 people are attending the conference, is going to be hard if every one of them registered with a variation of A.C.M.E, ACME Inc, Ac-me Ltd and so on.
The Fuzzy Lookup tool will add extra columns to the source table; showing the text that it thinks is the nearest match, and a score of “similarity”. The technology comes from Microsoft Research, and uses the Jaccard Similarity method of comparing sample data sets. One technique for comparing a couple of different columns is to set conditional formatting on the Similarity column and choose colour scales for easy identification of the ones likely to be correct; or simply put a filter on that column and hide rows below an arbitrary low bar (like 0.6). Then spin down the two columns to the left and check to see if they tally up, given the human eye for spotting similarity, spelling mistakes etc. You could even add a Y/N column to the right so you can manually affirm which is right and which is not, then filter on that to confirm.
Fuzzy Duck? Ducky Fuzz! Does he? |
Tip o’ the Week 328 – Clip for art’s sake
|
If you’re after some high-quality clip art to insert into you magnus opus, you could try a service called Pickit, previously known as PicHit.me. The Pickit Photo Finder app gives you a nice Modern app way of finding cool photos given a theme or keyword (though there’s a subscription fee if you want the higher quality pics). It’s even Cortana enabled, supposedly. There’s an Office Addin too, which lets you search for and add photos and art straight into your documents. Pickit is a Microsoft BizSpark success story, and the service runs on Azure. There are many ways of finding decent clipart for your projects – there’s Open Clip Art for an archive of more traditional vector & standard clipart image fare, or image hosting services like Pixabay, which offer free Creative Commons photos. Check out these other alternatives too. |
Tip o’ the Week 327 – To sleep, perchance to dream
|
The Windows Insider program is delivering various new builds to improve battery life for Windows 10 PCs & tablets that use “modern standby” (the mode previously known as “connected standby”) that lets them stay on the network and update certain data feeds whilst ostensibly being in sleep mode. Surface 4 and Book machines have had a few issues with sleep, but recent firmware updates should sort that out. As for the organic machine, there’s plenty of advice on getting better sleep. Problem page gurus warn against drinking coffee or caffeinated tea after lunch, recommend eschewing alcohol & talk about avoiding “screen time” up to 2 hours before bed in order to fall asleep more easily and get a better quality of sleep while you’re there. The Microsoft Band 2 does a good job of tracking its wearer’s sleep, either through detecting that you’re in the land of nod, or by the user initiating the sleep mode. The auto-detect function is there for times when you’re too tired/drunk/forgetful to remember to tap the sleep tile on the Band before dropping off, but there are other
The advice on reducing screen time before bed is partly because reading email or other things that stimulate your mind won’t let you doze off easily, but also because the device you’re using to do the reading might be fooling your brain into thinking it’s still daylight. The LCD/LED screens used by lots of devices – PCs, tablets, phones etc – have a bright, blue/white light that apparently stimulates the noggin in ways you don’t want as you’re about to drop off. Agony Aunts say, don’t use that technology in your bedroom at all, but there could be a better way, if you’re a habitual browser dans le lit. 4 years ago, ToW talked about the “colour of time”, and the same tool/advice is still very useful today – F.Lux is an app that runs on Windows PCs (and versions are available for Macs, iOS, Android & Linux).
Install it on your tablet, turn up the wick on its dim settings, and use it happily in the sack without fear of staying awake all night worrying. Unless, of course, you’ve got something to worry about. |
Tip o’ the Week 326 – Skype for Business, meet Skype consumer
|
Put simply, Skype for Business has the ability to communicate with users on the consumer Skype platform, as well as to federate with other parties, and Office365 has it enabled by default. This means you can exchange IMs and see presence for Skype for Business users outside your organisation.
Now, if you’re not federated (or
An even easier way is just to start typing the person’s name, Skype ID or Microsoft account, into the “Find someone…” search box within Skype for Business and click the Skype Directory button: you might well see them listed, profile pic & all – right-click on their profile to add them as a contact (which will kick off a contact request just like in consumer Skype). Once someone has been added from consumer Skype to your contact list, you can IM them, see their presence, and even fire up an audio or video call with them, without needing to use the consumer Skype client yourself. Nice if you’d prefer to keep your business contacts and your personal ones separate, but if your customer is asking to have a Skype chat with you… You can try this out by running both Skype and Skype for Business side-by-side and adding your contact, sending yourself a test IM, even cracking open a video or audio call.
Options are to ask the Skype user to join using the Skype for Business Web App, or use the consumer Skype client to call the phone number the Skype for Business meeting offers to regular phone users. For more details on how to set up and use Skype for Business federation, see here. Check this post for instructions about getting it working with consumer Skype (or here). And another. |
Tip o’ the Week #325 – your time, my time, everyone’s time
|
If we become more effective in some ways, decreasing the time it takes to accomplish mundane stuff, will we spend the regained hours doing more stuff, or just fritter it away meaninglessly? Discuss, ad inifinitum. Quick Time One thing you could do when deciding what to do with all the spare hours the world of Yammer, Slack, Outlook, Skype etc has delivered, is to go right now and uninstall QuickTime from your PC. After 20 years of providing the Windows version, Apple has decided (to quote El Reg) to take QT for a long drive down a country road. The US Dept of Homeland Security advises immediate removal of QuickTime, since it contains zero-day vulnerabilities which Apple will not fix, ever. Whilst thinking about security, wonder about the effectiveness of the Thousands Standing Around at US airports, or consider if you really need to change your passwords after all. Find Time A few months ago, a Microsoft Garage project released FindTime, an addin to Outlook and Office365 which allows a meeting organiser to send a request out to a group of people, to vote for when the best time to hold the meeting is. The best part is, the recipients don’t need to be using Office365, don’t even need to be in the same company – so you could ask your customers to join in the negotiation for when the best time to meet would be. |
Tip o’ the Week #324 – Delve into something new
|
If you can get access to Delve (either on https://delve.office.com or via https://portal.office.com, depending on your account and level of access), then it’s well worth playing with it for a while, especially if you work in a large company like Microsoft, where all sorts of interesting stuff is being saved onto shared document folders. One downside of Delve might be that nervous Nellies will stop putting their documents into shared areas in the fear that other people will read them, or that the default-to-open (for their internal staff) culture that typically pervades lots of companies will flip to an access-only-on-a-need-to-know-bassist. Delve lets you see what documents are popular, what people you are connected with are doing, and lets you search by document content or by author. Want to see what FY17 holds for your org? Wondering what juicy PPTs your VP has been editing lately…?
Announced recently, the Delve Analytics function (available to O365 users based on their license type), shows you not just what other people are doing, but how you are performing too. The Delve Analytics dashboard and corresponding Outlook Addin lets you see how you’re spending your time, and who you’re spending it with, promising to help you make the most of it.
The Outlook addin surfaces Delve info within Outlook’s reading pane, so as long as you’re looking at colleagues who’re in the same Office 365 environment (which might be an issue in MSIT, where there are several tenants), you’ll see stats about how often and effectively you email with each other. Here’s one example; judge not any of the numbers…
Eek. 3h 31m average response time. Must try harder to do less email and do more work. |
Have a Delve… 
Tip o’ the Week 323 – Some lesser-known Excel spreadsheetery
|
Spreadsheets did – or do, still – make the modern IT world go round. As it happens, VisiCalc powered the Jobs’n’Woz enterprise to mass success, as Apple IIs were selling (even fully kitted out at $5k+ a time, in the 1980s) to middle managers who were sick of the Data Processing department taking ages to turn around financial reports, so they took to sticking an Apple on their desk and doing the sums themselves. If you’re interested in all of this, see here. This threat from Valley spooked Big Blue enough to worry about the mainframe franchise being under threat, and after a couple of false starts and a skunkworks project called Chess, the PC was born. Lotus software quickly became the de facto spreadsheet provider running on PC-DOS (as it was so fast, meaning the spreadsheet jockeys ditched their Apple IIs and flocked to PCs), but Lotus got distracted with OS/2 while Microsoft’s Windows 3.0 started to gain traction. In place of WordPerfect, Lotus 1-2-3 and dBase, the 1st party Microsoft Office suite took early and full advantage of Windows, ultimately powering Office to the front. So, Excel trumped 1-2-3, just as Lotus eclipsed VisiCalc. That means Microsoft Excel for Windows has been around for a very long time, and there are many functions you’ve probably never used – but there are loads of useful tips that could make your life easier. Here are a few…
|
Tip o’ the Week 322 – Booking meeting rooms (again)
|
Building on last week’s ToW and on a topic that has been covered some time ago, let’s dig deep into the bowels of Outlook, going back almost 20 years to Outlook Forms to solve a very particular problem. As per ToWs passim (like Eyes), every item (message, contact, appointment etc) you open in Outlook is a bag of data fields that are rendered in front of your eyes by a form. It’s possible to design and publish custom forms to do more stuff, or in this instance, to fulfil a specific function and by pre-populating some data and by hiding other extraneous information. Show meeting rooms Meeting rooms are often set up as bookable resources within Exchange & Outlook – so you invite the room to your meeting and it automatically accepts, meaning you’ve reserved that resource. When trying to figure which rooms are free, if you only have a few meeting rooms then it might be easy enough to just show their calendars from the Room List (eg here). If you’re using a more modern version of Outlook and/or have more than a few rooms to deal with, then Room Finder is more useful. See here and here.
As an end-user, though, you may find that your IT department doesn’t manage the rooms the way you’d like – in a new building, for example, there might be no room list published – so not much help if you’re trying to book a room. Here’s a somewhat hacked-up solution which might be useful in other ways, though – it involves customising a form of your own, with your favourite rooms shown, so you can quickly check their availability. You could do the same thing with a group of people too, should you want. Let’s get building Start by going into your Calendar, and create a blank Appointment form, then follow the steps for adding the Design This Form command to the Quick Access Toolbar (or right-click the Ribbon when in a new
Now you’ll have switched to a form
Now that’s all done, Publish the new form as a custom name (something like <building name> Meeting Rooms) then hit the Publish button. This will now save the form into your own Calendar folder, so it will be available from any PC running Outlook.
You’ll now see the custom form will display only the grid view of room availability, with all of the rooms ticked. You won’t actually use this Make sure you close down the custom form without saving or sending anything. This approach is nicely flexible in that you can create your own “lists” of favourite rooms (eg all large customer rooms with AV, or all rooms kitted out with Surface Hub, devices in any location etc). If your desired selection changes, you can create a new form and Publish As using the name of an existing one to replace it (or open the existing custom form, enter Design This Form mode again, go to the Appointment tab and edit the list of invitees there). If you’d like to delete old forms then from the main Outlook window, go into File | Options | Advanced | Developers | Custom Forms | Manage Forms, and click on Set… to navigate to your own calendar folder, then delete the forms you no longer need. Phew. |
Tip o’ the Week 321 – Quick Access Toolbar customisation
 Most of us will have used the Quick Access Toolbar in Windows Office apps – even if only to click the floppy disk icon to save documents, which, like many others, makes no sense to modern life. The QAT (not to be confused with other, phonetically similar terms) lets you dock commonly-used commands to be constantly available on the top corner of your favourite Office apps.
In Outlook, try opening a message that someone else sent you (this one would be a good start); go to the QAT and click This will now add a new icon to the QAT, which will let you “design” whichever form you have open. In Outlook, a “form” is used to display In fact, adding to appointments is the best place to do it, since you can show the date and time that an appointment was created in your calendar; if you just find an appointment that you can’t remember the context of, you could quickly show the date/time you created it and that might help figure out how valid it is. If you have a meeting that someone invited you to, you’ll easily see the date/time it was sent, but if you’re the organiser, you won’t – unless you use something like Design This Form, then navigate to All Fields and choose Date/Time fields to show all the common date or time attributes of that form. For more context, this topic was covered some time ago on the Electric Wand blog and a previous tip, #102.
Whilst you’re playing with any application’s QAT, it’s worth having a look through the other commands you might want to add – like while still in OneNote, try your favourite OneTastic macros, for example.
This opens the door to being able to scan in old paper notebooks for easier reference/shelf space clearance, and ditch the dead tree notebooks for digital. If you’re like controversial car design Chris Bangle, you may beg to differ.
|
the down arrow to the far right side, choose More Commands… and then select “Developer Tab” from the Choose commands option. Now, pick the Design this Form option from the list and Add to the 
toolbar. Then press OK. 
status reporting, it’s quite handy to be able to colour cells or rows in tables to show their status, but the menu option to set the colour would need you to go into the table menu. To quickly add to the QAT for future easy access, click the down arrow / More Commands… / choose the Table Tools tab and look for Shading, then Add>> and OK.
On the topic of OneNote (and we’re still talking about the desktop one, not the Modern App version), there have been some updates shipped out to Office365 users that could be interesting – especially the ability to search handwriting, not just scribbled using a Surface stylus or similar, but grabbed from a photo by 
To check you’re on the latest version of OneNote via O365, go to the File menu, look under Office Account and the Office Updates section, and choose Update Now from under Update Options.Tip o’ the Week 320 – Give Modern OneNote a chance
|
If you really embrace note-taking when having meetings or phone calls, OneNote is an awesome way of collecting your thoughts for future recollection. Sometimes, reading back your notes might seem like jibberish, but at least you wrote something down. There are basically two versions of OneNote on PC – the full-fat, Office app with all the menus and ribbons and Most hard-core OneNote users would default to using the Desktop version since, from the outset, the trusted/modern So, if you’re a OneNote user stuck in the world of Desktop OneNote, there are a few reasons to give the Modern version a whirl, if you haven’t recently.
To to see if you’re running the latest version, try going into the Mobile/Modern app and click
|
