Microsoft Windows 8.1 Preview

Windows 8 Background

Windows 8 was released in August 2012 as a radical redesign of the previous Microsoft Windows desktop. It featured an attempt to design an operating system which would be suitable both for the traditional keyboard and mouse set-up and for the more contemporary touch screen (or tablet) devices, such as Microsoft Surface.

The applications created specifically for Windows 8 were implemented using a visual design language called Metro which has subsequently been renamed as Modern. Metro/Modern emphasises the use of very clean, clear typography.

The original design proved to be rather too radical an update for many long-time Windows users, some of whom struggled to perform basic tasks such as starting up an application or finding a recently used file. Windows 8.1 is the update that aims to make the transition from Windows 7 less drastic.

A Taste of Windows 8

My first experience of Windows 8 was typical. I went to visit a customer to deliver an Excel training course. Their IT guy took me under his wing and showed me his beautiful, brand new desktop computers kitted out with touch screens and loaded up with the latest version of Windows.

Hardly able to contain my excitement, I said "Let's start up Excel". A simple task you would think and after a good 10 minutes we had managed to get one machine running Excel. After 20 minutes we had all six going. Of course, this usually takes about 2 minutes.

At the end of the day, my IT guy reappeared and we decided the shut down all of the computers. This proved a little harder than we expected. In fact, we failed. So, being practical guys, we pulled out the power leads. Our excuse was that neither of us had encountered Windows 8 before. I was fascinated to see what Microsoft had made of Windows 8 since.

This review is based on the preview version; there may be significant changes before Windows 8.1 is released later in the year. I mainly used Windows 8.1 with a standard keyboard and mouse laptop but spent a few happy hours with a Lenovo ThinkPad to check out how it behaved with a touch screen.

Start Screen

Once you've signed in you come to the Start screen. This is the famous "dashboard for your world" and you can click any of the tiles to launch a Modern App. If you just want to get some work done then click the Desktop tile (at the lower left hand side) and use the Start button. If you're used to using Windows 8 the small swipe used to select a tile has changed. That now swipes you down to the Apps screen instead. To select a tile, press and hold on it.

Start Screen, the Desktop tile is lower left

The Start screen can be viewed as a replacement for the old Start menu. You can easily create a shortcut for a desktop application and it will launch on the desktop.

Any of the existing tiles can be removed or replaced and you can "pin" any tiles on your Start screen. But if you want to launch an application without creating a shortcut then you go to your Apps screen. On Windows 8 you had to swipe across to see your Apps but now you swipe down.

Display Apps

To go to your Apps screen using your mouse click the downward pointing arrow which is available at the lower left hand side of the screen below the tiles. It becomes visible when you hover your mouse in that area of the screen.

The Apps Screen

The view of the Apps screen shows the Modern Apps listed first and you either swipe or scroll across to see your Office Apps.

You can change this order to show the Office Apps first but whilst a swipe on a small screen is a minor matter it is a painful process with a mouse on a large monitor.

Firstly, you have to point down to the bottom of the screen, find a scroll bar which only becomes active when you hover over with your mouse and then scroll.

Scroll across for Office Apps

Windows 8.1 has a pretty face; it is graphically very attractive but the gloss is starting to come off the product, this is the first of several instances where you feel that this interface was not designed for traditional mouse users.

Of course, what you should do is change the sort order of your Apps. By default they are listed "by name", you just change the order to "by most used" and then you will probably not need to create any shortcuts.

Arranging Windows on the Desktop

One of the major criticisms of Windows 8 was that it was difficult to arrange the different application windows and that you could only have two apps visible at the same time. Relax, this only ever applied to Modern apps on the Start screen, not for apps on the desktop.

Many people need to copy and paste from one window to another and this was never a problem with Windows 8 nor is it an issue with Windows 8.1. Standard apps run on the desktop and you can arrange them as you always have without difficulty. After all, it is called "Windows".

The familiar Desktop

Start Button

The Windows 8.1 desktop is as familiar and as easy to use as any earlier version of Windows.

The original Windows 8 dispensed with the Start button which confused many people as they found that while it was easy to start one application it was not so obvious how to do another. The Start button has now been restored. The original Start menu has not, click the Start button and you go to the Apps screen.

Standard application windows on the Desktop

In Windows 8 it was possible to have two Modern apps on screen at once but one of them had to occupy a thin strip at the side.

