In most cases, you enter a formula in one cell, and then copy it to other cells in the same column or row, to perform the same calculation on a group of data.

I say usually because there can be very specific cases that require special tricks, like copying a range of formulas without changing cell references or entering the same formula in multiple non-adjacent cells.

In this tutorial, we are going to discuss different ways to copy formulas in Excel so that you could choose the one best suited for your task.

As you do this, the cursor will change to a thick black cross.

So, after copying the formula, verify that the cell references have got adjusted properly and produce the result that you want.

As you can see in the screenshot below, the formula is all right - relative to row 4, exactly as it should be.

In most cases, this works just fine, but sometimes it can mess up the existing formats in cells where the formula is being copied to.

A common example is overwriting alternate row shading like in the following screenshot.

But what if you need to copy a formula down a ten-hundred-line sheet Dragging the formula over hundreds of rows does not look like a good idea.

Luckily, Microsoft Excel provides a couple of quick solutions for this case as well.

In other words, as long as you have row headers or any other data in any of the left-hand columns, Excel continues to copy the formula.

So, if your worksheet contains any gaps, you will have to repeat the above process to copy the formula below an empty row.

Unlike the fill handle, Excel tables have no problem with copying the formula across the entire column even if the table has one or more empty rows.

If you prefer a visual way, select the range, go to the Insert tab Tables group on the Excel ribbon, and click the Table button.

Just right-click the table and choose Table Convert to Range in the context menu.

But what if you want to copy your Excel formula in non-contiguous cells or beyond the end of the source data Just use the old good copy paste way.

To copy the formula without formatting, choose an appropriate Paste option on the ribbon or in the right-click menu, as demonstrated in Copying an Excel formula without formatting.

To select non-contiguous cells, press and hold the Ctrl key.

Thats it The formula will get copied to all of the selected cells, and Excel will adjust relative cell references accordingly.

A few other techniques are described in the following tutorial: How to enter the same data into all selected cells at a time.

Excels Copy Paste feature offers even more flexibility with regard to paste options.

Alternatively, right click the cell and select Copy from the context menu, or click the Copy button on the Home tab Clipboard.

For example, you can select Formulas Number Formatting to paste only the formula and the number formatting such as percent format, currency format, and the like.

How To Copy A Formula In Excel For Mac