When you copy cells in an Excel worksheet, the process is generally straightforward. You click and drag to select the cells you want to copy and press Ctrl+C or choose Edit|Copy.
Most of the time you just want to paste cells, so you press Ctrl+V to paste them in. But with a little exploration of the Paste Special dialog box, you may find that you have some other options that can make your life a lot easier.
For example, suppose you wanted to paste the columns from a spreadsheet as rows. Impossible you say? No, actually it’s easy. The trick is to use Paste Special. Copy your column or columns and then click the first cell of the row where you want to place the data and choose Edit|Paste Special. Or you can right-click the cell and choose Paste Special. Either way, the Paste Special dialog box appears. Click Transpose and then OK. Now your column is a row. If you copied data from more than one column, the data from the column farthest to the left would be the first row, and subsequent rows would follow. So for example, column A could become Row 1, column B would become Row 2 and so forth.
Another cool way to use Paste Special is to copy the value of a cell without the underlying formula. Sometimes copying the formula to a new place can make a big mess, so when you copy it use Paste Special and click the Values radio button instead.
Here’s another one. Many times when you copy carefully formatted data from one worksheet to another, you lose the column widths and everything ends up all squished up. Rather than painstakingly reformatting all those columns, paste with Paste Special instead. Choose the Column Widths radio button and you’ll see that the columns come out fine.
You also can have Excel automatically calculate items as you paste. This one is a little tricky but can be useful. For example, if you copy a cell and then paste it onto another cell that has content in it, you can have Excel automatically add the two amounts together. For example, suppose cell A1 has the number 2 in it and cell B1 has the number 3 in it. Highlight A1 and press copy. Now click B1 right-click and choose Paste Special. In the Paste Special dialog box, under Operation click to select Add and click OK. Cell B1 now has a 5 in it. Pretty tricky, huh?