This has been changed and you can now have two apps with half of the screen each and you can drag the divider line between them to give one app more space if required.

Depending on the resolution and size of your screen, you may be able to have up to four modern apps next to each other.

Search

Search is much improved. The Windows 8 the system-wide search defaulted to searching for Apps only and threw you out onto the Start screen. You had to click on other categories such as Files or Settings if you were not actually searching for an app.

Search-just start typing!

Search now searches everywhere, including the internet. The Search pane is displayed on top of what you're currently doing.

Clicking any of the returned results will launch the relevant Modern UI app, desktop app or web browser.

But far and away the best bit about Search in Windows 8.1 is the bit you can't see. Search is so easy it hurts. You don't actually have to use any particular control in the interface to search.

You just type something in! Yes, look at the screen, type your search text, the Search pane opens and your search results will instantly be displayed. Brilliant!

We discovered this quite by chance when we were having a Windows 8 moment. Where was File Manager and Control Panel? We typed in "Control Panel" and soon found out. However, it must be said that at the time we had yet to discover our Charms.

Charms

Point your mouse to the relevant hot spots on the screen, that's the top or bottom right hand corners and your "Charms bar" will appear. But don't admire your Charms for too long because they will soon disappear unless you point your mouse towards them to make them active.

The Charms bar-over to the right

The Charms bar includes Search, Share-options for sharing what's currently on your screen, like a web page that you want to email, a Start Button, Devices-devices currently connected such as a printer or a phone and Settings for the majority of options previously available in Control Panel.

The expanded Change PC Settings in Settings gives the complex mass of Control Panel options a nice, clean, straightforward user interface that Windows has needed for years.

Another hidden gem in Settings is the Power icon. One of the most common user complaints about Windows 8 was that while it was fairly obvious how to get to the Start screen and reasonably obvious how to start an application hardly anybody could work out how to turn their system off.

That's how you shut down!

"How do you turn it off?" was the refrain. There are several ways of shutting down, none of them obvious but the easiest, if you know about your Charms, is to go to Settings and click the Power icon then choose Shut Down from the pop-up menu.

It's quite a good idea to learn your Windows shortcut keys if you are going to use Windows 8.1 without a touch screen.

You need your Windows key to switch between the desktop, where you do most of your work, and the Start screen and Apps screen. For example, to get to your File Explorer it's Windows key and you should see "File Explorer" listed on the Apps screen.

The other essentials are:

Charms bar is Windows key + C,
Settings is Windows key + I,
Lock Screen is Windows key + L.

See a comprehensive list of Windows 8 shortcut keys.

Lock Screen

If you sign out or take a coffee break you will return to your Lock screen and have to sign in again with your password. Pretty standard stuff and just what you want. 

The Lock Screen

The Lock screen can be set to turn into a photo gallery, picking photos automatically. So, it's maybe not a good idea to have inappropriate photos tucked away in a folder somewhere.

For tablet computers, you can unlock the camera or answer a Skype call without needing to fiddle with a password.

And it's goodbye to the good old fashioned three finger salute, CTRL+ALT+DEL. It's still supported but no longer needed. To go to the Lock screen deliberately it's Windows key + L.

Should you press CTRL+ALT+DEL out of habit then you need an extra click to choose Lock.

To get past the Lock screen, you swipe up, click your mouse or press a key on the keyboard. Our cat, Elvis showed us how to do this. Every time he sees a shiny screen or a glass window he swipes it with his front paws.

Customisation

If you are using Windows 8.1 on a non-touch system and predominantly use the desktop then a few changes will make a world of difference. There is a new tab, "Navigation", available when you right-click the desktop Taskbar and choose Properties. Here are the options which resolve most of the worse complaints which bedevilled Windows 8.

Three simple changes should make your day:

1. Start up straight to the desktop and bypass the Start screen.
2. Show desktop apps before Modern UI apps on the Apps list when sorted by category.
3. Force the Start screen to show on your main display if you have more than one monitor.

The Case For

1. Windows 8.1 is not the great leap as Windows 7 to Windows 8 was.
2. Using Windows 8.1 on a traditional desktop PC only requires a few hours familiarisation, a few tweaks and a bit of patience. And away you go.
3. Windows 8.1 is a fast, stable OS and that is for the pre-release version.
4. It's pretty, you do have to look at it all day long.

The Case Against

1. Windows 8.1 is still an operating system of two halves. If it is to take over the mantle of Windows 7 then it needs to cater to the target market, which is people using Windows at work on a laptop or PC without a touchscreen and using the desktop for the majority of the time. This is likely to be the case for the foreseeable future
2. These people are working and need to be productive, they probably do not want to jump around between a desktop and a glorified smart phone interface nor do they want to fiddle with settings.
3. All too often Windows 8.1 breaks the cardinal rule of good user interface design, Don't make me think. Until you are familiar with the new interface some simple tasks will seem to be overly complicated.

The Verdict

Windows 8.1 is good for touch devices but not so much for the traditional mouse, screen and keyboard set-up. It can not be described as unusable as some commentators claim, you can work around its foibles easily.

In the software training business we have seen the take-up of Windows User courses go down every year as nobody needs to be trained in the art of the obvious; new Windows 8.1 users may need a little familiarisation.

The comparison with other, more mature, products is inevitable. Apple iPhone and iPad are universally popular touch devices and with good reason; both work superbly well running iOS. Apple iMac does very well on OSX and you do not put down your iPad, turn to your Mac and starting prodding and pinching the screen. You adapt easily to the different standards.

Touch is useless for a large monitor; large touch screens are physically difficult to use as you have to sit closer and you end up with squinty eyes and tired arms. And God help nature's keyboard pounders using an on-screen keyboard; they'll end up whacking themselves in the nose. Attempting to blend together a tablet user interface with a desktop interface seems to be an unnecessary contrivance. Surely, touch for the big screen is merely a change of input device, maybe Magic Mouse or Trackpad!

So, is Windows 8.1 a triumph or a turkey? It's neither; it's not a turkey as it is quite usable once you get used to it and a significant improvement on the original Windows 8. Nor is it a triumph, more of a work in progress. A bold attempt to integrate touch with the traditional user interface that's maybe not quite as slick as you would like it to be.

List of Windows 8 shortcut keys.

Windows 8 Shortcut Keys

You'll definitely be needing your Windows key:

Shortcut Key	Action
Windows + D	Show Desktop
Windows + C	Open Charms Menu
Windows + F	Charms Menu - Search
Windows + H	Charms Menu - Share
Windows + K	Charms Menu - Devices
Windows + I	Charms Menu - Settings
Windows + Q	Search for installed Apps
Windows + W	Search Settings
Windows + Tab	Cycle through Modern Apps
Windows + Shift + Tab	Cycle through in reverse
Windows + .	Snaps an App to the right
Windows + Shift + .	Snaps an App to the left
Windows + ,	Temporarily View Desktop
Alt + F4	Quit Modern Apps
Ctrl + F4	Quit normal Apps
Windows + E	Launch Windows Explorer Window
Windows + L	Lock PC, show Lock screen
Windows + T	Cycle through icons task bar on task bar
Windows + X	Show Advanced Settings Menu
Windows + Page Down	Move Start Screen to right monitor
Windows + Page Up	Move Start Screen to left monitor
Windows + M	Minimise All Windows
Windows + Shift + M	Restore All Windows
Windows + R	Open the Run dialog
Windows + Up Arrow	Maximise the current Window
Windows + Down Arrow	Minimise the current Window
Windows + Left Arrow	Maximise current Window to left
Windows + Right Arrow	Maximise current Window to right
Ctrl + Shift + Escape	Open the Task Manager
Windows + Print Screen	Print Screen and saves to Pictures folder
Windows + Pause Break	Display System Properties
Shift + Delete	Delete a file permanently
Windows + F1	Open Windows Help
Windows + V	Cycle through notifications
Windows + Shift + V	Cycle in reverse
Windows + 0 to 9	Start App pinned at that position
Windows + Shift + 0 to 9	New instance of pinned App
Alt + Enter	Display Properties of current selection
Alt + Up Arrow	View parent folder
Alt + Right Arrow	View Next folder
Alt + Left Arrow	View Previous folder
Windows + P	Choose secondary display modes
Windows + U	Open Ease of Access Center
Alt + Print Screen	Print Screen of Window with the focus
Windows + Spacebar	Switch input language
Windows + Shift + Spacebar	Switch back to previous language
Windows + Enter	Open Narrator
Windows + +	Zoom In using the Magnifier
Windows + -	Zoom Out using the Magnifier
Windows + Escape	Exit the Magnifier

