As an example, if you took the small table below and wanted to copy and paste the calculated values on row 4, you’d need to deal with the fact that the formula will change – offsetting the D and the 2 reference to wherever you paste it (eg if you pasted the copy into E4, the formula would be =E2-E3) – normally, a powerful and useful function, but a potential nuisance.
An older UI for selecting the options is available if you click on the Paste Special… command at the bottom of the pop-out, or by pressing CTRL+ALT+V to pop out the Special dialog. One of the more particularly useful features of Paste Special in Excel is the Transpose option – if you select and Copy a row of data then Paste / Transpose it, the data is rearranged as a column (and vice versa). Great news in many cases, but if you want to paste cells and keep the original formulae (without resorting to using absolute references formula references using $ in the formula itself, eg setting =$D$2-$D$3), there are no default options to transpose the orientation of the cells but not change the formulae. One trick if you ever find yourself in this position, is to bulk change the formulas so they won’t get modified when you paste the cells; do a Find & Replace to change = to something like #=. After pasting and transposing, reverse the process to restore the formula
It’s an edge case but could save you lots of time if you need to do it. For most of us, getting to grips with shortcut keys in Excel would make things more productive – as well as numerous combos of CTRL-something, there are simple keys (like pressing F4, which repeats the very last command … so if you’ve just coloured a cell yellow, move the cursor to another cell and hit F4 to make that one yellow too… if you’re doing very repetitive things, this can save so much time). There are also more complex sequences; press the ALT key in Excel (and other Office apps, too) to see the key combos that A little bit of legacy/history – press ALT-E then S to jump to the Paste Special menu – why E? Even though it’s long gone, really old versions of Excel had an Edit menu, and the commands on any menu – in any application – that have an underscore under a letter (like Paste Special) are highlighting the key you can press to jump to that command. So ALT E / S used to be the combo to get Paste Special circa Excel 2003, and it still exists today. |
Category: Productivity
541 – shrtn ur url
In most email programs, in Word, and even in the new Yammer experience and some other web forum software, selecting some text and pressing CTRL-K lets you insert a URL under that text – so rather than saying “Flight Simulator – https://www.xbox.com/en-US/games/microsoft-flight-simulator”, you could just write “Flight Simulator”.
When it comes to sharing URLs with other people, though, you might still need the native URL
The first URL Shortening service was launched in 2002, tinyurl.com (and doesn’t the website look like a 2002 site?). The basic idea was that instead of having a 200-character URL, you could generate something that would have the form of the tinyurl domain and a random series of characters, such as https://tinyurl.com/yxtj4gft. When the user clicks the link, their browser goes to the TinyURL website and is then provided the full link to follow, and redirects to that. The primary benefit was to make it easier to share the URL, even if it’s not so memorable, however the developers later added the ability to provide a custom redirect name and, as long as nobody else has nabbed it first, you can use it – eg https://tinyurl.com/yammerofficespace. TinyURL has been overtaken by others, notably bit.ly, which Twitter switched to from having previously used TinyURL, and before later launching its own t.co. There are many others too, some connected with existing services – like the onedrive.com shortener (eg https://1drv.ms/u/s!AgMogCiKiWDFraIfifRzFKdjw4F1uQ?e=Yepjwh) which isn’t really very short, and which causes Bit.ly to get its Alans in a twist, as it seems it doesn’t like to shorten another shortener’s link.
There are some downsides to using this kind of service, potentially. What happens if the provider goes bust, or decides to start charging users where it was once free? Sites like Photobucket which started free but began charging users a “ransom” get internet warriors hot under the collar, but so far, sites like TinyURL and it’s progeny are mostly still free to use, with the operators selling aggregate data about the referrals being followed to fund their operations costs. Some shorteners decide to close down – like goo.gl – meaning there’s a risk that previously-shared short URLs won’t work in future (though in the case of Google’s shortener, they are keeping old links alive, just not allowing any new ones to be created). Similarly, if a shortener has a technical problem or security breach, it could affect the way it works – TinyURL reportedly having problems just this week.
All aka.ms links are by definition publicly accessible, but many are used to get access to sites that are for internal use, even though they exist beyond the firewall – Sharepoint sites, for example, or the intranet homepage, aka.ms/msw. Anyone could resolve the destination URL – even en masse as one enterprise developer has done, using Azure functions – but you still need to provide appropriate credentials to access the destination site. |
540 – All Together Now
Jared also announced Team Room services (more on rooms.microsoft.com), providing a management and monitoring service for physical rooms that are equipped with Teams kit, as well as the performance of the meeting space. The intent is to make the meeting experience better when we have a mix of in-person and remote people, as the world transitions to some kind of normality.
It takes some getting used to, especially if the meeting is one where there’s a predominant speaker – like a teacher – who appears in one of the chairs in the room rather than separate from it. Also, you might have some people who sit relatively close to their camera and will appear huge, while others look like they’d be sitting with feet dangling in the air. To learn more about how to use Together Mode, and for some info on what is planned for the future, see here. |
539 – Outlook calendaring fun
Here are a few simple tricks to remember when working with your calendar:
|
538 – OneDrive updates
Along the way quite a few associated names and services have bitten the dust – Microsofties celebrate/commemorate old products on the Next of Kin Yammer group: raise a glass to OneCare (an unfortunate name choice if you’re a Cockney, ain’t that Irish Stew), and all manner of other products that turned out to be Red Shirt / Non-speaking parts, like MSN Music/Zune Music/Xbox Music/Groove, and now Mixer. If you still have a “SkyDrive Camera Roll” folder in your OneDrive storage, that’s probably a legacy of having synced photos from a Windows Phone and then later having installed OneDrive on your modern mobile. You can rename the folder to something else now – at one point, it was not supported but that’s no longer the case. Using OneDrive on the move makes a lot of sense – even if only to back-up photos from your phone. The web UI lets you see the pictures in a variety of interesting ways, showing the places you’ve been or the things you’ve photographed. In OneDrive for consumers, you get 5GB of free storage on signing up – not bad, but Google Drive gives you 3 The pricing is such that unless you wanted to buy only a few extra GB, it makes sense to go for the M365 option – £60 a year for a personal subscription that gives a 1TB (ie 1000Gb) storage capacity, or pay £24/year per 100GB block if you want to buy storage on its own and forego the other stuff you get with M365, notably the Office apps. Despite a bit of confusion over what the differences are between OneDrive for Business and OneDrive (not described as for business, so presumably for home/personal use), it continues to evolve with additional capabilities – as covered in ToW passim. The OneDrive for Business / Sharepoint and OneDrive for Now, the OneDrive team has unveiled a slew of new features for both ODfB and OneDrive personal – like Dark Mode on the web client, or the ability to share files and folders more easily with colleagues, or share with family and friends by creating groups of people who will be sent an invitation to view and contribute. And the upload file size limit has been raised from 15GB to a whopping 100GB. |
537 – Teams Mute shortcut
Muting a participant displays a notification in their Teams client to the effect that they have been shushed and can unmute themselves, and if they’re dialled in, they’ll be told by an announcer that they’ve been muted and told how to unmute themselves. The person who’s been quietened doesn’t know who did the deed (one feature request is to log who’s doing it, as there have been reports of miscreants randomly muting and booting out participants from virtual classrooms during lockdown). If you’ve already muted a participant (or even all participants), it’s not possible to unmute anyone – only they can control that process, since an organiser unmuting someone could put them in an awkward position (#poorJennifer again?). There are requests to allow finer control of muting in some circs, though – preventing people from unmuting unless given permission, for example – in a large meeting, it could be better to police Q&A by asking people to “raise their hand”, and only allow them to unmute when the presenter virtually gives them the mic. If you’re going to do a load of noisy typing whilst on a call, one trick to memorise is to flick the focus from OneNote/Outlook/whatever to Teams by quickly tapping ALT+TAB, then tap CTRL+SHIFT+M to mute and unmute yourself – this is probably a lot quicker than trying to click the microphone icon within the Teams app. Anyway, enough about muting – time to go make some noise. Have a great weekend! |
536 – Spreading to friends and family
Other chat, video and collaboration tools have clearly been finding many new users during the COVID-19 lockdown… Slack, which established itself as a texty business collaboration tool (especially in the technology industry), has been overtaken somewhat by the rush to video calling and meeting. Slack’s partner AWS, who also have a video/audio/chat service called Chime, announced plans to integrate under the covers. Meanwhile, Slack thinks it’s finally time to ditch email and their CEO also has an interesting take on how remote working will evolve – will this be the end of the real estate bubble in the Bay Area, for example? Salesforce has launched a new offering called Anywhere, which aims to take back collaboration and comms tasks from Slack or Teams. And in the “you can tell any story you like by using the right set of numbers” file, Teams has been reported as outgrowing the media’s darling, Zoom, as the feature battles between the two have intensified. Skype and Google’s Teams will soon have the ability to show up to 49 people at once (having rolled out a 3×3 grid of video windows recently)… … and has also released an updated free offer, aimed at friends and family communications. Initially available in the mobile apps, the focus is on providing free collaborative functionality for groups you can set up, as well as being able to schedule video calls and meetings. If you don’t already have the Teams mobile This will guide you through the process of associating with an existing Microsoft Account, including signing up for free Teams service if you haven’t already. At the moment, the service is in Preview, and it does involve switching between profiles when you need to, but offers a load more than just WhatsApp-style text chat and the odd call. As well as file sharing, there’s even a “Safe” feature on its way, which will let you share WiFi Passwords or other more sensitive information that requires 2-factor authentication. So, for once in the last 3+ months, now’s a good time to spread something to the rest of your family and your wider circle of friends… |
533 – Revisiting TaskMgr
I’m the Microsoft developer that wrote TaskMgr at home in my den in about 1994 and then the NT silverback devs [ie Dave Cutler] let me check it into the main tree even though I was a greenhorn at the time. So that meant I got to bring it into work and polish it up and make it an official part of Windows, where it remains to this day. Dave tells his career story from a talk a couple of years back, but hit the news recently through a Reddit post from which the italic text above originates. He was inspired to apply to Microsoft in 1993 – having read the Hard Drive book (an excellent historical tome, having inspired at least a few great Microsofties to join up), then went on to write various money-saving optimisations for MS-DOS, and ended up in the NT team, leaving the company 10 years later. Dave also recommends another great history book – Showstopper! Back to the current era, Task Manager is still a really useful tool when it comes to figuring out issues with your Windows PC. If you think something is wrong (app starts bogging down, feels like the PC is in a bad way), you can quickly start Task Manager with the shortcut CTRL+SHIFT+ESC (easy to hit with one hand…)
If you can get to Task Manager, you can run a CMD or Powershell prompt, start explorer or msedge etc. Practical Example A colleague pleaded recently that he was having a poor experience with Teams, and queried, did he need to upgrade his 150Mbps internet connection? To check what kind of network performance you’re actually receiving, there are many speed test apps and sites. If you’re using the new Edge (if not, why not? Don’t use IE – it’s too old; stop using old Edge – it’s obsolete; the new Edge is fast and it’s better than Chrome), and you have Bing as your default search engine, all you need do is enter ? speed test into the address bar and you’ll get a speed test gadget to give you an idea of performance.
On the Processes tab, click on the CPU column to sort by what’s using the processor most – its also worth casting an eye on some other resources to make sure they’re not running out of steam; if you see an app consuming a huge amount of memory, it might be leaking, and shutting it down completely could make all the difference. Task Manager lets you kill processes (“End Task”) too, if the app has hung and won’t close cleanly. In fact, Dave said there should be nothing that Task Manager can’t kill (apart from some critical system processes – TM might have been able to kill them, but will also bluescreen the machine … so some protection has been added to prevent the user from doing something that would be instantly fatal to Windows – though TM is able to kill itself). Back to the Teams troubleshooting scenario – If you don’t see the PC getting nailed by some process (that isn’t Teams itself), then it’s worth looking at the Performance tab, and leave it running for a short while, paying particular attention to WiFi/Ethernet.
Finally, your network might perform brilliantly most of the time, but every so often you get a blip that feels like it’s dragging, then it picks up again. This could be spikes in latency, the enemy of anything real-time, like video calls. Try running the Microsoft Research Speed Test app – The Network delay is actually key here – if you had more than 1Mbps upload and 5Mbps download speed, that should be plenty – but if your network delay is commonly more than ~150ms, it’s going to start causing problems. If you have a network connection whose latency fluctuates a lot, there are a bunch of things you could do to seek and troubleshoot:
|
530 – Edge profile switching
Edge has the ability to sync favourites, passwords, credit cards, collections and other browser data all to other machines with the same profile address – so if you have a home PC and a work laptop, then having a “personal” profile on both could mean that suitable info will roam between the machines, but work specific stuff can be kept on your work profile. Some capabilities – like syncing history between machines – are “coming soon”. Lately, some versions of the Edge browser have been updated to sync extensions – like Lastpass, or the OneNote web clipper. Jump to edge://settings/profiles/sync within the browser itself to see the gist. Having multiple profiles lets you consciously separate home and work stuff, keeping social media, web mail or personal interest stuff in one window, and your boring old Sharepoint sites and PowerBI charts in another. Quickly minimizing your “home” window before sharing your desktop on a Teams call is perhaps the modern equivalent of the Boss Key. One tricky part is when you go to open a web link – other than from within a browser session itself – then the last window you were using could be the one to launch that site, meaning you might be crossing the streams and opening up work stuff in your personal profile or t’other way round. It’s possible to set a particular profile to be the default, or just let the machine decide.
If you try to open a site that wants to authenticate Automatic profile switching is available in Edge versions 83 and beyond – open edge://settings/help in the browser to see what version you have. To read more about automatic profile switching, how to enable it and how it works, see here. |
529 – To Do: Switch off Wunderlist
Nearly 5 years ago, Microsoft acquired a German developer called 6wunderkinder, who built a cool, cross-platform task management tool, Wunderlist. Over the half-decade since, the back-end of Wunderlist was basically rebuilt so it could run on Azure (instead of its previous cloud platform), and many of the team who had developed Wunderlist moved to working on the Microsoft To Do app suite. The
The Microsoft To Do service has The To Do team also updated the mobile apps (as announced on their blog), with a collection of new features and views of tasks, and the Windows app has also been tweaked lately too. New features include new Smart Lists, such as “All”, which shows everything in one huge list, grouped by category. “Tasks” across different apps are being integrated more and more – To Do now lets you create tasks from flagged emails, or integrate tasks from Planner. Teams is going to rationalise tasks into a single UI too. |