5 Things You Should Know About Paste Special
We’d like to cover five things you should know about paste special. But, hey, first things first, you should know about Paste Special itself!
The last time around, we spoke about the Transpose formula, and its miraculous ability to send you home in next to no time. Well, as it turns out, there’s an even easier way to warm yourself by the hearth at a reasonable hour, and it’s called Paste Special.
Paste Special is CTRL-V’s big brother, and boy does it pack a punch. In what’s to come, we’ll walk you through five things that Paste Special does in its own inimitable way. Try it out for yourself, and watch your productivity metrics zoom.
1. Transposes, fast: Now, I’ll be the first to admit that the Transpose formula is the cat’s whiskers, but I’ll also be the first to wish that there was a simpler way of doing things! And as it turns out, there is – it’s called the transpose option in Paste Special.
And say you want to switch it around to swap the rows and columns… Transpose it, in simpler words. Well, all you need to do is select said table, copy it, and head over to a fresh new range of cells. But (and here’s the trick) instead of simply pasting the table over, right click the first cell, and from the options that appear, choose Paste Special. And from within the pop-up box that appears, choose – you guessed it – transpose.
Easy as pie, isn’t it? Of course, we have one small table as an example here – when you have dozens of table, each with dozens of rows and columns, this feature really is like manna from heaven.
2. Copy just the formats over: Have you ever had that sinking feeling, looking over an Excel file that has the numbers down pat, but doesn’t even come close to the designated formatting? Take it from me, a long time sufferer of many a corporate design handbook, there is nothing quite as mind numbing as applying a standardized set of formats to hundreds of tables in dozens of sheets.
What will make your life easier is the option of copying just the formats over – choose a table on which formatting has been applied, copy it, but while pasting, choose formats only – and voila – borders, colors, fonts and italics et al will be magically transported over.
3. Copy Values: The flip side of it works just as well, really. If you want to copy over just the values, sans formatting, simply choose the Paste Special option of “Values” – this will copy only the meat (the number or the value itself) over, and leave all the fancy trimmings aside. Again, for those in the know, a positive boon, no less.
4. Column Widths: Excuse me if I sound like a kid in a candy shop, but the ability to magically transform column widths to required dimensions without having to squint and thread a needle is enough to make me want to shed tears of joy. Simply choose Column Width while pasting over, and your tables will look remarkably, gratifyingly similar to each other. It will make your manager gratified too – and that’s always a useful thing in December, eh? Holiday bonus?
5. Mathematical Operations, no less: If arrays and formulas are like East and West (and never are they going to meet), then Paste Special is just the thing for you.
Well, what if you need to multiply the first element in the first table with the first element in the second, and so on and so forth? Rather than having to worry about formulas and arrays and validation and all that, there is a simple way to do this. Simply choose the first table, and copy it. Go over to the second table, choose the first element, right click, choose paste special, and employ Multiply. Ta-that’s-all-folks-da!
And along with that, peace of mind, for free!
And if that’s not a bargain, I really don’t know what is.