Download Windows 8 Shortcut Keys as pdf

Star Trek Into Darkness

This starts with a groan-making scene of a couple of space guys running through a planet full of red weeds chased by hordes of teddy bear men but don't let that put you off, the rest of "Star Trek Into Darkness" is pretty good stuff. An exciting and dynamic action movie which can be enjoyed by normal people and not just the trekkie fan boys.

The USS Enterprise

A few too many scintillating action sequences though; there's a limit to how exciting watching your heroes running-really-fast can be and all the explosions, explosions already.

This is a common problem with special effects action movies; you can't hear the actors deliver their lines through all the racket and take away the special effects, what are you left with? Is there any story?

Fortunately in "Star Trek Into Darkness" there's plenty going on; Captain Kirk and Spock continue their bromance, Spock and Uhura show a side to their relationship that we havn't seen before, Leonard Nimoy makes another nostalgic guest appearance and the arch villain Khan (Booooo!!!) returns for another punt at inter-galactic world domination. It's a continuation of one of the greatest space operas with all of our favourite characters.

Benedict Cumberbatch plays the baddie

The new face is Benedict Cumberbatch playing a renegade Star Fleet commander. But is he really who he seems? Maybe there's more to it than that? This plot twists and turns like a Cetian eel.

Benedict does the tradition of dastardly, sneering Hollywood British villains proud. Alan Rickman would be delighted to see his performance. This is a sinister, brooding baddie with a serious attitude problem. Why is he so angry? 

Don't call me Ishmael, I am not going to spoil the story for you. This is one to go and see. Klingons on the starboard bow...

And for Albert King fans, watch out for the background music in the bar when Captain Kirk is drowning his sorrows after being sacked? That's right, "Everybody Wants to Go to Heaven (but nobody wants to die)" off Albert's 1971 album "Lovejoy".

Making Sense of Star Trek

I usually enjoy Star Trek movies but there always comes a time when I feel completely baffled. Like I have fallen asleep for a bit and missed something. It's always the same; a wave of excitement runs through the audience, Spock does something vaguely odd and all the trekkie fan boys beam and cast knowing glances at each other. Once, on holiday in San Francisco, I was shocked as the audience actually stood up and cheered. What's going on?

How many Spocks are there? Why is he phoning himself? What is Khan up to, why is he drifting through eternity with a crew of frozen peas? Trying to get some sense out of the trekkies is like communing with mad men; "Right, so there's been a split in the timeline and Spock is Jesus?", "No, no, no", "You're saying that Spock's mother is a whale?". "No, you idiot! Listen to me!"

Apparently you can't understand the new Star Trek stories unless you watch the old ones. I decided that it was time to get the low down on Star Trek and discover all I needed to know about Khan and the Genesis Device. I sat through the 1980's pot boiler, "The Wrath of Khan".

Wrath of Khan

I was caught in a trekkie trap. Yes, you will learn all you ever wanted to know about the voyage of the USS Reliant and the SS Botany Bay but you can't watch this unless you are already a hard-core Star Trek fan. It may very well give you a deep and meaningful insight to the storyline of "Into Darkness" but you will do yourself a serious mischief laughing at it or suffer a nasty attack of déjà vu.

Kajagoo-who, the crew of USS Reliant

The renegade crew of the USS Reliant. Any starship crew that looks so much like the 1980's pop band Kajagoogoo can't be taken seriously and as for having a space ship called Reliant, that is a real no-no.

No one can object to Kajagoogoo but I'd rather not see them in deep space. The lead singer, Limahl was such a mild and gentle soul that the very idea that he can be some kind of intergalactic   super-villain is ludicrous. Imagine being threatened by Limahl: "I'll blast you with my photon torpedoes!". "No, Limahl, don't. I've got a headache", "Oh, alright then".

Reliant, like Enterprise, is a fine nautical name for a space ship but to anyone who lives in the United Kingdom the Reliant can only ever be the Robin Reliant, a quirky three-wheeled motor car with a glass fibre body. The Reliant is very cheap to run and insure and has special place in British popular culture as the butt of all motor car jokes. Not very cool for a space ship.

Claudio Caniggia?

