#57: Excel-lent Conditional Formatting

Posted on December 13, 2024 (Updated December 11, 2024) by ewand

A theme of previous ToWs has been that applications often have lots more functionality than users either know or care about enough to utilise. Two of the simplest yet most impactful ways of handling data in Excel (and in Google Sheets, LibreOffice / OpenOffice etc, which basically copied the functionality) is to create tables from data, and to use conditional formatting to help them stand out.

Select a block of data – and for the purposes of these examples, we’re going to use some sample sales data – and on the home tab, it’s a few clicks to Format as Table. Even if you don’t intend to use more advanced formulae and get into naming tables and ranges, just doing the simple formatting and declaring the top row as headers gives you great ability to sort and filter the data quickly.

If you’re lucky, the table may automatically interpret the contents of your data, too – like understanding date fields. As we’ll get to later, you can even sort and filter by the appearance and not just the actual contents.

Users working on data in Excel which is clearly tabular but has not been defined as a Table, should almost be considered criminals.

Conditional Formatting made easy

Back on the Ribbon, the neighbouring Conditional Formatting control lets you add more pop to an existing Table or any other data. Select whatever cells, columns or rows you want to apply it to, and on the flyout menu you’ll have access to hundreds of options to visually distinguish certain data.

For simple “how to” and a cheesy video, check out the help on Use conditional formatting to highlight information in Excel.

If you need to do stuff that’s more complex, there’s also the option to write a formula but it’s quite different to regular Excel formulae – and can take a bit of working out, especially if it’s more complex. See the “Use a formula…” further down that previous help page.

Fortunately, there’s an easier way if you’re a Copilot user (and if you’re not, Microsoft has started pushing a free 1-month trial – just make sure you put a reminder in your diary or you’ll fall into the trap of subscribing to stuff you might not want). Rather than trying to write a formula and figure out the logic of it, you can just ask Copilot and it will comply…

After it has been applied, you could edit the rule to change its range, tweak the formula or adjust the formatting by going to the Manage Rules option under the Conditional Formatting menu. Make sure the “Show formatting rules for:” filter is set to the right area so you’ll see this and any other rules which may apply.

These rules are very useful for highlighting things that stick out – like due date on a pipeline report which have now passed, or a number that’s radically out of kilter with all the others in an export from a credit card account. If you’re dealing with very large sheets of data, you could filter the view not just by the values but by the colours that your formatting has set:

… thus temporarily hiding any of the rows which are not of interest.

Finally, you can interrogate data within Copilot without having to mess about with filters and the like, for example:

To validate that this is actually true, a pivot table can show the data by different dimensions and allow totalling, sorting and filtering: in this case, sorting (descending) by the sum of all orders:

Pivot Tables are some of the best magic that Excel delivers; it’s been a while since they’ve featured in ToW – leave a comment if you think that needs addressing. See here for more examples of Copilot prompts in Excel.

#55: Quick access to fave notes

Posted on November 29, 2024 (Updated November 29, 2024) by ewand

A few themes have re-appeared on Tip of the Week over the years … saving time by using keyboard shortcuts, finding useful but somewhat hidden bits of Windows or Office apps, etc. One of the most prominent seams to mine, however, has been an undying for Onay-no-tay.

The UX Paradox of Office Apps

Usability research into Office applications once found that 87%* of the new features users asked for, were already in the product – they just didn’t know how to find them. As more and more features were added to apps – Excel particularly, it seems – end-users just didn’t know how to “discover” them. By Office 2000, dynamic “intelli-menus” basically hid options which were more obscure or which an individual just didn’t use, and while it made things look simpler and less cluttered, it made the problem worse.

A wholescale UX rethink in Office begat the “Ribbon”, which is now pervasive in other apps; if you’re interested in such things, check out Jensen Harris’ 2008 presentation on what led to the Ribbon being conceived. The talk offers a great historical perspective but also goes over the thought processes on how these things come about.

* statistic is made up but the story holds true. Who cares if facts and figures are correct as long as the lies are well presented? How do you think Excel charts and PowerBI got so successful?

Not Just Another Toolbar

Even with the Ribbon to make things more ordered, sometimes it’s good to be able to jump straight to a feature you use commonly; the customizable Quick Access Toolbar on the top left of many apps gives you the ability to pin certain commands, and can be an invaluable way of getting to functions you like without delving into Ribbon tabs and menus.

Click the down-arrow to the right of the toolbar and you can pick from a set of suggested functions, or by customizing it, you can delve into any part of the extensive menus and pin just that one feature there. There are commands which are not even on the Ribbon, but you could pin them to the QAT if you like them…

The QAT is present in Word, Excel, PowerPoint, Access, Outlook (classic) – but not New Outlook, to some users’ chagrin. It’s not uncommon to find a similar UI feature in 3^rd party apps from the mid-2010s.

OneNote Favo(u)rites (again)

New Testament Tip of the Week #39 covered saving Favourites in OneNote: #39: OneNote Shortcuts, Favourites and Pins. Despite some of the guff being taken up with browser and mobile favourites, the good stuff in that tip was in (once again) recommending the fantastic OneTastic.

As well as providing an extensive macro capability, the OneTastic addin lets you pin a page or section to “Favorites”, and you can later go back to the same menu used to manage the pining, in order to access your previously pinned pages.

For extra goodness, try customizing the QAT and looking for Pin to Favorites…

Select it, click Add >> and hit OK. Now you’ll be able to access the drop-down for Favorites right there from the top left corner…

Happy Friday!

#54: New Outlook is coming, ready or not!

Posted on November 29, 2024 by ewand

Windows Mail or Outlook users might have already noticed that a “New Outlook” is coming. If you’ve already made the switch, you’ll be familiar with the new app’s look and maybe some of its shortcomings. For many people, it will be an improvement once they get used to it. Well, “some” rather than “many”, at least initially.

New Outlook is going to become the default mail client from January 2025. A blog post from this week’s Ignite outlines some of the new and “coming soon” features in New Outlook…

Copilot integrations. Lots of them. Organise meetings better, prepare, follow up, take better notes, have Copilot draft messages for you etc. Copilot for M365 is getting lots of new stuff too.
Erm… The only actual mention of “Outlook” in the Ignite session catalog is in the session, “Boost productivity with Copilot in Microsoft 365 apps”

There are lots of new features being built for New Outlook; some are filling in gaps compared to older experiences, though many are integrating new online services and apps (like Microsoft Places).

And there are plenty of things already in Outlook Web App (and therefore also in New Outlook) which are a step forward from dusty old Outlook (or Outlook (classic) as Microsoft is renaming it) – Snoozing email, pinning individual messages in the inbox, soon-to-arrive sorting by Copilot-deemed level of importance. If you’re responsible for delivering New Outlook to your users, check out the adoption site for more details of how to get the best out of it.

The timeline for Microsoft 365 users moving to New Outlook as a replacement for the old Outlook application has also been highlighted – from 2025, the current “Opt In” will become “Opt Out” – ie. Users will default to having the new client but can choose to revert to the old one. This will be delayed until mid-2026 for Enterprise subscribers. One glaring omission is the date when this Opt Out phase will be removed …

A quick history lesson

Outlook has been the primary email client for Windows & Office, since Office 97 appeared 28 years ago. Outlook evolved over numerous versions and was joined by a variety of other, different apps using the same name, even if they didn’t share anything else – mobile apps, based on software from acquisitions, and a web client that came from email server teams in Exchange. Even the old Hotmail service adopted much the same UI and was renamed Outlook.com.

Another similarly named app was Outlook Express, a rebranding of the previous free “Internet Mail & News”, used primarily to connect to ISP-provided services using internet standards (IMAP, POP, NNTP). Outlook Express eventually went away and was ultimately succeeded by Windows Mail, or Mail and Calendar.

Both of these – the heavyweight mail & calendar app for Office users, and the “lightweight” one that came with Windows 10/11, are on the verge of collision. It will be a long time before Microsoft can completely yank the carpet from underneath Outlook, but if you’re a Mail and Calendar user then your time is running out.

What will happen on 1^st January 2025? Will Mail users be forced to move to Outlook – seems likely. Will there still be a way to revert to Mail even if they don’t care about “support”? Expect there to be unsupported back doors for a while, but eventually resistance will prove futile.

If you find yourself using New Outlook and feel like reverting back for a few more weeks, there’s still time to go to Settings | General | About and hit the button to Open mail instead.

What’s what with New Outlook

The New Outlook application isn’t really a Windows app in the same vein as the old Mail app (which is/was a UWP app, whose raison d’être disappeared when Microsoft’s mobile ambitions died) or the classic Outlook (a regular Win32 app). New OLK is a wrapper on the Outlook Web App which is provided by Outlook.com/M365, so most of the functionality is really happening in a browser that is embedded in the application. This is part of a long-planned move of unifying the PC, Mac and web versions of Outlook and probably, in time, mobile ones too.

Some licensing changes were snuck in with New Outlook, then clarified as the restriction was lifted somewhat. [In a nutshell; if you only have an M365 Basic license, you cannot use New Outlook on your mailbox, even though you could use Outlook (classic)… think of it like closing a loophole which may or may not have existed previously]

What’s wrong with New Outlook

OK, there may be a few gotchas and glitches and some things aren’t quite finished, but so what?

If performs well enough – which it kinda-does – then who cares? Well, for Windows users at least, there are some pretty sizeable gotchas which are going to be hard to fix, if not impossible:

Offline. Every other email app that’s been written for Windows for years has had the ability to store an offline copy of your mailbox. Outlook 2003 and later even defaulted to using that local copy when running against an Exchange mailbox. Some offline capabilities have been / are being added to New Outlook, to help make it useable when you temporarily lose a connection, but so far, it’s a long way from having a full offline copy of your mailbox.
Gmail / iCloud / Yahoo! etc mail. Old Outlook and Windows Mail could both connect to Google Mail (and a variety of others, including IMAP mailboxes from old ISPs), by essentially downloading the email and doing everything locally, most likely synched with the original mailbox. New Outlook doesn’t have the offline spuds to do that yet, so it needs to sync all your Gmail into “the Microsoft Cloud” first – see here for “details”.
There’s no obvious way to see how much storage you’re using (or where the data is being held) though at least for now, there’s no additional charge. Some people might get a bit worried about Microsoft hoovering up all the email from a 3^rd party inbox and storing the data in its cloud… even if their responsible AI and privacy pledges say they won’t do anything with your email, it’s now not under your control.

Cross mailbox functionality is non-existent.

Search. Though you can add multiple mailboxes into a single New Outlook window, in effect you’re just pointing to nn number of M365/Outlook.com instances, one-at-a-time. Oh, and you can’t re-order them once they’ve been added.

This means that if you have – say – a work M365 mailbox, a private Hotmail/Outlook.com mailbox and maybe an old Gmail account, all synced and running in New Outlook, you can’t search for something across all of them. Ironically, the Outlook mobile client (at least on Android) does a great job, not only searching across account but presenting a single Inbox view composed from all of them.

Since each account is a separate thing on the back end, there is no way that cross-mailbox search will work unless there’s either a way of orchestrating it on the service (very non-trivial) or until all mail data is downloaded locally and the search carried out there. See item 1 in this list – not going to happen at all, or at least not in the foreseeable future.

Calendar. Other small gotchas get in the way here, too. Though the Calendar view tries to present a single view of a schedule composed from multiple mailboxes, when you create a new calendar entry, it starts from whatever your default account is.
Start adding details – subject, changing meeting times, etc – and if you realize you meant to use a secondary account, then it’s simple enough to click the drop-down box at the top of the calendar item and choose the account you wanted.
And everything you’ve just edited will be lost, with a blank entry now created in your other calendar.

Drag & drop. There’s no way of dragging stuff from one mailbox to another and there’s no way of importing from or exporting to a PST file either, yet. If you’re leaving a company and want to make sure you have all your private calendar entries and contacts, along with any personal mail you have stored in your work mailbox, better use Old Outlook to do that move.
Also, if someone sends you an attachment, in Old Outlook, you could drag and drop it into a folder elsewhere on your PC; no longer. You need to save it to OneDrive or download it to your computer first.

Zoom is horrible. Especially on smaller laptop screens, it might be necessary to zoom in and out of the preview pane in mail. In Old Outlook – just as in Word, Excel, PowerPoint, Edge, Chrome and however many other applications, if you use a mouse scroll wheel while holding down the Control key, the content in the application window will smoothly zoom in and out. Ditto zooming in and out using a pinch motion on a trackpad, or pressing CTRL +/-.
In New Outlook, some zoom motions don’t work (pinching on the trackpad) or if they do (mouse wheel + CTRL), it’s far from smooth and also zooms everything around the content too – including all the UI surrounding the content like the Reply buttons etc. It’s jarring evidence of the fact that everything in the preview pane is just the OWA browser view, and you’re zooming into everything, buttons and all, not just the content of the message

Example – here’s an email at 100% magnification:

Increase Zoom to 200%:

Yuck.

There are plenty of other things which grind the gears of online commentators, though some people quite like it (even if they later conclude that it’s still got room to improve).

If you’re using New Outlook, keep sharing your views on https://aka.ms/newOutlookFeedback and hopefully Microsoft will fix the stuff that’s not insurmountable, in addition to the quest to “Copilot all the things!”

#53: Right tool for the job

Posted on November 20, 2024 by ewand

