597 – Pivot table filtering

Posted on September 15, 2021 by ewand

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.

593 – It’s a Date

Posted on August 27, 2021 by ewand

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 8^th 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).

591 – An Excel Smörgåsbord

Posted on August 6, 2021 by ewand

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.

Alternatively, go back to the start of the process, select both Semicolon and space, and choose to combine the two, so the text in the columns is neater.

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?

587 – New Line please

Posted on July 12, 2021 by ewand

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.

Firstly, wrap text where it makes sense – so to stop it spilling into the next column and getting over-written by whatever text is or may be added in there.

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.
Much neater.

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.

If you show it as a multi-line view, it may be easier to click within that box to edit the text, move the cursor around and hit the ALT+ENTER shortcut to force a new line.

548 – A-List listings

Posted on September 23, 2020 by ewand

Updates flow to Microsoft 365 on a regular basis – there’s a published list of all the minor and major changes that are launched and on their way. As well as improving the current user experience and adding new features, occasionally whole new offerings are added – such as Microsoft Lists, which first made an appearance in July.

Lists gives an easy way of creating, sharing and managing lists of custom information within a team – tracking issues, recording assets, anything in fact, that might have used a shared spreadsheet to do it in a low-tech way. Lists was announced to provide a modern-looking, consistent way of managing lists through a variety of front-ends – including mobile apps, to come later this year.

You should be able to see Lists from the menu on Office 365 web apps – start at www.office.com and sign in with a business Office/Microsoft 365 login and the new icon will give you access to Lists – get started here.

Just like sharing forms or doing task management, there are often numerous ways to do the same thing – and in days of yore, that would have meant several competing and incompatible technologies, encouraged to fight it out with each other to try to ensure that the best one wins. Nowadays, with a more collegiate mindset, consistent ways of doing things show up in different user experiences – like To-Do and Outlook, StickyNotes and more. Expect deeper integration across other apps in due course

The new Lists experience is essentially just a great UI built on top of a mature back-end; SharePoint Lists, which have evolved over the last 10+ years, allowing the definition of custom columns and rules to validate data entry.

One new frontier is to integrate the new Lists UI into Teams; if you have ability to administer a Team, you will see an “add a tab” function alongside the Posts / Files etc tabs that are typically presented.

Adding a List tab will then walk you through a process to either choose an existing List (by entering the URL of the SharePoint site that hosts it) or by creating one by importing a spreadsheet, starting from a number of templates or by defining it from scratch

Have a play with Lists and think about how your team could use them in place of spreadsheets.

Microsofties: There’s an internal story about how Lists came about, and looking forward to where it’s likely to go in the future.

Check out Paul Thurrott’s excellent introduction to Lists. And there’s even a Lists Look Book.

542 –Excel Special Paste

Posted on August 7, 2020 by ewand

Anyone who spends an amount of time preparing spreadsheets in Excel, will be familiar with the frustrations that can come from cut, copy & paste. You need to know what the source of a cell is before you know how it will behave if you copy and paste it somewhere else, even to another sheet.

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.

You could decide to paste just the value itself (which means that if the values in D2 and D3 changed, cell D4 would be recalculated but your copy would not), or you could copy the cell, then copy original cell’s formula and paste that into the formula of the destination cell.

There are lots of “Paste Special” options, which will vary depending on what kind of data is in the clipboard. Right-click in a destination cell and the Paste Options menu will surface the commonly used variants, or click the arrow by Paste Special to see all the others. Move the mouse over that pop-up menu and the rest will fade away.

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.

Transposing

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.

NB: this can be a little hit and miss depending on how you’re using cell names or references – you may find that it’s more reliable if the reference is made to a cell on a different worksheet.

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 invoke each command group on menus or the Ribbon – if you can’t remember the shortcut, just press ALT then the key for the menu you want, then the key on the menu that equates to the command you’re looking for.

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.

Tip o’ the Week 485 – Excel and the Web

Posted on June 30, 2019 by ewand

For decades, it’s been possible to import data into spreadsheets from elsewhere. Excel supports many data sources, from basic stuff like CSV, ODBC and OLE DB, to more specific and advanced knowledge of particular data sources and types.

A recent tweet from @msexcel showed a simple video on how to grab data from a website – highlighting a capability that’s been in Excel for years but has been refreshed and made a lot easier to use.
Try this as an example:

In a new Excel workbook, go to the Data tab and choose From Web…
Try a web site that has data tables – eg
https://en.wikipedia.org/wiki/World_population
After confirming the authentication method to connect, Excel will retrieve the page and try to make sense of its layout – and let you choose the table you want to retrieve data from.
This is part of a new “Get & Transform Data” set of functionality that’s available to Office 365 subscribers, that uses Power Query to retrieve data from a variety of sources, and can include some advanced transformation and editing of the query too. See here for a more detailed tutorial.

If you want to revert to using the old data import methods, you can either enable the Legacy import wizards in Options (File > Options > Data > Show legacy data import wizards), or just type Legacy into the Search / Tell Me box in Excel, and see the available actions from there.
You can jump to the Search box quickly by pressing ALT+Q and jump straight to any Excel function, or get help on how to do many tasks.