The helmsman from the Reliant is a dead ringer for Claudio Caniggia, the Argentina International who did so well in the 1990 Italia World Cup. This one comes to a sticky end and is zapped by the boys from the Enterprise but I am glad to say that the real Claudio is still going strong.

For me, the biggest laugh was saved for the costumes of the USS Enterprise crew. They look so outstandingly silly, I know that this was a low-budget production but our heroes are here decked out in a sort of 19th century comic opera, ruritanian military uniform.

In the original TV series the crew wore a practical and workmanlike rig of boots, joggers and t-shirts which looked entirely believable. I know it's science fiction but to enjoy it you have to attempt to believe in it and to see the ship's company looking like they have enlisted in the Red Lancers, one of Napoleon's elite cavalry regiments, spoiled it for me. This is pure Flash Gordon.

Capt. Kirk joins the Red Lancers

Far from exploring strange new worlds and seeking out new life and civilisations the Red Lancers boldly went and invaded Russia in 1812 with the rest of the Grand Army. Riding horses.

Thankfully, in the "Into Darkness" production the Star Fleet uniforms are very tasteful and spiffy in a late 1990's Emporio Armani style. I've never seen a real Star Fleet officer in my life but I thought they looked the part.

Maybe I just wasn't entering into the spirit of the thing. And the next time Spock does something I don't understand, I'll let him get on with it. After all, he is Vulcan.

Alfie Boe and Les Miserables

I was in big trouble with The Boss over my ignorance of the phenomenon known as Alfie Boe and as for not being a Les Mis fan, it was the seventh circle under the pit for me. I knew that some serious crawling was needed. No problem, I've been crawling all my life.

How's about this for the Les Mis fan? The Les Misérables book! From Stage to Screen is packed with interviews and photographs of the cast and crew of the film, set designs and photographs, memorabilia posters, pictures of the original stage and costume designs and loads of other fascinating trivia. A chocolate box of fun for the serious Les Mis fan.

And all available for a knock-down price at CostCo. I carefully peeled off the price sticker (the old trick) and pressed it into his hot little hands. Job done, all was well with the world.

But my troubles were not yet over. I had been welcomed to the Boe fold but gently chided by some of our correspondents. Fancy not knowing about Alfie Boe and Les Mis, the poor boy needs to be enlightened.

One of them had very kindly suggested that a suitable course of treatment would be for me to get a copy of the DVD of the 25th Anniversary Concert of Les Miserables performed at the O2 Arena, purchase a nice bottle of wine, invite some friends over, crank up the volume and discover what I've been missing out on.

Now, I know that it was meant kindly but where I come from we call this being "stitched-up". Stitched-up like a kipper. Thanks.

Les Miserables, the treatment 

It must be done, there's no getting away from it. I was ready to be treated, processed and re-educated and here's all the good news; Les Mis on the disk and for drinks we are having a recent discovery of mine, Aperol Spritz.

This is an irresistible mixture of Aperol aperitivo, Prosecco and soda. Such a wonderful colour. The concoction is completed with ice and a slice of orange.

As an incentive to my finishing the treatment I was threatened with something far, far more dreadful than a few hours of Les Mis. Should I fail to pay attention or display insufficient enthusiasm then there was a very special treat in store for me. Yes, an evening with Michael Ball's Christmas songs instead.

By now you may well be thinking, "what's the matter with that?" Michael Ball is an most agreeable troubadour and is much admired. All I can say is Les Mis is utterly fantastic; the very, very best ever and I thoroughly enjoyed every wonderful minute of it. How long to I have to gush for until you put that Michael Ball CD away and stop threatening me with it? I've nothing against Michael Ball as such, after all he's married to Cathy McGowan of Ready Steady Go! fame. It's just that I don't like Christmas songs out of season.

Seriously though, I did enjoy the show, the Matt Lucas performance of Thénardier was particularly good. I'd like to thank you all for bringing a little bit of pleasure to my miserable existence.

But there is a bit of an issue that we need some advice on. As you know, there is an appearance by the "Valjean Quartet" at the end; Alfie Boe, Colm Wilkinson, John Owen-Jones and Simon Bowman. We were wondering how the hard core Alfie fans rated him alongside the other tenors.

