Alt+E+S for Paste Special Options

March 12, 2018 - by Bill Jelen

It is mouse-free Monday. Today, I want to introduce you to Alt + E S which is the gateway to the wonderful world of Paste Special options.

A few years ago, I was a judge for the ModelOff World Excel Championships. People from all over the world flew to New York to compete head-to-head in Excel. I talked to a lot of people including German Nande from AMT Training. This is a firm who trains Wall Street in Excel. These Wall Street firms (and their counterparts in London and Hong Kong) are serious about Excel and never using the mouse. I've heard some big firms make new hires total an entire page from a phone book on a 10-key adding machine until they can key numbers with 100% accuracy without ever looking at the keys.

Anyway, AMT offered a laminated tip card for Excel. Now - I sell a bunch of laminated tip cards, but none of them are as hard-core as the AMT card. There was a whole section of the card dedicated to the keyboard shortcuts for the various forms of Paste Special.

Say that you have to copy and paste as values.

1. Ctrl + C to copy
2. Alt + E S to open Paste Special dialog
3. V to choose Values
4. Enter to press OK

So, copy and paste becomes Ctrl + C Alt + E S V Enter

Look at all of the options in the Paste Special dialog. Each has one letter underlined. That is the letter that comes after Alt + E S and before the Enter.

To copy formats, shading, number formatting and column widths, Alt + E S T Enter immediately followed by Alt + E S W Enter. The T does Formats. The W does column widths.

Another great use is when you have a block of formulas with a border around the range. When you copy the top formula and paste, the top border gets pasted. Instead, Alt + E S R Enter will paste Formulas and Number Formats.

For the above example, had there been conditional formatting on the Gross Profit Percent, would Alt + E S X to paste all except borders.

If you want to turn data sidesways, Ctrl + C, select the new corner alt Alt + E S E Enter to transpose.

In the image below, the original values across the top had formulas and I want to convert those formulas to values as I transpose. You can combine letters from the top, middle, and bottom sections of the dialog. Ctrl + C, Alt + E S V E Enter.

Here is another one. You need to multiply all values by 97%. Copy the 97%. If you would Paste Special Multiply, you will lose that currency formatting. Instead, Ctrl + C, Alt + E S M V Enter to Paste Special Multiply Values. This will prevent the number formatting from changing.

If you master Ctrl + C Alt + E S, you will have access to 12 different ways to paste. I've used every circled option via a shortcut key in the last month.

If you've read this far, let me address some controversey. Why am I teaching you Alt + E S instead of the newer Alt, H, V, S? First, it is two key presses shorter. Second, all of these excellent keystrokes can be done in a tiny patch of keys on the left side of the keyboard. They are one-handed shortcuts.

• Ctrl + C Alt + E S V for values
• Ctrl + C Alt + E S T for formats
• Ctrl + C Alt + E S F for formulas
• Ctrl + C Alt + E S R for formulas and number formatting
• Ctrl + C Alt + E S X for all except borders
• Ctrl + C Alt + E S W for column widths

There are individual cases where you might come up with a faster way using the Format Painter or the right-click key to shorten one of these. But if you get used to Ctrl + C Alt + E S, then you have learned a dozen new keyboard shortcuts.

It is mouse-free Monday. You will be faster in Excel if you learn some keyboard shortcuts. I am suggesting that you try to give up your mouse in Excel for one hour each Monday. I will provide a new Excel Keyboard Technique each Monday. To learn Excel hot keys in a fun game, visit Hot Key Excellence

Title Photo: Bruno Glätsch / pixabay