Another cool Office 365 data feature is to use Data Types. Enter your data in a column, select it and choose the appropriate Data Type – let’s use Stocks as an example.

Once you’ve tagged the source data, clicking on the icon to the left of the data point will show a pop up with the background detail, or you can reference the fields within formulae to display or manipulate the data values.

Each data source presents a number of fields that can be discovered and selected through autocomplete in a formula, and the values can be refreshed easily.

See more detail on using the Stock quotes functionality, here.

Tip o’ the Week 483 – mobile OCR and Office

Posted on June 7, 2019 by ewand

Optical Character Recognition is one of those technologies which has gone from being just-about-possible at great expense and hassle, to so mainstream that people just assume it will work flawlessly, all in a relatively few years. Numerous companies offer OCR services or addins to line-of-business systems which help to prepare printed data for easier consumption – scanning invoices for example.

Consumers tend to use OCR in other ways; combined with language translation, you can point your phone at a foreign menu or sign and it may be able to help you understand. In OneNote, if you have captured an image (maybe through the clipper addin from your browser), then it can extract the text from that picture – not always perfectly, and not necessarily well-formatted, but it’s probably quicker than re-typing everything.

Near OCR functionality is also pervading the slew of freely available Office apps for Android tablets, phones and even Chromebooks, and similar versions for iPad and iPhone.

A recent addition to the iOS version of Excel is the ability to scan a table of printed data and use OCR plus a bit of tweaking, to import the data into the spreadsheet. See more here. The same functionality was first made available on Android a couple of months earlier …

Start with the grid capture icon on the toolbar of a new spreadsheet, and then use the camera to highlight the area of a document that you’re interested in – the UI will be familiar to anyone who uses Office Lens, as the same anti-skewing technology is used to prepare the “document” for importing.

Then the OCR goes to work and tries to lay out the data as closely as possible to its source – obviously, your accuracy will be improved by having a well-lit and clear original document, and you’ll get to tweak the contents in context of seeing the OCR’d data and the scan at the same time, before committing to insert it.

Tip o’ the Week 417 – Resolving aliases in bulk

Posted on February 15, 2018 (Updated August 11, 2021) by ewand

This might be a very old-Microsoft culture thing, but alias names have always

been a relatively big deal within the company; not an alias in the sense of a nom de plume or some alter ego, but a name curiously given to mean your login name.

Before enlightenment, Microsofties were emailed simply by sending to alias@microsoft.com – and still are, so even if the primary mail address is forename.surname@microsoft.com, you could still mail them at forenams@microsoft.com, or whatever their alias is.

The alias, or logon name, at Microsoft was historically an up-to-7- or 8-letter moniker, based by default on surname|first-letter-of-forename, eg billg or steveb.

In a company with a handful of people, it was easy to remember such a name for when you wanted to drop them an email, but with hundreds of thousands of mail addresses, you might need more room – when Exchange Server came out in 1996, it supported 64 characters in the alias name, though oddly, Microsoft has never embraced longer than 8-character aliases.

Back in the day, your mailbox was a folder on a Xenix server, then an MS Mail postoffice, and the folder names were restricted by the 8.3 filename format. There are probably too many legacy systems that also have an employee name represented by their 8-letter alias, and it still kinda works.

Aliases – and the cultural phenomenon of calling people not by their name, but by their alias… eg “we had a billg review” – were a central part of the 1995 Douglas Coupland book, Microserfs.

Some people at Microsoft still talk about an email distribution list as an “alias” – eg. “TAKE ME OFF THIS ALIAS!!” as a Reply-All (as opposed to a little “r”) to the occasional mail storms that amazingly still happen. They’re wrong – those are Distribution Lists (DLs) or maybe more correctly, Distribution Groups (DGs).

But the true “alias” lives on, even if the Skypey “Contact Card” UI in Outlook does its best to not show you what someone’s alias is (but you can usually still get to Open Outlook Properties, which shows you the traditional Outlook address book view, with alias in the very top section). Lots of reports from Microsoft’s internal systems will refer to an employee using their alias name, so it often helps if you can decipher an alias into the person behind it.

Resolving an alias to a name one-at-a-time is all very well, but when looking at a column of alias names in some spreadsheet, it’s a bit of a palaver to turn each of the FORENAMS into something meaningful.

Bulk alias resolver in Excel

Fear not, worthy reader, for a solution is to hand.

Simply download this macro-enabled Excel workbook, and unwip it/open it/save it somewhere locally
Click on the Enable Editing, then the Enable content button, so the macro can do its stuff
Now paste your list of chosen alias names in column A, then click the resolve button
The macro will now go through each alias in the first column, and resolve the name, then stick it in column B, if it can. If the name doesn’t work, then column B will just be the same as the alias in column A

This can be handy if you’re building Excel reports and want to add names to a table instead of aliases – you could sort the list of aliases alphabetically, run them through the resolver, and then reference the table with a VLOOKUP formula so you could hide the column of aliases from your report and show instead the derived real names.

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

Posted on September 19, 2017 by ewand

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

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

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

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

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

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

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

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