Contents
In this article, we will show you some more useful options that the tool is rich in. Paste special, namely: Values, Formats, Column Widths and Multiply/Divide. With these tools, you can customize your spreadsheets and save time formatting and reformatting data.
If you want to learn how to transpose, remove links, and skip blank cells with the paste Special (Paste Special) refer to the article Paste Special in Excel: skip blank cells, transpose and remove links.
Paste only values
Take, for example, a spreadsheet showing profits from sales of cookies at a bakery charity sale. You want to calculate how much profit was made in 15 weeks. As you can see, we used a formula that adds up the amount of sales that were made a week ago and the profit made this week. See in the formula bar =D2+C3? Cell D3 shows the result of this formula − $ 100. In other words, in a cell D3 value is displayed. And now it will be the most interesting! In Excel using the tool paste Special (Paste Special) You can copy and paste the value of this cell without formula or formatting. This capability is sometimes vital, and I’ll show you why later.
Suppose, after you have been selling cookies for 15 weeks, you need to submit a general report on the results of the profits received. You may want to just copy and paste the line that contains the grand total. But what happens if you do this?
Oops! Is this not at all what you expected? As you can see, the usual action copy and paste as a result copied only the formula from the cell? You need to copy and paste the value itself. So we will do it! We use the command paste Special (Paste Special) with option Values (Values) so that everything is done right.
Notice the difference in the image below.
Applying Paste special > The values, we are inserting the values themselves, not the formulas. Great job!
Perhaps you have noticed something else. When we used the command paste Special (Special Paste) > Values (Values), we’ve lost the formatting. See how the bold and number format (dollar signs) weren’t copied over? You can use this command to quickly remove formatting. Hyperlinks, fonts, number format can be quickly and easily cleaned up and you are left with just the values without any decorations that might get in the way in the future. It’s great, right?
In fact, Paste special > The values is one of my favorite tools in Excel. It is vital! I am often asked to create a spreadsheet and present it at work or community organizations. I’m always worried that other users might mess up the formulas I’ve entered. After I’m done with formulas and calculations, I copy all my data and use paste Special (Special Paste) > Values (Values) on top of them. Thus, when other users open my spreadsheet, the formulas can no longer be changed. It looks like this:
Pay attention to the contents of the formula bar for the cell D3. It no longer has a formula =D2+C3, instead the value is written there 100.
And one more very useful thing with regards to the special paste. Let’s say I want to keep only the bottom line in the cookie giveaway profit spreadsheet, i.e. delete all lines except week 15. See what happens if I just delete all these lines:
This annoying error appears #REF! (#REF!). It appeared because the value in this cell is calculated by a formula that refers to the cells above. After we deleted those cells, the formula had nothing to refer to and reported an error. Use commands instead Copy (copy) and paste Special (Special Paste) > Values (Values) over the original data (as we did above), and then remove the extra lines. Great job:
Paste Special > Values: Highlights
- Highlight data
- Copy them. If the data is not copied, but cut, then the command paste Special (Paste Special) will not be available, so be sure to use copy.
- Select the cell where you want to paste the copied data.
- Press paste Special (Special insert). It can be done:
- by right-clicking and selecting from the context menu paste Special (Special insert).
- tab Home (Home), press the small triangle under the button paste (Insert) and from the drop-down menu select paste Special (Special insert).
- Check option Values (Data).
- Press OK.
Insert only formats
Paste special > Formats this is another very useful tool in excel. I like it because it allows you to easily customize the appearance of the data. There are many uses for the tool Paste special > Formatsbut I will show you the most remarkable. I think you already know that Excel is great for working with numbers and for performing various calculations, but it also does a great job when you need to present information. In addition to creating tables and counting values, you can do all sorts of things in Excel, such as schedules, calendars, labels, inventory cards, and so on. Take a closer look at the templates that Excel offers when creating a new document:
I came across a pattern Winter 2010 schedule and I liked the formatting, font, color and design in it.
I don’t need the schedule itself, much less for winter 2010, I just want to remake the template for my purposes. What would you do in my place? You can create a draft table and manually repeat the template design in it, but this will take a very long time. Or you can delete all the text in the template, but that will also take a lot of time. It’s much easier to copy the template and do paste Special (Special Paste) > Formats (Formats) on a new sheet of your workbook. Voila!
Now you can enter data, keeping all formats, fonts, colors and design.
Paste Special > Formats Highlights
- Highlight the data.
- Copy them.
- Select the cell where you want to paste the data.
- Press paste Special (Special insert).
- Check option Formats (Format).
- Press OK.
Copying column widths to another sheet
Have you ever wasted a lot of time and energy circling around your table and trying to adjust column sizes? My answer is yes, of course! Especially when you need to copy and paste data from one table to another. Existing column width settings may not work, and even though the automatic column width setting is a handy tool, in places it may not work as you would like. Paste special > Column Widths is a powerful tool that should be used by those who know exactly what they want. Let’s take a look at the list of the best US MBA programs as an example.
How could this happen? You can see how neatly the column width has been adjusted to fit the data in the figure above. I copied the top ten business schools and put them on another sheet. See what happens when we just copy and paste the data:
The content is pasted, but the column widths are far from appropriate. You want exactly the same column widths as the original worksheet. Instead of configuring it manually or using autofit column width, just copy and paste paste Special (Special Paste) > Column Widths (Column Widths) to the area where you want to adjust the column widths.
See how simple it is? Although this is a very simple example, you can already imagine how useful such a tool would be if an Excel sheet contains hundreds of columns.
In addition, you can adjust the width of empty cells to format them before manually entering text. Look at the columns E и F in the picture above. In the picture below, I used the tool Paste special > Column Widthsto expand columns. So, without too much fuss, you can design your Excel sheet the way you want!
Paste Special > Column Width Highlights
- Highlight the data.
- Copy the selected data.
- Place the cursor on the cell whose width you want to adjust.
- Press paste Special (Special insert).
- Check option Column Widths (column widths).
- Press OK.
Paste Special: Divide and Multiply
Remember our cookie example? Good news! A giant corporation found out about our charity event and offered to increase our profits. After five weeks of sales, they will invest in our charity, so that the income will double (become twice as much) compared to what it was at the beginning. Let’s go back to the spreadsheet we used to record the profits from the cookie charity sale and recalculate the profits to account for the new investment. I’ve added a bar showing that after five weeks of sales, profits will double, i.e. will be multiplied by 2.
All income starting from the 6th week will be multiplied by 2.To show the new numbers, we need to multiply the corresponding cells of the column C on 2. We can do this manually, but it will be much nicer if Excel does it for us using the command paste Special (Special Paste) > Multiply (Multiply). To do this, copy the cell F7 and apply command on cells C7: C16. The totals have been updated. Great job!
As you can see, a tool Paste special > Multiply can be used in a variety of situations. The same is true with paste Special (Special Paste) > Divide (Divide). You can divide an entire range of cells by a specific number quickly and easily. Do you know what else? With help paste Special (Special Paste) with option Add (Add) or subtract (Subtract) You can quickly add or subtract a number.
Paste Special > Divide/Multiply: Highlights
- Select the cell containing the number you want to multiply or divide by.
- Copy the selected data.
- Place the cursor on the cells where you want to multiply or divide.
- Press paste Special (Special insert).
- Check option Divide (Split) or Multiply (Multiply).
- Press OK.
So, in this tutorial, you have learned some very useful features of the tool. Paste special, namely: they learned to insert only values or formatting, copy the width of columns, multiply and divide data by a given number, and also add and remove a value immediately from a range of cells.