Excel Efficiency: 11 Excel Formulas To Increase Your Productivity

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

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 Excel versions including Excel 2021 and Microsoft 365. 

Table of Contents 

  1. XLOOKUP Function
  2. LAMBDA Function
  3. LAMBDA Function
  4. CONCATENATE Function
  5. COUNTIF Function
  6. FILTER Function
  7. SORT Function
  8. FILTER Function
  9. SORT Function
  10. IF Function
  11. SUMIF Function
  12. SUMIF Function
  13. UPPER, LOWER, PROPER Functions
  14. FLASH FILL Function
  15. FLASH FILL Function
  16. Convert to Stocks Function 
  17. Watch: #quicktip 2 Excel Formulas To Increase Your Productivity

Excel Functions To Increase Your Productivity

XLOOKUP Function

Use the XLOOKUP function to find things in a table or range by row. 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)

XLOOKUP Function Example:



LAMBDA Function

Use 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 and, define and name, a LAMBDA function 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: = LAMBDA(x,y,x*(1-y))

LAMBDA Function Example:

LAMBDA 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. Add double quotation marks with a space between them:" ". For example: =CONCATENATE("Hello", " ", "SoftwareKeep!").
  2. Add a space after the Text argument. For example: =CONCATENATE("Hello ", "SoftwareKeep!"). In this example, the string "Hello" has an extra space.

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

CONCATENATE Function Example: 


COUNTIF Function

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: =COUNTIF(A2:B22,A25)

COUNTIF Function Example: 


Read: How To Use “If Cell Contains” Formulas in Excel

FILTER Function

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)

FILTER Function Example: 


SORT Function

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.

For example, to Sort text.

  1. Select a cell in the column you want to sort.
  2. On the Data tab, in the Sort & Filter group, do one of the following: 
    • 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)

SORT Function Example: 


IF Function

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

IF Function Example:


Learn: How to Separate First and Last Name in Excel

SUMIF Function

Use the SUMIF function to sum the values in a range that meets specified 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: 



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)

UPPER, LOWER, PROPER Functions Example: 



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

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

It allows Excel 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 


Watch: #quicktip 2 Excel Formulas To Increase Your Productivity

Final Words

We hope these Excel Functions will be of great help to you. Excel can make you highly productive and improve your function levels.

We're glad you've read the article up to here :) Thank you :)

Looking for more tips? Check out our other guides in our Blog or visit our Help Center for a wealth of information on how to troubleshoot various issues.

Sign up for our newsletter and access our blog posts, promotions, and discount codes early. Plus, you'll be the first to know about our latest guides, deals, and other exciting updates!

Need more help?

» 13 Tips To Master Excel Without Breaking a Sweat
» Excel Tutorial for Beginners: Tips You Need to Know
» How to Manage Your Finances With Microsoft Excel
» How Do You Insert Line Breaks in Excel?
» How To Make a Pareto Chart in Excel
» excel scroll lock