Contents
Some of you must have paid attention to such an Excel tool as paste Special (Special insert). Many may have experienced bewilderment, if not frustration, when copying and pasting data into Excel. You inserted data and got something completely different from what you expected to get. I will show you some cool tricks using special insert and I’ll show you how to use some of the useful features of this tool so that the data always looks exactly the way you want it.
After reading this article, you will learn how to transpose, remove links, and skip blank cells using the paste Special (Special insert). Each section is followed by a short step-by-step summary. Use the shortcut links or print these resumes on paper, place them near your computer and use them as a handy guide. Well, let’s get started!
If you want with a tool paste Special (Paste Special) learn how to paste only values or formatting, copy column widths, multiply and divide data by a given number, and add and remove a value from an entire range of cells at once, refer to the article Paste Special in Excel: Values, Formats, Column Widths.
Basic Knowledge of Paste Special
Before using the tool paste Special (Paste Special) You must have something on the clipboard, otherwise, when you try to use these functions, they will be grayed out and not active, as in the picture below.
First of all, you must copy the data to the clipboard, otherwise you will not be able to use the function Paste special. The figure shows that on the Ribbon and in the context menu, the tool paste Special (Paste Special) not active:
First you must select and copy any information. After this action, Excel stores the copied data in the clipboard. Now you can use Paste special. There are two ways to call this function. Both open a dialog paste Special (Paste Special), which provides access to a whole range of useful settings (see the figure below).
Use one of the following methods:
- On the Advanced tab Home (Home) click on the small triangle under the word paste (Insert) and from the drop-down menu select paste Special (Special insert).
- Right-click, and then from the context menu select paste Special (Special insert).
Note that after you have copied the data, Paste special becomes available.
Convert columns to rows (or vice versa)
Let’s leave the basics behind and let’s cheer up a little. In the following example, we will use a table created to control calories during a diet. Let’s say the table suits you, but you don’t like the fact that the product names are arranged vertically. I would like to arrange the days of the week vertically, and the product names in a row. In other words, you want to transpose this data.
To do this, select the data, copy it, put the cursor in the desired cell, open the tool menu paste Special (Paste Special) and select Transpose (Transpose) – all this is shown in the images below. See how easy it is to make a table the way you want? Imagine all the possibilities that the tool gives Transpose (Transpose).
First example of table transposition:
Another use of transposition that I really like is copying data from some source and adding it to the current table. This is a great way to bring together and reconcile data from two different sources. All data will look the same, and you can easily customize the formulas.
For example, you get this template (see below) provided by the company to fill out an expense report when attending a conference.
However, at the conference you attended, an electronic report was provided in the following form:
Just copy the required information (in our case B1:B11), place the cursor in the cell where you want to paste this information (cell C3 in the template), click paste Special (Paste Special), and then select the option Transpose (Transpose). Here’s what happens:
Excellent! Now all the information looks the same and you can keep adding rows for all the trips you make.
Transpose – briefly about the main thing
- Select data.
- Copy the selected data. Team Cut (Cut) won’t let you use Paste specialso use the command Copy (Copy).
- Place the cursor in the cell where you want to paste the data.
Note: Make sure there is enough space to insert the data. Don’t highlight an entire row or column unless you have enough free space. Make sure that new data will not be overwritten by existing ones that you need.
- Press paste Special (Special insert). This can be done in 2 ways:
- Right click and select from context menu paste Special (Special insert).
- On the Advanced tab Home (Home) under command paste (Insert) click on the small triangle and from the menu that opens select Payou are Special (Special insert).
Both methods will open a dialog box paste Special (Paste Special), in which you will find many useful settings.
- Check the option box Transpose (Transpose).
Removing hyperlinks (a lot and quickly)
It was fun! Now let’s try another interesting function special insert. This trick is especially useful when you want to paste hyperlinked text. Hyperlinks sometimes come to hand while working with data in Excel. Notice the blue underlined text in the image below. Each cell in a column A contains a hyperlink. Each time you click on a cell, the computer opens a hyperlink. You can right click on each cell and select command Remove hyperlink (Remove hyperlink), but it will take forever. Imagine if your table contains 2000 rows and 1000 columns.
Instead, you can use the special insertto remove all those hyperlinks at once. Ready? Select all cells from which you want to remove hyperlinks and copy them. Place the cursor in a new cell.
You can try to paste the copied data into the same cells, but in this case, Excel may retain the blue color and underline. If this happens, just clear the format.
Press paste Special (Special insert). In the dialog box that appears, select Values (Values) and press OK. After that, all hyperlinks will be removed, and you can move the data to its original location. Very simple, right? Look at the pictures below for a step-by-step implementation of this operation.
Quick removal of hyperlinks – briefly about the main thing
- Select all cells from which you want to remove hyperlinks.
- Copy them.
- Place the cursor in the cell where you want to paste the copied data.
- Press paste Special (Special insert).
- Select item Values (Values).
- Press ОTO.
- Cut and paste cells wherever you want.
Skip empty cells
Here is another example (see below). Let’s say I have a list of family members’ birthdays and want to replace the family member’s name with their real name (from my family contacts list). You could copy and paste each cell individually, but that would take a very long time (especially if the list is long). Let’s try to do this with special insert.
Regular command paste (Insert) won’t help here. Why? – can be seen in the figure below. If you copy the information (A6:A19) and paste it into cell E4, then these sticky blank cells from the column A will overwrite the data in the column Ethat you would like to keep. Would you like to know a way to skip empty cells?
To do this, copy cells A5:A19, then place the cursor in the first cell of the area where you want to paste the copied data (E4). Next click Easter Special (Paste Special), check the options Skip Blanks (Ignore empty cells) and click OK. Voila! You have successfully copied data from a column A into a column Esaving all the necessary data. Great job! This is a simple example, but it shows all the possibilities that the tool opens up for you and your tables. Skip Blanks (Ignore empty cells).
Skip empty cells – briefly about the main thing
- Select the data you want to copy.
- Copy them.
- Place the cursor in the starting cell of the area you want to copy to.
- Press paste Special (Special insert).
- Выберите Skip Blanks (Ignore empty cells).
- Press OK.
Now you know the basic principles of the tool Paste special and some cool and super cool tricks. You can transpose, remove hyperlinks, and skip blank cells.