Search Not Just Numbers

Tuesday 22 September 2015

Excel Tip: Make it easy to enter the arguments for a function

I have said this many times before, but one of the things I like most about working with Excel is that there is always something new to learn.

I don't just mean learning new features as they are added. I mean learning things that have always been there that you didn't know about!

A blog post on another Excel expert's blog taught me a really simple one the other day, that I'm not sure how I've missed!

Reading a post on Charley Kyd's ExelUser blog on Excel's Five Annuity Functions, he revealed a simple feature that I had never come across, but could be useful when entering any function in Excel.

Did you know that you can start typing any function and then press Ctrl-Shift-A for excel to fill it in with the names of the arguments, which you can then simply replace with the actual arguments.

For example, if you type:

=VLOOKUP

and press Ctrl-Shift-A, Excel puts the following into the formula bar:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You can then replace these argument names with the arguments for your particular scenario.

Alternatively, you could even leave it as is and name the cells and ranges that hold the arguments with these argument names as range names.

i.e. name the cell that contains the value you want to look up, lookup_value and the range that contains your lookup table table_array, etc.

I'm not sure I would use the range naming approach, not least because you could then only use the function once in your spreadsheet!

Using Ctrl-Shift-A to give me a skeleton to enter the function arguments does sound useful though.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

1 comment:

  1. Thank you for introducing me to this. I am not sure whether I will use this functionality on Excel's built in functions, but I can surely see myself using this for UDF's that I write in VBA.

    ReplyDelete