search
HomeTopicsexcelSharing practical Excel skills: 'Paste Special' turns out to have so many functions!

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!

Sharing practical Excel skills: 'Paste Special' turns out to have 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.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

Select all the data in the salary column, click the "Paste" button drop-down menu under the "Home" tab, and click "Paste Special".

Sharing practical Excel skills: Paste Special turns out to have so many functions!

In the pop-up dialog box, select "Number" and "Add".

Sharing practical Excel skills: Paste Special turns out to have so many functions!

At this time, the data in the salary column increased by 100.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

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?

Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

It’s very simple, select the salary area in the second column and press ctrl c to copy.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

Then select the salary area in the first column.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

When we need to copy cells with formula operations, if we copy them directly, the following problem will occur:

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

In this case, you also need to use the Paste Special as Value tool to get the correct paste result.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

The transposed result is as follows:

1Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

Sharing practical Excel skills: Paste Special turns out to have so many functions!

At this point you will see that the entire table area has been "screenshoted".

2Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

2Sharing practical Excel skills: Paste Special turns out to have so many functions!

At this time, the table is copied to the sheet2 worksheet in the form of a picture.

2Sharing practical Excel skills: Paste Special turns out to have so many functions!

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.

2Sharing practical Excel skills: Paste Special turns out to have so many functions!
Sheet1 table

2Sharing practical Excel skills: Paste Special turns out to have so many functions!
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!

Statement
This article is reproduced at:部落窝教育. If there is any infringement, please contact admin@php.cn delete
Excel RANK function and other ways to calculate rankExcel RANK function and other ways to calculate rankApr 09, 2025 am 11:35 AM

This Excel tutorial details the nuances of the RANK functions and demonstrates how to rank data in Excel based on multiple criteria, group data, calculate percentile rank, and more. Determining the relative position of a number within a list is easi

Capitalize first letter in Excel cellsCapitalize first letter in Excel cellsApr 09, 2025 am 11:31 AM

Three ways to convert the first letter of Excel cell When processing text data in Excel, a common requirement is to capitalize the first letter of a cell. Whether it is a name, product name, or task list, you may encounter some (or even all) cases where letters are inconsistent. Our previous article discussed the PROPER function, but it capitalizes the initial letter of each word in the cell and the other letters lowercase, so not all cases apply. Let's see what other options are available through an example of my favorite villain list. Use formula to capitalize the initial letter The first letter is capitalized, the remaining letters are lowercase Capitalize the initial letter, ignore the remaining letters Using the text toolbox: "Change case" Use public

Complete guide to Google Sheets conditional formatting: rules, formulas, use casesComplete guide to Google Sheets conditional formatting: rules, formulas, use casesApr 09, 2025 am 10:57 AM

Master Google Sheets Conditional Formatting: A Comprehensive Guide This guide provides a complete walkthrough of Google Sheets' conditional formatting, from basic rules to advanced custom formulas. Learn how to highlight key data, save time, and red

Google Sheets basics: share, move and protect Google SheetsGoogle Sheets basics: share, move and protect Google SheetsApr 09, 2025 am 10:34 AM

Mastering Google Sheets Collaboration: Sharing, Moving, and Protecting Your Data This "Back to Basics" guide focuses on collaborative spreadsheet management in Google Sheets. Learn how to efficiently share, organize, and protect your data

Custom Data Validation in Excel : formulas and rulesCustom Data Validation in Excel : formulas and rulesApr 09, 2025 am 10:24 AM

This tutorial demonstrates how to create custom data validation rules in Excel. We'll explore several examples, including formulas to restrict input to numbers, text, text starting with specific characters, unique entries, and more. Yesterday's tuto

Google Sheets basics: edit, print and download the files in Google SheetsGoogle Sheets basics: edit, print and download the files in Google SheetsApr 09, 2025 am 10:09 AM

This "Back to Basics" guide delves into essential Google Sheets editing techniques. We'll cover fundamental actions like data deletion and formatting, and then move on to more advanced features such as comments, offline editing, and change

Calculating time in Google SheetsCalculating time in Google SheetsApr 09, 2025 am 09:43 AM

Mastering Google Sheets Time Calculations: A Comprehensive Guide This guide delves into the intricacies of time calculations within Google Sheets, covering time differences, addition/subtraction, summation, and date/time extraction. Calculating Time

How to use IFERROR in Excel with formula examplesHow to use IFERROR in Excel with formula examplesApr 09, 2025 am 09:37 AM

This tutorial demonstrates how Excel's IFERROR function handles errors, replacing them with blanks, alternative values, or custom messages. It covers using IFERROR with VLOOKUP and INDEX MATCH, and compares it to IF ISERROR and IFNA. "Give me a

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.