10 Excel Paste Special Tricks to Make you a Pro

0 Flares Filament.io 0 Flares ×

To be an Excel pro you must be a master of Paste Special.

Copy and paste is one of the most common tasks a computer user performs daily. Paste Special in Excel allows you to specify what elements of the copied item get pasted. It also allows you to perform operations on values and other cool stuff.

This blog post explores 10 Excel Paste Special tricks that an Excel pro needs to know.

Watch the Video

1. Convert a Positive Value to Negative

The first trick is to convert a positive value to negative. When copying or importing data into Excel, negative values are often not represented in the correct way.

To quickly convert them; type -1 into a cell on the spreadsheet. Then copy this value, select the range of cells you want to convert, right click and select Paste Special, Values and Multiply from the operations area.

Convert positive values to negative

2. Transpose a Data Range

Excel is set up to work with a header row and the data down columns. By setting up your spreadsheets up in this way your Excel life will be easier and filled with happiness.

However you may receive spreadsheets from other sources that have the data arranged along rows.

Data arranged in rows

Fear not as the Paste Special feature can transpose this data in a jiffy.

Select the range of cells you need to switch from rows to columns (or vice versa). Take a copy by pressing Ctrl + C.

Right click where you want to put the transposed range and select Transpose from the paste options.

Transpose a range from rows to columns

3. Remove a Formula from a Cell, but Keep the Result

After using formulas to calculate cell values, you may not need the formula any more. This is typical when creating spreadsheets that you plan to forward to a customer.

By removing the underlying formula, others cannot see the calculation used. It will also speed up your spreadsheet as it reduces the number of calculations your spreadsheet needs to perform.

If you don’t need the formula anymore, remove it. Paste Special makes this easy.

Copy the range of cells that contain the formulas. Then right click, select Paste Special and Values or Values and Number Formatting.

In the image below the Values option is being shown on the right click shortcut menu.

Remove formulas using paste values only

4. Repeat a Column Width

A relatively unknown paste option is the one to copy the width of a column. This can be very helpful to quickly and effortlessly make multiple columns the same width.

Select a cell in the column with the width you wish to repeat and press Ctrl + C for a copy.

Then select the columns you want to paste onto, right click and select Paste Special, and select Column Widths.

5. Convert Text to Number

When downloading or importing data from other sources, it can need some cleaning before we go to work on analysing it.

A common scenario are numbers being stored as text. We can get Paste Special to solve this problem by multiplying the values by 1.

Type the number 1 into a cell on the worksheet. Copy it, select the cells you need to convert, then right click and select Paste Special. Select the Values and the Multiply option.

Convert text to numbers using Paste Special

6. Paste Chart Formatting

Paste Special is not only reserved for handling cell values, it can also paste chart formatting. This can help you create consistent charts in reports, and also save lots of time.

Select the chart containing the formatting you want to repeat and press Ctrl + C to copy it.

Now right click on the chart you want to apply the formatting to, click the list arrow to the Paste button on Home, and select Paste Special and choose Formats from the options.

Paste chart formatting from one to another

7. Convert to Percentage

If you have a bunch of values that you need to convert into percentages, Paste Special is here to help.

Type 100 into a cell on the spreadsheet. Then copy this value and select the range of values you want to convert.

Right click and select Paste Special, Values and select the Divide operation.

The values can then be formatted as a percentage.

8. Add Ten Percent onto a Value

By using the operations area of the Paste Special window, you can perform all kinds of calculations.

Another common one would be to add, or subtract a percentage from a value.

Lets say we want to add 10% onto a value.

Type 110% into a cell. Copy that cell value and select the range you want to increase by 10%.

Right click and select Paste Special. Then select Values and the Multiply operation.

9. Add Values to Existing Cell Values

Possibly the most useful day to day paste operation would be to add some values onto a range of existing values. For example, this weeks sales onto last weeks sales to total them.

Select the new range of values. Take a copy with Ctrl + C. Select the existing range of values, right click, select Paste Special, Values and Add.

10. Skip Blanks in a Range

When pasting values over existing values, the range you are copying from may contain blanks. And you may not wish for these blanks to replace any current values in that cell.

This can be avoided by selecting the Skip blanks option in the Paste Special window.

Skip blanks when performing Paste Special

More Useful Excel Tricks

5 thoughts on “10 Excel Paste Special Tricks to Make you a Pro

Leave a Reply

Your email address will not be published. Required fields are marked *