Excel Formulas You Might Not Know About Yet

Featured image by Pexels via Pixabay

Despite the time people spend using Excel, according to Acuity Training’s research, fewer than half have ever had any formal training in how to use Excel. Even fewer know how to use Excel formulas to their advantage.

Nonetheless, Microsoft Excel is one of the tools that many entrepreneurs use day in and day out to manage data in their business. With the help of Acuity Training we have shortlisted five new Excel formulas all entrepreneurs should know.

1. XLOOKUP Is an Excel Formula to Help You Find Data

Entrepreneurs often spend time summarizing their data. Excel’s lookup functions are well-suited for this purpose.

Recently, Excel announced a new lookup function they call XLOOKUP. This Excel formula is more flexible than the previous lookup functions such as VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP is only available in Excel 365 and later versions.

To use this formula, type =XLOOKUP. Then choose from among the following functions:

Lookup_value: This is the target value you want to look up from the data
Lookup_array: This is the range of values from which you want the formula to find the target value
Return_array: This is the range of returned values if the target value is found
If_not_found: Optional. This refers to the returned value if Excel does not find the target value
Match_mode: Optional. In this case, 0 = exact match (by default); -1 = exact or next smallest match; 1 = exact or next larger match; 2 = wildcard entries
Search_mode: Optional. 1 = start lookup from first value in the range (default); -1 = start lookup from the last value in the range

Refer to the example below to gain a basic understanding of how the Excel formula XLOOKUP works.

2. MAXIFS and MINIFS Formulas Will Find Values Based on Your Criteria

MAXIFS and MINIFS are Excel formulas you can use to return a value based on multiple criteria.

You can use the logical operators (<, >, =, <>) to define your criteria. Both functions can work on dates, texts, numbers, and other data types.

The MAXIFS Excel formula will return the maximum number that supports the criteria you define. The MINIFS will return the minimum number that supports the criteria you define. Both functions have a similar formula structure. For example:

=MAXIFS(max_range, criteria_range1, criteria1,…)

These are the options that work with this Excel formula:

Max_range: This is the range of values from which the maximum value will be returned
Criteria_range1: This is the range for the first condition you want the formula to use
Criteria1: This is the actual condition to evaluate the criteria range

Refer to the examples below, where the operator has used both functions to quickly identify the maximum and minimum Total Sales for all the “Departmental Stores” located in the “North” region.

3. Dynamic Array Is an Important New Excel Update

Dynamic array is one the most important updates that Microsoft Excel has implemented to Excel’s formula engine. It works best with formulas that return multiple values and means that there is no need to drag your formula to the last cell of the range.

By defining the range in the formula, the dynamic arrays automatically spill the output values throughout the defined range.

In the below example, we used the LEFT function to separate the Country Code. By defining the range in the formula, we got the returned value for the whole range at once.

Please note that dynamic array formulas are only available as native formulas in Excel 365 and later versions. Also, dynamic array formulas are a big topic so if you’d like to learn more go to the Acuity Training website and search for “dynamic array formula.”

4. Use the Excel Co-Authoring Formula to Collaborate

Entrepreneurs often need to work in a collaborative environment. Co-authoring in Excel streamlines the process of sharing a workbook with other team members. The co-authoring feature rolled out on Excel 365 and later versions.

With co-authoring, collaboration with other team members becomes flexible and easy. For example, you can share your workbook with other team members and allow them to work on it without leaving the Excel window.

To do this, follow these simple steps:

First, you need to make sure that the workbook is uploaded on OneDrive or Microsoft SharePoint.

After uploading the workbook onto the cloud, then click on the Share button at the top right corner of the Excel window.

At this point, if you haven’t already uploaded the workbook to the cloud, a prompt will appear to tell you to do that.

Next, a new window will appear asking for the email addresses of the members with whom you want to share the workbook.

5. Work More Efficiently with Excel Shortcut Keys

There is a huge range of shortcut keys that Excel offers to speed up your work. These shortcut keys make you super-efficient once you get a grip on them.

Below are some of the most frequently used shortcut keys that you can include in your routine while working on Excel.

There are several other shortcut keys that are quite helpful in getting the task done quickly. By learning and practicing them, you can surely increase the speed that you work in Excel. For full details on Excel shortcuts see the article on the Acuity Training site which lists more Excel shortcuts.

Final Thoughts

Entrepreneurs deal with large volumes of data but learning a few Excel skills can save a lot of time. Hopefully this article will inspire you to keep improving your skills.