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:
- Enter 1% in an empty cell and copy it.
- Select the incorrectly entered percentages.
- 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%.
- Just type 1.05 in a cell, copy it,
- Select the
sales values,
- Open Paste Special (CTRL+ALT+V), choose Multiply,
- 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:
- Enter 12 in an empty cell and copy it.
- Select the annual values.
- Use Paste Special with the Divide option (CTRL+ALT+V),
- 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.
Learn With Us