In our opinion, Simon Bowman got the thumbs down. Not that he was bad but that the others were so good. Alfie, we thought, was very good but his delivery is so powerful that it could be overwhelming. He is a bit over the top. The Boss rates the mellow tones of Colm Wilkinson and, as it well known that Welshmen are the best singers in the world, my vote went for John Owen-Jones. My family are Welsh but, as it is well known that Welshmen are very fair-minded, I can assure you that this has not influenced my judgement.

Anyway, I can now present the case for the defence and offer some pretty solid documentary evidence that I am watching and thoroughly enjoying "Les Misérables". Just in case you can't tell who's who, Alfie is the good looking guy on the telly and I'm the lazy slob with his feet up wearing the Wabis.

To finish, here's an incredibly dull Les Mis story. Some years ago I used to work in Manette Street, which is just up the road from the Palace Theatre in London's West End.

Occasionally, I liked to go for a quiet lunchtime drink at the downstairs bar in the theatre as all the local pubs were packed out with noisy students from Central St Martin's. One day I was chatting with the barman and he said that they'd be closed next week for redecoration as there was a new musical opening. "Oh, what is it?" says I. "Some French thing about Victor Hugo" says he. We both shook our heads and agreed, "Can't see that running for very long...."

Excel Calculations without Formulas

If you use Excel on a regular basis then you probably know all about formulas and functions but it's too easy to get into a mental rut and neglect some of Excel's simpler operations. Here's a typical example, my worksheet contains the Sales Forecast figures for the next few months and I've just been told that I now have to increase all the figures by 4%. 

So what do you do? Copy and Paste all the numbers to another worksheet, write a formula to multiply everything by 1.04 then Copy and Paste Special as Values to fix the numbers and finally, copy all the new numbers back to the original worksheet. Well, you could but....

Paste Special Operations

Instead of removing the numbers to another worksheet and doing formulas you can manipulate the cell values in place. Type the multiplier value of 1.04 into an empty cell (any cell will do) and then copy it. Now that you have your value on the Clipboard you can apply it to the numbers.

Select your numbers and then choose Paste Special and in the Operation section click the Multiply option button. Click the OK button and all your numbers are uplifted by 4%. You don't need the 1.04 multiplier in the cell any more and you can delete it whenever convenient.

Halving numbers, doubling numbers, converting negatives to positives (multiply by minus 1), adding one set of numbers to another or subtracting. These tasks can all be effected without writing a single formula. The Paste Special command is usually available in the right-click shortcut menu or the Edit menu or the Paste control on the Home tab for newer Excel versions.

Finding the Numbers in a worksheet

When you have numbers in a worksheet that you need to select and you don't want to have to do the selection yourself try using Go To Special.

For example, in the previous example I wanted to select the numbers on a worksheet so that I could multiply them. I did not want to select the cells containing formulas, just the cells with normal numbers or, in Excel speak, the numeric constants.

Choose Go To Special and then click the controls to specify what class of worksheet data you want to have selected. In this case, the Constants option button and the Numbers check box. Click the OK button and Excel selects those values wherever they are on the active worksheet.

Should you select a range before choosing Go To Special then the cell selection is confined to the currently selected range.

So, where is Go To Special? It depends on the version of Excel that you are using, if you have the older versions with the drop down menus then you should choose Edit, GoTo and then click the Special command button. In newer Excel versions with the ribbon, go to the Find & Select control on the extreme right hand side of the Home tab, click the control and it's in the drop down menu. 

Related Posts

Excel-Calculating age from date of birth
Excel-Sorting by last name
Excel-Switching columns to rows
Excel-AutoSum Revisited

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.

Excel Sorting by Last Name

You have a list of names in your Excel spreadsheet with both the first and the last names in the same cell and you want to sort your list alphabetically by the last name. Not too much to ask, is it?

The bad news is Excel sorts on the entire entry in the cell reading from the left hand side and you can not specify any particular element of your text to provide the sort order. That's what you want but you just can't have it. Sorry.

The only way to do this is to isolate the last names into a separate column and then base your sort on that column and not on the existing names. Make sure that the sorting column is right next to one of the existing columns in your worksheet so that Excel captures it as part of your list. You can always hide your sorting column. 

There are several methods of isolating the last names. In this article we shall be discussing Flash Fill, Text to Columns, Find and Replace and Formulas. To make your life easy, try to make your text as regular as possible as most of these processes are about finding a space in the middle of a bit of text. Any double-barrelled names like Ann Marie, Jean Paul or Wynn Jones will be much easier to process if you substitute the space with a dash; Ann-Marie, Jean-Paul or Wynn-Jones.

