What is the problem with the fill-handle?
Copy Paste, is a very common task in Excel, especially when you use the fill-handle. But, 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 we copy the formulae in B7 and B8 with the fill-handle.
Sure, you have copied the formulae but also the formatting of the 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 right button of the mouse.
- 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