Pivot Tables are a somewhat esoteric spreadsheet feature which everyday Excel users probably shy away from, but for the enlightened can help to turn potentially large tables of raw data into summarised views that can be easily rearranged.
Excel makes it easy to take a static table with rows of data arranged into columns that correspond to measures or fields – like sales of a product, or assignments of a relationship such as an export from a CRM system – and then represent the inherent hierarchy in a super-powerful fashion.
If you haven’t really used Pivot Tables, the simplest way to get your head around what they can do is to try messing around with some real data, or use some relatively simple sample data to unearth the power within. Free sample data at Contextures is a good place to start – in fact, here’s one sample that has been turned into a Pivot table to get started with (just download it somewhere, open the Zip file and then open the example XLSX file within).
The sample data is sales of foods over a period of time; look at the FoodSales tab to see the raw data. The pivot table quickly lets you summarise by region, or category, or whatever fields are defined.
By showing the PivotTable field list (right-click on the table and you’ll see either show or hide field list at the bottom of the menu), you can easily drag things around to change the grouping order.
It’s easier to just try it out than to try and explain, though there are plenty of tutorials around – from overviews to walkthrough videos or all kinds of more advanced stuff. And if you end up making a mess of the table, there’s also CTRL+Z to undo…
If you’re dealing with lots of fields that would go in the Rows section then you might find it easier to use the Classic layout by right-clicking on the table and choosing Pivot Table options, though you might want to remove the auto-totalling for most of the fields (right-click on a field and choose Subtotal…) otherwise the display gets a bit unwieldy.
When handling very large volumes of data, filtering using the drop-downs to the right side of field names is a great way of showing only the specifics you’re interested in, though sometimes it’s easy to forget there’s a filter in force and think that the data you’re working with is not showing the full picture. To reset all the filters back to the start, just go to the Data tab and Clear the filters from there.
If you want to filter on a specific value – eg. Let’s say you have a list of customer account names and you want to show only the customers associated to a single account manager (regardless of geography, industry etc), you could click the filter on the account manager field and find the person’s name within.
In our foodstuff example, we could have a different layout of fields and want to filter on City – but a quicker way to doing so is to just right-click on the name you want to focus on, and choose Filter > Keep Only Selected Items and it will quickly set the filter to just that name.
Before The Event, you’ll probably recall being presented at in a stuffy airless room, mainlining caffeine to stave off the postprandial doldrums in attentiveness. “On this slide…”, the presenter might have said, before reading out all the text that’s now being shown on a slightly-too-small screen.
Some would apologize for the fact that the chart/table of data/timeline with 6pt text annotations etc, was too small for the audience to read. “I know this is an eye chart, but…”
So hurray when all such in-person meetings were banished to Teams or Zoom if you’re lucky, or if you’ve been a horrible person in a previous life, you may have inflicted upon you Webex, Amazon Chime or whatever Google calls Hangouts these days.
As an attendee, however, the Teams UI can get a bit busy if you want to follow online chat and see other attendees as well as the content being presented. You can make life a bit easier by going full-screen, from the view control in the top left.
As well as tweaking the layout, and hiding/showing components like chat or the participant list, you can zoom the Teams client in and out by using CTRL = and CTRL – (or CTRL + / – on your numeric keypad if you have one), or by holding CTRL and moving the mouse wheel up and down, if you have a suitably-equipped rodent connected. This method, however, just makes the Teams UI get bigger and smaller, so although it might increase the size of the pane being used to present content, it is a marginal gain.
Enter, a greatly useful tip espoused by Belgian usability maestro, Ingmar Boon – click on the content being shown in a meeting, then use CTRL+mousewheel (or if you have a Surface device and the touchpad is enabled then use the pinch in & out gesture on the touchpad). Teams will now let you zoom in & out and pan around the content being shared. C’est manifique!
Emojis can trace their roots back to the first 🙂 from September 1982. Originally knows as emoticons or simply smileys, many of us have adopted these icons like a form of punctuation, especially in social media / Yammer / Teams type comments. This topic was last visited 4 years ago in ToW 391.
Emojis are mostly agreed and defined by the Unicode Consortium, which controls the Universal Coded Character Set, adopted by many systems to maintain compatibility between each other. When a user sends a symbol in a text message, the phone of its recipient needs to know which character was being sent or confusion may occur. Interpreting what the actual emoji symbol means is still down to the end user, and there are many pitfalls to avoid.
Once both sender and recipient of a message or comment agree which emoji to display, the application or platform they’re each using still has to decide what it will look like, and sometimes the iconography – and therefore the subtext – will have changed over time; see the Pistol Emoji (emojipedia.org) as just one example.
Microsoft decided to adopt a “flat” emoji look in the Windows 10 timeframe, but that is starting to change again with the upcoming release of Windows 11 and the evolution of Microsoft 365 – as Art Director and “Emojiologist” Claire Anderson previewed, we’re going 3D and Fluent, due late this year. Oh, one more thing…
ToW reader Paul Robinson draws attention to the shortcut way of inserting emojis in Windows – it’s been a feature for a while now – just press WindowsKey + . and it will allow you to insert emojis into pretty much anywhere that accepts text.
The UI for the emoji panel is changing in Windows 11 too, with GIFs and other types of symbol being included and the whole thing is easier to search. A useful tooltip shows you what the symbol represents, though as said before, be careful with the potential interpretation of some of them. Peachy.
In Windows 10, the same keystroke brings up a simpler yet slightly more confusing UI. Both old and new (under the Symbols grouping) provide a neat way of finding and inserting other special characters; arguably quicker than fishing about in the Office menu, and certainly better than faffing around with typing in ANSI codes.
Paul likes to start Teams channel names with an emoji, and if you want to illustrate one difference between old world and new, try using them in email subject lines and see just how they appear in Outlook versus Outlook Web App…
When you create an appointment in Outlook and decide to turn it into a Teams meeting by clicking the icon on the Meeting tab, a bunch of custom fields are added to the meeting item in your calendar to define how it should be handled by the Outlook application, allowing such functionality as right-clicking on an item in your calendar and joining the meeting from there.
Then there’s the text that gets added to the end of any existing appointment text, which gives dial-in info and provides a link for users who like to click on URLs or who are running a calendaring client which doesn’t support Teams natively. Some degree of customization can be done to this auto-text, but it’s an admin task rather than an end-user one.
LinkedIn’s #1 fan, Brian Galicia, got in touch to draw attention to an option in Outlook which lets you make every meeting a Teams meeting (since the days of meeting people face to face now feel like a distant memory). Fortunately, it only adds the appendages to a calendar appointment when you start to invite people to it, so if you put stuff in your calendar to remind you to do things In Real Life, it won’t get in the way.
The option is accessed from the main Outlook window, under File | Options | Calendar, and is just above the groovy feature which lets you choose to shorten the default meeting time, so as to allow you and the attendees to get out of your chair once or twice in a working day.
From the ToW history files: When you create a thing in your calendar that’s just for you, that’s an Appointment. When you start to invite other people to your thing, then it becomes a Meeting. The Outlook UI changes when you’re dealing with Meetings vs Appointments (e.g., see tracking information on who accepted your meeting invitation, etc).
When the Teams integration to Outlook was first rolled out, the workflow to create a meeting was typically to put the time in your diary, invite your desired attendees, then click the Teams Meeting button to add all the extra stuff that anoints the meeting to become a Teams one.
That was a one-way process, though – if you clicked in error or decided to forego the online element, you either had to hack out the properties and text (since merely removing the “join” links in the text didn’t get rid of the Join Meeting UI in Outlook, as that was lit up by the contents of the various custom fields in the item) or, more likely, ditch the meeting and create a new one.
Happily, Outlook now lets you do the removal from within its UI. You’ll find that under Settings on the Meeting tab, where you can also control some other functions, like whether external attendees need to be held in the lobby or whether you let them straight in.
The bypass feature is meeting-specific, so if you are scheduling 1:1s with customers or partners, you might want to let the striaght through, but if hosting a larger meeting then having a lobby could let you get your internal team straight before bringing in your guests.
Following last week’s missive on Notepad, including the obscure tip on how to create a log file, the topic of inserting and handling dates in other applications is worth a (re-)visit. Each individual app may choose to offer different methods and formats, but for common Office applications there are a handful of memorable tricks and shortcuts.
In Word, there are plenty of ways to insert and manage dates – perhaps the most useful way to remind the reader when the document was last updated (manually showing when a document was last reviewed or published). On the Insert tab, you’ll find Date & Time on the right-hand side, letting you add appropriate info in the format of your choice. You can also tick a box to update the field automatically, though that simply means every time the document is opened, it will show today’s date… which feels a bit pointless.
More useful could be to tell the reader when the document was created or last saved, by referencing the actual properties of the document (though be careful; auto-save might mean someone opened an old document, realised it was irrelevant, but had inadvertently saved it back).
On the Insert tab / Quick Parts, look under Field, then pick the doc property and format you’d like to show.
It is worth pointing out that showing a date as 10/1/21 (or similar) is ambiguous given that a few hundred million people will expect it be month-day-year while many of the remaining 7 billion will assume the day comes first, with a couple of billion presuming the format should normally start with the year, such as yyyy-mm-dd (which is arguably the most sensible of all; and it sorts properly, too).
A more daily usable short format like dd-mmm-yy (ie 13-Aug-21) should perhaps be the norm, especially when the date is appearing as text in a document. Pressing SHIFT+ALT+D in Word will insert the current Date as a field (so you can edit the format to remove ambiguity) and SHIFT+ALT+T inserts the current time too. In PowerPoint, both of these combos bring up the “Date & Time” dialogue to add the chosen content and format as plain text.
When formatting dates, incidentally, the convention is that two letters refer to the short number (eg dd = 13), whereas 3 d’s or m’s will use the short form of spelling the day or month, with 4 meaning the whole thing (ie Friday, August). Try formatting a cell in Excel as Custom, and you can preview what the format would be, by typing in a variety of letters.
While in Excel, it’s worth learning the short cut key to insert the date and time – CTRL+; and SHIFT+CTRL+; respectively (no doubt there’s a reason why Excel has a different shortcut to other Office apps – some legacy of Lotus 1-2-3 perhaps?).
OneNote fans will want to remember that SHIFT+ALT+D / T combo as it inserts the date/time into the notebook; really handy when taking notes of a phone call or similar. SHIFT+ALT+F puts both day and time, something that Word doesn’t offer. In both Desktop OneNote and users of the Windows Store version, it’s just plain text that gets added, so you’re on your own when it comes to formatting.
OneNote pages will typically have a date & time showing under their title – on the Desktop version, it’s possible to change that so as to mark a page as having been recently updated. No such luck on the lame duck Store version.
At least when stalwarts insist on writing – or worse, saying – a short-form date as something like “ten one”, there’s more than half of each month where one number in the date could only mean “day” – starting with the thirteenth (as in, 8/13 can never by the 8th of a month, but 8/12 could be a few days before Christmas to Europeans, or the date when tweedy Americans start looking for grouse in the Yorkshire moors and Scottish Highlands).
The simple text editor Notepad has been around since the dawn of Windows – it’s one of the few apps that was in the box with Windows 1.0 and is still there 36 years later, in Windows 10 and 11. Many people will encounter Notepad because they open a txt or log file, but some still fire up Notepad to quickly scratch something down, like a number being read out to you over the phone, when they say “do you have a pen and paper handy?”. Normally, It should take you under to two seconds to get Notepad running from anywhere – Press WindowsKey+R notepad ENTER.
Another handy use of Notepad is to quickly strip text of formatting; you might find that copying and pasting text from multiple documents often drags unwanted font choice, size, colours etc. In many apps you have the option of pasting something as Text Only, but if not, then putting the decorated text into Notepad first, then selecting and copying it again from there will mean it pastes quickly and cleanly into the destination document. Sometimes, it’s actually quicker to use Notepad as a middleman too (especially if you favour the CTRL-C / CTRL-V method of clipboard interaction).
Some people – for whatever self-flagellatory reasons – actually use Notepad for taking notes during meetings or calls, and then maybe format their raw text into something more structured afterwards. ZDNet’s Microsoft commentator Mary Jo Foley is devout Notepad user. The fact that it’s simple and quick appeals to many, it seems.
Notepad was turned into a Store app in mid 2019 and has gained a few tweaks to functionality, though nothing that normal people might notice. It’s getting a new icon in Windows 11, and who knows what other advanced functionality might follow.
Despite its relative simplicity, there are some obscure features – like the ability to add content to the header and/or footer of a page that’s being printed, even if there’s nowhere to save that setting (since a TXT file is just that, until you start getting into the intricacies of different text file formats and what that might mean to applications which may consume the text file you’re editing).
Following last week’s F4 tip for Office apps like Excel, ToW reader Flaviu Comanescu-Balla goes one better in highlighting that pressing F5 in Notepad will insert the current date and time, so if you are keeping phone notes or something, you can quickly annotate them.
In fact, Flaviu also spotted an even more obscure feature, where if you put .LOG as the first line in a Text file saved from Notepad, every time you open that file, the current date and time is appended at the end, so you can jot something down, save it again and keep a log of activities.
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.