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

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

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

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

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

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

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

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

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

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

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.

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

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