To be an Excel pro you must master these Paste Special tricks.
Copy and paste is one of the most common tasks a computer user performs daily. Paste Special in Excel allows you to specify which 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 – Excel Paste Special Tricks
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.
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.
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.
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.
4. Repeat a Column Width
A relatively unknown Paste Special trick 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.
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.
7. Convert to Percentage – Paste Special Tricks
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.
Thank you very much Alan for sharing these valuable tips! I appreciate the tricks related to making calculations 🙂
Thank you sir Alan for sharing new trick . please share your
new tricks and teach us smart work.
Awesome!
Thanks Alan
What a great video,
Once again Thanks so much
You’re welcome John, thanks.
I’ve only read the first two items (so far) and they have solved most of my Excel problems! Thanks!
Roger
That is awesome! Thanks Roger.
how to use add operation to add the values in two columns with links
Not sure I understand. Links to where the numbers are copied from, or links from the cell?
The hyperlink from a cell is carried across with the number used in the add operation.