When you write a new formula in Microsoft Excel, half the battle is finding the correct function to use. Luckily, Excel provides a way for you to quickly locate the function you need for your situation.
Access the Insert Function Tool
Open your spreadsheet in Excel and select a cell. If you plan to insert the function once you find it, selecting the cell gives you a head start. You can then open the Insert Function feature one of two ways.
- Click the Insert Function icon (fx) on the left side of the formula bar.
- Go to the Formulas tab and click “Insert Function” on the left side of the ribbon.
How to Find a Function in Excel
When the Insert Function window opens, you’ll see a search option along with a drop-down box. This gives you two easy methods for finding the function you want.
To use the search, enter a keyword or phrase into the Search for a Function box at the top of the window and click “Go.” For example, if you’re looking for a function that will help you with time and date-related calculations, trying searching for “time.”
The drop-down list will also help you quickly find a useful function. Click it, next to the words “Or Select a Category,” and pick a category. You’ll see common options like Financial, Logical, Text, and more. By selecting a category you’ll see all available functions within it.
One other category to make note of is Most Recently Used. As the name implies, you’ll find those functions you’ve used lately here. This can be helpful if you used a function say, last week, but can’t remember what it was. Check this category and the function might pop out at you.
In most cases, whether you search or select a category, this simply narrows down the choices for you. So the next step in finding the function you need is checking out the descriptions. Click a function in the list and you’ll see its description and syntax at the bottom of the window.
For example, you might need a quartile function. You can see that Excel offers a few different options. By selecting each and viewing their descriptions, you should be able to see the correct one for you.
If once you narrow down the function choices and view the descriptions, you’re still unsure if it’s the right function for you, you can get additional help. Select the function in the list and click “Help on This Function” on the lower-left corner of the window.
This takes you to the Microsoft Support website for the function which provides additional information and example uses.
|Mastering Excel Functions|
|Functions||AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH
· MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR
|Types||Basic · Budgeting · Data Entry · Logical · Text · Time and Date|
|Explained||Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas|
Use a Function You Find
When you locate the function you want to use, be sure you have the cell selected where you want to insert it. Then, either double-click the function in the list or select it and click “OK.”
You’ll then see the Function Arguments tool open for you to set up the formula using the function.
At the top, enter the data set, number, array, or whatever the function calls for.
At the bottom, you’ll notice that brief description of the function once more, but you’ll also see additional details and the result of the final formula.
Let’s look at a simple example. Here, we selected the SUM function. In the Function Arguments window, we entered the cell range in the Number1 box. You can see to the right of the box, the values in those cells and you can view the result of the formula in two spots in the window.
When you finish adding all details for the formula, click “OK” and the formula will pop into your cell with its results.
Try Formula AutoComplete
One other way to find a function in Excel is by using Formula AutoComplete. This is useful if you know the function you want but aren’t sure of the variation you need.
When you type an equal sign (=) into a cell and start entering the first letters of a function, you’ll see a drop-down list of matches. You can click each option in the list to see its description display next to it. Then to use the function, double-click it and you’ll see the arguments it needs.
For example, we know we need a COUNT function, but aren’t sure which one. We enter
=COUNT and see a drop-down list of options. The description for COUNTIF lets us know this is the one we need.
We double-click “COUNTIF” in the list and then see the arguments needed for the formula. This allows us to simply enter what’s asked for and complete the formula.
Working with functions and formulas in Excel can be a little intimidating if you don’t do it often. But with helpful features like these, finding and using the function you need can be a lot easier.
- › Functions vs. Formulas in Microsoft Excel: What’s the Difference?
- › How to Find the Day of the Week From a Date in Microsoft Excel
- › 11 Little-Known Excel Functions That Are Very Useful
- › How to Use the SUBTOTAL Function in Microsoft Excel
- › The Basics of Structuring Formulas in Microsoft Excel
- › How to Combine Data From Spreadsheets in Microsoft Excel
- › How to Find a Value’s Position With MATCH in Microsoft Excel
- › Your Google TV Will Now Show You More Ads