Think of this week’s tip as not one single dish, but rather a series of related snacks; a groaning table of cold hors d’oeuvres or sizzling spicy tapas, with one common theme – they’re all about Excel.
May the F4 be with you
One of the neatest features in Office yet largely hidden; the F4 key repeats the last command without needing to faff about selecting the option from the menu. So what, you might say? Well, what if you’re formatting cells and want to repeat the same format over and over again – you could change one, then use the Format Painter option to apply that to select other cells, or possibly just apply the format you want, then select each additional cell in turn and press F4.
One slight downside is that it only repeats the very last action, so changing a number format and then making it bold wouldn’t be easily repeatable since those are two actions. Still, there are so many uses for this “Magic Key no one knows about”.
CTRL+y does the same thing in case you’ve got one of those annoying keyboards where the function keys do other things, you never know without looking if you’ll be pressing F4 or changing the system volume.
Layouts and Tabs
Now, Windows has lots of tricks for arranging application windows side by side, especially if you have multiple monitors; there’s a particularly shiny new way of doing it in Windows 11 with Snap Layouts. In the context of Excel, that’s OK if you’re using two spreadsheets side by side and you might want to reference or copy data between them, but Excel has its own window-handling functions that could be more useful.
The key scenario here is that you can open the same document in more than one window (by clicking New Window, on the View tab) and then show different parts of it side/side – separate tabs, perhaps, or different areas on the same sheet.
The Synchronous scrolling feature means you can also keep the cursor at the same point in both sheets, making it easier to compare. If you have functions on one tab that depends on data from another, you could change the data in one window and see its impact in real time in the other.
Click on the Arrange All menu option to automatically distribute the open Excel windows, optionally confining the process to just the windows from the active workbook.
Transpose data with paste
It’s a fairly common exercise to take a load of data that’s in one format and want to represent it differently; there is a useful Transpose feature that takes data from columns and paste it back as a row, or vice versa. One useful scenario could be when you want to take the names of everyone who got an email or meeting request and put them in a tabular format.
Start by copying and pasting the names/addresses from Outlook’s To: field into a new cell: you’ll see that is’ one long string of text that needs to be broken down, but in this case, each address is delimited with a semicolon (“;”).
Having selected the cell, go to the Data tab in Excel and choose Text to Columns then choose Delimited and select the semicolon. Once the wizard is complete, you’ll end up with each address in its own column. Now select the multiple cells and copy to clipboard.
Next, put the cursor on a lower row or maybe a new sheet altogether; right-click on the destination cell and under Paste Options, look for the Transpose icon (with the two arrows); hovering over the different icons in this menu will preview what you’re going to do. Click on the icon to commit.
Now you have a list of addresses on their own row, and without the “;”s, but they do have a leading space ahead of all but the first one. It might be quick to correct each line in turn, and there’s always the TRIM function which could be used to tidy stuff up through formulae.
When you’re happy, remove the original line that had the text in columns, leaving just the separate email addresses on their own rows.
Now, snacks just make you hungry, don’t they?
Even fans of OneNote – either the full-fat Windows x86 version or the versions targeted at other platforms, mobiles and the Microsoft Store, both of which have been covered extensively in ToW passim – will likely use only a fraction of its total functionality. Did you know, for example, that pressing SHIFT+CTRL+> or SHIFT+CTRL+< increases or decreases the font size of the selected text? Or holding CTRL while pressing DEL or BACKSPACE in a block of text deletes the word either side of the cursor, and not just a single character? (Actually, these are true of other Office apps as well, though not Excel, presumably because using Excel for text formatting is considered deviant and weird).
There are many useful features hidden in plain sight, like the tagging functionality which sits on the Home tab. In OneNote for Windows, if you have the ribbon expanded, you’ll see a series of icons to mark selected text with a Tag, and for the top 9 you can tag the text by pressing CTRL+n, where n is the number in the list.
The idea with tags is that you can quickly reference back to the specific text that you’ve highlighted and tagged, via a hyperlink. As well as the variety of in-the-box tags, it’s easy to add custom ones: click on the down arrow at the bottom of the list and choose Customize Tags… then you can supply your own description and choose the icon and colours.
Finding previously-tagged text uses the seriously powerful but sometimes obtuse search function in the sidebar; if you use OneNote to take notes from Outlook meetings, by default you might see hundreds of links that appear to be tagged.
Try using the Search filters at the bottom to restrict the results set, so you only show tags within a given notebook location or across all your notebooks, but for a specific time.
The “OneNote for Windows 10”, Mac and mobile versions of OneNote handle tags slightly differently; while more-or-less compatible the degree of functionality does vary between the clients. Generally speaking, you can find tags across them all, though you may be restricted in editing or creating them. The OneNote mobile app supports a “To Do” tag, for example. The web clients don’t offer custom tags at all, and don’t allow tag-specific search (other than just text indexing).
In the OneNote for Windows 10 store app, you can search for Tags but custom ones created in the desktop app don’t appear in the Tags list when editing a page. Only a handful of tags are initially offered in the store version, and if you add a custom one it’s still possible to press CTRL+n to use it, but you need to count where your tag is in the list as it doesn’t show you the shortcut.
Custom tags added in the store version don’t appear in the tags list of any other client though do sync across other devices, to some degree.
Given the slight rough edges between the versions if you routinely open the same notebook in mobile, web and store/desktop apps, then Tags may not prove so useful – but if you tend to stick to a single UI – especially if it’s the older desktop one – then it’s worth exploring how custom tags could help you organize your stuff.
Great News! Another family of products has been announced during Inspire – previously a mega event held in Las Vegas, now a carefully-choreographed series of pre-recorded sessions being shown as-live with real people providing Q&A support. Many companies have moved their productivity and communications services to the cloud (Office 365 largely being supplanted by Microsoft 365 as more security and management stuff was added), and shifted some or all of their server estate to someone else’s datacenter too. Increasingly, if people were physically sitting in an office anymore, the only on-premises compute would be the PC they’re using (plus some networking gear, and a printer or two).
Windows 365 delivers a “Cloud PC” – literally a machine running Windows, which is remotely accessed by an end user and stays just like they left it when they disconnect, but is managed and secured centrally. As you may expect, there will be various SKUs depending on how capable you want it to be; Paul Thurrott opines that there will be many options, as “Microsoft is addicted to tiers”.
General Availability is due on 2nd August; it’s sits on top of the existing
Initially, at least, Windows 365 will be offered only to businesses already using Microsoft 365; the model being that you choose how many machines you want, and what size they’ll be (datacenter location, how much memory/CPU/storage etc), and the actual machine will be running in the Microsoft datacenter, allowing you to remotely access it from anywhere and on any device.
According to Mary Jo Foley, it will be reassuringly expensive so use cases will be carefully chosen rather than thinking everyone will sit at home running W365, accessing it over some ancient PC. For more details on machine sizing and the mechanics of provisioning and managing Windows 365, see here.
Interesting examples given during the announcement were the remote government of Nunavut, or having hundreds of interns joining Microsoft for the summer; normally they’d come to the office and be given a PC but since they’re all at home, the cost and time burden of configuring the PCs and shipping them out would have been high. Instead, they’re given a virtual desktop via Windows 365 – created en masse in a few minutes – and they connect to that from whatever kind of device they already have at home. When their tenure is up, their access is removed and there’s no data left behind on their iPad/Mac/Chromebook or home PC. Maybe 2022 could finally be the Year of the Linux Desktop?
For the rest of us; Windows 10 is still moving forward and the latest release due later this year has entered its latest stage of testing – Windows 10 21H2. And Windows 11 got another update to 22000.71, offering a variety of tweaks and polish. Even though Windows 10 is a modern OS with lots of great functionality, if you have already switched to Windows 11, using a machine with Win10 feels like going back in a time machine.
If you were hiding under a rock, you may have missed the unveiling of the next release of Windows. Early adopters on the Windows Insiders program (which can be joined in from the Windows Update settings page on Win10) can already upgrade to Windows 11; currently that means being in the Dev channel (the most aggressive in terms of pushing our updates), so if you’re willing to run the risk of suffering a bit of discomfort, then you can get access to the preview bits now – or maybe wait until a bit later in the year and a more complete and stable build will make it to the Beta channel. If you have a Thurrott.com account, see what Paul thinks about whether you should try it out or not.
Windows 11 promises not only a design refresh, but an under-the-covers shift from a security and reliability perspective, which means the compatibility list is pretty restrictive – it’s being targeted at newest hardware that supports updated security and performance management features. While many fairly recent machines will pass the test, DIY home PCs and older laptops are not likely to cut the mustard. The Windows 11 update and support cadence has been unveiled recently too. Maybe the ideal solution will be to buy a new PC when Windows 11 arrives…
Microsoft people who set up their Insider enrolment as being associated with a @microsoft.com email address will see additional options around which Branch or Ring to use – if that’s you, then unless you’re technically self-sufficient and very comfortable with the level of pain you may feel, be careful. External users get to join Dev / Beta / Release preview Channel.
Improved and updated functionality includes not just the fancy new Taskbar and Start menu – there are lots of areas where deeper integration with app functions and the OS itself will help to make it a slicker experience overall.
The virtual desktop experience has been improved somewhat – you can set up multiple desktop environments, then easily switch and drag/drop apps between them, but there are improvements over the same feature in Windows 10 – you can set different backdrops/themes for each and they persist between machine reboots.
Press WindowsKey+TAB to see the desktops and manage the apps – that’s the same key that used to control the Win 10 Timeline feature which has now been removed.
When it comes to moving windows around, there’s a greatly enhanced Snap experience, so you can arrange windows by either dragging them to the appropriate corner of the screen or by pressing WindowsKey+Z to bring up a dialog that will snap your current window to the selected location. Newly added is a 3-line view if you have a portrait-aspect display.
Some features that have been disclosed – like running Android Apps on Windows – will be delivered in later previews, and doubtless some that are present now will change before final release. There’s already been an update which adds more tweaks and fixes some bugs.
A “Windows Health Check” app was released briefly which would report if your PC was suitable for Windows 11, but was short on information as to why a particular PC might not be upgradeable, so the team has pulled the app for now and instead points to the info on Windows 11 Specifications. At least while it’s in Dev channel, it is possible to get Win11 on machines which don’t meet the bar, though there is a warning that your experience may not be all there and some things won’t work. YMMV.
Lots of terms in computing have their roots in an earlier time, where the association has long since disappeared. The mouse was so called because of its “tail” connecting it to the machine; when was the last time you used a wired mouse? Then there’s the apocryphal story of a young person, on first encountering a 3.5” floppy disk (er, not so floppy any more) thinking that someone had 3D printed the save icon.
As well as the QWERTY keyboard layout, a few things were carried over to the modern computer from the typewriter – the backspace and tab keys and the carriage return key. Purely mechanical typewriters had an end-of-line lever, which caused the paper to feed one line and the whole mechanism of the roller to shoot back to start a new line. Electric typewriters had the innovation of not requiring the dings and the manual whirrs, by pressing the RETURN key to automate the carriage – the symbol still displayed on most computer keyboards today is indicative of the physical action.
Early computer systems aped the same approach of the line feed (ie the paper being shuffled up one line) and the carriage return (going back to the left side), as being separate activities and they were given specific control codes – so CR, LF and CR+LF are still things. For some time, consternation still applied as Windows considered that CR + LF needed to be noted to really start a new line, whereas the Unix fraternity simply thought that LF was all you needed. It is possible to hack the registry so Notepad acts Unixsy should you need it to.
In most applications, if you want to start a new line, you’d just press Return or Enter (in effect, the same thing, though not always the case). Pedants would say that ENTER doesn’t mean you necessarily need a new line, you’re just committing some data you’ve typed, redolent of the old terminal where you might be submitting a form rather than typing in free text.
Applications perform sometimes completely different actions when you press a modifier key like CTRL or ALT, and ENTER. In Word, CTRL+ENTER starts a new page, ALT+ENTER repeats the last typing action. In Outlook, CTRL+ENTER sends the current email and ALT+ENTER – like the same keystroke normally does when looking at a file in Windows Explorer – shows the properties of the current message.
In Excel, CTRL+ENTER has some other meanings, notably it completes the entry of data into a cell, without moving the selection to the next cell along or to the line below (depending on config). SHIFT+CTRL+ENTER can be used to create a powerful but quite complex array formula. ALT+ENTER also has a useful trick for formatting text in-cell, alongside some tips to control cell text formatting.
Sometimes, however, the layout doesn’t quite sit right unless you resize the column, and that might not be ideal. If typing in new text into a cell, you can force a new-line within the box by pressing ALT+ENTER. For existing text in a cell, one solution to put a new line in is to double-click in the cell, which will insert a flashing text cursor and you edit text directly in the cell rather than the formula bar text box at the top of the sheet. Move the cursor using the arrow keys and/or by clicking the mouse elsewhere in the text; press ALT+ENTER to force a new line in the box.
Another way of editing text in an existing cell; select it and the text will be displayed in the formula bar, but only the first line, unless you have the formula bar expanded out, by clicking the down-arrow on the right.
This tip has been a very long time coming. Back in ToW 479, the subject of running WordPress on Azure was mooted, and it prompted an internal-to-MS conversation about the guidelines for publishing stuff externally.
The extended back story is that there were hundreds of employee blogs which had been published under the technet.microsoft.com and msdn.microsoft.com sites, both of which URLs could trace their birth back to the 1990s, and a project was underway to clean them up and rationalize somewhat.
Initially, guidance to MS bloggers was (basically) “unless you’re an official blog, you have <nn months> to move your stuff elsewhere before it gets deleted.” Certainly, there was to be no new content after the cut-off date.
That guidance relented somewhat and content from relatively active blogs was migrated to the Microsoft Docs archive though taking a trip through the final posts from the ToW host blog, The Electric Wand, shows that lots of graphical content was not carried across – more of a lift & dump than a lift & shift.
Anyway, the solution for Tip o’ the Week was to move to an external website – www.tipoweek.com – which is hosted in Azure and, like about a third of all websites, running under the content management system, WordPress.
Setting up a WordPress site is pretty straightforward, really – though you do have a variety of options on what kind of site you want to build. If you need a complex site with lots of control over it, then you might want to run it in a Virtual Machine or a container. For most of us, though, a simple App Service will suffice. From the home page of your Azure subscription, just Create a resource and search services and marketplace for WordPress, then select the WordPress App Service from the multitude of options you might get.
For more tips on how best to get up and running with WP in Azure, see here.
One retiring Microsoftie (not the shy type, but leaving the company, today in fact), emailed last week to point out that the tipoweek.com website was being flagged in Edge as Not secure. Oh Noes!
This has, in fact, been a niggling issue for a while, since Chrome (and Edge, given its diet of Chromium) instituted a policy of flagging any website that doesn’t use the secure HTTPS protocol & SSL by default.
Secure Sockets Layer, if you’re not overly familiar with it, relies on a way of encrypting data travelling between two points, using a previously-generated pair of mathematically-linked digital keys. If you have one key, you can use it to encrypt data which can only be decrypted by the other key in the pair (ie you can’t even use the same key that encrypted the data to decrypt it again). Typically, one of these keys is publicly accessible and the other is kept private.
One way of sharing a public key is to embed it in a site’s SSL certificate, which is in turn validated by a mutually-trusted third party (called a certificate authority). If you visit the website for an institution like a shop or a bank, then your browser will download the site’s certificate, validate that it’s still current and trusted, then use that public key to encrypt data sent to the site. Since that data can only be decrypted using the corresponding private key, we can validate that the site is not being impersonated.
The whole public/private key encryption process has something of a computational overhead associated with it, but once we have established a secure connection, we could use a faster encryption technique for data sharing by using a single key that can both encrypt and decrypt the same data.
In other words, if I go to a website that presents me a certificate specifically issued for that URL’s domain, I can be sure that the site handing out the cert is who they purport to be. This could be validated by me generating a random set of numbers, encrypting it with the public key and sending that to the site; it would decrypt the gobbledygook with the private key that only it has, and we now both have the same set of data that has been securely shared between us. That would form the symmetric key that we can use for the rest of the connection.
For more detail on these kinds of topics, check out the Cryptography 101 podcast on Hanselminutes.
In Edge, if you want to look at a secure site’s certificate, click on the padlock icon (or the handbag icon as some people once saw it – that meant it was safe to shop) – and click the “Connection is secure” banner, then click the little certificate icon in the upper right.
The trouble is, if you’re hosting a hobby or a community web site, paying for an SSL certificate might seem a bit of overkill; web hosting companies will try to bundle them into domain protection and other security features which might be no big deal for a commercial enterprise but a little stiff for a parish newsletter.
Fortunately, there are alternatives, though they do need a bit of spade work to get up and running. Hanselman (yes, him again) discussed using an extension and an organisation called Let’s Encrypt, whose goal it is to make the web 100% secure. They have issued over 225 million SSL certs, and will generate 3-month-validity certificates free of charge, as an alternative to paying anything from $60-200 a year to a commercial issuer. With a bit of practice, it doesn’t take long to create and manage the certs and if you only need to do it 4 times a year, then it could be time well spent and money well saved.
An alternative method was written up by fellow Microsoftie Andreas Pohl, using a slightly more manual method to create the certificate then import into Azure; if you’re looking for an excuse to get Windows Subsystem for Linux up and running, then this could be it.
Once you have the certificate exported to a file, it’s a matter of a few clicks to import it into the Azure App Service that is running WordPress, set up the bindings appropriately, and you can then flick the switch to make the site only service up content over HTTPS.
Back in the mid/late 20th century, the mainstream car market in developed countries was quite localized, where certain brands were seen as the default. Italians drove Fiats and Lancias; even until fairly recently, pretty much all you’d see in French towns were Citroëns and Renaults. The biggest blue-collar rivalry for Brits, Aussies and many Americans was undoubtedly… are you a Ford family, or a GM family?
In the UK’s 1970s, Ford had the Fiesta (small), Escort (mid), Cortina (large), Capri (sporty) and Granada (executive). GM operated in mainland Europe as Opel (Kadett/Rekord/Monza/Senator etc) and in the UK, as Vauxhall (Chevette/Cavalier/Carlton etc). Brits of a certain age may fondly? remember the Escort-sized, everyman family car: the Vauxhall Viva. The announcement of the employee wellbeing platform, Microsoft Viva thus brought a misty-eyed moment of reflection for some…
Since the unveiling in February 2021, Viva functionality has been gradually added to a variety of Office 365 experiences from Topics (based on what was called Project Cortex), Learning (highlighting online learning materials from a selection of company-curated sources, including stuff from LinkedIn Learning), Connections (a modern take on the company intranet) and the first module which was available, Insights, which is accessed via an app in Teams.
The Insights-defined “Virtual Commute” and calendar-blocking Focus Time has been mentioned previously in ToW #577, but it’s had a new shot in the arm as well as announcements about forthcoming improvements, such as the ability for Teams to quieten notifications when you’re in a focus period, and quiet time when Teams and Outlook will shush pinging you outside of working hours.
Now rolling out to Viva Insights is a set of mindfulness and meditation exercises curated from Headspace, who produce a load of online video as well as Netflix series and in-flight channels. See more about Headspace in Viva Insights, here.
Subscribers to Office 365 / Microsoft 365 obviously get a load of services like email, OneDrive storage, SharePoint and so on, as well as client apps like the full-blown Office suite. Over the years, the app experience has got quite a lot closer with the web clients sometimes advancing faster than the desktop or mobile apps, meaning that it’s increasingly viable to live your life entirely in the browser.
The Office home page – on www.office.com when you’re signed in using your M365 account, or maybe even installed as an app on your PC – shows a list of available apps if you click the grid icon in the top left. Initially you’ll see the most popular or your own most recently used apps, but try clicking on “All apps” for the full list of what else is offered.
What you’ll see depends on what kind of subscription you have and what previews you might have opted into, as well as what apps may have been published by your subscription’s administrators (eg internal HR website or IT support desk sites could be listed there).
To keep things interesting, you can also install most of these web apps as Progressive Web Apps on your PC – using Edge, go to the Settings “…” menu in the top right, and look for the Apps menu option. They will then appear in the Start menu, can be pinned to the Task Bar and run in their own discrete window, just like a “real” program would.
One app which could roll back the years for a lot of people is Visio. Microsoft bought the diagramming software company at the turn of the century, for what was the largest acquisition to date – check out the list of other deals and see if you can remember many of those other $100M+ names…
Microsoft Visio became a premium addition to the Microsoft Office suite, latterly being sold as an add-on like Project. The software has continued to evolve over the years and has its own band of fans who use it for mind mapping, flowcharting, network diagrams, room layouts and so much more. You can even build Power Automate workflows using Visio (see more here).
It was recently announced that Visio is coming to a good many Office 365 subscriptions next month, for no extra charge. The “lightweight” web app approach is not going to supplant the full application for more complex purposes, but it still offers a wide range of templates that can be used to start some fairly snazzy drawings, all done in the browser.
If you’d normally turn to PowerPoint to try to create graphical documents like flow diagrams or simple org charts, keep an eye out on the All Apps list to see when Visio makes an appearance, and give it a try.
The Pandemic may have exposed millions of new WFHers to the delights of video conferencing, but the Zoom in this week’s ToW has nothing to do with the eponymous meeting company, rather it deals with a very cool yet somewhat obscure feature in PowerPoint, called, er, Zoom.
Way back when, there were numerous product incubation groups in Microsoft, who tried out new features as addins or companion products; over time, most of them have disappeared or the prototype products they produced made their way into the mainstream (or just quietly went away). Innovation continues within the various engineering groups, of course, and some is curated in the Microsoft Garage.
One OfficeLabs project that showed promise was pptPlex – an addin to PowerPoint that made it easy to create and present “non-linear” presentations, offering a kind of “Seadragon” type experience of zooming into content. The actual pptPlex software is long-gone but if you want a reminder of what it was like, or even to recall how funky Office 2007 looked, check out this video tutorial.
But like other Labs innovations, the ideas were re-born in other ways, in this case in the PowerPoint Zoom feature. This lets you essentially embed a thumbnail of a slide into another one, optionally even replacing the mini-version of the slide graphic with a custom image; in the example shown below, rather than a miniscule chart of stock price, we’ve put the company logo instead, but clicking on it drives a nice zoom transition to a separate slide which has the real chart.
It’s probably easiest to see by example so check out the download here – to experience the Zoom effect, put the file into Slideshow mode.
The “new” Edge browser has been around long enough to be just “the Edge browser”, given that old Edge is not only deprecated but being removed from Windows 10. If you’re still clinging onto Internet Explorer, then get ready for its demise; it’s got a year left, then the plug gets pulled. Plenty of ToW’s past have dealt with the Edge browser, especially the benefits of having multiple profiles (useful to keep work and personal stuff separate, or even having different sets of credentials for common sites, like demo accounts vs real users).
A feature which was added to the profile functionality was the ability for Edge to switch between them automatically – in other words, Edge would try to determine if you’re opening a site in the “wrong” profile, and it also lets you set the default on which profile should be used when you click an external link in an email or a document (other than in the browser itself). This not only changes how the site might be displayed, but also sets where the browser history is saved, and which set of cookies, cached usernames / passwords etc should be used…
You could set that a specific profile should be used for “external” links; if set to “Work”, then all external links (from other apps) would open in that profile; automatic does a pretty good job but sometimes gets flummoxed by M365 sites like Sharepoint, which could mean clicking a link to a doc in Outlook will try to open it in your personal profile, and fail to authenticate (since you’d want it to use the work profile’s credentials).
Similarly, if you had it set up to open links in whichever profile was last used or to hard-default to Work, then clicking a URL that you’d want to view in Personal might cross the streams. Fortunately, an unreleased feature could be just what you need in this scenario.
There’s a still-experimental “flags” feature set that is part of both Google Chrome and Microsoft’s Chromium-based-Edge; different releases of each browser have a changing set of flags features which can be enabled if you know they’re there and are prepared that they might not work, or at least might not work in the same way as they eventually will.
Many flags tweak how the browser operates under the covers and will never be noticeable by the average user; others enable features that are hidden by default or are still early in their development cycle.
One flag which is eminently and quietly useful in this potential multi-profile befuddlement is edge://flags/#edge-move-tabs-to-profile-window. It does pretty much what it says; right-clicking on a browser tab will let you switch it between the profiles, so if you’re presented with a login screen for a site that should be opened in the other profile, one click is all it takes to flick it across.