• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

Customizing Excel XY Scatter Charts

Linear Regression with Excel Charts

Array Formulas in Excel for Engineering Calculations

Hyperbolic Curve Fitting in Excel

Colebrook Equation Solver in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in