{"id":8286,"date":"2018-08-23T11:21:05","date_gmt":"2018-08-23T11:21:05","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=8286"},"modified":"2018-08-23T11:21:05","modified_gmt":"2018-08-23T11:21:05","slug":"paste-special-function-microsoft-excel","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/paste-special-function-microsoft-excel\/","title":{"rendered":"Paste Special function in Microsoft Excel"},"content":{"rendered":"

Hi All,<\/p>\n

Do you know apart from only copy and pasting the whole content with formatting in Excel, we can paste a certain attribute such as Value only, Column Width, Formula only, formatting or comment in Excel.<\/p>\n

Paste specials give you an opportunity to choose what we want to paste in cells. When standard copy\/paste do not give appropriate results, Excel\u2019s Paste special give a wide range of options in which we choose specials elements and mathematical elements.<\/p>\n

For Example, if we do any calculation in to the cells and wanted to paste only it\u2019s value in the same cell or somewhere in another sheet or we want to paste only the column width or paste only comments or wants to transpose the data or we can create a link between cells or between the sheets etc.<\/p>\n

The Following screenshot demonstrates all the option available in Paste Specials.<\/p>\n

\"pic-1\"<\/p>\n

Note: \u201cPaste special\u201d option available only once data is copied.<\/p>\n

How to Use \u201cPaste Special\u201d-<\/p>\n

1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Copy the source cells or range. (use shortcut Key\u00a0Ctrl+C<\/u><\/strong>)<\/p>\n

2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select destination cell<\/p>\n

3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Home->Paste Drop Down-> Paste Specials(use shortcut Ctrl+Alt+V)<\/p>\n

4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Choose the option, Click Ok or Press Enter.<\/p>\n

Yes! It is as simple as copy and paste\ud83d\ude0a<\/p>\n

Shortcut keys for Paste Specials-<\/p>\n

1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Paste special shortcut key for Ms-Office 2007-2016 \u2013\u00a0Ctrl+Alt+V<\/p>\n

2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Paste special shortcut key for all Ms-Office Version-\u00a0Alt+E then S<\/p>\n

When you press the shortcut key (Ctrl+Alt+V ) you will get the following window-<\/p>\n

\"Pic-2\"<\/p>\n

We can take any option either click with Mouse or just press the respective alphabet key on the keyboard, which is defined by Underlined \u201c_\u201d , for example if we want to use column widths press\u00a0Ctrl+Alt+V then W<\/p>\n

Lets explore few most usable features listed under \u201cPaste Special\u201d:<\/p>\n

\u00a0<\/strong><\/p>\n

    \n
  1. Paste only \u201cValues\u201d in Excel-<\/strong><\/li>\n<\/ol>\n

    Suppose we have an inventory sheet containing multiple functions\/formulas. We need to share this sheet with our clients, however we only need to share value not formula. When we copy and paste the cells, Excel gives us a weird data.<\/p>\n

    So how we can paste only values from a database?<\/p>\n

    1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select the cell or range including formula then copy it (Ctrl+C)<\/p>\n

    2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Click on Destination cell-> Home Tab-> Paste Special-> Values<\/p>\n

    Or Press\u00a0Ctrl+Alt+V\u00a0then V<\/p>\n

    4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ok<\/p>\n

    \"Pic-3\"<\/p>\n

      \n
    1. How to copy and paste \u201cComments\u201d <\/strong>only :<\/strong><\/li>\n<\/ol>\n

      \"Pic-4\"<\/p>\n

      In column \u201cD\u201d, cells indicate the presence of comments by showing red color triangle on the top right corner of the cell.<\/p>\n

      Steps to copy \u201cComments\u201d-<\/p>\n

      1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select the source cells or range then Copy (Ctrl+C)<\/p>\n

      2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Destination cells-> Ctrl+Alt+V then C<\/p>\n

      3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ok.<\/p>\n

      \"Pic-5\"<\/p>\n

        \n
      1. How to Paste \u201cColumn Width\u201d:\"Pic-6\"<\/strong><\/li>\n<\/ol>\n

        Here, we need to copy column \u201cA\u201d range and paste only column width column \u201cE\u201d (refer: Screenshot)<\/p>\n

        Steps to paste \u201cColumn widths\u201d:<\/p>\n

        1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Copy column A<\/p>\n

        2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select destination cell ->Press Ctrl+Alt+V<\/p>\n

        3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Either click on Option \u201cColumn Widths\u201d or Press \u201cW\u201d<\/p>\n

        4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OK<\/p>\n

          \n
        1. How to Paste \u201c<\/strong>Chart Formatting\u201d:<\/strong><\/li>\n<\/ol>\n

          Paste special not only help us to paste values but also it can paste the formatting of charts. This can help us to maintain the consistency throughout all the charts.<\/p>\n

          Steps to paste \u201cChart Formatting\u201d –<\/p>\n

          1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Copy the source chart (Ctrl+C)<\/p>\n

          2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Click on Destination chart<\/p>\n

          3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Home Tab-> Paste special<\/p>\n

          4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select the option Formats<\/p>\n

          5-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ok<\/p>\n

          \"Pic-7\"<\/p>\n

            \n
          1. How to convert Text into Numbers?<\/li>\n<\/ol>\n

            When we download or import a workbook from other sources, data doesn\u2019t come in a desired format. Most of the time we face a problem that numbers are behaving like text and they are not converting into the number after applying the number formats. \u201cPaste special\u201d can be used here-<\/p>\n

            1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Type Numerical 1 anywhere in you sheet-> Copy It<\/p>\n

            2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select the data range, which is coming into the text format.<\/p>\n

            3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ctrl+Alt+V<\/p>\n

            4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Click on \u201cMultiply\u201d<\/p>\n

            5-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ok<\/p>\n

            \"Pic-8\"<\/p>\n

              \n
            1. Data Transpose-<\/strong><\/li>\n<\/ol>\n

              Sometime we need to convert row data into column and vice versa.<\/p>\n

              1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Copy the source data<\/p>\n

              2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Click on Destination cells<\/p>\n

              3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ctrl+Alt+V then E<\/p>\n

              4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ok<\/p>\n

              \"PIc-9\"<\/p>\n

                \n
              1. Add values to the Existing database–<\/strong><\/li>\n<\/ol>\n

                Probably this operation we do on daily basis to add some value to the existing values. For example, this week total net sales would add to our existing\u00a0 Net sales. Apart from using the function, we can do this with only copy and paste.<\/p>\n

                1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Copy the Source data i.e. Column \u201cC\u201d (Refer screenshot below)<\/p>\n

                2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Click on destination cells i.e column \u201cA\u201d<\/p>\n

                3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Ctrl+Alt+V<\/p>\n

                4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0In the operation section click on Add<\/p>\n

                Note: There are four option \u2013<\/p>\n

                1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Add- To add the numbers<\/p>\n

                2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Subtract- To subtract the numbers<\/p>\n

                3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Multiply- To Multiply with numbers<\/p>\n

                4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Divide- To divide the numbers<\/p>\n

                \"Pic-10\"<\/p>\n

                Problem: <\/strong>Paste special not working?<\/strong><\/p>\n

                If the paste special option is not working properly, It\u2019s likely to be because of one reason-<\/p>\n

                How will you recognize that your paste special option is not working- when you copy your data then do the right click, if paste special option is greyed out then might be its disabled in the background.<\/p>\n

                To cure this problem, follow the steps –<\/p>\n

                File\u00e0\u00a0Option\u00e0\u00a0Advance\u00e0 check \u201cShow paste options button when content is pasted\u201d<\/p>\n

                \"Pic-11\"<\/p>\n

                This is how you can use paste specials options in your Excel sheet.<\/p>\n

                If you have any query suggestions, write down in the comment box.<\/p>\n

                Happy Learning\u00a0\ud83d\ude0a<\/p>\n

                <\/div>\n

                <\/path><\/svg><\/i> \"Loading\"<\/p>\n

                <\/div>\n","protected":false},"excerpt":{"rendered":"

                Hi All,
                \nDo you know apart from only copy and pasting the whole content with formatting in Excel, we can paste a certain attribute such as Value only, Column Width, Formula only, formatting or comment in Excel.
                \nPaste specials give <\/p>\n

                <\/div>\n

                <\/path><\/svg><\/i> \"Loading\"<\/p>\n

                <\/div>\n","protected":false},"author":1,"featured_media":8298,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[265,266,263,243,264,257,260,259,249,256,248,285,286,287,288,289,244,250,252,245,253,254,246,251,267,255,262,258,247,261,284,290,291,292],"class_list":["post-8286","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-corporate-training-companies","tag-corporate-training-companies-in-delhi","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-how-to-use-paste-special","tag-how-to-use-paste-special-in-excel","tag-how-to-use-paste-special-in-microsoft-excel","tag-how-to-use-paste-special-in-ms-excel","tag-learn-how-to-use-paste-special-function-in-excel","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-online-training","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses","tag-paste-special-function-in-excel","tag-what-is-paste-special","tag-what-is-paste-special-function-in-excel","tag-what-is-paste-special-function-in-microsoft-excel"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=8286"}],"version-history":[{"count":1,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8286\/revisions"}],"predecessor-version":[{"id":8299,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8286\/revisions\/8299"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/8298"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=8286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=8286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=8286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}