Home
> Tips and Tricks > Microsoft Excel replace formula with result

## Microsoft Excel replace formula with result

You can replace the formula in a cell with the actual result in MS Excel.

**Note: Make a backup of the file before you do the following steps ( this will replace the formula with the actual result )**

**Example 1: Full formula replace**

Lets say you have 3 columns in the sheet as below

Column 1: first name

Column 2: last name

Column 3: formula to concatenate first name and last name

Example data:

Column 1: John

Column 2: Doe

Column 3: =CONCATENATE(Column 1, ” “, Column 2)

**Steps (keyboard):**

- Click on Column 3 ( the formula bar will display the formula instead of the actual text )
- Press F2 ( for edit )
- Press F9 ( for actually calculating the formula )
- Hit “Enter”
- The cell will now have the actual value instead of the formula

**Steps (not so keyboard ):**

- For those who are mouse users do this
- Select the cell ( or cells ) –
**this works with multiple cells** - Click the “Copy” button
- Then
**expand**the “Paste” button – you will see paste options ( Paste Special ) - Choose Paste Special and then choose “Values”

**Example 2: Partial formula replace**

Lets say you want to replace only a part of the formula – Example Columns

Column 1: John

Column 2: Doe

Column 3: New York

Column 4: =CONCATENATE( CONCATENATE(Column 1, ” “, Column 2), “: “, Column 3 )

Column 4 will display John Doe: New York

Lets say you want the name to be constant but the city will dynamically change – then you can lock a part of the formula

**Steps:**- Click on Column 4 ( the formula bar will display the formula instead of the actual text )
- Press F2 ( for edit )
- Select CONCATENATE(Column 1, ” “, Column 2)
- Press F9 ( for actually calculating the formula )
- Hit “Enter”
- The cell will now have part of the formula calculated
- Try changing the city from New York to Atlanta

Advertisements

Categories: Tips and Tricks

Comments (0)
Trackbacks (0)
Leave a comment
Trackback