Bespoke Microsoft Office Development 
 Consultancy|

Excel

|Word|PowerPoint|Outlook|VBA|C#|Tools|Newsletter  
 Excel > Functions > Editing Functions< Previous | Next > 

 

How to Modify your Functions ?

 
 

There are several ways you can change the arguments passed to your functions.

 
 

Which method you use will depend on how complicated the formula is and your personal preference.

 
 

1) Using the Insert Function button to display the Function Arguments dialog box.

 
 

2) Using the shortcut key (Shift + F3) to display the Function Arguments dialog box.

 
 

3) Editing the formulas manually using the Formula Bar.

 
 

4) Editing the formulas manually using the shortcut key (F2).

 

 

Formulas with One Function

 
 

If your formula only contains one function you can display the Function Arguments dialog box by pressing the Insert Function button on the Formula Bar.

 
 

You can also use the shortcut key (Shift + F3) to display the Function Arguments dialog box.

 
 

This will allow you to change the necessary arguments. Press OK when you have finished your changes.

 
   

 

Formulas with More than One Function

 
 

Pressing the Insert Function button will display the Function Arguments dialog box for the first function in the formula.

 
 

In this case the function UPPER() is the first function in this formula.

 
   
 

When a formula contains more than one function you need to place your cursor inside the function you want to edit before you press the Insert Function button.

 
 

This can be done either in the Formula bar or in the actual cell.

 
 

In this case we have moved the cursor so it inside the LEFT() function.

 
   

 

Editing Formulas and Functions Manually

 
 

Sometimes the quickest and easiest way to modify a function (or text) is to edit it manually.

 
 

If you are not too familiar with the function and the required arguments then it is best to use the Function Arguments dialog box.

 
 

You can edit your formulas manually either by using the Formula bar or by editing within the cell itself.

 
 

First lets show you how you can edit formulas using the Formula bar.

 
 

Select the cell containing your formula and move your cursor to the Formula bar.

 
 

This allows you to highlight, edit and type your changes directly without having the use the Function Arguments dialog box.

 
 

A tooltip will automatically indicate which arguments are required for the function you are currently editing.

 
   

 

It is also possible to edit your formulas (as well as text) directly in the cell.

 
 

This can be done by pressing F2 when the corresponding cell is selected and positioning the cursor over the function.

 
 

If this does not happen then check your (Tools > Options)(Edit tab, "Edit Directly in Cell") option.

 
   

 

Things to Remember

 
 

  • When you edit a cell that contains a formula Excel will automatically colour code any referenced cells.
  •  
     

  • The colour used to identify the range of cells being referenced corresponds to the colour that appears in the formula.
  •  
     

  • Pressing F2 when a cell is selected should allow you to edit the cell contents. If this does not happen then check your (Tools > Options)(Edit tab, "Edit Directly in Cell") option.
  •  
     

  • You can drag the fill handles to increase or reduce the referenced cells.
  •  
     

  • You can also click on the border of the outlined range and move the whole outline to a new range.
  •  

     © Better Solutions Limited 04-Apr-2014< Previous | Top | Next >