• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Paste Special Operations

You are here: Home / Excel / Excel Paste Special Operations
June 9, 2014 by Mynda Treacy

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.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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

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

Easily Remove Excel Password Protection

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

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

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

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

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

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

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

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category: Excel
Previous Post:Sorting in Excel PivotTablesSorting in Excel PivotTables
Next Post:Plot Excel Data on a MapPlot Excel Data on a Map

Reader Interactions

Comments

  1. Jim

    January 20, 2015 at 3:15 am

    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
    • Mynda Treacy

      January 20, 2015 at 9:38 pm

      Great tip, Jim! Thanks for sharing. I’ll try to remember that one.

      Mynda

      Reply
  2. DETRA Putman

    August 12, 2014 at 6:51 am

    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
    • Mynda Treacy

      August 12, 2014 at 8:53 am

      Thanks, Detra. Glad you found it useful 🙂

      Reply
  3. manou

    July 1, 2014 at 8:56 am

    thank you so mush,your works It works great
    Regards
    manou

    Reply
    • Philip Treacy

      July 1, 2014 at 10:08 am

      You’re welcome.

      Phil

      Reply
  4. Gary

    June 16, 2014 at 11:00 am

    Fantastic Mynda. I didn’t know you could do such things with Paste Special.

    Reply
    • Philip Treacy

      June 16, 2014 at 11:02 am

      Thanks Gary, glad that you’ve learned something new.

      Phil

      Reply
  5. Pablo

    June 15, 2014 at 11:49 pm

    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
    • Mynda Treacy

      June 16, 2014 at 8:28 am

      Cheers, Pablo. Stuart also mentioned the ‘Right-click’ key in his comment above.

      Mynda

      Reply
  6. KV

    June 12, 2014 at 11:03 pm

    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

      June 16, 2014 at 11:18 am

      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
      • KV

        June 20, 2014 at 3:11 pm

        That’s great Phil, thanks ! 🙂

        Reply
        • Philip Treacy

          June 20, 2014 at 8:05 pm

          you’re welcome

          Reply
  7. Bryan Metz

    June 11, 2014 at 11:13 pm

    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

      June 12, 2014 at 9:01 am

      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

        June 16, 2014 at 9:20 pm

        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
        • Mynda Treacy

          June 16, 2014 at 9:23 pm

          🙂 I have CTRL+S OCD!

          Reply
  8. Susan

    June 11, 2014 at 10:25 pm

    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

      June 12, 2014 at 9:00 am

      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
    • KV

      June 12, 2014 at 10:56 pm

      That’s a new keyboard shortcut I’ve learnt today…
      THANKS Susan 🙂

      Reply
  9. stuart

    June 11, 2014 at 8:09 pm

    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

      June 11, 2014 at 9:21 pm

      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
      • stuart

        June 12, 2014 at 11:08 pm

        ah! – well if your hand is on the mouse, instead of the right-click key, right-click the mouse!

        Reply
        • Mynda Treacy

          June 13, 2014 at 8:20 am

          🙂 he, he. Noooo. Using the mouse is too slow.

          Reply
  10. Tahir

    June 11, 2014 at 5:29 pm

    Excellent Demo.

    Learned extra ordinary by this tutorial.

    Reply
  11. Jef

    June 11, 2014 at 3:22 pm

    This is great. Handy paste tricks. Thanks for sharing.

    Reply
    • Mynda Treacy

      June 11, 2014 at 7:42 pm

      Cheers, Jef and Tahir 🙂

      Reply
  12. KV

    June 11, 2014 at 1:49 pm

    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

      June 11, 2014 at 1:52 pm

      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

        June 12, 2014 at 10:54 pm

        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

          June 13, 2014 at 8:25 am

          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

    June 11, 2014 at 1:16 pm

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

    Reply
    • Mynda Treacy

      June 11, 2014 at 1:19 pm

      Awesome! Glad I could teach you a new trick 🙂

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.