Choose the best function with this hidden tool

Which Excel Function Should You Use?

By Marie McCooey

Have you had this dilemma?

With over 400 functions in the latest version of Excel, anyone could be confused.

How do you find the best one for the task?

Do you wish there was an assistant to guide you?

You’re in luck.

I’m going to share a secret tool that I use when I don’t know which function to use.

What is this powerful tool?

It’s the Insert Function feature.

How can you reveal this hidden gem?

Let’s take a look at an example, to understand how this useful feature works.

In this example, the worksheet displays the start and end date for each project.

You need to calculate the number of work days between the two dates excluding holidays, but don’t have a clue which function to use.

In this worksheet, you entered the start and end date for each project.

And, now you need to calculate the number of work days between the two dates excluding holidays.

But you’re not sure which function to use.

Function to calculate number of work days

That’s where the Insert Function feature comes to the rescue.

Click the cell where you’d like the formula’s answer to display.

Choose one of the following options to activate the Insert Function feature.

1. Click the Insert Function button at the left end of the formula bar.

Insert Function button

or

2. Click the Insert Function button at the left of the ribbon on the Formulas tab.

Insert Function on ribbon

or

3. Press SHIFT + F3.

The Insert Function feature displays.

Insert Function dialog box

3 simple ways to use Insert Function to find the right function

  1. If you don’t know the function name, type a word or brief description of what you are trying to accomplish in the Search box and click Go.
    Excel lists functions which match the description.
    Click each function name to display a description of the function.
    Insert function - unknown function name
  2. If you know the function name you are looking for, type the function name in the Search box and click Go.
    The function displays at the top of the Select a function list and a brief description of the function and its structure displays.
    Insert function name known
  3. Click the Select a category drop-down arrow to display a list of categories.

Select a category

Choose one of the following category options:

  • Most Recently Used to view a list of the last 10 functions used
  • All for a list of all functions in alphabetical order
  • A Category name to see an alphabetical list of functions in the category

Browse through the categories, selecting a function to read its description and view its arguments.

When you’ve found the function that will provide the answer you’re looking for, in this case the NETWORKDAYS function, click OK to select.

The Function Arguments dialog box sample shown below displays prompts for each required and optional argument.

Required arguments, like the Start and End dates, are bold to indicate they are required.

Optional arguments, like the Holidays argument, is non-bold to indicate it is optional.

Function arguments populated

After you’ve entered the Project Start and End dates and the range of holidays in this example, Excel calculates and displays the result in the Function Arguments dialog box.

Click OK to close the Function Arguments dialog box and view the function result in the spreadsheet.

Unlock the power of Insert Function today

The next time you’re creating a formula and struggling to find the right function, use the Insert Function feature to discover your options.

Leave a Reply

Your email address will not be published. Required fields are marked *