In this article, we will learn how to add number to existing cell value in Excel. Often we see that we need to add a particular number to all the cells in a column to increase the value. There are multiple ways to do so in Excel. Throughout the article, we will describe these ways one by one with examples for better understanding.
How to Add Number to Existing Cell Value in Excel: 3 Easy Methods
Adding a number to an existing cell value can be done in basically 3 ways. For our methods to describe, we will consider this real-life example dataset which shows the Employee ID, Salary, and Bonus of any organization. The ways to add a number to an existing cell value are given below.
1. Using Simple Formula
We can simply use the Excel formula bar to add a number to any existing cell value. But the catch here is that we will need to create a new column of these updated values. To do so we need to follow these steps. In this step, we will increase the salary of every employee by 100$.
Steps:
- At first, we need to select the cell where we will keep the updated value. In our case, the cell is E5 where we will keep the updated values C5. The entire column called New Salary will keep the updated values.
- Then we will write the following formula in cell E4.
=C5+100
- Now, press Enter.
- As a result, we will see E5 is containing 5100$ cell value which is an increment of 100$ of C5.
- Next, we will select E5 and drag the Fill Handle till E10 to complete the whole series.
- In the end, we will get the New Salary column with updated values.
Read More: How to Add Digits to a Number in Excel
2. Use of Paste Special Option
This is the most common method in Excel to add a number to an existing cell value. It is also one of the most time-saving ones. The steps are given below. Here also we are following the same example that we want to add 100$ to everyone’s salary.
Steps:
- In the beginning, we need to write 100 somewhere on the worksheet. Here in our case, we have written in F5Â cell.
- After that, we need to press Ctrl + C and select from E5 to E10 where we want to add the number.
- Then we need to select Paste Special from the paste options in the Home tab in the Ribbon.
- Next, a dialog box will appear named Paste Special. We need to select Add and then press Enter.
- Finally, we will see all the values of the Salary column is updated and increased by 100$.
3. Applying VBA Code
In this method, we will use VBA code to add a number to the existing cell value. The steps are given below.
Steps:
- Firstly we need to go to the Developer tab in the Ribbon and select Visual Basic. We can also do the same thing by pressing Alt+F11.
- Secondly in that window, we need to select Insert and click on Module.
- Thirdly copy and paste the following code in the text area under the General section. We can change the number we want to add by manipulating Num = 100 code.
Sub add_n()
Dim w_s As Worksheet
Dim r_s As Range
Dim rng As Range
Dim number As Double
Dim m As Long
Dim n As Long
Dim l_a As Long
Dim l_rs As Long
Dim l_c As Long
Dim ar() As Variant
Set r_s = Selection
number = 100
For Each rng In r_s.Areas
If rng.Count = 1 Then
rng = rng + number
Else
l_rs = rng.Rows.Count
l_c = rng.Columns.Count
ar = rng
For m = 1 To l_rs
For n = 1 To l_c
ar(m, n) = ar(m, n) + number
Next n
Next m
rng.Value = ar
End If
Next rng
End Sub
- Furthermore, we need to save the Excel file as a Macro-enabled Excel file or with XLSM extension by pressing Ctrl+S and then choosing XLSM as the file type.
- Again we need to select the cell E5-E10 where we want to add the number and then click on Macros in the Developer tab.
- A dialog box named Macro will appear. We need to select Add-num and then click on Run.
- Finally, we will see all the values of the Salary column is updated and increased by 100$.
Add Text to Existing Cell Value in Excel
We can also add text to any cell. Let’s say we want to Add the alphabet A with all the Employee IDs and then update them under the New ID column. We can do it using the CONCATENATE function. The steps to do so are below.
Steps:
- First, we need to select cell E5.
- Second, we need to click on the Function icon.
- Third, a dialog box named Insert Function will appear. Here we will search for the CONCATENATE function and press Enter to insert it.
- Next, we will select B5 for Text1.
- For Text2 we will write A and then press Enter.
- As a result, we will see 1A in the E5Â cell.
- We will fill other cells by dragging the Fill Handle.
Download Practice Workbook
You can download the practice workbook from here.
Things to Remember
- Paste Special is the most suitable feature to add numbers to a whole column or selection.
- VBA code and Paste Special can work on multiple columns.
- The Developer tab needs to be activated while applying the VBA.
Conclusion
Adding numbers to an existing cell value is a useful feature to update any dataset. The article was all about it. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.