Efficiently Locating and Mastering Necessary Microsoft Excel Formulas: Tips & Tricks for Professionals

Quick Links

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.

WinUtilities Pro

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 | |

VSDC Pro Video Editor is a light professional non-linear video editing suite for creating a movie of any complexity. It supports the most popular video/audio formats and codecs, including 4K, HD and GoPro videos. Preconfigured profiles make the creation of videos for various multimedia and mobile devices absolutely hassle-free.

Key features:

• Import from any devices and cams, including GoPro and drones. All formats supported. Сurrently the only free video editor that allows users to export in a new H265/HEVC codec, something essential for those working with 4K and HD.
• Everything for hassle-free basic editing: cut, crop and merge files, add titles and favorite music
• Visual effects, advanced color correction and trendy Instagram-like filters
• All multimedia processing done from one app: video editing capabilities reinforced by a video converter, a screen capture, a video capture, a disc burner and a YouTube uploader
• Non-linear editing: edit several files with simultaneously
• Easy export to social networks: special profiles for YouTube, Facebook, Vimeo, Twitter and Instagram
• High quality export – no conversion quality loss, double export speed even of HD files due to hardware acceleration
• Stabilization tool will turn shaky or jittery footage into a more stable video automatically.
• Essential toolset for professional video editing: blending modes, Mask tool, advanced multiple-color Chroma Key

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.

ZoneAlarm Pro Antivirus + Firewall NextGen

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.

Win Blog

Efficiently Locating and Mastering Necessary Microsoft Excel Formulas: Tips & Tricks for Professionals