Paste in Excel is not simply copying data from one place to another; using Paste offers many other functions. This article will guide Excel users on how to use Paste and its various features.
When learning Excel, you've undoubtedly encountered lessons on using Paste, and you've probably noticed a command called Paste Special. So, how does Paste Special differ from regular Paste in Excel, and are there any issues you might not have understood when learning Excel ?
Instructions on using the Paste feature in Excel
Furthermore, whether Paste Special will bring any benefits to users or make your Excel usage faster will all be clarified with the features listed below by TipsMake, giving you a new perspective on using Paste in Excel.
You can quickly access the items right here:
1. Copy formatting in Excel using Paste
The Paste feature allows you to copy any cell or row anywhere; however, with regular Paste, you'll overwrite existing data. How can you copy fonts, bold text, italics, and many other attributes without changing the data? It's very simple when you use Paste in Excel.
Step 1: Let's take the example of wanting to copy the red and bold text attribute of the Grading Rules into the table above. First, press Ctrl + C to copy the Grading Rules.
Step 2: Select the entire area where you want to copy the formatting, right-click and choose Paste Special > Paste Special .
Step 3: In Paste Special, select Formats and then click OK.
Step 4: You will see that the entire table has been changed to bold and red, meaning you have completed the formatting copying process without losing any data.
2. Perform the calculation.
When you copy a cell containing entered formulas, how do you make Excel automatically understand that the next row is the same calculation you entered? Of course, we won't be using drag-and-drop to let the system calculate automatically. Here, we'll use Paste Special.
Step 1: As in the problem, we will Ctrl + C (Copy) cell F6, which is the sum of the first value.
Step 2: Click on the box below, select Paste Special , and then select Paste Values (the one with the 123 icon at the top).
Step 3: Immediately, we will see the result in the cell below, which has automatically calculated the sum after referencing the first row exactly.
But as mentioned, the example above is very simple and helps you understand how to use Paste in Excel when you want to perform calculations. You can apply them to spreadsheets and columns with larger units than the example above.
3. Copy the column width.
This is one of the most common ways to use paste in Excel. With the regular paste method, we can't copy the entire length and width of the new column; we have to follow the new column's index. The way to get a new copy that's exactly the same as the old one is to copy the width using Paste Special.
Step 1: As you can see in the image, this is how to copy using Ctrl + C and then paste normally; the length will not automatically stretch.
Step 2: Copy the necessary data from the left column, then right-click on the right column, select Paste Special , and choose the line spacing icon as shown in the image.
Step 3: Immediately, you will have a perfectly copied document thanks to the Paste Special feature.
4. Data Swap
Data swapping is a way to change data orientation, from horizontal to vertical and vice versa. Swapping is used for many purposes; however, in this article, TipsMake will guide you through the most intuitive way to use this tool when using Paste in Excel.
Step 1: We have a table with 2 rows and 5 columns representing Month and Sales. Our task is to display them vertically.
Step 2: Click outside the Sheet, select any cell, right-click, choose Paste Special , then select the Transform icon .
Step 3: Immediately, you will see that the horizontal data has been converted to vertical data. This is indeed one of the great uses of Paste Special, isn't it?
5. Copy chart formatting
You want to copy a new chart format or style without having to re-enter the information if you change the format or don't merge data. The following method will show you how to copy chart formatting in Excel.
Step 1: You have two images with two different chart displays and different data. Our task is to copy the formatting from the left image to the right image without changing the data on the right.
Step 2: If we use the standard Copy and Paste method, you will see that the formatting in the right-hand panel remains unchanged, and the data is also merged.
Step 3: To copy the chart format, copy the chart on the left, then move to the other chart, press Alt+E+S , and select Formats .
Step 4: The result will be as shown below; the chart on the right retains the same data, but its shape has changed.
6. Replace the formulas with the calculated results.
It's difficult to describe exactly what this method is used for, but you can get a general idea of how to use Paste in Excel from the following example; see if it's any different from the usual method.
Step 1: Here we have a spreadsheet with the total score already entered as a formula combining Literature and Math scores. Our task is to copy all the data from the total column into the Math score column so that the total is added together again. Let's try the usual method.
Step 2: If using the standard copy-paste method, after performing the `tpanf` operation, the returned value will be a `#Value!` error.
Step 3: So, what's the difference with Paste Special? Do the same as above when copying, but use Paste Special and select Paste Value as shown in the image.
Step 4: As a result, we have just replaced a column in the formula with the calculated result, thus completely removing the formula during the copying process and creating a new total with the formula remaining the same.
This feature is often used to replace the RAND() function with its value instead of allowing the formula to calculate itself. Of course, RAND() is not a very frequently used function.
7. Copy data validation
Excel's Data Validation feature helps you manage columns and tables very effectively. You can create many rules for them, but how do you apply those same rules to a new spreadsheet or a new column?
Step 1: In the image, we have a column already set up with the relevant job titles within the company.
Next is a column containing the titles of the company's departments. The problem is how to change the titles from one column to the other?
Note : Once Data Validation is set, you will not be able to edit the cell directly.
Step 2: To do this, right-click on the item you want to copy and select Paste Special twice, as shown in the image.
Step 3: Select Validation and then OK .
Step 4: Immediately, we can see that all the settings from the left-hand panel can now be used for the right-hand panel.
8. Ignore a series of cells with empty values.
The Skip Blanks feature allows you to replace the current value while ignoring cells with no value in the original data. This means you don't need the original data cells to complete the calculation, preventing errors. Although this method is rarely used and difficult to implement on large models, it's one of the very useful features added to Paste Special.
Step 1: Imagine we have a data table with many different data types. How can we copy data from one column to another without causing errors, regardless of whether the data is in the same or different formats?
Step 2: The Values > Skip Blanks feature in Paste Special will help you do that. This feature is quite similar to Format, but the special thing is that the formulas and formatting are preserved.
9. Paste reference
Simply put, this is a way to reference a large amount of data instead of entering each row individually in Excel. Paste references will help you access data faster. The example below doesn't show you all of its features, but it helps you easily understand what Paste references is.
Step 1: Here we have a data table, and the requirement is to reference the entire "Full Name" column downwards. So how do we do that?
Step 2: During the copying process, you will encounter quite a few formatting errors and size issues.
Step 3: However, the option in Paste Special > Paste Link will help you perform the referencing very quickly.
Step 4: The result will immediately appear as the full name column. Although Paste Reference is more commonly used for calculations, the example above should give you a better understanding of how to use this tool. For data that is missing, the value 0 will be returned.
10. Delete text on the web and in other programs.
It can be very frustrating when you copy data from the web or other programs and have to preserve its formatting and size in an Excel spreadsheet. There's a way to eliminate all of that, forcing the copied data to follow your original formatting, or at least Excel's formatting.
Step 1: Here we have a table divided into two results: one for regular copying and one for special paste.
Step 2: In normal mode, the text retains its original size and the UTM Avo font we used earlier.
Step 3: But when using Paste Special as shown below, you will see how the same text is displayed.
Step 4: The default font has been set back to Calibri, which is completely different from the style above.
So how is this feature useful to you? Imagine copying a long section of a webpage, possibly thousands of words with countless formatting and colors. It's very difficult to standardize them in Excel. This is much easier in Word, so the Paste Special tool will help you solve this problem.
Above, TipsMake has guided you on how to use Paste in Excel, right? Indeed, Paste has many features that we may not know about or have explored much. But through this article, TipsMake has added a large amount of Excel knowledge to our readers. We hope that in future articles we will continue to explore Paste for you.
Excel includes many mathematical functions, among which basic functions like SUM, MAX, MIN. are the most frequently and continuously used. If you are not yet familiar with these basic Excel functions , take some time to review them.
To use Excel more effectively, in addition to using Paste, you should also learn about Excel keyboard shortcuts. These important shortcuts will help you use the software more proficiently, and are very helpful even with Paste Special.