My Online Training Hub

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

Excel Paste Special Operations

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 CTRL+SHIFT+V (Microsoft 365 only) or CTRL+ALT+V, 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

But at least now we Microsoft 365 users can use the new keyboard shortcut CTRL+SHIFT+V to paste values.

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Posts

Learn Excel Fast
Break free from endless Excel tutorials with goal-setting strategies and practical tips for mastering Excel through focused practice.
Excel Formatting Tricks
7 simple yet powerful Excel formatting tricks that will transform your spreadsheets into visually engaging and professional-looking reports.
Pareto Analysis in Excel
How to efficiently apply the Pareto Principle in Excel using sorting, filtering, PivotTables, and more to uncover key insights.
Dynamic Calendar
How to create a dynamic Excel calendar that updates automatically, highlights important dates, and adapts to any year.
Dynamic Dependent Data Validation Lists
Easily create dynamic multi-level dependent drop-down lists in Excel. Speed up data entry and reduce errors.
Spreadsheet Automation Tricks
5 essential Excel tricks to automate your spreadsheets and save hours of work each month. Learn how to update drop-down lists, consolidate data, and create dynamic text labels with ease.
Excel Tables Tricks
Excel Tables are a must have skill for all Excel users and their Structured References are a much simpler way to dynamically name ranges.
New Excel Formula Editor
The new Excel Formula Editor enables you to more easily write, debug, and manage formulas with a modern interface.
Microsoft Forms Update
Microsoft Forms simplify data collection with Excel integration and prefilled links accessible from any device. Create, edit, and sync forms.
Hidden Excel Tools Boost Productivity
Discover five hidden Excel tools that can transform your workflow and boost productivity, making you more efficient.


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 *