My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel / Excel Paste Special Operations
by

Copying and Pasting are two of the most common tasks you’ll perform in Excel.

This is especially true for me and I can tell because the shortcut keys (CTRL+C for copy and CTRL+V for paste) on the new keyboard I got just last week are already showing signs of wear.

Most of the time I don’t just Paste, I Paste Special (right-click > Paste Special or ALT+E, S or CTRL+ALT+V):

Today I’m going to cover the ‘Operation’ commands in the Paste Special dialog box because in recent weeks I’ve had a few questions from members which were easily solved using these:

paste special dialog box

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

1. Convert Values to Percentages

paste special convert values to percentages

Convert Values to %: Copy cell containing 100 (A5) > select values in cells C5:C9 > right-click and select Paste Special > select 'Values' and 'Divide' (or ALT+E, S, V, I).

Then format as percentages: CTRL+1 > Format as percentages.

2. Add a Range of Values to Another Range of Values

paste special add values

Add range to another range: Copy first range of cells > select values in cells second range > right-click (or ALT+E, S, V) and select Paste Special > select 'Values' and 'Add' (or ALT+E, S, V, D).

3. Convert Text to Numbers using Multiply

paste special convert text to numbers

Convert Text to Numbers: Copy cell containing 1 > select values in cells containing text > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).

4. Convert Text to Numbers using Add

paste special convert text to numbers

Convert Text to Numbers: Copy any empty cell > select values in cells containing text > right-click and select Paste Special > select 'Values' and 'Add' (or ALT+E, S, V, D).

5. Add 10% to all Numbers

paste special add 10% to values

Add 10% to all Numbers: Copy cell containing 110% > select values you want to increase > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).

6. Reduce all Numbers by 20%

paste special reduce values by 20%

Reduce all all Numbers by 20%: Copy cell containing 0.80 > select values in cells that you want to reduce > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).

7. Convert Negative Values to Positive and Vice Versa

paste special convert negative values to positive

paste special convert positive values to negative

Convert Negative to Positive and vice versa: Copy cell containing -1 > select values in cells to be converted > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).

Problem with Paste Special

For a long time I wished there was a way to set the default paste to ‘Values’ just like you can in the Word Advanced Options (File/Windows button > Options > Advanced > Cut, copy and paste section):

Word set default paste

Or, at the very least a shorter shortcut key for Paste Special > Values, since having to press the unwieldy combination of ALT+E, S, V and then ENTER was just too prone to error for my fingers. Even the other option of CTRL+ALT+V, V then ENTER is only for double-jointed people!

Thankfully my friend, Jon Acampora, created an add-in for Excel called PasteBuddy. I’ve been using it for a few weeks now and I can’t imagine ever going back.

PasteBuddy

PasteBuddy allows you to create custom shortcut keys for any of the Paste Special commands. You can even choose your own key combinations so, if like me, you just can’t get your fingers (on one hand) to press CTRL+ALT+V, then you can choose a different letter or character on the keyboard.

Configure PasteBuddy

You can configure up to 12 different shortcut keys for any of the Paste Special commands in the Setup Window:

Configure PasteBuddy

Once you’ve configured it as you want this window closes and you can merrily Paste Special to your heart’s content.

If you use the Paste Special dialog box often, or you’re left handed, then PasteBuddy is going to give you the shortcuts you’ve been yearning for.

You can find out more about PasteBuddy here.

Disclosure: If you purchase PasteBuddy I make a few dollars. I don't recommend any old product though. It has to be great quality and relevant to my readers. I love PasteBuddy and I hope you will too.

More Excel Posts

excel templates
Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files
How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel
Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online
5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.
10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features
Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation
Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT
The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.
Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection
Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category:

Reader Interactions

Comments

  1. Jim

    I frequently want to replace a bank of formulae with their values immediately after calculation
    Since these are usually all entered together (with ctrl-enter or double-clicking the fill handle – these are great tips too btw), the range is already selected:
    Now, using the right mouse button grab and drag any border in any direction and then back again before releasing the button, then click paste values or go back to the keyboard and hit a “v”
    Sounds long-winded but it’s really easy and well worth leaving the keyboard for (I usually avoid using the mouse too!)

    Jim

    Reply

  2. DETRA Putman

    Wow! I use “past special” all the time to paste the value of formulas. I had no idea there were so many other options.
    Thank you so much for sharing your knowledge.

    Reply

  5. Pablo

    Hello,
    Not too long ago, Chandoo send a keyboard shortcut for the paste special.
    I n his example he used values. Just copy as usual with Ctrl V and paste special with the key than duplicates de mouse right button and V.
    This key is like the Windows key, but it’s on the right side of the keyboard.
    Or just use the mouse, shortcut menu or right mouse key.
    It works great
    Thanks
    Pablo

    Reply

  6. KV

    Is there any way to subscribe to the comments on a post, via email ?

    It would be great to receive the comments via email if a particular topic is of interest, rather than remembering to check each topic every few days.

    Reply

    • Philip Treacy

      Hi KV,

      I’ve just added some code to allow you to do this.

      Below the Submit button on the comment form you’ll see a link that allows you to sign up for notifications of new comments.

      You can sign up for notifications on posts you’ve already commented on too, no need to leave a comment just to receive notifications.

      Regards

      Phil

      Reply

  7. Bryan Metz

    One of the first macros I ever wrote was for Paste Special (Values). Originally the idea was it would take the cells you had highlighted, copy them, and paste the values back into the same cells. I’ve since updated it to also act as a standard Paste Values if there’s already something in the clipboard. I have it set to a keyboard shortuct of Ctrl+S which is SO much easier than Ctrl+E,S,V!

    Reply

    • Mynda Treacy

      Hi Bryan,

      I gasped when I read that you repurposed CTRL+S for paste values. As much as I like Paste Values, I couldn’t give up the save shortcut for it 🙂

      Mynda

      Reply

      • Bryan Metz

        I know, it’s a controversial choice. But for some reason, I never got into the habit of using Ctrl+S, even though I DO compulsively save my files. For a while there I was using Alt+F,S (don’t know why), but now I put the save button in my QAT and any time I touch the mouse I head up there to click on it.

        And if I’m honest… I probably use paste values more than save… 🙂

        Reply

  8. Susan

    One of my favorite Paste Special commands is Ctrl+Alt+V and then w to paste column widths and then Ctrl+V to paste the rest. Whenever I need to copy some data to a new sheet, I do this so that the columns widths are correct 🙂

    Reply

    • Mynda Treacy

      Cheers, Susan. I too like the Column Widths option.

      You must be one of those double-jointed people I mentioned in my post if you can wrangle your fingers to press CTRL+ALT+V. Impressive 🙂

      Mynda.

      Reply

  9. stuart

    paste-values shortest keyboard-shortcut i’ve found is the right-click key followed by v (only excel2010 or later)
    (right-click key is between space-bar & right ctrl-key – also called program-key or menu-key)
    or if you’re a mouse-person, if not pasting far away, no need to copy, just right-drag the cell or range in any direction (even back to original location if just converting formula to value), on release select ‘copy here as values only’

    Reply

    • Mynda Treacy

      Cheers, Stuart. Great tips, although that Right-click key requires me to either let go of my mouse or move my hand a long way 😉

      I still prefer something with the speed and convenience of CTRL+C then CTRL+V, except for pasting values. Even the mouse action of right-click and drag is too many steps for me.

      What can I say, I’m fussy 🙂

      I’m sure there are others who will appreciate your tips, so thanks for sharing.

      Mynda.

      Reply

  12. KV

    Hi Mynda, there is an option to make the PasteSpecial > Values or Formats commands into single click operations.
    Just add them to the QAT 🙂

    In fact, most of the PasteSpecial options can be added to the QAT depending on your preferences.

    Reply

    • Mynda Treacy

      Indeed, KV. However I prefer a keyboard shortcut since I’ve just pressed CTRL+C and my fingers are ready to complete the pasting 🙂

      Reply

      • KV

        I agree Mynda – even I hate moving my hands off the keyboard unless there’s no other option except to use the mouse 🙂

        Almost all buttons on the QAT have keyboard shortcuts for them, depending on (a) the sequence in which they are added to it, and (b) the number of buttons added to the QAT.

        Starting from the left, the first button on the QAT is assigned the keyboard shortcut Alt + 1, the second one is Alt + 2, and so on.
        E.g., on my computer, PasteValues is fifth on the QAT, so I use Alt + 5 for that command.

        Reply

        • Mynda Treacy

          Great tip, KV. Thanks.

          Although I’m preferring my CTRL+SHIFT+A shortcut that I set up in PasteBuddy. The keys are nice and close togehter which means the chance of pressing the wrong key is reduced, and they don’t require double jointed fingers to press.

          I think think there is something to suit everyone with all the tips shared here. Thank you all.

          If we could just get that ‘Right-click’ key beside the CTRL key on the right hand side of the keyboard moved to where the Windows key is, then it would be even better. Not holding my breath though.

          Mynda

          Reply

  13. Cathy

    Wow, I had no idea. I hope you work through the other Special Paste features as I’m seriously missing out.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *