Boost Your Excel Efficiency: 11 Formulas to Enhance Productivity

Master the following 11 Microsoft Excel Formulas and use them to increase your productivity and Excel efficiency.

Microsoft Excel is the most useful productivity tool in the Microsoft Office toolkit, both personally and professionally. The models of useful Excel functions and formulas are nearly endless and few people, if not one, can exhaust all of them. You can use Excel for all types of purposes, including managing data, creating budgets, extrapolating data, creating graphs and charts, planning, programming, and more. This also means Excel has specific formulas for particular functions and situations. 

So, in this gude, we take a look at 11 new and essential Excel formulas that will make you more productive. We’ll be brief and succinct.

Note: These functions and formulas are available in the latest Microsoft Excel versions including Excel 2021 and Microsoft 365.

Excel Functions To Increase Your Productivity

XLOOKUP Function

Utilize the XLOOKUP function to locate items within a table or range based on row values. If no match exists in your search, XLOOKUP will return the closest (approximate) match.

This is an upgrade compared to VLOOKUP.

For example, if you’re looking for revenue for a given company product for multiple years, unlike Excel VLOOKUP, adding a column using XLOOKUP won’t break the formula.

The formula: =XLOOKUP(A11,A2:A8,B2:H8)

LAMBDA Function

Use the Excel LAMBDA function to create custom, reusable functions in Excel and call them by a friendly name. This is one of Excel’s powerful functions that can help you create your own calculations.

Once you create, define, and name, a LAMBDA function, it is applicable anywhere in an Excel workbook. 

LAMBDA functions can be simple or complex, combining various Excel functions into one formula. 

In the example below, we intended to calculate income after tax deduction.

The formula is: = LAMBDA(x,y,x*(1-y))

CONCATENATE Function

Use the CONCATENATE function to join two or more Excel text strings into one string.

In the example below, I’m trying to combine the first and last names in one cell.

There are two ways to use it:

1. Insert double quotation marks separated by a space between them: ” “. For instance: =CONCATENATE(“Hello”, ” “, “FastSoftware!”).
2. Include a space following the Text argument. For instance: =CONCATENATE(“Hello “, “FastSoftware!”). Here, the string “Hello” is followed by an additional space.

The formula is: =CONCATENATE (A2,“ ”,B2)

COUNTIF Function (Excel formulas)

Use COUNTIF, an Excel statistical function, to count the number of Excel cells that meet a predefined criterion. For example, use it to count how many times a given city appears in your customer list.

The example below counts the times the companies have been the best in different years.

The formula is: =COUNTIF(A2:B22,A25)

FILTER Function (Excel formulas)

The Excel FILTER function allows you to filter a data range based on a defined criterion, such as a Boolean (True/False) array. Use it to filter a range with given criteria.

Notes: In Excel, an array can be a row of values, a combination of rows and columns of values, or a column of values. 

In the example below, we filtered to return only people who made more than $50,000 after we sorted their yearly salary in descending order.

The formula: =SORT(FILTER(A2:B11,B2:B11>E1),2,-1)

SORT Function (Excel formulas)

The SORT function helps you sort the contents of a range or array of data (text, numbers, dates, times, etc., in one or more columns. Take note that date sorting in Excel is a must-have skill!

For example, to Sort text.

1. Choose a cell within the column you wish to arrange.
2. Navigate to the Data tab, located in the Sort & Filter group.

• • To quickly sort in ascending order, click. (Sort A to Z). 
  • To quickly sort in descending order, click. (Sort Z to A).

In the example below: we first sorted the table by name (the first column), then we sorted by date and income, respectively by adding the 2 and 3.

The formula: =SORT(A2:C11,3)

IF Function (Excel formulas)

Use Excel’s IF function to make logical comparisons. The IF function is a popular Excel function that allows you to make logical comparisons – between a value and what you expect. 

An IF statement can have two results: True and False, or return predetermined values.

• The first result is if your comparison is True
• The second is if your comparison is False.

In our comparison, the logical comparison should return the word adult if the age is above 18. Otherwise, it will return a child.

The formula =IF(A2<18,”Adult”,”Child”)

SUMIF Function (Excel formulas)

Utilize the SUMIF function to calculate the sum of values within a range based on given criteria.

For example, if in a column with numbers, you want to sum only values larger than 5, the SUMIF formula can help you out. An example formula is =SUMIF(B2:B25,”>5″).

In the example below, we’re trying to figure out the number of sales for a given model of car.

The formula: =SUMIF(A2:A16,A18,B2:B16)

SUMIF Function Example: 

UPPER, LOWER, PROPER Functions

The Excel Upper function converts all letters to their respective uppercase (capital letters). Use the lower function to convert all letters to lowercase and the Proper function to capitalize the first letter in each word and make all other characters lowercase.

• =UPPE: Convert text to uppercase (capital letters)
• =LOWER: Converts text to lowercase
• =PROPER: Converts text to proper case (sentence case)

For each of the formulas here, we use the information from (A2:A10)

FLASH FILL Function (Excel formulas)

Use FLASH Fill to fill your data when it senses a pattern automatically.

For example, use Flash Fill to separate first and last names from a column or combine them from two different columns. It senses a pattern and completes the job.

Note: Flash Fill is only available in Microsoft Excel 2013 and later.

In the example below, we typed the first name, the last name, and the initials, and excel recognized the pattern and reproduced the desired result.

FLASH FILL Function Example: 

Convert to Stocks Function 

In 2019, Microsoft released a new Excel feature in Office 365 Excel that allows users to pull real-time stock prices into their spreadsheets. (Excel formulas)

The Convert to Stocks Function converts company names or ticker symbols to get current stock information.

It allows Excel formulas to pull data related to stocks, bonds, currency, or cryptocurrencies through Linked Data Types.

This provides financial metrics for companies of your choice. Read more here.

Convert to Stocks Function example (Excel formulas)