How to Copy SUM Formula in Excel (6 Easy Methods)

While working in Excel, users have to apply various types of functions and their corresponding formulas. One such function is the SUM function of Excel. Applying the formula to each cell of a large data set is tedious and time-consuming. Copying the formula in such cases can save the users’ time. In this article, I will show you how to copy a SUM function formula in Excel.

How to Copy SUM Formula in Excel: 6 Suitable Ways

In this article, you will see six easy ways to copy a SUM formula in Excel. From using the Copy and Paste command of Excel to using AutoFill, keyboard shortcuts, and many other techniques you will see to copy the SUM function.

To illustrate the procedure further, I will use the following data set.

1. Utilize Copy and Paste Command

You will see the easiest and most original way to copy a formula in this procedure. Here, you will see the utilization of the Copy and Paste commands of Excel. Go through the following steps for a better understanding.

Steps:

• First of all, in cell E5, apply the following SUM function formula.

• Press Enter to see the desired result after applying the formula.

• Select cell E5 and right-click on the mouse after selecting it.

• After that, choose the Copy command from the context menu.
• Correspondingly, you can press Ctrl + C on your keyboard for copying the formula.

• Select cell range E6:E10 to show the result after copying.

• Right-click on the mouse after selecting the cell range, and then from the context menu select Paste.
• Instead of selecting the above command, you can press Ctrl + V for getting the same result.

• Finally, you will get the SUM function formula in the desired cell range after performing the previous command.

Read More: How to Copy Exact Formula in Excel

2. Use Keyboard Shortcut

Secondly, you will see me demonstrate the use of two different keyboard shortcuts to copy the same formula. Each of these shortcuts will perform differently depending on their use.

2.1 Press CTRL + Enter Keys

By using this first shortcut, you can write the necessary formula in a single and use it over an entire cell range. Let me show you how to do it in the following steps.

Steps:

• Firstly, select the cell range E5:E10 just like in the following image.
• After selecting the range, E5 will become the active cell here.

• After selecting the cell range, insert the following SUM function formula in cell E5 directly by the keyboard.

• Finally, press Ctrl + Enter on your keyboard at once and it will copy the same formula in the whole data range just like in the following picture.

2.2 Press CTRL + D Keys

The second keyboard shortcut is slightly different than the previous one. Here, you will have to enter the formula in a single cell first and then select the desired cell range to apply the formula through this shortcut. Let’s see the following steps for a better understanding.

Steps:

• Just in like the previous method, apply the same formula in cell E5.

• Select cell range E5:E10 and simultaneously, press Ctrl + D on the keyboard.

• This action will copy the formula to the lower cells of the above cell range.

Read More: How to Copy Formula Down with Shortcut in Excel

3. Imply AutoFill Feature

The easiest way to copy a formula to the multiple cells in a cell range is the implementation of the AutoFill feature of Excel. Here, the feature will help you to copy one single formula to an unlimited number of cells. You will see the detailed brief of using this feature in the following steps.

Steps:

• use the formula in one of the cells of the result column.

• After using the formula, you will find a plus sign at the bottom right corner of the cell after placing the mouse there.

• Drag the plus sign to the lower cells to fill them with the value from the formula of the cell E5.

Read More: How to Copy a Formula in Excel with Changing Cell References

4. Copy SUM Formula to Non-Adjacent Cells

What will you do if you don’t want to copy the formula across a whole data range? Instead, you want to copy it into some non-adjacent cells. The following steps will help you in this regard.

Steps:

• Add the formula in cell E5 and press Enter for the result.

• Right-click on your mouse after selecting cell E5 and from the context menu choose Copy.

• Press Ctrl on your keyboard and select as many cells as you want from your data set by simultaneously clicking the left button on your mouse.

• After completing the selection again right-click on your mouse and this time press Paste.

• This will copy the formula into non-adjacent cells of your preference.

Read More: How to Copy a Formula in Excel Without Changing Cell References

5. Insert Excel Table to Copy SUM Formula

This method or technique to copy the formula demonstrates the usage of the Table feature of Excel. By converting your cell range into a table, you can copy a single formula into multiple cells. To do that, go through the following steps.

Steps:

• Select the whole data range that is B5:E10 for this example.
• Go to the Insert tab of the ribbon, and from the Tables group select Table.

• After filling the Create Table dialog box with the necessary information press OK.

• In the E5 cell of the table, insert the following formula.

• Press Enter and this action will automatically fill the whole data range E5:E10 of the table using the above formula.

Read More: How to Copy a Formula across Multiple Rows in Excel

6. Run a VBA Code

At last, rather than using any keyboard shortcut or copy command, you will see the application of a VBA code in the last method of this discussion. By inserting the proper sequence or the right command while writing the code, you can accomplish many tasks without using any functions or features. So, let’s go through the following steps.

Steps:

• First of all, after creating the result column beside the primary data set, go to the Developer tab of the ribbon.
• From the Code group, select Visual Basic.

• Then, from the Insert tab select Module in the Visual Basic window.

• Copy the following code into the module.

Sub Copy_SUM_Formula()
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("E5").Select
Selection.AutoFill Destination:=Range("E5:E10"), Type:=xlFillDefault
Range("E5:E10").Select
End Sub

Sub Copy_SUM_Formula()

Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"

Range("E5").Select
Selection.AutoFill Destination:=Range("E5:E10"), Type:=xlFillDefault
Range("E5:E10").Select

• Save the code and press F5 or the Run button to run the code after keeping the cursor in the module.

• You will be able to copy the SUM function formula using the VBA code.

Read More: How to Copy Formula to Entire Column in Excel

Things to Remember

• While writing or inserting the formula, remember to give proper cell reference, otherwise, you won’t get the desired values.
• Use the keyboard shortcuts as per instruction to get the required value.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to copy a SUM formula in Excel. Please share any further queries or recommendations with us in the comments section below.

Related Articles

• Creating and Copying Formula Using Relative References in Excel
• How to Copy Formula in Excel to Change One Cell Reference Only

<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel