How to Copy and Paste Formulas in Microsoft Excel

Along with the powerful formula features available in Microsoft Excel is the ability to reuse formulas without having to retype them. Ready to save some time with your spreadsheets? Here’s how to copy formulas in Excel using a variety of methods.

Absolute Versus Relative References When Copying Formulas

Before you begin, it’s important to note the cell reference style you use when you copy and paste a formula in Excel.

If you use absolute cell references in the copied formula, these remain unchanged when you paste the formula. This means that you’ll need to adjust the pasted formula to accommodate the new cell references. Absolute references contain a dollar sign ($) before the column, row, or both in the cell reference. For example, $A$1 uses an absolute column and row.

If you use relative cell references in the copied formula, these automatically adjust to accommodate the new cell references when you paste the formula. Relative references do not contain a symbol before the column, row, or both. For example, A1 uses a relative column and row.

Let’s look at an example.

Here, we have a formula in cell C1 to add the values in cells A1 and B1. We want to copy this formula to cell C2 and add the values in cells A2 and B2.

If you use absolute references as shown in the formula, those references do not automatically change to reference the new values. You must manually adjust the references in the formula to obtain the correct result.

Using the same example with relative references instead, the references automatically update to use the correct cell values.

Note: Keep in mind that although Excel is smart enough to change relative cell references for you, this may not work as expected if you’re copying the formula to non-adjacent cells or across different worksheets.

Also, remember that no matter which cell reference style you use, you should confirm the copied formula provides the results you expect after you paste it in the destination cell. If something isn’t right, take a look at ways to fix a formula problem in Excel.

Copy Formulas With Keyboard Shortcuts

If you’re a fan of using Excel keyboard shortcuts to perform actions quickly, then this is an ideal method when you want to copy and paste formulas as well as formatting.

  1. Select the cell containing the formula and use Ctrl + C on Windows or Command + C on Mac to copy it.
  1. Go to the cell(s) where you want to paste the copied formula and use Ctrl + V on Windows or Command + V on Mac to paste it.

Tip: You can paste the same formula to multiple cells by selecting them all before you use the paste keyboard shortcut.

Copy Formulas With the Ribbon and Paste Special

Keyboard shortcuts aren’t the only methods for copying and pasting formulas in Excel. You can use the built-in tools on the ribbon which include a Paste Special option if you want to include formatting in the operation.

  1. Select the cell containing the formula and head to the Home tab.
  2. Choose Copy in the Clipboard section of the ribbon.
  1. Go to the cell(s) where you want the copied formula.
  2. On the Home tab, use the Paste button to paste the formula and associated formatting.
  1. Alternatively, you can select the arrow attached to the Paste button to do one of the following:
  • To paste only the formula without the formatting, pick Formulas.
  • To paste the formula and its number formatting, pick Formulas & Number Formatting.
  • To paste the formula, number formatting, font, border, and shading, pick Keep Source Formatting.

Copy Formulas With the Shortcut (Right-Click) Menu

For a copy and paste method that falls between keyboard shortcuts and ribbon buttons, you can use the shortcut, or right-click, menu.

  1. Select the cell containing the formula and right-click.
  2. Choose Copy near the top of the shortcut menu.
  1. Go to the cell(s) where you want the copied formula, right-click, and move to the Paste Options section.
  2. Choose Paste to paste the formula and formatting.
  1. Alternatively, move to Paste Special and pick Formulas, Formulas & Number Formatting, or Keep Source Formatting as described in the section above.

Copy Formulas With Autofill

One convenient way to copy formulas to adjacent cells in Excel is using the Autofill feature. You may have used this tool to create a series of dates or numbers in your sheet, but it works just the same for copying formulas in neighboring cells.

  1. Select the cell containing the formula and grab the fill handle which is the square in the bottom-right corner of the cell. You’ll see your cursor change to a plus sign.
  1. Drag down or across depending on where you want to paste the formula.
  1. Release and you’ll see your formula copied and pasted to the new location(s).

Copy, Paste, Complete

The nice thing about having different ways to copy and paste Excel formulas is that you can use what you’re most comfortable with at the time. Plus, you can combine some of the methods. For instance, you can copy with a keyboard shortcut and paste with a right-click. Let us know which method you find easiest.

Now that you know how to copy formulas in Excel, check out our tutorial for copying a worksheet or workbook too.



Source : How to Copy and Paste Formulas in Microsoft Excel

Comments

Post a Comment

Popular posts from this blog

How to Add or Remove a Secondary Axis in Microsoft Excel Charts

Image
When you have a chart with mixed data types or a dataset that varies broadly between series, consider using a secondary axis in Microsoft Excel. With it, you can display the data you need without overwhelming or confusing your viewers. When to Use a Secondary Axis As mentioned, if you have different data types or your series amounts widely vary, these are good times to use a secondary axis. For instance, you can see that the column chart below doesn’t accurately depict the visitors and sales in our dataset. There’s a large variance in the sales amounts, making it hard to get a decent picture of the number of visitors per month. Plus, the sales are in dollar amounts, whereas the visitors are basic number values . If your chart would benefit from a secondary axis like this one, here’s how to add one as well as remove a secondary axis later if you change your mind or your data. How to Add a Secondary Axis in Excel To add a secondary axis to your Excel chart, you’ll use the combo ...
Read more

How to Use and Customise the Status Bar in Microsoft Excel

Image
Excel’s status bar is one of the hidden gems (and an unsung hero) that makes it the default spreadsheet software you turn to. It quietly resides at the bottom of the Excel window, often overlooked but brimming with valuable information and customization options. In this article, we will look at it and show you how to take advantage of it. Excel Status Bar Default Options Even before you customize the Excel status bar, it’ll display some helpful information and give you access to some useful functionalities. The common Excel options you can find as default on the status bar are: Cell Mode Macro Recording Tool Accessibility Check Page Number Statistics/Mathematical Options Worksheet Views Zoom Options Let’s see what each of them does and how you can use them. Cell Mode At the leftmost side of the Excel status bar, you’ll find the cell mode indicator. It’ll inform you about the status of the currently selected cell. There are four different status modes: Ready . It ...
Read more

What Is Windows AI-Powered Copilot?

Image
Microsoft has released its brand-new artificial intelligence (AI) companion, Microsoft Copilot. Previously known as Bing Chat, Copilot has a series of new abilities that can help you get more done in less time. In this article, we’ll provide a brief description of each of Microsoft’s new AI copilots, then we’ll focus on the Windows 11 copilot and what it can do to boost your productivity. What Is Microsoft Copilot? Microsoft Copilot is an artificial intelligence chatbot you can ask to perform different tasks, including generating text and images and answering general questions. Microsoft’s plan is to have a Copilot in each of its apps to provide assistance and improve user experience across the Microsoft ecosystem. Here are the different Copilots that are currently available or slated for future release: Windows 11 Copilot: Available for free as a preview in North America, Asia, and South America, Windows 11 Copilot lets you perform tasks, get answers, and create AI-generate...
Read more