Excel has several built-in paste functions that can help you avoid re-doing the same work over and over again. As the saying goes, there’s no need to reinvent the wheel!
In this post, I’ll go through some of the paste special options that you’ll likely need for your day job.
Sounds good? Then let’s get going!
How To Copy Paste Your Data In Excel
Once you copy your data (CTRL+C), then hit ALT+E, S to bring up the Paste Special menu. Alternatively, you can right-click and click on the Paste Special menu.
Paste Format
You’ve just finished setting up a nicely formatted table with all the only find out you need to do it 5 more times. Never fear, you can use the paste format function to save you time.
You can use the paste special format function, ALT+E, S, T. This will also import your conditional format too. This will apply the formatting from the copied cells to the new cells.
Related article – How To Paste Values In Excel On A Mac
Paste Column Width
Similar to the paste format, you can also bring across column widths. There’s no need to go back and forward, checking the column width to the last pixel. Instead, paste column width with ALT+E, S, W.
Paste Formula
This function is very useful when you want to copy blocks of formulas to a new range of cells, without bringing across the formatting as well. Otherwise, you could just drag the formulas or a straight copy-paste which will paste everything from the original source.
Pro tip – make sure you anchor your cell references properly before pasting formulas. Otherwise, it might result in formulas referencing blank cells!
Paste And Apply Maths
When you copy data, you can also quickly apply simple math operations to the pasted range. For example:
You can copy A2 to A5, and paste it to B2 to B5 and this would result in 2, 11, 18, and 10. The drawback of this paste is that it wipes the original data. I find that I use this mostly for model flags or converting figures from negative to positive (i.e. multiply by -1).
Skip Blanks Cells
This is useful for cleaning up data with lots of blank cells. Instead of using a filter or formula, you can use the shortcut ALT+E, S, B. This will remove any blank cells in a range when the data is pasted. Remember, you can combine this with other paste special options like value, formula, comments, etc.
Take only the good stuff!
Related article – How To Fill Blanks With Value Above In Excel
Paste Comments
You can copy comments from one cell to another and avoid re-typing or pasting text repetitively. The shortcut is ALT + E, S, C.
I can honestly say I’ve used this particular option less than a handful of times in my career, but just an FYI that it’s available!
Paste Validations
Instead of re-creating validation lists, you can copy-paste from an existing list. This option won’t import the selected option from the original source either. The shortcut is ALT+E, S, N.
Interesting read – How To Format Yes Or No In Excel
Transpose Your Data
The last one I’d like to mention is the copy-paste transpose function. If you have data running down a column and need to transpose to go across the row, you can use the Paste Special Transpose function.
How To Paste Cells In Reverse Order In Excel
Unfortunately, Excel does not have an in-built function to reverse a list of data in Excel. However, this can be done with a simple formula.
With data in range A1:A10, enter this formula in cell B1 and copy it down =INDEX($A$1:$A$10,COUNTA(A1:A$10),1)
Alternatively, you can also reverse the list with a filter. Change it from ascending to descending (or vice versa) and then you can copy-paste.
Thanks for reading and I hope you found this helpful!
You may also be interested in:
