Buzz
When learning Excel, you must have encountered lessons on how to use Paste function, and therein, you must have noticed a command line called Paste Special. So, how does Paste Special differ from regular Paste in Excel that you might not have fully grasped while learning Excel?
Guidance on utilizing Paste feature in Excel
Furthermore, what benefits will Paste Special bring to users or will it make your Excel usage faster? All will be clarified with the features listed by Mytour below, providing you with a fresh perspective on using Paste in Excel.
- Guidance on utilizing Paste feature in Excel
- * 1. Copy format in Excel using Paste
* 2. Perform calculations
* 3. Copy column widths
* 4. Swap data
* 5. Copy chart formats
* 6. Replace formulas with calculated results
* 7. Copy data Validation
* 8. Skip a series of empty cells
* 9. Paste transpose
* 10. Delete text on the web, in other programs
You can quickly access the items here:
1. Copy format in Excel using Paste
Step 2: Left-click on the bottom cell, select Paste Special, and choose Paste Values with the 123 icon at the beginning.
Step 3: Immediately, we will see the result of the bottom cell automatically calculating the sum after projecting the first row exactly.
However, as mentioned, the above example is very simple and helps you understand how to use Paste in Excel when you want to perform calculations. You can apply them to larger spreadsheets, columns with units larger than the example table above.
3. Copy the width of the column.
This is one of the common uses of paste in Excel, with the usual paste method, we cannot copy both the length and width of a new column but must follow the index of the new column. The way to help you have an exact copy with the old copies is to copy the width using Paste Special.
Step 1: As shown in the image, this is how to copy Ctrl + C then Paste normally, the length will not automatically expand.
Step 2: Copy the necessary data in the left column, then right-click on the right column, select Paste Special, and choose the stretch icon as shown in the image.
Step 3: Immediately, you will have a perfect copy with the Paste Special feature.
4. Data Swap
Data swapping is how you change data in different directions. From horizontal to vertical and vice versa. Swapping is used for many purposes, however, in this article, Mytour will guide you on the most visual tool for using this feature when using Paste in Excel.
Step 1: We have a table with 2 rows and 5 columns including Month and Revenue. Our task is to display them vertically.
Step 2: Click outside the Sheet, select any cell, right-click, choose Paste Special > select the Transpose icon.
Describing the precise utility of this method might prove challenging, but envision its application through the following example. Explore the nuances of utilizing Paste in Excel under the given scenario.
Step 1: Here, we have a pre-existing spreadsheet with combined formulas for language and mathematics scores. Our task entails transferring the entire column of total scores into the mathematics section for additional summation. Let's experiment with the conventional method.
Step 2: If one resorts to the usual copy-paste method, the resulting values yield a #Value! error.
Step 3: So, what distinguishes Paste Special? Follow the same copy process as before but opt for Paste special and select Paste Value as depicted in the illustration.
Step 4: By replacing a column within the formula with the calculated result, we eliminate the need for formulas during the copy process, thereby generating a new total while retaining the original formula.
This feature finds great utility in replacing sequences of RAND() functions with their values instead of allowing the formula to recalculate. Of course, RAND() isn't a heavily utilized function.
7. Copy data Validation
Excel's Data Validation feature aids in effectively managing columns and datasets. While you establish numerous rules for them, applying similar rules to a new spreadsheet or column poses a challenge.
Step 1: In the illustration, we have a column set up with various job titles relevant to the company.
Following that is another column listing departmental titles within the company. The task at hand is how to transition titles from one column to the other?
Note: Once Data Validation is set, you won't be able to directly edit the cells.
Step 2: To execute, right-click on the area to be copied, select Paste Special twice as shown in the illustration.
Step 3: Select Validation then OK.
Step 4: Instantly, we see that all settings from the left table are now applicable to the right table.
8. Skipping a series of empty cells
The Skip Blanks feature allows you to replace current values while skipping cells without values in the original data. Thus, you can complete calculations without needing values in the original data cells, preventing errors. Though seldom applied and challenging to implement on large-scale models, it's one of the valuable features of Paste Special.
Step 1: Imagine having a data table with various data types. How do we copy from one column to another without causing errors, regardless of whether they're the same or different formats?
Step 2: The Values >Skip Blanks feature in Paste Special accomplishes this. Similar to Format, but the unique aspect is that formulas and formats remain intact.
9. Paste Special
In simple terms, this is a method of referencing large amounts of data instead of manually inputting each line in Excel. Paste Special allows for quicker data retrieval. While the example below may not fully showcase its capabilities, it provides a clear understanding of what Paste Special referencing entails.
Step 1: Here we have a data table, and the requirement is to reference the entire Name column below. How do we do it?
Step 2: During copying, you often encounter numerous formatting and size errors.
Step 3: However, choosing Paste Special >Paste Link will swiftly execute the referencing process.
Step 4: The result immediately presents the Name column. Although Paste Special referencing is more commonly used for calculations, the example above surely enhances your understanding of this tool. For data without values, it returns 0.
10. Deleting text from the web and other programs
It can be quite inconvenient when you copy data from the web or other programs and have to retain their original formatting and size in an Excel spreadsheet. There's a way to remove all of that and ensure that copied data follows the formatting you're currently using, or at least that of Excel.
Step 1: Here we have a table divided into 2 copy results, one using regular copy and the other using special Paste.
Step 2: With regular copying, the text retains its original size, in the UTM Avo font that we previously used.
Step 3: However, when using Paste Special as shown below, you'll notice how that text segment is displayed.
Step 4: The font has been reverted back to Calibri by default, quite different from the previous style.
So what's the utility of this feature? Imagine copying a lengthy excerpt from a webpage, possibly containing thousands of characters with various formatting styles. It's challenging to standardize them in Excel. This feature is much easier to handle in Word, hence the Paste Special tool aids in resolving this issue.
Here, Mytour has guided you on how to use Paste in Excel. Indeed, Paste has numerous features that we may not have known about or explored extensively. Through this article, Mytour has enriched readers with a wealth of Excel knowledge, and we hope to delve further into Paste in upcoming articles.
Excel comprises numerous mathematical functions, among which basic functions like SUM, MAX, MIN... are the most frequently and continuously used. If you haven't mastered the basic functions in Excel yet, take advantage of your spare time to review them.
To become more proficient in Excel, besides utilizing Paste in Excel, remember to explore Excel shortcuts. Mastering Excel shortcuts is crucial as they enhance your proficiency with the software, providing substantial assistance even with Paste Special.
