There are 3 ways to copy formulas without the formatting of your cells when you do a copy-paste
- Paste-Special Formula
This is the most used technique. After copying the cell, you paste it with this option
- The fill handle and then use the options
When you use the fill handle, you have paste options when you release the button of the mouse
- The fill handle with the right-click
You can use the fill handle with the right-click of the mouse to display options
What is the problem with the fill-handle?
Copy-paste is a very common task in Excel, mainly when you use the fill handle. However, when you use this tool, you copy the contents of the cells AND the formatting, with the risk of destroying the existing format.
For example, let’s copy the formulae in B7 and B8 with the fill handle.
Sure, you have copied the formulae but also the formatting of cells B7 and B8. This is not good 😡
How to copy formulas without formatting?
To avoid this situation, Excel proposes three techniques
Technique 1: Copy Paste Special – Formula
The paste special tool technique allows you to paste only one element of your source, such as formulae, formatting, comments, column width, ….
- Start by copying your cells (here B7 and B8) with the keyboard shortcut Ctrl + C
- Select the cells where to paste these formulas (C7 to F8)
- Click on the icon Home > Paste > Formula
Now, only the formulae are copied, and not the formatting.
Technique 2: The fill-handle with options
Here, we will use the fill-handle, but we will correct the error seen with an option.
- When you release the mouse button, and the formatting is overwritten (initial situation).
- But maybe you don’t have the tooltip option that appears at the end of your selection.
- When you click on this icon, you see different paste options
- Select the option Fill Without Formatting
- Only formulae are copied with the fill handle 😀
Technique 3: The fill-handle with the right-click
The last solution copies only the formula, the fill-handle, with the mouse’s right button.
- Select your cells to copy
- Use the fill handle to extend your selection, but use the right button of the mouse.
- When you release the button of the mouse, you display more options
- Here again, you select Fill Without Formatting
- Only formulae are copied, and not the formatting.