Anyone who has worked in IT for long enough will likely have seen cases where unwitting users are wielding completely the wrong utility or application to get stuff done. Perhaps the entire company finance system is running on an old Access database, or the accountants were using a spreadsheet for holding something other than numbers? It’s one thing having lots of tools, but knowing which one to use when is sometimes a lost art.

Sometimes, organizational culture is to blame – if all you have is a hammer, everything looks like a nail (as how Microsoft leaders once felt about Oracle’s Larry Ellison saying that “database” was the answer to every question). Some companies use email for everything, others have moved all their internal comms to Teams or Slack, and occasionally use email only for customers.

The advent of Electronic Forms

One early measure of effectiveness of newly-installed IT systems, was the inefficiencies it managed to replace – and reducing paper forms was one often paraded benefit. Literally cutting red tape, not only speeding everything up and reducing wasted paper, moving to electronic forms was and is an easy case to make. Nowadays, you’d use a web form onto some kind of cloudy data store without even thinking about it, but it wasn’t always so simple.

In the late 1990s, forms were a key component of “Groupware”, with Lotus Notes being the early market leader (and which spurred Microsoft into competitive action in trying to build an alternative).

Microsoft had a separate E-Forms product as far back as the early 1990s, running on top of the old MSMail system, later being migrated into Exchange. The idea was that companies could easily make forms to send around in email, capturing data fields and making smart routing and workflow decisions along the way. It’s safe to say, they never really took off…

Outlook picked up forms duty (see here, in the cutting edge “Developing a workflow application” Exchange 5.5 whitepaper). There are still vestiges of Forms Designer in Outlook today (if you’re on Outlook (classic) rather than the upstart New Outlook, that is).

Forms in the 2020s

It’s so easy to use forms now – quickly building a web front end to a set of data is par for the course with Google Forms and Microsoft Forms, to name just two examples. Both are available in free versions (using a consumer Gmail/Outlook type login) or are part of corporate packages which bring extra functions and access to other data.

It is easy to create a form with some simple validation, and then collect responses from people – anonymously or (if they’re in your organization) capturing the logged-in username of the person who submitted it. Results are easily summarized and viewed with charts, word clouds~~wordclouds~~ and the like.

Each form is basically a series of questions, with different types used to validate data – like getting a rating, picking a date, choosing from set options or even entering specific types of text or numbers.

There are lots of scenarios where a simple form could take the place of sending an email – like registering for an event and collecting dietary requirements, or asking a group of people for a time and place that works best to meet; instead of trying to juggle lots of responses, a form could be the ideal way to present options and get their selections.

For meeting arranging scenarios there are numerous ways of trying to make this simpler – from websites like Doodle, the various Calendly/Bookings options for 1:1 meetings, or the former add-in utility FindTime for finding group availability in Outlook, which has now been replaced with a built-in Scheduling poll feature.

2020s meet 2000s

There are some things which should be easy, using Microsoft Forms, that are just not. Even though Forms can be run inside a M365 organization’s own tenant, and therefore we know who everyone is as they’ve already signed in, there’s no way of adding a “Person” to a form, such that they could be picked from the directory.

To do that needs to revert to an altogether older form technology – the SharePoint List. Originating from 2001, SharePoint really hit its stride by 2007, offering lots of web-based collaboration functionality that almost equalled what Lotus was doing a decade earlier. Microsoft did have another forms/data toolset, InfoPath, with SharePoint integrations – but that’s gone away now, not replaced with any single thing. We don’t really talk about InfoPath any more.

Using SharePoint and with~~With~~ a bit of nous, you can~~could~~ quickly build a detailed list – think of it like a simple database – and generate a form with data validation, branching logic and so on.

But a much easier way is to look at the newer Lists web app, which combines simple forms stuff with a SharePoint based back-end, meaning there’s more integration with M365, including directory integration …

… which looks a lot better than having to type someone’s name in.

Lists is part of M365 (look in the app grid on the top left if you go to Office.com and sign in, then peek under the More Apps section). )

In true Microsoft fashion, there are many ways to skin this feline – there’s also Loop, which could be used to do all kinds of groovy things in browsers, Teams, Outlook and more. Oh, and PowerApps. Mash all these tools together and you can build a spidery app legacy to keep your successors entertained for years.

#45: Copilot updates flying in

Posted on September 20, 2024 (Updated September 19, 2024) by ewand

Microsoft unveiled “Wave 2” of Copilot for Microsoft 365 earlier this week; if you haven’t seen the video, you can catch it here, or check out Teams guru and MVP Tom Arburthnot’s tl;dr summary of the key announcements and the demos.