Using Flash Fill

What could be easier, type in a few suggestions (I did Doe and Doetta) and then shoot over to the Data tab and click the Flash Fill control. To be fussy, I could say that I really wanted to have Wynn-Jones instead of Wynn so I should have included one as an example but it's only for sorting so I'm happy.

What's your problem? You're looking at your Data tab and you don't have a Flash Fill control? That's because it's new with Excel 2013.

That's the problem with this method; you need the software. You either have to buy a new copy of Excel or check out the rental version on Office 365.

Flash Fill is far and away the best method for this exercise and I would throughly recommend it as the program is so good at picking out patterns from your examples. The only drawback is that you would have to repeat the exercise whenever you added new names to the list. It's worth buying a new copy of Excel just for Flash Fill.

Using Text to Columns

Text to Columns is where you can use the space between the first and last names as a "delimiter" and have Excel generate two columns of data; one with the first names and the other with the last names. You delete the first names column and keep the last names as your sort column. There will be issues with titles, middle names and initials etc.

Start by inserting a few blank columns into your worksheet and then copy and paste your names column so that you have two sets of names. Click the column letter at the top of the copied column and look for the Text to Columns command, it's either on the Data tab or on the Data menu if you have an older version of Excel.

This is Step 1 of the Text to Columns Wizard and here you just need to specify that you are processing Delimited data and then click the Next button to move on to Step 2.

Step 2 is where you specify the Delimiter; clear the Tab check box and click the check box for Space then examine the Data preview. As you can see, it's not perfect as every space character has been used as a separator but it has done the bulk of the work so click the Finish button.

Here's the resulting text, it's been chopped-up (or parsed) into separate fragments based on wherever a space character was found in the original text. There's still a bit of work to do as you need to delete the unwanted columns. It's always a good idea to insert a few extra blank columns into your worksheet before using Text to Columns as this will avoid your accidentally over-writing any existing data. 

Using Find and Replace

Again, this is all about using the spaces as separators and employs two passes of Find and Replace in combination with wildcards. This is definitely one for all the Find and Replace fans, I am constantly amazed at how creative and ingenious some people can be with Find and Replace.

Click the column letter at the top of one of your columns where you have the names and then replace every space with an arbitrary character, in this case an @ sign. You can use any character you like, just make sure that it's a character that would not be found in any of the names. Type a space into the Find what box and an @ sign into the Replace with box then click Replace All. Now all the names look something like this: Bill@Bloggs, John@Smith etc.

The next job is to strip out all the text up and including the last @ sign by replacing it with nothing which will then leave the last text element which is, of course, the last name. Type the following expression into the Find what box, ?*@ and leave the Replace with box empty. Click the Replace All button and you are left with the last names.

The wildcard characters used here are the question mark, ? which means "Find any type of character" and the asterisk,* which means "Find everything". Therefore ?*@ means "Find everything leading up to and including the last @ sign".

Using Formulas

And then there are the Excel fans who would not dream of using Find and Replace because, for them, everything is done with formulas and functions for they can not be parted from their commas and brackets. The formula solution uses Text functions and is quite complex but it is constructed in stages; use the FIND or SEARCH function to read the text from the left hand side to find the position of the first space character. Then use the RIGHT function to extract the text after the space.

This is the first step, locating the first space in the first cell:

=FIND(" ",B2)

This formula gives the result of 5, the first space is located at character index 5, after the first four characters, "John". The FIND function is case-sensitive which does not matter if you are finding a space but it could be an issue for some other characters, in which case you should use the SEARCH function which is not case-sensitive.

The next job is to extract the text from the right hand side up to, but not including, the space character which has now been located. Of course, the names are of varying length so you need to calculate how many characters in from the right hand side for which you need the LEN function. So the LEN of the cell minus the FIND value gives the number of characters required.

Here's the finished formula:

=RIGHT(B2,LEN(B2)-FIND(" ",B2))

Be careful with the commas and the brackets if you are hand typing or click the Insert Function (fx) button and let Excel do them for you.

The final job is to copy the formula down the column and extract all the last names.

