There’s a moment in everyone’s experience with Excel when you are both fairly comfortable using the tool and at the same time recognize just how much more there is to learn! This is often at the point where you want to take on a new task and realize that tools you need aren’t already in your toolbox. Sometimes it happens when you do a repetitive task and realize that there has to be an easier way that you’re just not seeing.

One of those hidden time-saving gems buried in Excel is the Paste Special tool. Here are four less-known features that will save you time and – in the right circumstance – just might save the day.

Transpose Columns and Rows

You are comfortable with entering data, creating formulas and applying different forms of analysis. If data is in a table format, you’re good. And then…. you realize that you really needed your columns to be rows and your rows to be columns. Maybe you have to work with data copied from an external source, or maybe you just didn’t get it right the first time. Either way, you’re stuck until it’s fixed. Now what?

Before you spend an hour copying cells into a new table, try Copy/Paste Special. The Transpose option has just what you need, and you’ll use it a lot.

1. Select the data you need to transpose
2. Copy to the clipboard using Ctrl+C or Right-click > Copy
3. Place your curser where you want the transposed data to go
4. Right-click and select Paste Special
5. In the dialog, put a check by the Transpose checkbox
6. Click OK

Your row and columns are swapped and you can get on with your analysis. This will come in especially handy when you advance to PivotTables and are working with complex analysis where the formatting of your data is key.

Skip Blanks

You are familiar with how to copy and paste cells into new locations, but have just run into a new situation: You have a column of new information that needs to be incorporated into an existing column…but not every value has changed. Look at this example:

You have Old Product Numbers in column A, but only a few rows in column B need to be updated into column C. You need a way to paste ONLY the cells that have information in them and leave the rest alone.

Paste Special can do this.

1. Select the range of cells to copy, including the blanks
2. Hit Ctrl+C or Right-click > The selection’s borders become a dotted marquis letting you know it is the active copied range
3. Select the range of cells you want to update
4. Right-click and select Paste Special
5. In the dialog, put a check by the Skip Blanks checkbox
6. Click OK

Excel pastes the source cells over the destination cells if there is any data, and leaves the destination cell the same if the source cell was blank. This is a much faster way to copy data than from cell to cell when there are blanks in a column.

Change Negative Numbers to Positive (and vice versa)

You are comfortable importing or copying data from multiple sources and combining them into larger sheets of data for analysis. And then… you realize that your sources are using different accounting standards and you need to convert an entire column of numbers from negative to positive, or vice versa. Here is an example:

You have imported bank statement data and want to combine it with credit card data to get a complete report of all transactions your business spent money on in a given month. The bank uses positive numbers for deposits and negative numbers for expenses. The credit card, however, uses positive numbers for purchases and negative numbers for payments. You want to change the credit card purchases to negative numbers so you can total the amount you have spent.

To convert your negative numbers to positive numbers, multiply them by negative one using the Paste Special Multiply feature:

1. In a helper cell, type -1
2. Select the helper cell and hit Ctrl+C or Right-click > The cell borders become a dotted marquis letting you know it is the active copied cell
3. Select ALL the cells you want to convert
4. Right-click and select Paste Special
5. In the dialog, click the Multiply radio button in the Operation section
6. Click OK

Excel will multiply all selected cells by the value in your copied cell, changing them only by their negative or positive value. This is far easier than retyping or creating an entire helper column to multiply by -1.

Paste Values

You have really dug into Excel and created beautiful sheets full of formulas. Then, you run into a case when you want to build upon or share your results without referencing the entire worksheet. You might want to copy just the results of a calculation into a new workbook so that others can see your data without giving away a proprietary method of calculating it, or being forced to include additional data. Consider this example:

All of the green numbers are source data. Everything in black is a calculation. By default, Excel will copy the formulas and try to adapt cell references to the pasted location. So, if we want to share only the data in G & H columns, for example, we would get errors – zeroes – because the copied formula does not have the original B2 column to pull from in the new sheet

To copy and paste ONLY the values, or results, of the cells:

1. Select the cells to copy and hit Ctrl+C or Right-click and choose The selection’s borders become a dotted marquis letting you know it is the active copied range
2. Place your curser where you want the data to go
3. Right-click and select Paste Special
4. In the dialog, click the Values radio button in the Paste section

Click OK

Only the results will be copied into the new location. Your destination results will no longer be tied to the original data by a formula, so do not use this method if you wish it to reflect changes made to the source data. But, you now have a clean table of just numbers to build new analysis upon.

Spend some time learning all the tools that are wrapped into Paste Special. You might find something hidden there that saves you a lot of time!