In the previous article "Practical Excel Tips Sharing: Eliminating Vlookup "BUG"", we learned about the method of eliminating Vlookup "BUG" and making empty returns empty. Today we are going to talk about "Paste Special". It turns out that using Paste Special can achieve so many functions!
When it comes to copy and paste, the first thought in many friends’ minds is ctrl C and ctrl V. In fact, the functions achieved by such operations are very limited. When we learn the special paste tool in excel, we can meet various needs in actual work.
1. Paste selectively and do data calculations
Every year at the beginning of the month, my friends have worked hard to finish the form and will hand it in immediately. The salary was paid in the form, but the boss suddenly said that all employees' salaries would be increased by 100 yuan, which would be added directly to the salary column data. At this time, my friend's first idea is to use the sum function to solve it. In fact, it is more convenient to use selective paste.
Enter the number 100 in the blank cell next to the table, select the cell, and press ctrl c to copy.
Select all the data in the salary column, click the "Paste" button drop-down menu under the "Home" tab, and click "Paste Special".
In the pop-up dialog box, select "Number" and "Add".
At this time, the data in the salary column increased by 100.
In fact, in addition to addition operations, multiplication, division and subtraction can also be implemented. The operations are similar to the above.
2. Selective pasting to achieve data verification
I don’t know if you have noticed in the previous explanation, "Selective Paste" There is an option to "skip empty cells" in the Paste dialog box. Do you know how to use it?
In a company, two or more people often work together After making salary statistics, you need to summarize the data. Below is a table made by two people. We need to summarize and merge the data into one column and find the people whose wages have not been counted. When there is a lot of data, human eye verification is impossible. My first idea is to use the VOOLKUP function to solve it. In fact, you can also use selective paste instead.
It’s very simple, select the salary area in the second column and press ctrl c to copy.
Then select the salary area in the first column.
Click the "Paste" drop-down menu under the "Home" tab and click "Paste Special". In the pop-up dialog box, check "Value" and "Skip empty cells" and click OK.
At this time, the data in the two columns of salary are merged into the first column. From the merged result, we can easily see that the salary of Sanmao was missed.
3. Paste selectively into numerical values
Follow the regular copy and paste table, and it will appear A question, how to paste the table style? As shown in the picture below, I just want to copy the list of people, but the format is also copied.
To solve this problem is very simple, we also use the Paste Special tool.
First select the table area and press the ctrl c shortcut key to copy.
Click a blank cell, then click the "Paste" drop-down menu under the "Home" tab, and click the "Value" button in the drop-down menu.
At this point we can see the comparison effect. The left side is the result of simple copy and paste, and the right side is the result of selective pasting as a value.
When we need to copy cells with formula operations, if we copy them directly, the following problem will occur:
In this case, you also need to use the Paste Special as Value tool to get the correct paste result.
4. Transpose function in Paste Special
Sometimes, our table After finishing, if you want to transpose the rows and columns, you can also use the "Paste Special" function to achieve this.
First select the area, press ctrl c to copy, and then click in the empty area.
Click the Paste drop-down menu under the Home tab and select Paste Special. Check the "Transpose" button in the pop-up dialog box and click OK.
The transposed result is as follows:
At this time, some friends raised questions, so I won’t Can I just paste it in the original area? The answer is definitely no! In the picture above, you can see that the two tables after transposition have different numbers of rows and columns, and different area sizes. Pasting cannot be done in the original area and must be pasted in a blank area.
5. Paste selectively to achieve "screenshot"
When we need to make a work report, we need to copy the excel table to word or In PPt, after copying, you will find that the table is deformed. At this point we need to fix the shape of the table.
Select the table area, press ctrl c to copy, click on any blank cell, and select the "Picture" button in the "Paste" drop-down menu.
At this point you will see that the entire table area has been "screenshoted".
Friends who love to study may think that we use this screenshot-like function to save the table in the form of a picture. The data at this time cannot be modified. , so what should I do if I want to change the data?
First select the table area, press ctrl c to copy, and then select the blank cells in sheet2. Click "Linked Image" in the "Paste" button drop-down menu.
At this time, the table is copied to the sheet2 worksheet in the form of a picture.
When we change the data in sheet1, the table image in sheet2 will update the data in real time. As shown in the figure below, in the new year, Harano Shinnosuke's seniority salary became 200. After changing the data in sheet1, the data was automatically updated in sheet2.
Sheet1 table
Sheet2 table
The special paste in Excel is shared here. In fact, there are many It has many functions. Friends, you can come down and study it yourself. You will find that learning this tool saves time and effort.
Related learning recommendations: excel tutorial
The above is the detailed content of Sharing practical Excel skills: 'Paste Special' turns out to have so many functions!. For more information, please follow other related articles on the PHP Chinese website!

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于折叠表格的相关问题,就是分类汇总的功能,这样查看数据会非常的方便,下面一起来看一下,希望对大家有帮助。

在之前的文章《实用Excel技巧分享:利用 数据透视表 来汇总业绩》中,我们学习了下Excel数据透视表,了解了利用数据透视表来汇总业绩的方法。而今天我们来聊聊怎么计算时间差(年数差、月数差、周数差),希望对大家有所帮助!

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于AGGREGATE函数的相关内容,该函数用法与SUBTOTAL函数类似,但在功能上比SUBTOTAL函数更加强大,下面一起来看一下,希望对大家有帮助。

在之前的文章《实用Word技巧分享:聊聊你没用过的“行号”功能》中,我们了解了Word中你肯定没用过的"行号”功能。今天继续实用Word技巧分享,看看Excel表格怎么借用Word进行分栏打印,快来收藏使用吧!

在之前的文章《实用Excel技巧分享:原来“定位功能”这么有用!》中,我们了解了定位功能的妙用。而今天我们聊聊合并后的单元格如何实现筛选功能,分享一种复制粘贴和方法解决这个问题,另外还会给大家分享一种合并单元格的不错的替代方式。

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于zenmm制作倒计时牌的相关内容,使用Excel中的日期函数结合按指定时间刷新的VBA代码,即可制作出倒计时牌,下面一起来看一下,希望对大家有帮助。

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于如何使用函数寻找总和为某个值的组合的问题,下面一起来看一下,希望对大家有帮助。

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了关于折线图的相关问题,下面就根据一些示例来看一下怎么去应用,希望对大家有帮助。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Zend Studio 13.0.1
Powerful PHP integrated development environment

Atom editor mac version download
The most popular open source editor

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Dreamweaver Mac version
Visual web development tools
