Tip o’ the Week 396 – Handling dates in Office apps

Posted on September 19, 2017 by ewand

There are many times when you may need to deal with dates in ordinary applications – and there are a few shortcuts that you can make it easier.

In Outlook, any time there’s a date field (like when you’re setting a reminder, or entering the start date/time for an appointment) you can choose or enter a regular date, or put in an expression – like “2 days” or “next Tuesday” – and Outlook will figure out the offset from today, and will set the appropriate date.

In some date fields (like an appointment start time), if you say “4 days” then press enter or TAB, it will evaluate the new date; if you return and put “4 days” again, it may add those extra days to the last date. Try a few other things like “next Christmas”, “3^rd Sunday in November”, “2mo” , “7d” or some special days – there are some surprising ones there, like “Lincoln’s birthday”, and other events with static dates … though nothing that might change the actual date from year to year (like Easter, or Thanksgiving).

In Excel, press CTRL+; to insert the current date into any cell – add a SHIFT key to insert the time instead. Excel are many date-oriented functions, but you don’t always need to write functions – simple maths can work on date fields – calculating the number of days’ difference between two dates, for example, or adding a number of days to a start date.

In the desktop OneNote app, if you want to edit the date and time at the top of a page, click on the field and you’ll see a clock or calendar icon appear next to it – click on that is set to, click on that to change the value; handy if you’re updating some reference material and want to make it clear that it’s recent.

Another way might be to insert the current date or time into the text: to do so, press SHIFT-ALT-D, or SHIFT-ALT-T for the current time, or SHIFT-ALT-F for the current date and time. The last one is really handy if you’re taking notes about a phone call, and want to quickly note the time that your insurance company said that everything was all fine, or when you started the indefinite call to the airline. The same shortcuts apply to the desktop OneNote 2016 application and also the OneNote store app.

Word also supports SHIFT-ALT-D and SHIFT-ALT-T like OneNote, though inserts a date or time field rather than a simple bit of text, and is slightly different to the Date & Time command on the Insert tab, which gives a bit more control over the formatting at the point of insertion, rather than requiring the user to insert the field then go back in to edit the format.

Since Outlook uses Word as its text editor behind the scenes, the same shortcut keys will also insert date fields into the text of an Outlook email.

Tip o’ the Week 329 – Fuzzy Duck? Yes, he does

Posted on June 3, 2016 by ewand

Sometimes, when writing the ToW, the topic is inspired by a specific problem that someone has emailed me – it’d be a lot better if they’d email me the solution to a problem, but never mind – and sometimes it comes about because of an issue I’ve spent ages struggling with and then happened upon a solution. Today’s is following one of those latter episodes.

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.

A relatively little-known Excel addin might come to the rescue (technically described as a technology preview in the EULA, but it’s been around for a little while in its last variation, and a few more before that; so probably is not going to advance a great deal more) – the Fuzzy Lookup Add-in for Excel. Simply take two sources of data (formatted as tables), create one or more mappings between them, and run the tool to see what it comes up with.

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.

After installing the Fuzzy Lookup addin, you’ll get a fairly detailed Readme and a nicely illustrative Excel sample file showing some share price comparisons (with company names in wildly different formats being matched with eerie accuracy). It might be in preview but it could be exactly what the Excel jockey needs.

Fuzzy Duck? Ducky Fuzz! Does he?
(look it up on Wikipedia – NSFW, obvs).

Tip o’ the Week 323 – Some lesser-known Excel spreadsheetery

Posted on April 15, 2016 by ewand

Spreadsheets did – or do, still – make the modern IT world go round. Until Dan Bricklin and VisiCalc invented the familiar grid-based software environment, business school boffins had to manually write up large grids of numbers on blackboards, and routinely calculate the impact of changes in any one “cell” based on a book of formulae. An error-prone process that could, as you’d imagine, take a long time.

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 1^st 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…

Text editing – yes, yes. Spreadsheets are for putting in numbers, making calculations and drawing up charts… but some management types do like to go on about other stuff in text fields. Did you know if you press ALT+ENTER whilst entering text, you’ll add a new line to the text box and, most-likely, resize it in the sheet?
Change the Enter key – when Power Users press ENTER, they mean, er… moving to the cell below? What if they’d prefer to move to the cell to the right? Well, you can change it … just go to File | Options | Advanced…
The power of F4 – one of the handiest shortcut keys in any Office app, pressing F4 simply re-does whatever the last action was. Say you’ve just changed a cell’s format; well, instead of using Format Painter to select the formatting and paste it into another one, you could just press F4 to apply the same changes to another selected cell. And keep on pressing F4 to re-apply the same settings to other cells too. CTRL+Y has the same effect.

Stripey rows are a nice way of drawing differentiation within tables – if you select cells and use the Format as Table command on the Home tab, one of the side effects of tablifying your cells is to give you the option of making the rows and/or columns stand out from each other. If you want to apply the same sort of formatting to a block of cells without making them act like a table (maybe you’ve copied and pasted cells from a proper table and then removed some of the rows, thereby breaking the colour sequence), then a simple trick is to apply formatting:

Select your block of cells then go to Conditional Formatting on the home tab
Choose a new rule, then use a formula…
Enter =MOD(ROW(),2)=0 as the formula itself and then click the Format… button to select the formatting you’d like to apply to every other row (fill a colour, for example). You could try the same trick with =MOD(COLUMN(),2)=0 if you’d prefer… or change the number to highlight only every 10^th row, etc.

Add a calculator to the Quick Access Toolbar – the QAT was featured the other week, and here’s another handy use for it. Although spreadsheets are great for calculation, sometime you don’t want to add a formula to process numbers, but would rather tot them up yourself and add the result to your sheet. If you’ve done this before, there’s no need to feel inadequate – assuage your tech guilt and put a shortcut to the Windows Calculator by clicking the down-arrow to the right of the Quick Access Toolbar, selecting All Commands from the drop-down, and then Adding the Calculator so you can launch it easily in future. (Or just press Windowskey+R then enter calc).