BTW, if you are a Microsoft partner, you might have heard during MCAPS Start in July, that (woo-hoo!) Microsoft was going to give you some free Copilot licenses as part of the benefit of being in the partner program.

The latest Benefits Guide shows that Copilot goodness is indeed coming in January 2025, but it might be a bit less generous than expected… Basically, don’t bank on getting more than a handful of seats as part of the package, and if you don’t think Copilot gives you enough benefit to bother paying for it, then, well…

Talking of “what’s new” with Copilot, Microsoft veteran Malcolm Bullock has a thought-provoking explanation of what he means by “Nothing has changed but everything is new”.

Some of the stuff that was announced in the Wave 2 jamboree will be coming later this year, such as Copilot automatically prioritizing your emails in Outlook. Other pieces – the Narrative Builder in PowerPoint, for example, are here already.

The PowerPoint Copilot functionality is amazing. Give it a couple of lines of content and it will generate a whole load of slides by finding out information to flesh out your idea. If you’re an expert in an area then it might seem to give a glib, high-level overview with not-quite-correct images, but all of that can be tweaked and updated later.

Here’s an example:

… and literally a few moments later, it presented…

Not bad at all. At least it has a framework which could be used to flesh out stuff in more human detail.

While Copilot might have its most immediate use in helping to create content like images or writing documents/emails for you, there are other collaborative benefits too, some of which were covered in the announcement. As Malcolm says above, maybe we will need to tweak how we collaborate (using channels instead of chat for group discussions, for example) to properly harness the capabilities to good effect.

On asking Copilot in Edge to summarize the announcement page to less than 200 words, it offered:

· Copilot Pages: Introducing a dynamic, persistent canvas for AI collaboration, allowing teams to work together in real-time with AI-generated content.

· Enhanced Microsoft 365 Apps: Significant improvements in Teams, Excel, PowerPoint, Outlook, and Word, including new features like Copilot in Excel with Python for advanced data analysis.

· Copilot Agents: New AI assistants to automate and execute business processes, with an easy-to-use agent builder for creating custom agents.

· User Feedback and Updates: Over 700 product updates and 150 new features based on feedback from nearly 1,000 customers, improving performance and user satisfaction.

So there are lots and lots of new features coming, if not here already. Yay.

Meetings, transcripts and notes

One of the nicer new widgets that Copilot has brought recently is for putting meeting notes into OneNote. Previously, to record what happened in a meeting, you’d ask either Teams Premium or Copilot to generate some kind of notes, then copy/paste the text into OneNote alongside other stuff you might have jotted down yourself during the meeting.

Now, it’s made the process a whole lot easier – first, you need to be sure the meeting has been recorded or transcribed. If you go back to the Meeting inside Teams (look in the Chat node), you might see a Recap option which will give you the summary of what happened, along with actions that were discussed:

Now, go into OneNote, navigate to your existing notes page for a meeting (or create a new one) and go to Insert Meeting details. It will offer you a pane on the right side showing a selection of meetings from your calendar.

Previously, this would have copied just the bumph from Outlook like the date/time, subject and who the attendees were – useful as that is – but now has added a bunch more…

It’s a brilliantly useful way of adding some extra content to notes you might already be taking, or just to more easily organize notes and follow up actions from within OneNote rather than grubbing about in Teams to find them.

#40: Product Roadmaps – over/under promise/deliver?

Posted on August 16, 2024 by ewand

Since the early days of personal computing, products were always defined and sold to their eager customers on the basis of what features they had, or were going to have. ACME Computers would produce a feature matrix showed its widget program was better than XYZSoft’s similar one because it could start quicker or store more pages or print nicer fonts or whatever seemed important at the time.

Talking about features – or, even better, showing them – would be enough to convince users to open their chequebooks, so before RoI, business value, personas or use cases showed up, the product feature sheet and product demo were all important.

The brilliant Bob Cringely wrote in his seminal tome Accidental Empires of many significant bits of the history of the PC, Mac et al (or Accidental Empires: How the Boys of Silicon Valley Make Their Millions, Battle Foreign Competition, and Still Can’t Get a Date to give its full title). One tale was of a young Bill Gates demonstrating Word for Mac 3.0 somehow navigating a demo of a product so buggy that any number of clicks in the wrong place could have blown the whole thing up.

As well as selling what you have – or are going to have, real soon now – to prospective customers, there’s also a need to show that more stuff is coming down the line. The Product Roadmap shows long-term commitment and vision but also ties you into doing things that people bought your product for, even if they prove harder than you thought or less important because other things have changed.

