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
- XLOOKUP Function
- LAMBDA Function
- LAMBDA Function
- CONCATENATE Function
- COUNTIF Function
- FILTER Function
- SORT Function
- FILTER Function
- SORT Function
- IF Function
- SUMIF Function
- SUMIF Function
- UPPER, LOWER, PROPER Functions
- FLASH FILL Function
- FLASH FILL Function
- Convert to Stocks Function
- 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:
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:
- Add double quotation marks with a space between them:" ". For example: =CONCATENATE("Hello", " ", "SoftwareKeep!").
- 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.
- Select a cell in the column you want to sort.
- 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:
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)
UPPER, LOWER, PROPER Functions Example:
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.
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