Copy the formula and not the formatting in Excel

Copy the formula and not the formatting in Excel
Last Updated on 15/03/2024
Reading time: 2 minutes

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.

Standard way to use the fill handle to copy cell with formula

Sure, you have copied the formulae but also the formatting of the cells B7 and B8. This is not good 😡

Fill Handle copies the content and the fomatting

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, ....

  1. Start by copying your cells (here B7 and B8) with the keyboard shortcut Ctrl + C
  2. Select the cells where to paste these formulas (C7 to F8)
Select the area where to paste
  1. Click on the icon Home > Paste > Formula
Paste special formula

Now, only the formulae are copied and not the formatting

Only formulae are copied

Technique 2: The fill-handle with options

Here, we will use the fill-handle but we will correct the error seen with an option.

  1. When you release the mouse button, and the formatting is overwritten (initial situation).
  2. But, maybe you don't have the tooltip option that appears at the end of your selection
Fill Handle options
  1. When you click on this icon, you see different paste options
Detail of the fill handle options
  1. Select the option Fill Without Formatting
  2. 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.

  1. Select your cells to copy
  2. Use the fill handle to extend your selection but use the right button of the mouse
  3. When you release the button of the mouse, you display more options
More options with the right button of the mouse
  1. Here again, you select Fill Without Formatting
  2. Only formulae are copied and not the formatting

Leave a Reply

Your email address will not be published. Required fields are marked *

Copy the formula and not the formatting in Excel

Reading time: 2 minutes
Last Updated on 15/03/2024

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.

Standard way to use the fill handle to copy cell with formula

Sure, you have copied the formulae but also the formatting of the cells B7 and B8. This is not good 😡

Fill Handle copies the content and the fomatting

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, ....

  1. Start by copying your cells (here B7 and B8) with the keyboard shortcut Ctrl + C
  2. Select the cells where to paste these formulas (C7 to F8)
Select the area where to paste
  1. Click on the icon Home > Paste > Formula
Paste special formula

Now, only the formulae are copied and not the formatting

Only formulae are copied

Technique 2: The fill-handle with options

Here, we will use the fill-handle but we will correct the error seen with an option.

  1. When you release the mouse button, and the formatting is overwritten (initial situation).
  2. But, maybe you don't have the tooltip option that appears at the end of your selection
Fill Handle options
  1. When you click on this icon, you see different paste options
Detail of the fill handle options
  1. Select the option Fill Without Formatting
  2. 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.

  1. Select your cells to copy
  2. Use the fill handle to extend your selection but use the right button of the mouse
  3. When you release the button of the mouse, you display more options
More options with the right button of the mouse
  1. Here again, you select Fill Without Formatting
  2. Only formulae are copied and not the formatting

Leave a Reply

Your email address will not be published. Required fields are marked *