Does saying you’re going to deliver this feature or that function tie one hand behind your back, but without it, customers could go elsewhere? In the old days, a roadmap or a demo of something that wasn’t really finished was as much a reason to stop people buying a competitor’s product, causing them to wait to see how yours turns out, as it was to get them to commit to buying something today – especially when the thing you’re showing isn’t yet available.

In the 1980s and perhaps later, Microsoft was a well-established peddler of “vaporware” – BillG even received a “Golden Vaporware” award for the years-late arrival of Windows 1.0, though the practice of promising much a long time before delivery had been going on for more than a century before.

When it all goes wrong

Sometimes a company will have scored such a momentous own-goal that its roadmap is more a plan for recovery and survival, than a yellow brick road to a brighter future. One such example is maker of homey WiFi HiFi gear Sonos, who rushed out a whole new software stack so they could launch some new products.

Sadly, the new app was missing a lot of features from the old one, was slow and unreliable and in forcing it out, they shot themselves in both feet and greatly annoyed many of their loyal fans.

Sonos’ CEO later had to apologize and promised to make things better over coming months, surely made harder by recently announcing a 6% staff layoff. Added to the 7% cuts made the year before, whatever the future holds for them might be that bit harder to reach.

Microsoft Roadmap update

Sometimes, a roadmap leads to a cul-de-sac – the product is killed, dies of natural causes or similar. But when it supposedly gets many users, the majority won’t really care what features and functions are being added day-to-day.

Over in Redmond, the roadmap of specific products and features might seem less important (unless they’re selling the products, or others selling products to them), yet quite some effort goes into maintaining roadmaps for the Microsoft 365 offerings. Presumably it’s to keep existing customers informed and happy enough, reminding them what they’re getting for their continued subscription. Or sometimes to provide early signal that certain things are going away, even if only so they can later point to that notice when someone moans about their favourite thing being wiped out.

The Roadmap site has been growing its coverage outside of core M365 products, and there are other sources of roadmap info – Azure, Windows (and info for Insiders), Dynamics & Power Platform and probably more.

In other parts of Microsoft, the moderately-loved Paint 3D – the supposed successor to the venerable MSPaint – has now been given it’s marching orders. Back when the future was in 3D – from the TV in your living room, to the massive goggles on your face, it’s was all about that 3^rd dimension until it wasn’t.

#34: Bringing AI to the Whiteboard

Posted on July 5, 2024 (Updated July 4, 2024) by ewand

One of the joys of in-person group meetings is when someone grabs a whiteboard marker and starts laying out their still-forming thoughts to the enthralled audience, almost as popular as the person who always asks a question 2 minutes before the meeting is due to end. Thankfully, there is a digital whiteboard for use in virtual and hybrid Teams meetings, too. And like seemingly everything else, it’s getting a sprinkle of Copilot-y Goodness.

The Whiteboard app has appeared in previous ToW’s (before the Great Reset) – here. As a quick summary: if you’re a Microsoft 365 subscriber, you’ll find the Whiteboard tucked under More apps in the grid on the top left on numerous sites…

… or available directly on https://www.microsoft365.com/apps/ or just launch it directly from https://whiteboard.office.com/. A Windows app is available in the Store, though it’s really just a wrapper for the web experience.

Whiteboard is intended as a multi-user collaboration tool, available in the browser as above, or in Teams, by using the Share button (NB: if you look under the Apps button to the left of Share, you won’t easily find this Whiteboard, but there are other “Whiteboard…” 3^rd party apps which will show up: YMMV).

One very cool new feature is the ubiquitous Copilot option; it can help get you started on a brainstorming exercise, for example. Start by giving it an idea of what you’re trying to work on

… and it will come back with headings which can be quickly added as Post-it style notes

Selecting one of them and choosing Categoris|ze …

… will arrange them into subject blocks.

And selecting any one and selecting Suggest will go a level deeper and bring up some additional points.

As a discussion starter, it’s brilliant. Give it a try and see what kind of inspiration you might find.

The main Whiteboard info page is here. There are some cool templates available for getting started with some pretty detailed layouts for workshops, Kanban boards etc; more info here.

RIght, now there’s only 5 mins to go, the meeting is starting to wrap up – for goodness’ sake, keep your hands down.

#31: Easy and Excel-lent Data sources

Posted on June 14, 2024 (Updated June 12, 2024) by ewand

Most people who have spent time using Microsoft Excel will realize that it probably has more capabilities than they’ll ever understand, much less use. There are so many functions used to collate, display and interpret data that it’s no wonder people turn to using it for all sorts of things.

