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):

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

Steps (not so keyboard ):

  1. For those who are mouse users do this
  2. Select the cell ( or cells ) – this works with multiple cells 
  3. Click the “Copy” button
  4. Then expand the “Paste” button – you will see paste options ( Paste Special )
  5. 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:
  1. Click on Column 4 ( the formula bar will display the formula instead of the actual text )
  2. Press F2 ( for edit )
  3. Select CONCATENATE(Column 1, ” “, Column 2)
  4. Press F9 ( for actually calculating the formula )
  5. Hit “Enter”
  6. The cell will now have part of the formula calculated
  7. Try changing the city from New York to Atlanta
Advertisements
Categories: Tips and Tricks
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: