Combine UDF’s and Named Cells to Boost Efficiency in Excel

Two of my favorite Excel features are named ranges and user defined functions. It also just so happens that these two features can be used together to really boost your efficiency.

User-defined functions can be created to perform calculations you find yourself doing repeatedly. You’re free to do really anything you want to do in these functions, including creating arguments as you see appropriate.

As an example, I’ve created a very simple user-defined function to calculate force by inputting the two arguments: mass and acceleration.

Of course, to use the UDF in Excel you just type the name of the function.

Excel recognizes the name of UDF’s you create and suggests them in the Intellisense popup below the cell as you are typing.

You can even press the Tab key to auto-complete it, just like with Excel’s built-in functions.

Unfortunately, though, for UDF’s the Intellisense popup doesn’t provide any guidance about what arguments are required to calculate the function.

If you’ve forgotten the arguments, going back in to the VBA editor to look them up is a pain.

Lucky for us, there is a much easier way: use the shortcut Ctrl+Shift+A to automatically fill them.

Then, just double-click the argument to select it, and select the cell containing the appropriate value.

This shortcut even works for built-in Excel functions too.

Slick, huh?

But it gets even better.

With a little bit of planning ahead we can use Named Cells with Ctrl+Shift+A to automatically populate the UDF arguments and save a ton of time.

Before entering the UDF into a cell on the worksheet, first define names for the input cells that match the UDF argument names.

Then, when you enter the UDF and type Ctrl+Shift+A to populate the function, the arguments will match the named cells and you don’t have to select anything!

A little bit of planning here can save you a ton of time, especially when you have a UDF with many arguments, or multiple UDF’s that have similar arguments.