There have been numerous attempts to make user-friendly data tools for Excel, from web-scraping 3^rd party sites to the short-lived Money in Excel for American users which bit the dust before it was barely out of diapers.

More recent releases of Excel include several Linked Data Types which can retrieve and manipulate data from “reputable sources of data, such as Bing”… (which, incidentally, had its 15^th birthday recently). Companies with suitable data governance can expose internal info for analysis, or regular end users can get started with share prices, currency conversions and geographical data.

In the Data tab in the current versions of Excel on multiple platforms, you’ll see 3 or 4 types of data that can quickly be inserted – they will perform a lookup on external information and return a data set in the background which can be displayed and otherwise interacted with using formulae, lookups and other standard data tools in Excel.

Getting real-time data is pretty straightforward – create a blank table with a single column in which you’ll enter your key data items that you want to expand on – for currency conversions, it would be a pair of currency symbols (USD:EUR or GBP/USD etc) that you then select and mark as Currency from the data tab. That then lets you easily add other columns for specific lookup data, and that can be referenced itself through other formulae too.

Stock lookups work similarly, by entering the ticker symbol in one column and potentially going through a matching exercise to find the right one. Handy, if you have a workbook for calculating when you can stick it to the man and retire to a patch in the Tuscany hills: you can automatically look up the stock values and convert their currencies too, if required.

There’s some location stuff as well, invoked by entering city or area names; it’s more text-based reference info which is returned, though it might be possible to feed some of the data into a Map Chart for further visualization.

#25: Where shall we meet again?

Posted on May 3, 2024 by ewand

If you are of the 400 million or so users of Microsoft 365, you probably use email as one of the key services, provided by Exchange Online. A little more than 28 years ago, Microsoft released Exchange Server, its first proper server product* and a key progenitor of Active Directory, an enabler of pervasive use of email in business and even standardising the means to deliver mail to your mobile.

Though its main usage is email and calendaring, Exchange was designed in an era when “groupware” was the next big thing; the idea was that the server would be a database of loosely structured data that end users would interact with using forms to display and edit those data. As it happens, an email message is just a bunch of data items like the recipients’ names, the subject and a whole load of hidden fields used in routing, storing and managing that message. When you open that message in Outlook, it uses a form which looks like an email to interpret the fields.

Outlook 97 and its numerous successors all feature a form editing capability – right-click on the toolbar, choose Customize then on the resulting dialog, you’ll see a hidden “Developer” tab.

Tick that box to unveil it and you’ll see a new ribbon tab with all kinds of functions, one of which is to choose a form from various libraries, or even design a new one (based on an existing form, such as an email or a contact card).

A relatively simple use of this kind of thing would be to add a custom field or two to an existing form; it might be easier to use categories but if you wanted to include some more structured information to a contact form, for example, you could put additional data fields with validation logic behind them. Essentially what you’d be doing here is adding some custom fields to the database, and you’d also be customizing the form which is used to view and edit that newly extended data structure.

For one practical application of this technique, see ToW 259, which shows you how to track who you sent and received Christmas cards to/from, by modifying your contacts form.

The downside of customizing a form is that you need to distribute the newly extended form otherwise people won’t be able to see the additional fields – that’s essentially an automatic process when using Exchange and Outlook within a single organization (the form is published on the server), but it becomes a whole lot messier when dealing with external parties. And it doesn’t work at all on web, or mobile, or the “New Outlook” (which is basically the web client wrapped up to look like an app). So, it’s super useful these days.

New Outlook, new Meetings

One of the subtle changes that New Outlook introduces to anyone who switches from Old Outlook, is a change in name to a key part of its functionality. In Outlook, you have appointments (which are things you put in your own diary) and meetings (which are appointments to which you invite other people or resources – or have been invited by the organizer – and that changes the form which is used to interact with the calendar entry).

New Outlook simply has “Events”, which essentially combines the previous two concepts in one. As well as a different UI, New Outlook adds some other functions, like the “In-person event” button, which lets you make the point that a meeting is taking place IRL rather than only virtually.

This same functionality is not available in Old Outlook or in mobile versions, only in Web App and New Outlook – but that doesn’t really matter too much, as all the “in-person” switch does is to append “[In-person]” to the end of the Title line.

Clearing the switch removes the text, though it’s just searching for that string and hacking it out (set a Subject line of I like people [In-person] [In-person] [In-person] and then toggle the switch back and forth a couple of times to observe). A more elegant way of building this would have been to make a new field on the Appointment / Meeting object called “in-person” and then had Outlook et al flip that switch, rather than just tagging a bit of text on. Oh well…

If you’d like to explore what lies beneath your favourite Outlook form, try opening the item (Contact, message, Task etc) in Old Outlook, and if you don’t see a Developer tab, repeat the exercise from earlier and right-click / customize / enable Developer. You’ll then see a “Design This Form” option, which flips your current item into developer mode, exposing hidden regions. Look in the “all fields” tab, and you’ll find every property that has been set on the item.

This trick can be useful for finding out when an appointment has really been created in someone else’s calendar (if they share their calendar) – if they are suddenly unavailable to meet, you could see if they had that blocking appointment in there already or just created it after you’d asked…

APPENDIX

Email was first invented in 1971, and by the mid 1990s it was in common use amongst people who worked on terminals or desktop computers as a large part of their job. While standards like X400 and SMTP existed for exchanging email with other entities, you weren’t going to be emailing your bank manager or booking a doctors appointment. For some reason, X400 never really took off as intended – who knows why? Most companies which used email relied on it for internal communications – IBM’s NOSS internal system (a PROFS implementation) had over 350,000 users by the end of the 1980s, with no easy way to send email to external parties.

As dial-up services like AOL and ComuServe started gaining popularity amongst home users, and those walled-garden opened up and allowed exchange of email over the internet, companies began to adopt email more widely and people would start to put their email address on their business cards (alongside their fax number).

*Exchange was arguably Microsoft’s first proper server product, apart from Windows NT Server on which it ran. There were other back-end products which pre-dated Exchange, like SMS (systems management software), SNA server (for communicating with mainframes) and the early NT variants of SQL Server which had been based on Sybase’s SQL Server. These and Exchange had been bundled together as Microsoft BackOffice for a while. Exchange was the first Microsoft server product that most end users would interact with.

#21: Dating OneNote

Posted on April 5, 2024 by ewand

It’s not hard to find websites listing “the best apps for … in 2024”, usually covering the same options that were doing the rounds in 2023 and 2022, with a few tweaks. The category of note taking, across web, desktop and mobile, is a common theme, sometimes offering head to head comparisons between leading options like Evernote vs OneNote. If you’re committee to one note-taking app, you’re not very likely to switch, but maybe there are people who scatter their stuff across multiple services and apps and are looking to centralise on just the one.

OneNote has had plenty of attention in ToW’s passim – see the archive – but even with decades of familiarity, it’s easy to miss some really useful capabilities that can improve the user experience. Let’s have a look at some, concerning the topics of date (and time, probably, since the continuum thingy means the two are inextricably linked).

Inserting dates & times

This one is easy, and its utility will depend on how you go about taking notes. If you start a new page for each conversation or topic, then you’re probably covered to a large extent. When you insert a page, OneNote will automatically tag the top of it with the current date and time…

In the days when (at least) two OneNotes (yes) were jostling for position, the new One wouldn’t let you edit the date or time of an existing page, but the old One did … and since it vanquished the upstart, still does. Just click the date (or time) and then the calendar (or clock) icon that appears next to it, and you can set the appropriate measure.

If you’re more the type who has one long page of notes (around a single topic, or a single person who you meet multiple times, for example), then inserting the present date/time is near essential when appending or updating stuff – just go to the Insert menu and choose your datum | data.

Of course, keyboard warriors will want to remember the handy shortcuts to insert the current date (ALT+SHIFT+D), time (ALT+SHIFT+T) or both (ALT+SHIFT+F). The first two also work in Word and in Old Outlook (which uses Word as its editor), but don’t work in New Outlook, which doesn’t.

Reviewing old edits

One easily-missed trick in OneNote is to see when a piece of text was last updated. It’s pretty clear if you’re sharing the workbook with someone else, as their updates are (optionally) highlighted and can also be searched for.

Look at your own notes and if you hover the mouse over any text or other content, you’ll see a small grey paragraph marker on the left; right click on the text and you’ll see, at the bottom of the context menu which appears, the author who made the last change, and when.

Search and find by Date

If you have a lot of notes, searching for a specific term might return many results, possibly spread across multiple notebooks and not necessarily presented in a useful order:

Look at the bottom of the search results dialog, however, and you’ll see an obscure feature: Pin Search Results (ALT-O), which will open the results in a side window, allowing you to filter and sort them more effectively.

This clearly makes it easier to find the most recent edits you’ve made with your search term. Add this ALT+O to your OneNote arsenal. While you’re at it, make sure you also install OneCalendar, which shows a view of your previously-edited pages on the days you edited them.