You can leave the formulas in the cells as it will not affect the sorting and it will make any additional records much easier to process as you can just copy the existing formulas.

A final thought, why didn't we have two columns in the first place? 

Related Posts

Excel-Calculating age from date of birth
Excel-Calculations without formulas
Excel-Switching columns to rows
Excel-AutoSum Revisited

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.

Switching Excel Columns to Rows

Of course, if you want to impress, you should really call this "Transposition". Excel data can be rearranged from columns to rows or vice versa. And you can transpose as many rows or columns as you like all in one go. All you need to decide is whether you want to do the rearrangement just once or whether the transposed data needs to update to reflect any changes made to the original.

Static transposition, where the data is rearranged just once is really easy to do. However, dynamic transposition, where you have two sets of Excel data; one arranged in columns and the other in rows, is much more difficult and involves your entering an array formula.

Static Transposition

If you know how to Copy and Paste then you'll find this one a breeze, the copy bit is as normal but there is a variation to the paste bit. Select the original range of cells and Copy them. Then click a blank cell that is away from the original range and Transpose; there is no need to select the entire range for the transposition, a single cell is all you need.

Finding the Transpose command depends on which version of Excel you are using. If you have a modern version of Excel with the fancy ribbons then you should be able to find Transpose in the Paste control on the Home tab or in the shortcut menu when you right-click.

Should you have one of the good old fashioned versions with the drop-down menus then look for Paste Special which is found in the Edit menu, failing that right-click after you have done your Copy and you may very well find Paste Special in the shortcut menu.

When the Paste Special dialog appears you need to find the Transpose check box, give it a click and then click the OK button. Sounds easy doesn't it? But I often find myself staring at the screen muttering "now, where's that Transpose thingy..." Because it's right down the bottom, where it always has been.

Dynamic Transposition

In the previous example we transposed our data and ended up with two independent ranges of cells, one of which you would probably want to delete. However, you might want to keep both ranges and have the transposed data change when changes were made to the original. This where you need to have a formula. The most painful method would be to go through each cell, enter an equals sign and click the corresponding cell in the original range. Very tedious indeed.

A much better method would be to create an array formula using the TRANSPOSE function. Array formulas are not easy to enter and most sensible people run screaming from the room at the mere mention of them. So don't get annoyed if this formula takes a few goes to get right. Like most Excel formulas you have to persevere and suffer for a bit until you feel confident.

Array formulas are entered into ranges of cells in one go, not entered into single cells and then copied which is what we are used to. You select the range, enter the text of the formula and finally, press CTRL+SHIFT+ENTER on your keyboard to enter your formula into the selected range.

The first job is to count the number of rows and columns in the range that you wish to transpose and then select a range of empty cells whose dimensions correspond to the inverse of the original range. For example, I want to transpose the range D4:G6, which is a range with 5 columns and 3 rows, so I select an range of 3 columns by 5 rows.

Now we enter the required formula which is =TRANSPOSE(D4:G6) and then holding down the CTRL and SHIFT keys, press ENTER or click the Enter box in the formula bar.

If you have a version of Excel that pops up the list of functions as you type then you can accept TRANSPOSE from the list by pressing the TAB key. Array formulas are identified in the formula bar enclosed in braces (the squiggly brackets) but you do not type in the braces when you enter the formula.

You may not change part of an array formula so if you want to delete your formula, select the entire formula first before pressing the Delete key. To edit the formula there is no need to select the whole array first but don't forget to press CTRL+SHIFT+ENTER to accept the edit.

The Excel shortcut key to select the current array is CTRL+/ (front slash) so if you have a huge transposition just click one cell and then the short cut key will select the rest of it for you.

When you are counting the number of columns or rows in a large range it is all too easy to lose count and very frustrating when you have to start over again. "One, two, three, four... " Such fun. Use the Excel functions ROWS or COLUMNS to calculate the dimensions of large ranges rather than count them. For example, the formula =ROWS(A1:D50) returns the value of 50.

Transposition formulas are not the easiest of formulas to get right but, like all formulas, once they are done they will look after themselves and update automatically.

Any changes made to the original range are immediately reflected in the transposition.

Related Posts

Excel-Calculating age from date of birth
Excel-Calculations without formulas
Excel-Sorting by last name
Excel-AutoSum Revisited

Training Courses

If you've still got that "I just don't know what I'm doing" feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.