The Robservatory

Robservations on everything…

 

How to: Simplify value pasting in Excel

I tweeted this over the weekend, but thought it might be worth a bit more explanation here. By default, when you copy and paste something in Excel, Excel defaults to pasting everything from the copied cell: the formula, shading, borders, font, style, etc.

Excel dialogSometimes this is OK, but often I just want the values from a cell or range—either because I'm using them in another table with different formatting, or to convert a formula into fixed values.

Out of the box, if you want to paste just the cell values in Excel, you have to select Edit > Paste Special, then navigate the world's busiest dialog box (as seen at right), click on the tiny Values radio button, then hit Return.

If you're doing a lot of value pasting, this is a royal pain. Thankfully, it's pretty easy to fix this design stupidity.

How you fix this depends—for the first step—on whether you're running Excel 2008 or Excel 2011.

In Excel 2008, select View > Customize Toolbars & Menus, then click the Keyboard button, located at the bottom right of the dialog box that appears. In Excel 2011, select Tools > Customize Keyboard.

At this point, regardless of which version you're using, you'll see a dialog very similar to this one:

Custom1

This is a very powerful dialog box; it allows you to create shortcut keys for nearly any Excel command. Because Paste Special is in the Edit menu, click on Edit in the Categories box, then find Paste Values in the Edit box:

Custom2

Once selected, click in the "Press new shortcut key" box, then type the keys you'd like to use. I like Shift-Control-V, because it's relatively easy to type, but I won't hit it accidentally. Use what works best for you.

Important: Merely typing the keystrokes in this box does not mean you're done. If that's all you do, you've not actually changed anything: make sure you click the Add button, or your changes aren't actually applied.

Custom3

When you press Add, you'll see your chosen keys show up in the "Current keys" box; when you see them there, then you can back out of the dialog box. Click OK once (Excel 2011) or twice (Excel 2008), and you're done.

This is one of the first changes I make whenever I install a new copy of Excel; it makes pasting values about as simple as can be.

4 Comments

  1. Hello. I've followed these steps numerous times (using Excel 2011). It adds the new keyboard command to the menu, but when I perform the key-combo in a file, it does the default paste still, rather than the paste values that I set it to. It's incredibly frustrating. Have you a solution for this?

    1. I'm not sure what to suggest -- I just tested it again on a fresh Excel 2011, and it works as described. (I used Control-Option-V for my test, but the actual keys shouldn't matter.)

      -rob.

  2. Hi, I can't find the same option - Customize keyboard - in Excel 2013, could you help me please?

    1. You can't find it, because Microsoft removed all the keyboard customization stuff. Sorry.

      -rob.

Comments are closed.

The Robservatory © 2022 • Privacy Policy Built from the Frontier theme