Excel Paste Special Tricks

Most Excel users rely on the Paste Special dialog box for one thing: pasting values. But did you know there are 16 other Paste Special options that can work Excel magic if you know how to use them?

In this blog, I'll share some of my favourite shortcuts to transform messy data into clean, well-organized spreadsheets while speeding up your workflow.

Watch the Excel Paste Special Tricks Video

Master these Excel Paste Special tricks with our quick reference cheatsheet and try them out yourself with the practice file:

Get the Cheatsheet & Practice File

1. Quick Paste Values with a Shortcut

If you're still using the Paste Special dialog box just to paste values, there's a faster way. Instead of opening the Paste Special dialog, just use the CTRL+SHIFT+V shortcut (for Microsoft 365 users) to paste values directly.

If you're using an older version of Excel, use CTRL+ALT+V to open the dialog box, press V for values, and hit Enter.

2. Convert Whole Numbers to Percentages

Have you ever had percentages entered as whole numbers, making them more difficult to use in formulas?

Let's say you have tax rates that were entered incorrectly as whole numbers like this:

Instead of re-entering everything, use Paste Special to fix this in a snap. Here's how:

1. Enter 1% in an empty cell and copy it.
2. Select the incorrectly entered percentages.
3. Open Paste Special (CTRL+ALT+V), choose Multiply, and press OK.

Voilà! The values are now correctly formatted as percentages.

3. Increase Values with Multiplication

Another powerful use of Paste Special is to increase values by a specific percentage. For example, you can quickly create a sales forecast by increasing current sales by 5%.

1. Just type 1.05 in a cell, copy it,
2. Select the sales values,
3. Open Paste Special (CTRL+ALT+V), choose Multiply,
4. Rename the column to reflect the new forecasted values.

4. Divide to Split Annual Values

Need to break annual amounts into monthly figures? Here's a fast way:

1. Enter 12 in an empty cell and copy it.
2. Select the annual values.
3. Use Paste Special with the Divide option (CTRL+ALT+V),
4. Rename the column to "Monthly Budget." Now, you have monthly amounts derived from annual values in a flash.

5. Quick Add or Subtract Values

Adjusting prices or quantities with a consistent value? Use the Paste Special Add or Subtract functions:

Add values: let's say there's a $2 price increase across all products to cover a levy. Enter 2 in a cell, copy it, and use CTRL+ALT+V with the Add option.

Subtract values: let's say you need to reduce your quantities by 3. Enter 3 in a cell, copy it, and use the Subtract option in Paste Special to update your stock levels.

6. Paste Formulas Without Formatting

If you have a formula to paste but want to keep your table's existing format intact, use the Paste Special Formulas option. This pastes only the formula, avoiding the risk of messing up your formatting.

7. Multiply Formulas

Did you know you can also use Paste Special to multiply formulas? For example, you can add a 10% sales tax by multiplying the formula by 1.1.

Before applying the calculation, the formulas are:

Type 1.1 in an empty cell and copy it, select the selling price cells, and use Paste Special with Multiply.

Excel will update the formula with parentheses around the original formula, multiplied by 1.1:

8. Copy Formats Only

Want to copy formatting but not data or formulas? Select the formatted cells, click the Format Painter icon, and apply the formatting across multiple ranges.

This handy tool even copies conditional formatting rules.

Bonus tip: Double-click the icon to paste the format multiple times, and press Esc when you're done.

9. Skip Blanks

Need to consolidate two tables without overwriting existing data? The Skip Blanks feature is perfect for this.

For example, if you have sales data from two managers, copy the second manager's data, select the first manager's table, and use Paste Special with Skip Blanks. Excel will paste the data while ignoring any blank cells, allowing you to consolidate effortlessly.

10. Transpose Data

Transpose is one of Excel's hidden gems, allowing you to switch rows into columns and vice versa. Just copy your table, use Paste Special with Transpose, and you can even choose to paste values only or include formatting. It's that easy to rearrange your data.

11. Paste Links

If you want to link data between sheets or workbooks without writing formulas manually, try the Paste Link option.

Copy a cell from your data source, go to your destination, and use Paste Special with Link. Excel will create a dynamic link that updates automatically whenever the source data changes.

12. Copy Chart Formatting

Do you spend time perfecting the formatting of one chart and want others to match? You can copy the formatting from one chart to another using Paste Special. Select the outer edge of the chart you want to copy, CTRL+C, select the outer edge of the target chart, and paste the formatting using Paste Special.

This trick saves time and ensures a consistent look across all your charts.

Bonus Tip: Use F4 to paste the formatting to further charts with one click.

Excel Magic at Your Fingertips

Excel's Paste Special is far more powerful than most people realize, offering a range of tricks to speed up your workflow. By mastering these shortcuts, you'll be able to handle complex data manipulations with ease.

If you'd like to master more shortcuts and tricks like this, check out our Excel Expert course and 10x your productivity.