Tutorial – All Excel tricks to Copy-Paste

Basic copy-paste techniques

The action of copying a cell (content, format or formula) is so common that there are several techniques for doing it. The first technique is to use the Ribbon:

Select the cell you want to copy.
Then click on the Copy icon (circled icon).

You will see a moving rectangle with dotted lines around your active cell. This means your cell has been copied and is waiting to be pasted.

Select the cell where you want to paste the contents of the first cell.
Click on the Paste button.

As you can see, the content, format, and alignment have been copied to B7. However, copy-paste doesn't copy the column widths.

Shortcuts for faster copying

Faster and easier than using the ribbon, you can use the Ctrl + C / Ctrl + V shortcuts you're probably already familiar with:

Ctrl + C: Copy the contents of the selection.
Ctrl + V: Paste the contents of the clipboard (the name given to the memory area that stores your data).
But Excel has its own tool: the fill handle (the square at the lower-right corner of the active cell).

The fill handle option in the active cell

If you want to copy data to adjacent cells (below or to the right), click and drag:

Move to the bottom-right corner of your cell. Your mouse cursor will change to a thin-rimmed cross.
Click and stretch your selection.
When you release the mouse button, all the cells are copied.

Copying a formula

What happens when you copy a cell that contains a formula? Excel automatically adjusts the cell references according to the direction you copy in.

For example, we have the initial formula Total = Price * Quantity (i.e. C2*D2).
When you copy it downward, the row references change automatically.

This way, the formulas in the following rows are always correct 😉.

Now, if you copy to the right, the column references will change:

Copy formula with reference to the right

Worked example: copying a formula that references the row above

The formula in C3 adds cell B3 and C2. If you copy this formula down to C4, what will the references become?

Following the same rule as above — copying down shifts every row reference by one — the formula in C4 becomes =B4+C3.

Paste Special

There's nothing secret or mysterious about this term (don't worry 😉).

As we saw earlier, a normal copy-paste copies the content, the format, and the formulas. But sometimes you don't want to copy everything — copying the color or the borders can do more harm than good.

That's why Excel lets you paste only certain aspects of the source cell.

After copying your cell, you can open the Paste Special options from the Ribbon.
If you haven't copied a cell first, you won't see all these options.

Paste Special: Formula

As the name suggests, only the formula in the source cell will be copied, not the format (no color or border).

In the first situation, a standard copy-paste overwrites the formatting.
In the second case, the Paste Special Formula option copies the formulas while keeping the existing layout.

Discover another trick to paste in formula in this article.

Paste Special: Values

In certain situations, you don't want to keep the formula — just the result.

For example, we used the UCASE function to capitalize the names in column A. The problem is that the capitalized names in column B are the output of that function.
To keep ONLY the result, you need to convert the formulas in column B into values.
Pasting as values is essential here, to keep the result and not the formula.

Paste formatting

Here, we want to copy the format of the source cell. This is quickly done with the "Format Painter" tool.

Worked example: which paste option keeps only the formula?

Which paste option should you use to copy only the formula?

Question which paste special option to use in this situation

As explained above, it's the Formula option in Paste Special: it copies the formula only, leaving the existing formatting untouched.

Creating lists of numbers

As we just saw, the fill handle lets you copy. But you can also use it to easily create a list of numbers. Here's the trick:

Start by writing the first value.
In the cell just below it, write a second number.
Select the two cells.
Use the fill handle to extend the series of numbers.
Excel will keep the same gap between each number in the series.

Creating a series of dates

Even more useful is using the fill handle with dates.

When you have a date in a cell, Excel automatically expands the days with the fill handle.
If you extend the series using a right-click drag, you get extra options, like weekdays.
If you enter two values with a gap of one month or one week, Excel keeps that gap and correctly handles month-end or year-end.

You now have every trick to copy-paste efficiently in Excel — from the Ribbon and keyboard shortcuts to the fill handle, formula references, and the Paste Special options. With a bit of practice, these reflexes will save you a lot of time in your day-to-day spreadsheets.

Continue learning

Keep building your Excel skills with the rest of this tutorial series: